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 Wise Owl course listed below!

Category ==> SSAS - tabular  (29 exercises)
Topic ==> Creating a data model  (2 exercises)
Level ==> Relatively easy
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.

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!

Pivot table by product and quadrant

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
tblCentre Centre
tblPos Pos
tblProduct Product
tblQuadrant Quadrant
tblRegion Region
tblStore Store
tblTown Town
tblTransaction Transaction

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:

Final model

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:

Adding a measure

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!

This page has 0 threads Add post