Developing models in SQL Server Data Tools

The modeling environment in SQL Server Data Tools (SSDT) is almost identical to that of PowerPivot. There is more functionality available; for example, the ability to create partitions and apply security to the model. However, the general approach to modeling in SSDT is identical to PowerPivot. That is, the import of data, its definition (tables and columns), and the creation of relationships and measures are the same.

This recipe demonstrates how models can be developed in SSDT by extending the model imported in the Importing models into SQL Server Data Tools recipe. Here, we add a Geography table to the model to show the sales region that the purchase occurred in. Later on (see the Securing data with roles recipe) this table will be used to secure the model by allowing specific users access to privileged regions.

Getting ready

Open the model that was developed in the Importing models into SQL Server Data Tools recipe and ensure that the file Geography...