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

Software ==> SSAS - multidimensional  (21 exercises)
Version ==> SSAS 2008 R2 and later
Topic ==> Calculations  (1 exercise)
Level ==> Relatively easy
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.

This exercise uses the project that you are currently working on!  Within this project, create a cube using measures taken from two tables:

SSAS Analysis Services exercise - Calculations (image 1)

Choose measures from both the transaction and the quadrant target tables

Rename your measure groups in the Cube Structure tab:

SSAS Analysis Services exercise - Calculations (image 2)

We'll give the measure groups better names

Configure the dimension usage for the measure group taken from the tblQuadrantTarget table:

SSAS Analysis Services exercise - Calculations (image 3)

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:

SSAS Analysis Services exercise - Calculations (image 4)

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.

This page has 0 threads Add post