UOM as a Measure

In the last post, I explained how to add the Last Update Date to a Scorecard. In this post, we will go for the Units column.

UOM as a Measure

The problem with the UOM (Units of Measure) is similar to the Date, in the way that both are measures that can’t be aggregated, but the main difference is that in a good cube design we will store UOMs as they were another normal dimension, while Dates are kept directly in the fact table.

The solution described below, consists on having a calculated member that will retrieve the right UOM description based on the material queried (material dimensional filter). This means that it will behave as a normal measure (all measures show data accordingly to the dimensional filters selected) but instead of aggregating numbers it will return a string description.

First step: Have a UOMs Dimension

In my case, UOMs come from a SAP column called MEINS. In my fact table F_MSEG I have a MEINS_ID field that relates the fact table with a dimensional table called D_MEINS which contains the UOM details:

Second step: Create a UOM measure

In order to have the calculated member working properly we need to create an invisible Measure from the MEINS_ID field in the fact table:

Configure the measure as shown in the screenshot. Notice that the Visible field is set to False.

This measure will give us the UOM ID from the fact table that we need to query the Units dimension (created in the next step) when browsing the cube.

Third step: Create the Units dimension

From the D_MEINS table in the cube create a dimension called Units.

There is a key configuration to be done here, the MEINS attribute needs to be configured as follows:

  • KeyColumns: D_MEINS.MEINS ID
  • NameColumn:D_MEINS.MEINS
  • ValueColumn: D_MEINS.MEINS ID

This configuration stores the attribute by MEINS ID column but displays the MEINS column content. For example the [D_MEINS].[MEINS].&[1] expression refers to the UOM with MEINS ID = 1 but will return the string KG (assuming that KG is the UOM with MEINS ID = 1).

Forth step: Create a Calculated Member

In the Calculations tab create a new calculated member called Units:

The MDX Expression for this calculated member is:

IiF (Measures.[UnitsID] = 0, “”, StrToMember(“Units.&[” + VBA!CStr(measures.[UnitsID]) + “]”).Properties(“Caption”))

This expression creates a new Measure (Parent hierarchy field) called Units (Name field) in the Facts (Associated measure group field) measure group.

When this new measure is queried it will execute the Expression code. The explanation of the code is as follows:

When Units measure is queried, Units will query automatically the UnitsID measure (the invisible one created on step two). If the value is equal to 0, Units value will be equal to “” (empty string), otherwise Units value will be the “Caption” property of the member in the Units dimension with ID equal to UnitsID.

Let’s have a little example to understand this expression:

In the Units dimension we have 2 units, ID = 1 for KG and ID = 2 for TON.

In the fact table we have an entry with the following data:

  • Material ID = 1
  • Units Id = 1
  • Quantity = 500

The final user will have the following measures and dimensions:

  • Measures: Quantity, Units (this will be the calculated member)
  • Dimension: Material, Units (this is the dimension)

When the user selects the Quantity measure for that Material, he will see the value 500. If the user selects the Units measure, it will trigger the calculated member expression. It will pass the value of the hidden measure UnitsID, which will be 1, to the Units dimension to fetch the property Caption, which will return KG. Easy! 🙂

Fifth step: Show the Units in a scorecard

In PerformancePoint Services you need to add a New Actual row to your KPI and call it Units or UOM. Then configure the Data Mappings as follows:

  • Select a measure = Units
  • Aggregate members by = Maximum

It’s important to set the Aggregate members by to Maximum to avoid PerformancePoint to try to aggregate the values. By choosing either Maximum or Minimum PerformancePoint performs a Sort operation at a binary level and returns back the top/bottom value.

Tagged with: , , , , , , , , ,
Posted in PPS, SSAS
3 comments on “UOM as a Measure
  1. An Excellent Post On…

    This article was discussed on Twitter this week….

  2. May I just say what a relief to find an individual who truly
    knows what they’re discussing on the web. You actually know how to bring a problem
    to light and make it important. More people must check this out and understand this side of
    your story. I was surprised that you aren’t more popular
    since you most certainly possess the gift.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

%d bloggers like this: