Analysis Services (SSAS)

Multi-language support for SSAS

April 8, 2021 by Dinesh Asanka

Introduction

After discussing many features in SQL Server Analysis Services (SSAS) in order to carry out much richer analytics activities, we are going to discuss another feature in SSAS named Multi-language support for SSAS. In a previous article, we discussed how to create SSAS OLAP Cubes and how to access the OLAP cube using Excel. Further, we discussed how to include hierarchies in SSAS in order to improve the data analysis capabilities. In addition to those features, we discussed how to create perspectives in OLAP Cubes and how to perform management activities in SSAS.

Building SSAS tabular models using Biml

March 30, 2021 by Hadi Fadlallah

This article will explain how to use the Business Intelligence Markup Language to create an SSAS tabular model.

Building SSAS OLAP cubes using Biml

March 16, 2021 by Hadi Fadlallah

In this article, we will explain how to use the Business Intelligence Markup Language (Biml) to build SSAS OLAP cubes, noting that this article is the ninth article in the Learn Biml series.

Implementing an SSAS Tabular Model for Data Analytics

August 4, 2020 by Dinesh Asanka

Introduction

Having discussed different aspects of MDM SSAS cubes, we will look at the Microsoft recommended OLAP tool, SSAS Tabular Models for data analytics. There are a few advantages of using Tabular Models over MDM that are listed below.

Enhancing Data Analytics with SSAS Dimension Hierarchies

July 22, 2020 by Dinesh Asanka

Introduction

This article will discuss how SSAS Dimension Hierarchies can be used to analyze data much efficiently. If you are a data analyst, you want to start the analysis with a higher hierarchy. Then navigate the narrow attributes when required. For example, it will be better to start with analyzing revenue by year. If you need to analyze further into the data, you can choose the needed year and expand the Quarter -> Month, respectively. Let us see how we can create SSAS Dimension Hierarchies in OLAP Cubes to suit different requirements.

Improve readability with SSAS Perspectives

July 9, 2020 by Dinesh Asanka

Introduction

In this article, we will be looking at a feature of SQL Server Analysis Service (SSAS) OLAP Cube that is SSAS Perspectives. We discussed creating SSAS OLAP Cubes in a previous article: OLAP Cubes in SQL Server. In an SSAS OLAP cube, there can be a large number of measures, dimensions and dimension attributes. The following screenshot is the star schema for the selected example that was created from the AdventureWorksDW sample database that can be visible at Data Source View.

SSAS Database Management

June 1, 2020 by Dinesh Asanka

There are different aspects of SSAS Database Management that we will be looked into detail in this article. We will be looking at backup, processing, and synchronization.

Deploy tabular databases in SSAS on AWS RDS SQL Server

June 1, 2020 by Rajendra Gupta

In this article, we are going to explore Analysis Service for AWS RDS SQL Server in detail.

How to deploy SSAS cubes

April 9, 2020 by Aveek Das

In this article, I’m going to discuss the different ways in which we can deploy SSAS cubes. SSAS or SQL Server Analysis Services is an enterprise-grade analytical data engine which helps business to create semantic models that can be used for reporting and making better decisions. The semantic layer created by SSAS can be accessed by client tools like Power BI, or Excel and reports can be prepared from thereon. SSAS comes with a few different versions, which are as follows.

Warnings in SSAS Cubes

April 3, 2020 by Aveek Das

In this article, I’m going to list out some of the most common warnings we encounter while developing SSAS Cubes and the reason for those warnings. As you are aware, SSAS cubes are developed using the SQL Server Data Tools (SSDT) for Analysis Services (Visual Studio), all the examples will be taken from Visual Studio 2017 only.

Introduction to Many-to-Many Relationships in SSAS

March 26, 2020 by Aveek Das

In this article, I’m going to explain what many-to-many relationships in SSAS are and how to implement the same in a SQL Server Analysis Services (SSAS) project. For the sake of this article, we will only consider the Multidimensional Cube and not the Tabular. Also, this article assumes that you have some fair knowledge of building SSAS cubes from scratch.

Manipulating SSAS OLAP cubes using SSIS

February 12, 2020 by Hadi Fadlallah

There are many operations that we may run while working with OLAP cubes such as cube and partitions processing, executing administrative tasks, performing analysis and more… Three query languages that are used to run these operations: multidimensional expressions (MDX), data mining expressions (DMX) and XML for Analysis (XMLA). We can mainly write and execute these queries in SQL Server Management Studio, but many times we need to implement them within a flow that executes other tasks over other services (database engine, file system…)

OLAP Cubes in SQL Server

February 6, 2020 by Dinesh Asanka

An Online Analytical Process (OLAP) cubes are used as an analytical tool. An OLAP Cube is a multi-dimensional database that is optimized for reporting purposes.

Association Rule Mining in SQL Server

January 28, 2020 by Dinesh Asanka

Association Rule Mining in SQL Server is the next article in our data mining article series in which we have discussed Naïve Bayes, Decision Trees, and Time Series until now. Association Rule Mining, also known as Market Basket Analysis, mainly because Association Mining is used to find out the items which are bought together by the customers during their shopping.

Linking relational databases with OLAP cubes

January 10, 2020 by Hadi Fadlallah

One of the most asked questions is how to link an OLAP cube with a relational database, or in another way how to join the result of an MDX query with a table stored in a relational database.

Microsoft Time Series in SQL Server

December 12, 2019 by Dinesh Asanka

The next topic in our Data Mining series is the popular algorithm, Time Series. Since business users want to forecast values for areas like production, sales, profit, etc., with a time parameter, Time Series has become an important data mining tool. It essentially allows analyzing the past behavior of a variable over time in order to predict its future behavior.

Microsoft Decision Trees in SQL Server

September 12, 2019 by Dinesh Asanka

Decision trees, one of the very popular data mining algorithm which is the next topic in our Data Mining series. In the previous article Introduction to SQL Server Data Mining, we discussed what data mining is and how to set up the data mining environment in SQL Server. Then in the next article, Microsoft Naïve Bayes algorithm was discussed. In this Article, Microsoft Decision Trees are discussed with examples. The Microsoft Decision Trees algorithm is a classification and regression algorithm that works well for predictive modeling. The algorithm supports the prediction of both discrete and continuous attributes.

An efficient approach to process a SSAS multidimensional OLAP cube

August 15, 2019 by Hadi Fadlallah

Introduction

While building and deploying an SSAS OLAP cube, there are two processing orders that you can choose from when you create a process operation:

Naive Bayes Prediction in SQL Server

August 2, 2019 by Dinesh Asanka

In this article, we will walk through Microsoft Naive Bayes algorithm in SQL Server.

Introduction to SQL Server Data Mining

July 23, 2019 by Dinesh Asanka

Prediction, is it a new thing for you? You won’t believe you are predicting from the bed to the office and to back to the bed. Just imagine, you have a meeting at 9 AM at the office. If you are using public transport, you need to predict at what time you have to leave so that you can reach the office for the meeting on time. Time may vary by considering the time and the day of the week, and the traffic condition etc. Before you leave your home, you might predict whether it will rain today and you might want to take an umbrella or necessary clothes with you. If you are using your vehicle then the prediction time would be different. If so, you don’t need to worry about the rain but you need to consider the fuel level you need to have to reach to the office. By looking at this simple example, you will understand how critical it is to predict and you understand that all these predictions are done with your experience but not by any scientific method.

SSAS interview questions for Multidimensional models

May 13, 2019 by Daniel Calbimonte

Introduction

SSAS interview questions is a new article for people looking for SSAS jobs.

In our previous article, we talked about SSIS interview questions. In this new article, we will talk about the SSAS interview questions to be prepared for an interview for a job using SQL Server Analysis Services.

Dynamic Partitioning in Azure Analysis Services (tabular)

August 6, 2018 by Istvan Martinka

Objective

The real-life requirement

Disclaimer: I assume dear Reader, that you are more than familiar with the general concept of partitioning and star schema modeling. The intended audience is people who used to be called BI developers in the past (with a good amount of experience), but they have all sorts of different titles nowadays that I can’t keep up with… I won’t provide a full Visual Studio solution that you can download and just run without any changes or configuration, but I will give you code can be used after parameterizing according to your own environment.

Top 4 options for fixing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error in SQL Server 2016

June 26, 2018 by Sifiso Ndlovu

I’ve always been in favor of an orthodox strategy when it comes to applying SQL Server updates which often goes like:

Instead of installing SQL Server Cumulative Updates, wait for release of service packs
When a service pack is released, install it in phases starting from the non-production environment (i.e. DEV, UAT) to eventually roll it out on production

Time Intelligence in Analysis Services (SSAS) Tabular Models

March 20, 2018 by Thomas LeBlanc

In the analytical world, time is an important slicer. The ability to view data over time helps analyze measures in actionable steps. An Analysis Service (SSAS) database can help accomplish this important step. Business users are going to want the ability to see measures a like year to date as well as the period over period comparisons. SSAS enables Time Intelligence with 2 features: the date table and DAX functions.

Automate documentation of SQL Server Analysis Server Tabular Model

March 13, 2018 by Divya Agrawal

There has been an ever-growing discomfort on documenting things especially when it’s very dynamic in nature and I was at one time undergoing the same. We have been developing SQL Server Analysis Tabular Model’s, which were quite a many in numbers and documenting 50 or 60 models manually is a big effort. In addition, development always demands change or enhancements that incurs changes on the documentation that is a continuous process. Adding the changes on the documentation is a time consuming process and sometimes loses track or remains inconsistent with the code built which is a critical issue when the system is in production for years.

I decided to automate the documentation of the SSAS model, to ease the manual effort here as well as save time. In addition, help the end users to see and review the latest change on the models quickly without even bothering the development team.