Search This Blog

Monday, December 21, 2009

A different approach to modeling units

SQL Server 2005 | SQL Server 2008

While units are a built-in functionality in some OLAP databases, we need to take care of them in SSAS on our own. Usually I model units as a dimension so that the facts are linked to the unit they belong to. However, units usually must not be aggregated (like adding up meters with liters), and therefore we would set the IsAggregatable property to false. The user first has to choose a unit before the result is displayed. In some cases this may not be that clear. For (local) currency for example the values are aggregatable as long as the currency is consistent which may or may not depend of the currency unit. As long as the selection (no matter of how the filtering was done) leads to a single currency it would be possible to display the result in local currency. Whenever there is more than one currency involved, the result cannot be shown.

Taking the usual approach with the non-aggregatable unit dimension you may find a situation like below:

image_thumb

For this example we have stores aggregated to some kind of store group with sales. As the currency is a non-aggregatable dimension you don’t see totals for the rows (just for the columns). Assuming we set our default member for the currency unit dimension to some currency not included (or to unknown like I did), you don’t see any value at all, if the currency unit is not included in the query:

image_thumb1

So everything’s fine with this approach. But usually we don’t analyze by currency and if we simply put it on a filter, we might miss values of the other currencies like in the following screenshot of the same sample cube (the value for ‘Other’ gives no hint that there might be other sales here too):

image_thumb2

So the idea could be to tell the cube to display the currency values as long as the displayed cell contains only one currency unit. For other cells we can only display a warning. The following screenshot shows the final result:

image_thumb3

Please note, that although we don’t have the currency unit included in this pivot table on either the axis or the filter, the cube knows that certain cells only result in EUR values and therefore can be aggregated while other cells consist of more than one local currency and therefore cannot be aggregated.

To explain how this can be done, let’s first look at the model of our sample cube:

image_thumb5

The currency unit is linked to the fact table using its primary key UnitID. For the cube we include this key is a measure to the cube… hey, this sounds weird, why should we use the surrogate key as a measure?? Well, we even use it twice, once aggregated by the Min function and one aggregated by the Max function.

image_thumb6

Now, for any cell, where “Minimum CurrencyID” equals “Maximum CurrencyID” we are sure that we only have one currency unit in this cell. This can be used in a calculated member. I set the visibility of the original measure Amount to hidden and add a calculated member like this:

CREATE MEMBER CURRENTCUBE.[Measures].Amount
AS iif([Measures].[Minimum CurrencyID]=[Measures].[Maximum CurrencyID],[Measures].[AmountLocal],NULL),
FORMAT_STRING = iif([Measures].[Minimum CurrencyID]=[Measures].[Maximum CurrencyID],
strtomember("[Currency].[Unit].&["+CStr([Measures].[Minimum CurrencyID])+".]").properties("Format String"),
""),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Sale' ;

As you can see we also take the format string (e.g. “$”#,##0.00) from the currency table to display each currency properly.

For the above screenshot I modified this code a little bit and returned the value 0 instead of NULL. I also used the text “multiple units” as the default format string for this value 0. I prefer the NULL value though as it is “more” accurate.

As long as you’re using Excel 2007 compliant format strings (as describe in one of my previous posts), everything should display properly in Excel too as shown in the following screenshot:

image_thumb11

Other clients can also leverage the formatting defined in our cube. For example in Reporting Services you could use the above formats if you refer to the FORMATTED_VALUE property instead of the VALUE property. So instead of

=Fields!Amount.Value

we would use:

=Fields!Amount.FormattedValue

Here’s a simple report based on the sample cube:

image_thumb8

3 comments:

  1. Can you please post the sample code.

    thanks

    ReplyDelete
  2. I want to say thank you for your great post.

    While I think this is a great method, being new to BI I am also curious on what you meant by "use a parent-child structure as this allows you to store data at different levels in the tree-like structure."

    Can you elaborate or point me to any write-up on the above method?

    TIA

    ReplyDelete
  3. In Microsoft BI (and in most other systems also) you can define a dimension to have a tree-like structure where each node contains a link to its parent node (parentid). This means that unlike "normal" dimensions you do have each level of granularity as a single node and therefore you can link your fact records against different granularity levels of your dimensions.

    ReplyDelete