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)
Hi Pinal, great read your articles!
I think Parent-Child hierarchies in tabular model are supported. Can you verify?
Yes.. it is possible..
Thanks for your help Akanksha.
I am still in a dilemma to use tabular model over the conventional Multidimensional Cube. Looks to me after initial reading that the Conventional OLAP cube. Do you recommend should be override the conventional cube and switch this to new tabular model or stick with the older one or Both…. Please clear the doubts really help… Thanks
There is no need to switch to tabular. There are some feature that we don’t have in Tabular yet but as you know Tabular is so much easier than multidimensional…
Cool Post. Thanks
Hi there! Can you please help. how to set up the workspace server for the tabular model after you have created the project. the workspace is not being connected. how to do it? thanks
First you should have an analysis service instance in Tabular mode.Then you should right-click on model.bim file and in properties tab, you can change workspace server to the analysis service instance.
Hi there! I have seen many post and video related to this topic. But, still I am confuse why we need to use tabular model over relational DB, on both we need to create relation between tables or views and both can used in power pivot. So, why we need to use tabular model and what are the advantages to use tabular model over Relational DB.
Everything looks grates but I have one question, how do you continue to load into the model after you initiate the project and import data first time?