SSAS - tabular | Creating a data model exercise | Create, deploy, browse and slice a data model in SSAS Tabular

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the course listed below!

Software ==> SSAS - tabular  (29 exercises)
Version ==> SSAS 2012 and later
Topic ==> Creating a data model  (2 exercises)
Level ==> Average difficulty
Course ==> SSAS - Tabular Model
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

If you haven't already done so, run the SQL script in the above folder in SQL Server Management Studio to generate a database (not for commercial use or copying) called MAM.

The aim of this exercise (the steps for which are described in the following pages) is to create the following pivot table:

Slicing by shopping centre

This shows, for example, that total sales for Mammal products in London for Shopping Centre centre types were 664.

Start by importing the tables that you'll need into your new model: tblSpecies, tblAnimal, tblProduct, tblTransaction, tblPos, tblStore, tblCentre, tblCentreType, tblTown and tblRegion.

Create a good data model, hiding all the tables and columns that a user wouldn't want to see, and also create a measure giving the total quantity sold for the transactions table.

Now deploy your cube to the production server on your computer (which may be remarkably similar to the workspace server).  Connect to this Analysis Services database in Management Studio, and browse your model:

Deployed data model

If you include the CentreTypeName, RegionName and SpeciesName attributes and the Sum of Quantity measure, this is what you should see.

While still browsing your cube in Management Studio, choose to analyse it in Excel and create the final pivot table:

The final pivot table

The final pivot table, complete with slicer.


Finally, go back to the original model and add in the tblQuadrant table to your model, creating the necessary relationship between this and the Region table.  Redeploy your model and refresh in SSMS and Excel to display the above pivot table, but by quadrant, not region:

Data by quadrant

You'll need to redeploy your model and refresh your pivot table before you can see these results.


Close any open Excel workbooks down without saving any changes (unless you want to save them for posterity/future reference). 

This page has 0 threads Add post