SQL SERVER – What is SSAS Tabular Data Model and Why to Use it

Analysis services in SQL Server 2012 can be either deployed in multi-dimensional mode or tabular mode or power pivot for SharePoint as well. Tabular mode is a new enhancement in SQL Server 2012 analysis service database structure. It is a columnar database capable of incredible performance and compression ratio. At this point, there is a lot of confusion in users on why to use the tabular model when we already have multidimensional model.So, let’s discuss these points first before creating a tabular model project. Let us learn about the SSAS Tabular Data Model.

Why to Use:

1) Tabular model is quite easy to understand and implement, and is particularly made for empowering information workers.

2) The tabular model uses DAX for scripting which is similar to using excel formulas and is faster to learn.

3) It uses Vertipaq (x-velocity) engine for in memory column storage, which gives great performance and direct query mode for retrieving data and aggregates directly from the database which is beneficial for querying data in real time.

4) Powerpivot models can be easily upgraded to tabular models, thus providing a path for business users and IT professionals to author models in familiar tools like MS Excel.

5) All the client applications that support multi-dimensional mode will also support tabular and work natively with it. This is because tabular uses the same data provider that understands both MDX and DAX queries.

When Not to Use :

1) When the source is based on dimensional modeling and has complex relationships with very large volume of data.

2) No support for writing back or parent, child hierarchy

3) When you want to do complex calculations, scoping and named sets

Getting started with the Tabular Model Project :-

Click on the new project in SQL server data tools, there will be 5 templates options under Business Intelligence à Analysis Services. For this example, I’m creating an Analysis service tabular project.

As soon as you click ok, you will be prompted to specify an instance of analysis services installed in tabular mode. This is the instance which will be used for creating a workspace database for the project.

Once you click ok, you will notice that, a model. bin file is generated. To import data in the tabular model, go to model in the toolbar menu and click Import data from source.

As you can see, you can import data from a variety of other sources. For the purpose of this example we will choose Microsoft SQL Server and click on Next >

Here you will be asked to enter credentials to connect from the data source. After entering the credentials, click Next >

Here you have to choose whether you want to import data from tables and views or either write a customized query to specify the import data. For the purpose of this example, I’ve taken the first option.

As I choose the first option we will get a list of tables and views from the database we selected in the earlier steps. Here you can select the tables and views that you want to import or optionally choose one table and click on the “selected related tables” button to include all related tables based on relationships. Click on Finish button

After you click Finish button import process will start, you can see the status of the import in the below screen

After the import process is finished, you can see the project, either in grid or diagram view. By default, you will see the grid view of all the tables you selected.

Click on the bottom right corner to see the same in the diagram view

Right click any table to see various options that will enable you to create relationships, hierarchy or hide table from the client tool.

To summarize it all, we have created a tabular mode project, and imported data from a data source into this project. Thank you Namita for your help in writing a blog post.

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