Understanding MDX Calculations Part 1 – Overview

What are Calculations?

Calculations are items in a cube that are evaluated at runtime (at query-time instead of during processing).

There are three types of calculations:

  • Calculated Members / Calculated Measures (Tuple Based Calculations)
  • Named Sets (Set Based Calculations)
  • Script Commands (normally for SCOPE)

Calculated Members / Calculated Measures

Calculated Members return a follow url single member, either a measure value or a member from dimension. When you create a calculated member, you are creating a new member on a hierarchy of one of your dimensions. Most often, calculated members are created on the Measures dimension, and are called Calculated Measures, but it is important to understand that they can be created on any hierarchy.

Calculated Measures examples:

  • Summing two or more amount measures to create total amount measure
  • Product Contribution to Parent
  • Product Contribution to All Products

Calculated Members examples:

  • Combining multiple members into a single member

All Calculated Members need to have a Parent property that determines where the Calculated Member is visible. Normally it is the measure dimension(Calculated Measures) but it can be other dimension(Calculated Members), Some other properties include Format String, Visible, Fore Back Color, Font Size.

  • FORMAT_STRING
    • Standard values are Currency and Percent
    • Custom values as #,# ; -#,# ; 0 ; 0 (first part is for Positive Values, 2 for Negative, 3 for Zero , 4 for Null)

There are three types of Calculated Members

  • QUERY SCOPED (No Caching): WITH MEMBER <CalcMember> AS <Expression>, <Properties>
  • SESSION SCOPED (Session Caching): CREATE MEMBER <CalcMember> AS <Expression>, <Properties>
  • QUERY SCOPED (Global Caching): CREATE MEMBER CurrentCube.<CalcMember> AS <Expression>, <Properties>

purchase dapoxetine online Basic CACLULATED MEMBER example

WITH MEMBER [Product].[Product].[All].[Two Special Products] AS 
    Aggregate({[Product].[Product].&[223],[Product].[Product].&[486]}) 
SELECT 
  [Order Quantity] ON COLUMNS
 ,{
    Head
    (
      [Product].[Product].[Product].MEMBERS
     ,5
    )
   ,[Two Special Products]
  } ON ROWS
FROM [Adventure Works];

http://alternativespacetime.com/?attachment_id=1086 Basic CALCULATED MEASURE example

WITH MEMBER Measures.[Total Sales Amount] AS 
    [Measures].[Internet Sales Amount] - [Measures].[Reseller Sales Amount] 
SELECT 
  {
    [Internet Sales Amount]
   ,[Reseller Sales Amount]
   ,[Total Sales Amount]
  } ON COLUMNS
 ,Head
  (
    [Product].[Product].[Product].MEMBERS
   ,5
  ) ON ROWS
FROM [Adventure Works]

In above example we are referring only measures to form Tuple and these types of calculations can be moved to actual measures which will get stored while processing resulting in improved query performance

Named Sets

Named sets are used to return zero or multiple members at a time.

Examples include:

  • Top 5 Selling Products
  • Most Recent 15 Days
  • Core Product Group

Sets may be DYNAMIC (evaluated with each query) or STATIC (evaluated once when the CREATE SET statement is executed).

WITH SET FiveBestProducts AS
  TOPCOUNT
    (
    [Product].[Product].[Product].Members
    ,5
    ,[Measures].[Internet Sales Amount]
    ) 
  MEMBER [Product].[Product].[All].[Five Best Products Together]	
       AS AGGREGATE(FiveBestProducts)
  MEMBER [Product].[Product].[All].[Other Products]			
       AS AGGREGATE([Product].[Product].[Product].Members - FiveBestProducts)
SELECT [Measures].[Internet Sales Amount] on COLUMNS
      ,{
         FiveBestProducts 
        ,[Other Products]
        ,[Five Best Products Together]
        ,[Product].[Product].[All] 
        } on ROWS
FROM [Adventure Works]

Script Commands

The scope statement specifies a part of the CUBE (subcube) to which its calculations apply. The following MDX script example uses a SCOPE statement to sets the value of the Amount measure, in the Finance measure group of the Adventure Works DW Multidimensional 2012 sample cube, to 10% higher for the children of the Redmond member in the Customer dimension.

SCOPE 
  (
    [Customer].&[Redmond].MEMBERS
   ,[Measures].[Amount]
  );
  THIS = [Measures].[Amount] * 1.1;
END SCOPE;