Implementing an SSAS Tabular Model for Data Analytics

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.

Column Store -> SSAS tabular uses the xVelocity engine which is a column-based engine. Due to the column-based nature, it uses better compression. This means that Tabular models have disk space advantage over MDM cubes
In-Memory -> Default option for Tabular data models is in-memory that will increase the usability performance
Easy Usage –> Unlike MDM, to design a model in tabular is very much easier. If you know relational database design patterns, you can become a good tabular model designer. Apart from the easy usage, tabular uses DAX query which is very much equivalent to Excel expression. Therefore, DAX expressions are much easier than MDX that is used in MDM cube
Cloud-Ready -> Azure Analysis service has only the tabular option as a PaaS option, not in MDM. This means that when are developing tabular models, it is easier to deploy them to the azure. If you have MDM cubes, either you need to have a virtual box with SQL Server Analysis Service instance or else you need to re-write the MDM cube to Tabular data models

Installation

It is important to note that, you need to have different instances for MDM and Tabular. This means at the time of installation, you need to decide what type of SQL Server Analysis Server you need. Further, after installing the SSAS service, you cannot convert to another service. In addition to the server, you cannot convert the MDM to tabular and vice versa after installing the SQL Server Analysis Service.

Modelling

Let us see how to model using the SSAS Tabular Model with the sample database, AdventureworksDW. In the SQL Server Data tool, let us create an Analysis Service Tabular Project.

When the tabular is created, you need to provide a tabular name, workspace server and the compatible level that you are willing to deploy this tabular model to. Following is the Tabular Model Explorer that you will see after the creation of the Tabular Model.

Let us first create the Data Sources that will make a connection to the SQL Server database AdventureWorksDW.

The following screenshot shows the possible data sources for the Tabular models which covers database such as SQL Server, Oracle, Microsoft Azure, DB2 etc.

After choosing the database, next, we need to provide the server name and database as shown below.

Next, let us choose the following tables and rename the tables with some friendly names as shown in the below screenshot.

We have mainly chosen FactInternetSales and its related table. This means we need to choose FactInternetSales, DimCurrency, DimCustomer, DimDate, DimProduct, DimProductCategory, DimProductSubcategory, DimPromotion, DimSalesTerritory tables.

We do not need all the columns for the model. Therefore, we need to remove the unnecessary column by clicking the Preview & Filter button.

You can remove the columns by un-ticking the column name.

We have removed some audit dates and different language columns. Then data will be imported to the project. If you missed removing any columns, you can remove the columns after importing them as well in the later stage.

The following is the SSAS Tabular model data structure.

If there are foreign key relationships available for the tables, those relationships will be available in the above diagram. If there are no relationships defined in the database level, you need to create relationships in the Tabular model. Please note that you cannot define many-to-many relationships like MDM cubes.

You can hide Key columns as they are needed for the relation and you do not want users to see them. Further, you can rename the columns with friendly names as shown below.

In the above SSAS Tabular model, you will see that the Date table is related to the Internet Sales table three times but only one relationship is with Active and the other two relationships are inactive as shown below.

In MDM, there is an option of Role Playing Dimension where one dimension can be linked to multiple keys in the fact tables. However, the role Playing Dimension option is not available in Tabular modelling. Therefore, you need to add multiple instances of the Date table. However, these are independent instances and you need to configure them differently as shown below.

The next important aspect of the Tabular modules is the ability to create columns. For example, let us create, Full Name column, using the First Name, Middle Name and Last Name, Age from the Date of the Birth column, Income Group, Margin form the Revenue and Cost.

The following are the simple DAX function that can be used for the calculated columns.

Column	Expression
Full Name	=TRIM(CONCATENATE( CONCATENATE(Customer[First Name] , ” ” & Customer[Middle Name]), ” ” & Customer[Last Name]))
Age	=DATEDIFF(Customer[Birth Date],TODAY(),YEAR)
Income Category	=IF (Customer[Yearly Income] < 25000,” 0 – 25K”,IF(Customer[Yearly Income] < 75000,” 25K – 75K”,IF( Customer[Yearly Income] < 150000,”75K = 150K”,” 150K-200K”)))

These calculations are done in the customer tab as shown below.

Similarly, you can calculate the Profit for the Internet Sales table using =’Internet Sales'[Revenue] – ‘Internet Sales'[Cost] formula.

Measures

Now let us create measures for the Internet Sales table. We will create measures for Quantity, Revenue, Cost and Profit columns.

In the above model, Quantity, Cost, Revenue and Profit is summed and Margin measure is created with the Profit / Cost formula. You can use different types of formatting such as Percentage, Numeric, Currency etc.

After these configurations, now you are ready to test the Tabular Model using Microsoft Excel. This is the standard pivot table usage so the end-users will not have any impact.

Hierarchies

Like we created hierarchies for MDM models, we can create hierarchies for the SSAS Tabular Models as well.

After selecting the hierarchy, you need to drag and drop the columns to the desired hierarchy level.

In the above example, two hierarchies are created namely Calendar and Fiscal. It is important to note that, you need to create the same hierarchies in other date tables as they are not automatically created.

Now let us view the hierarchy from Microsoft Excel as shown in the below screenshot.

You will see that the Month Name and Week Name is ordered by the alphabetic order not by how it should be ordered. Ideally, Month Name should be ordered by the Month Number while the Week Name should be ordered by the Week Number of the Week.

Selecting the Properties of Month, you should select the Sort By Column as the MonthNumberofYear as shown in the below screenshot.

Similarly, Week Name should be ordered the DayNumberofWeek column. Now you can see that the Month and Week Name are accordingly as shown in the below screenshot

Deploying the SSAS Tabular Model

After configuring, you can process and deploy the tabular model so it is ready to access by the end-users. You can process all tables of selected tables. When the processing is done, we have to proceed with the deployment of the tabular. This is the final screen that you will get after the deployment of the tabular.

Then the SSAS Tabular Model should be available for the end-user access. You can access the Tabular from the SQL Server Management Studio (SSMS).

Other features of SSAS Tabular Models

There are few other important features in SSAS Tabular Model such as Key Performing Indicators, Partitions, Translation and Perspectives similar to MDM cubes. You can create roles and provide row-level security as required for the users to access the Tabular Models.

Apart from the in-Memory option, there is a Direct Query option so that SSAS Tabular models can directly get the data from the data sources. However, there are a few limitations in the Direct Query option in Tabular Models.

Conclusion

SSAS Tabular model is a simple tool that can be used to analyze data. Apart from the simplicity of the usage, there are performance benefits with this option. It uses the DAX query which is similar to the Excel expressions. Further, it has features such as KPI, Partitions, Perspective.

Author
Recent Posts

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka