Search This Blog

Sunday, July 3, 2011

Same measure in different granularity

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

In an earlier post I wrote about handling different granularity in a dimension-fact relationship. This time I want to get back to this topic from the end-user perspective. To illustrate this, I use a very simple data model here:

Unbenannt5_thumb3

For this model, we have daily revenue data and monthly revenue plan data. Here is the link from the measure groups to the dimensions:

Unbenannt6_thumb1

In this example, both measure groups only have one measure: revenue. Since measures are a dimension of its own in the cube, measure names have to be distinct, so I named the measures “Revenue” and “Plan Revenue”.

Unbenannt7_thumb1

If your cube contains more data from other measure groups, things can soon be getting confusion with many measure groups. This post is about two common solutions in order to reduce the number of measure groups here.

Two approaches are shown here:

  • Combining both measures in one measure group by using a calculated measure
  • Introducing a scenario dimension

Before we start, one remark. The last post was about using translations in the cube the add more flexibility regarding the naming of the measures. Since the measures are clearly associated with a measure group you could be tempted to use the same name for both measures (which is possible for translations). This is how the result would look like in the cube browser:

Unbenannt8_thumb1

However, when using a Pivot client like Excel you usually don’t see the measure group anymore. A simple analysis could look like this:

Unbenannt9_thumb

So from your Pivot table you cannot tell which is the actual and which is the plan revenue. Therefore I recommend not to use the same name for different measures in the different measure groups.

Combining both measures in one measure group by using a calculated measure

One possible option is to add a calculated measure the the Revenue measure group. In order to do so, I renamed the plan revenue to “Plan Revenue Internal” and set its visibility to hidden. Then the calculation could be as shown below:

Unbenannt10_thumb

This is how the cube looks like in the cube browser:

Unbenannt11_thumb1

By “copying” the plan measure into the revenue measure group you only have one visible measure group left, while both values are still separated by the name.

As I started the post about the different granularity, how does this look like at the detail level? The following screen shot shows the date dimension at the day level:

Unbenannt12_thumb3

Assuming you have set the “IgnoreUnrelatedDimensions” property of the plan revenue measure group to false, only actual values are displayed here, no plan values. So this approach works really well.

Introducing a scenario dimension

Another common way for implementing this situation is to use a scenario dimension. The scenario dimension contains two entries: Actual and Plan. The data from the fact table FactOrder is linked to the scenario member ‘Actual’ while the data from the FactPlan table is linked to the scenario member ‘Plan’. This can be easily done in the data source view (DSV) as shown below for the plan table:

image_thumb

The fact tables are linked to the scenario dimension in the data source view:

t1_thumb

The scenario dimension should be set IsAggregatable=False (as it makes no sense to aggregate actual and plan data). Also we should provide a default element. This is shown in the screenshot below:

image_thumb1

For the cube, we only want to have one single measure revenue which is neither of the two existing measures. Therefore we make both existing measures invisible. In order to distinguish them from visible measures, I prefixed them with an underscore:

image_thumb2

Both measures have their Visible-property set to false. The only visible measure in this case has to be a calculated measure that takes the data from one of the two invisible measures depending on the chosen scenario. Since our scenarios are distinct and not aggregatable, we can simply add both measures (as one of the two is always zero):

image_thumb3

Choosing this model for implementing the different granularity, the cube shows only one measure:

image_thumb6

If the scenario dimension is not used, actual values are shown. You can still easily display both values by putting the scenario dimension on one of the axis:

image_thumb5

If you compare this screenshot with the one from above you will see the same values, only the presentation is a little bit different (first option had two measures, last option one measure and a scenario dimension).

Summary

Both options shown here enable us to have the same measure in two or more measure groups of different granularity. The second option with the scenario dimension looks a little bit more tidy from a technical perspective. However, sometimes the first option is easier to use, especially when you add other calculated measures with formulas that are combining two or more scenarios (for example a measure like ‘Actual to plan ratio’). For those measures it’s not possible to assign them to one scenario. On the other hand, choosing the scenario dimension can dramatically reduce the number of visible measures in your cube, which makes the cube easier to understand for end-users. So depending on the requirements and the structure of the data, one of the two options will be the best to choose.

2 comments:

  1. Hi, thanks for the post. It really looks awesome. By the way, i have a question, if i am getting the store weeks (i.e., whether the store exists or not), sales by each channel for the stores but when i try to see the data for Store Weeks and Sales it actually sum all the data. Sum Aggregation on Sales Data make sense but the aggregation on the Store Weeks is wrong. It should be 1 for the store despite of how many channels the data that we have. How can we resolve this issue? (Max and Min function doesn't make any sense for the data).

    Adil.Majeed@intellytics.com

    ReplyDelete
  2. cool step by steps, but the images are too blurry.

    ReplyDelete