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
- 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].& 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.