Calculations in SSAS using MDX

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,

Meaning Of 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.

Names Set: Named sets return a dataset based on defined logic. They are primarily useful to create datasets that are often requested from the cube. Named sets are of two types: Static and Dynamic. The difference between these two is that static named sets are calculated when they are requested the first time in a session and dynamic named sets are calculated each time a query references it. In this section we will look at how to create dynamic named sets. Open the cube designer, and click on the Calculations tab. Click on “New Named Set” from the toolbar and key in the values as shown in the above screenshots.
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 SalesDetails in the form view which is created by adding Unit price measure from Internet Sales and order quantity 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

After create calculate measure once process the cube then calculate member shows on the measures group

By: Mohan Punugoti

No Comments

Leave a Comment

Your email address will not be published.