SSAS - MULTIDIMENSIONAL EXERCISES▼
SSAS - multidimensional | Calculations exercise | Simple calculation in cube using MDX or otherwise
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.
You can learn how to do this exercise on the relevant Wise Owl classroom training course (sadly for the moment only in the UK).
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.
This exercise uses the project that you are currently working on! Within this project, create a cube using measures taken from two tables:
Choose measures from both the transaction and the quadrant target tables
Rename your measure groups in the Cube Structure tab:
We'll give the measure groups better names
Configure the dimension usage for the measure group taken from the tblQuadrantTarget table:
The targets link to the other tables at month, animal and quadrant granularity level
Create the following two calculations (whether in form view or by scripting them):
- [Mid target] (the midpoint between the higher and lower targets)
- [Excess over mid target] (the difference between sales value and this mid-target amount)
Here's one view of what your cube might look like:
Your cube if you include all 5 measures of interest
If yoiu're using Analysis Services 2012 or later, you'll need to analyse your cube in Excel to see a pivot-table-style view of it similar to the one shown above.
Save your cube and close down all windows.