Microsoft SQL Server Analysis Services Tabular

This database is currently one of my favorite databases to store data to answer analytical questions. A certain aspect that describes this database from a technical point of view is: SSAS Tabular (a shorter name) is a column-oriented in-memory database.

From a user perspective, it's valid to say that it's very close to the user, the data analyst, due to a great variety of frontend tools, that empower the user to analyse large amounts of data in a self-service Business Intelligence approach. Some of these tools are MSFT Excel, MSFT Power BI, and Tableau, just to name a few.

 

What makes this database special are its different flavors, it's known as Power Pivot as local variant that can be used from within Excel workbooks, more or less the same version is used by Power BI Desktop, where it fuels the analytical reports. in comparison to Power Pivot one has to acknowledge, that the Power BI version is more up to date, due to the focus of Microsoft on their self-service Business Intelligence offering, but this is also due to a more frequent update cadence of Power BI. The same version that runs in Power BI Desktop, can also

be used by the cloud based Power BI Service.

SSAS Tabular can be used to analyze large datasets with billions of rows using the on-premises variant available with MS SQL Server or as fully managed cloud service. What makes the cloud based variant of SSAS Tabular very special is the ability to scale the service easily to the demands of different workloads.

 

Here I will describe different modeling scenarios to provide optimal analytical performance and user experience, but also topics that have to be considered in more complex deployment environments and large datasets.

One of the most powerful features of SSAS Tabular is the query language DAX. This will be found under the Process menu in its own chapter.

SSAS Tabular Data Modeling

One of the most important aspects in finding answers to my analytical questions is to get my Tabular data model right.

 

Sometimes it seems to me that most of the time and effort is spent on creating powerful DAX statements. Much is already said and written about DAX. Sometimes I'm thinking that this power is used to much, reviewing some DAX code from customers or friends from the great community community.powerbi.com. And that this power is used to compensate an insufficient data model.

At a first glance that SSAS Tabular model seems simple: just relate tables. In the last months I have seen a lot of complex DAX statements to create complex date related calculations, but without a calendar table. Just one fact table!

 

Me: "Why did you not use a Calendar table?"

Him: "Table relations, like in RDBMS? That's too complicated, can't we solve this in DAX?"

 

This experience made me think, and I started to prepare a little slide deck, that helps to evangelize for a more sophisticated data model (at least to use a dedicated date table). For this reason I'm going to write a little bit about my personal experiences and the reasoning behind some design decisions.

 

Maybe you will find me talking about some concepts that are not familiar to you, if this will be the case I recommend this great reading "Analyzing Data with Microsoft Power BI and Power Pivot for Excel." by Marco Russo and Alberto Ferrari. This books lays the foundation for successful data modeling using SSAS Tabular, Power BI and Power Pivot.

 

My personal experience is: An appropriate data model is the foundation for any analytical data processing. No matter if I'm going to write a DAX statement or creating an R predictive model, or analyzing twitter feeds.