WISE OWL EXERCISES
SSAS - TABULAR EXERCISES
SSAS - tabular | Creating a data model exercise | Create and pivot a simple 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!
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.
Create a new tabular model project in Visual Studio called EXnn (where nn is this exercise number), and rename the model within this to SimpleModel (because it will be!).
The aim of this exercise is to allow users to see the following tables/columns when creating a pivot table based on your model. Details of how to do this are in the following pages, so make sure you read on!
The first few of the 13 products listed in the pivot table.
First import from the MAM database the following tables (with friendly names as given):
|Table name||Friendly table name|
See if you can include at this point only the columns you need either for final display or to link tables together.
Hide the tables and columns which the user shouldn't see - you should now have something like this:
The model, with tables and columns hidden from client tools as appropriate.
Add a measure to your model to show the average price for transactions:
One way to add the measure you'll need.
Analyse the model in Excel to get the pivot table shown at the start of this exercise!