WISE OWL EXERCISES
SSAS - TABULAR EXERCISES
- Creating a data model (2)
- Excel pivot tables (1)
- PowerView (2)
- Power BI Desktop overview (1)
- Other data sources (1)
- Calculated columns (4)
- Measures (3)
- Changing query context (2)
- The EARLIER Function (1)
- DAX queries (3)
- Date calculations (3)
- Hierarchies (1)
- KPIs (2)
- Perspectives (1)
- Prototyping using PowerPivot (1)
- Security (2)
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!
- Go into SQL Server Management Studio;
- Open the SQL file you've just unzipped (you can press CTRL + O to do this); then
- 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:
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|
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:
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:
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:
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:
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).