SSAS Calculated Member Based on Dimension Attribute

Creating a calculated member is a great way to add functionality to your cube. Most times calculated members are based entirely off values in your fact table, but every so often you need to pull a figure from a dimension. One such example would be if you have an exchange rate that is based on a location and is not time dependent.

img1

To use this value inside a calculated member, your calculation needs to leverage the ‘key’ property of your dimension. In the example below, the dimension is called ‘Location’ and has an attribute called ‘Exchange Rate’. The ‘Amount Base’ is the value we want to adjust from the fact table:

[Measures].[Amount Base]*[Location].[Exchange Rate].currentmember.properties("key")

The end result in the cube is as follows:

img2

The problem with this, is that as we are dealing with a calculated member, the amounts do not roll-up or aggregate to the parent levels. The calculation of the member needs to change to take this into account using a small amount of recursion:

IIF(
    IsLeaf([Location].[Exchange Rate].CurrentMember),
    [Measures].[Amount Base]*[Location].[Exchange Rate].currentmember.properties("key"),
    Sum([Location].[Exchange Rate].Children, [Measures].[Amount])
   )

This will in turn give you the desired results:

img3

Advertisements

About John Winford

Based in Vancouver, Canada I’m an IT professional with a number of specialties. First and foremost I am the front-line between the business users I enable and the technical team I represent. Not content to simply push the paper I also get my ‘hands dirty’ when required. I have an extensive amount of experience with technical project management, ERP implementations, BI work, and development across SharePoint, CRM and generic Widows applications.
This entry was posted in Business Intelligence. Bookmark the permalink.

3 Responses to SSAS Calculated Member Based on Dimension Attribute

  1. bidyut121 says:

    Fantastic, I had a different requirement, but the explanation of the IsLeaf function helped to resolve my issue.

  2. Sumit says:

    Hi,
    I have a requirement. I have two columns in fact table called Amount and MarkupPercent. They are also measures in the cube. I have a requirement where I need to add another calculated member called “MarkupValue” which is defined as (Amount * MarkupPercent) / 100. The issue I am getting is in the value. When I add Item on the rows, the values do not match the individual lines. Ex:

    Item Amount Markup Markup Value
    Item1 1000 2 20
    Item1 2000 3 60
    Item1 3000 6 180

    When this is added to a report I get following output

    Item1 Amount Markup Value
    Item1 6000 220

    The desired output is

    Item1 Amount Markup Value
    Item1 6000 260

    How do we achieve this? What am I doing wrong?

    the Calculated member expression is

    [Measure].[Amount] * [Measure].[MarkupPercent] / 100 / Line count

    • John Winford says:

      Hi,

      I have had this problem before myself. Basically what is happening is SSAS is calculating at the aggregate level instead of at the detail level and rolling up the result. e.g.) You are receiving 220 as the answer because this is what is happening: (1000 + 2000 + 3000) * (2 + 3 + 6) / 100 / 3. That equates to 220.

      Have you tried putting the calculation in the SQL data set behind the scenes? This is generally the easiest way to do things.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s