SQL Server Analysis Services Tabular Data Model


By:
Overview

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.

Explanation

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.

A New Project in the SQL Server Data Tools

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.

Data Model in the SQL Server Data Tools

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.

Model Properties in the SQL Server Data Tools

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.

SQL Server Data Tools Table Import Wizard

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.

Tabular Model Explorer in the SQL Server Data Tools

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.

Data and Formulas in the Model in the SQL Server Data Tools

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.

Table Properties in the SQL Server Data Tools

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.

Model Menu in the SQL Server Data Tools

Finally a Tabular Data Model is ready for processing and deployment. We would look at this in the next chapter.

Additional Information
  • Consider exploring the different types of properties for different database objects in a SSAS Tabular data model from here.





Comments For This Article

















get free sql tips
agree to terms