SQL Server Analysis Services Tabular Data Model
By: Siddharth Mehta
Data model development in SSAS Multidimensional and Tabular mode is almost the same in many aspects. Data is read or imported from the data source depending on the configuration. After the tables are available in the data model relationships and calculated fields or tables are configured. Analytical constructs like Measures and KPIs are created using DAX formulas. Different properties related to a project, model, tables, columns, and PowerView reporting are configured during the course of the data model development. Finally perspectives are configured with a list of database objects which almost concludes the development phase. We already understand the meanings of all these terms and database objects from the previous chapters. In this chapter we will explore the AdventureWorks tabular data model that we have restored on the SSAS tabular instance earlier.
1) Open SSDT and create a new project that would import from an existing tabular database as shown below. Select the database that we have restored earlier and create the solution.
2) Once the project is created, open the data model and it should look as shown below. Different tables with columns along with inter-relationships can be seen in the diagram view of the data model.
3) Model properties can be used to configure whether the queries should be sent to the data source directly or the data should be kept in the Vertipaq cache by configuring the DirectQuery property.
4) SSAS supports a variety of data sources. Development of a SSAS Tabular Data model generally starts with import of tables from data source. SSDT provides a data import wizard as shown below.
5) The tabular model has two panes to browse the model - Solution Explorer and Tabular Model Explorer. The solution generally has just one file - Model.bim which we can explore in the model. The Tabular Model Explorer facilitates categorical and quick access to all the database objects in the Tabular Data Model as shown below.
6) When you open the table, the data in the model can be seen along with the DAX formula for measures / calculations as shown below.
7) Each table contains table level properties along with reporting properties. Configuring these properties for reporting clients makes the task of report developers a little easier.
8) Once the data model is ready, Perspectives and Translation can be configured in very much the same way as it is done in a multidimensional data model. The Model menu in SSDT provides options to configure almost all the database objects in a Tabular Data Model.
Finally a Tabular Data Model is ready for processing and deployment. We would look at this in the next chapter.
- Consider exploring the different types of properties for different database objects in a SSAS Tabular data model from here.