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.

1_SQL_Server_Implementing_Calculations_in_SSAS_using_MDX_Part1

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.

2_SQL_Server_Implementing_Calculations_in_SSAS_using_MDX_Part1

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.

3_SQL_Server_Implementing_Calculations_in_SSAS_using_MDX_Part1

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

4_SQL_Server_Implementing_Calculations_in_SSAS_using_MDX_Part1

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

   

3 Comments on “Implementing Calculations in SSAS using MDX – Part1”

  1. Hi,

    I have a sales(SalesID, GoodsType, GrossSale, AverageSale). I was to write two MDX expression in MS Visual Studio SSAS cube’s Calculation tab to calculate Sum of GrossSale and Average of GrossSale where GoodsType is “Food”. If someone please write the MDX expression for me?

    I wrote this expression for sum but it doesn’t work!

    CREATE MEMBER CURRENTCUBE.[Measures].Sales
    AS SUM([Measures].[Gross Sale], [Measures].[Datatype] = “2”),
    VISIBLE = 1 , DISPLAY_FOLDER = ‘Forecast’ ;

    ……………………

    I have tried in this way also

    CREATE MEMBER CURRENTCUBE.[Measures].Sales
    AS SUM([Measures].[Datatype].&[6], [Measures].[Gross Sale]),
    VISIBLE = 1 , DISPLAY_FOLDER = ‘Forecast’ ;

    I should elaborate bit more, please check bellow,

    I have a data table. Where GrossSale and Datatype are columns. Say in GrossSale I have (100, 200, 400, 130, 350) and in Datatype I have (5,2,6,8,2) respectively. Now I want Sum of GrossSale where I have Datatype 2. Then I have GrossSale = 550, that is 200+350. Because for Datatype 2, I have 200 and 350.

    If someone can help me please?

  2. Hi all,

    I have a problem with Calculated %. Here is an example:
    I have:
    [Country].[Country],[Time].[Cal Year-Month]: dimension.
    [Measure].[Amount]: fact
    I create calculated measure:

    CREATE MEMBER CURRENTCUBE.[Time].[Cal Year-Month].[All].[Latest Month] AS
    Sum([Measures].[Gross Media Spend],ClosingPeriod([Time].[Cal Year-Month].[Cal Year-Month])),
    FORMAT_STRING = “#,##0;-#,##0”, VISIBLE = 1;

    Country—Latest Month
    USA——-600
    AU——–300
    UK——–100
    Total—–1000
    I want change to % mode as bellow:
    Country—Amount
    USA——-60%
    AU——–30%
    UK——–10%
    Total—–100%

    How can I do it in this case with Calculated Measure?

    Thanks very much.

Leave a Reply

Your email address will not be published.