Scorecards have KPIs with measures that can be aggregated like value or quantity, but what about having Date and Units (UOM: Units of Measure) also?
Dates and Strings cannot be aggregated, but there is a little trick in SSAS to include these measures in a cube. Here is how I did it.
Date as a Measure
In my Scorecard, final users were interested in having the last update date for the KPIs. This helps them to know how fresh the values are. As I’m using SAP as my data source I get one “Posting Date” (BUDAT field) for each record in the database. The only thing that I need to do is to configure SSAS 2008 R2 to get the latest date from the queried aggregation and assign a good format.
First step: Keep the date in the fact table
In order to show the date as a value for a KPI, we will keep the “Posting Date” field in the fact table along with the other measures.
Note: This doesn’t mean that you shouldn’t relate the fact table with your Time dimension table. If you need to query your fact table by date, then you will need to relate the fact table with the Time dimension table regardless of keeping or not the Date field in the fact table.
Second step: Add the Date field (BUDAT) as a measure in the measure group
In our example, the BUDAT field needs to be added as a measure called “Posting Date” in the “Facts” measure group. Just drag and drop the BUDAT field from the table diagram to the Measures box on the left hand side in the Cube Structure designer in BIDS:
Third step: Set up Measure properties
Open the Properties toolbar for the BUDAT field and do the following configuration:
- AggregateFunction = Max: SSAS will return the maximum value found for this measure in the queried aggregation. With this configuration we will always obtain the last date available in the query; in our example, final users wanted to know the last posting date.
- DataType = Inherited: This is an easy way to configure SSAS to follow whatever has been configured in the data warehouse. In my case BUDAT is a “date” data type in the data warehouse database.
- FormatString = dd-mmm-yyyy: Final users are used to see dates in the 02-May-2011 format. You can adjust this field to your convenience.
- Name = Posting Date: This is the displayed name when a user queries the cube.
Source = F_MSEG.BUDAT (Date): This field doesn’t need to be configured, but I just wanted to highlight it here to show you that this will be the column queried to fetch the values for the measure.
Forth step: Display the values in the Scorecard
After building the cube, let’s configure PerformancePoint Services 2010 to correctly display our new non-aggregated measure.
Create a “New Actual” row in the KPI that you would like to add the “Posting Date” measure. In our example this New Actual row will be called Last Update Date:
Configure the Data Mapping as follows:
- Select a measure = Posting Date: Just choose the measure that we configured before.
- Aggregate members by = Maximum: In theory you shouldn’t configure this property to Maximum as it already comes from SSAS, but I like to knot everything tight J
Note: You can add dimensional filters. In the screenshot above are hidden for privacy purposes.
With this you will have the last update (maximum) date (BUDAT) showing in the scorecard:
In the next post I explain how I did the Units (UOM) field as it is a bit more complicated that the date.