Implementing Calculations in SSAS using MDX – Part1

Calculations in Analysis Services help to extend the cube capabilities to make the BI applications more powerful. A calculation is nothing but an MDX expression or script which is used to define a calculated member, a named set, or a scoped assignment in a cube. Using calculations, we can, not only add objects defined by the cube data but can also refer other parts of the cube, other cubes, or even information outside the Analysis Services database using expressions.

To add or edit calculations, use the Calculations tab in the cube designer in BIDS. Have a look at the following screenshot which shows the various panes inside the Calculations tab.

The tab has three panes which are Script Organizer, Calculation Tools and Calculation Expressions which supports a form view and a script view. We can create a Calculated Member, Named Set or Script Command using the Calculations tab either by right clicking in the Script Organizer pane or by using the buttons provided in the toolbar, as shown in the above screenshot. The calculations tab supports two different views when viewing or editing calculations,

• Form View – Provides form editors to view and edit calculations in an organized and user friendly manner. Using the metadata, functions and available templates, we can easily create calculations even with limited knowledge of actual script commands.
• Script View – Is meant for more advance users who can directly write the script to create required calculations. It displays the entire MDX script associated with the cube as well as displaying the metadata, functions, and tools available to the cube.

(This is all about the designer. In this part I am going to show a simple example of creating a calculated member using the AdventureWorksDW2008 database. I have created a sample cube using Internet Sales and Reseller Sales as measure groups and some of the selected dimensions for this example.)

Calculated Members

A calculated member is defined using an MDX expression, based on which its value is calculated during run time. The cube only stores the definition of a calculated member and not a value hence avoids increasing the cube size by adding calculated members to the cube. The value only gets calculated in memory whenever required to answer the query.

Calculated members can be defined for dimensions or measures. The members defined for measure dimensions are called as calculated measures. Calculated members are defined using the data available in the cube and can be made complex by combining data with arithmetic operators, numbers, and functions. For example, the AdventureWorks sample cube is having Internet Sales and Reseller Sales measure group with Sales Amount measure in each group. Using calculations, we can create a calculated measure to combine both the sales amount.

The above picture shows the calculated measure called Total Sales in the form view which is created by adding Sales Amount measure from Internet Sales and Reseller Sales measure groups using MDX expression. We can also specify a suitable format for this new calculation. The same can be viewed in the script view which shows the actual script involved in creating this calculated measure.

Once deployed the calculation created is available for browsing under the available measures as follows.

In the next part of this series, we will check another option in the Calculations tab which is a Named Set.
Regards

Amit Karkhanis

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook