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.

Software ==> SSAS - tabular  (30 exercises)
Version ==> SSAS 2012 and later
Topic ==> Creating a data model  (2 exercises)
Level ==> Average difficulty
Subject ==> SSAS training
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). Once you've done this:
  1. Go into SQL Server Management Studio;
  2. Open the SQL file you've just unzipped (you can press CTRL + O to do this); then
  3. Execute this script.

This will generate the database that you'll need to use in order to do this exercise (note that the database and script are only to be used for exercises published on this website, and may not be reused or distributed in any form without the prior written permission of Wise Owl).

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.

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

Pivot table

The final pivot table will include a slicer by habitat.

There is lots of help given below - read on!

Create a new SSAS Tabular project called Bad Habitats, and start by importing and renaming the tables that you'll need into your new model:

Table Friendly name to use
tblCentre Centre
tblCentreType CentreType
tblHabitat Habitat
tblProduct Product
tblPurchase Purchase

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 purchases table (call this Total Quantity).  Your model should now look something like this:

Final model

Notice that you'll need the Animal field from the Product 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:

Browsing the model

If you include the habitat, centre type, animal and total quantity measure in your "cube", 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:

Final table

The final pivot table, complete with slicer.


Finally, go back to the original model and add in the tblEnvironment table to your model, creating the necessary relationship between this and the Habitat table.  Redeploy your model and refresh in SSMS and Excel to display the above pivot table, but sliced by environment, not habitat:

Pivot table by environment

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). 

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post