This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SSAS - tabular (30 exercises)
Topic ==>	Creating a data model (2 exercises)
Level ==>	Average difficulty
Subject ==>	SSAS training

Create a full data model in SSDT, deploy it and create a pivot table including a slicer from it

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.

Before you can do this exercise, you'll need to download and unzip this file. Once you've done this:

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

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	Contains
Centre	A list of shopping centres
CentreType	Types of shopping centre
Habitat	Animal habitats
Product	The products sold
Purchase	Purchases made

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 model 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 Environment 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: