559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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:
|Centre||A list of shopping centres|
|CentreType||Types of shopping centre|
|Product||The products sold|
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:
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).
25 Aytoun Street