SQL SERVER – What is SSAS Tabular Data Model and Why to Use It – Part 2

In my last article, I talked about the basics of tabular data model and why use it. Then I demonstrated step by step creation of a basic tabular model project. In this part I’m going to throw some light on how to create measures and analyses in excel. Let us learn about the SSAS Tabular Data Model.

If you look at the tabular project closely, you will notice that we have not defined any measure yet. So, in the first step we will define the measure first. Open the solution and select the column you want to define as a measure. Then, click on the summation icon on the toolbar. You will see the aggregated results at the bottom of that column. You have also other choices as well like average, min, max, count and distinct count.

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image001

After creating the required measures, we need to analyze our data in excel. To do this, click on the excel icon in the upper left corner of the toolbar.

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image002

This will open your analysis in excel. Notice the pivot table field list here. I have highlighted the measures that we created in the earlier step. Now, we can use these measures in our analysis

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image003

Now, we have to put the required fields in their respective places as column labels, row labels, Values and Report filter for analysis. See below snapshot for details, it shows region wise sales on a yearly basis

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image004

You can even apply filters on the above analysis by placing the slicer field in report filter. In our example, we will take an English product name as a filter.

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image005

You can use the filter as depicted in the below snapshot.

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image006

Optionally, you can also use the slider to filter data more interactively.

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image007

Further to improve our analysis, we can insert pivot charts

SQL SERVER - What is SSAS Tabular Data Model and Why to Use It - Part 2 image008

That’s all for this time, in my next post I’m going to show in detail about how to create hierarchies, perspectives, KPI’s and many more features. Thanks Namita for helping with the article.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – How to Change Compatibility of Database to SQL Server 2014
Next Post
Developer’s Life – Attitude and Communication – They Can Cause Problems – Notes from the Field #027

Related Posts

Leave a Reply

Menu