Date as a Measure

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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:

  1. Select a measure = Posting Date: Just choose the measure that we configured before.
  2. 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.

Advertisements
Tagged with: , , , , , , , ,
Posted in SSAS
4 comments on “Date as a Measure
  1. Good site! I really love how it is easy on my
    eyes and the data are well written. I’m wondering how I might be notified whenever a new post has been made. I have subscribed to your feed which must do the trick! Have a nice day!

    • jbartual says:

      Thank you so much for subscribing. I don’t post too often as I only post things that are difficult to find elsewhere or that took me a bit of time to figure out and I want to keep them here as a reference for the future.

  2. Vincent says:

    Hi,
    Do you know if it is possible to be in data entry with date as measure?

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

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

Join 24 other followers

Categories
%d bloggers like this: