14.03.2010

# Effects of attribute relationship settings on calculations

Technical Value

SQL Server 2005 | SQL Server 2008

This post is again about attribute relationship. I recently saw a very good presentation by Michael Mukovskiy, a colleague and friend of mine, regarding attribute relationship and its influence on calculated members.

In order to keep things simple, I start with a very simple date dimension having the following attributes and relations: Let’s assume we also have a measure group “Sales” with a measure “Quantity”. For our cube we also want to display percentage of the sales with respect to the year (eg. January: 10%, February: 12% etc.). In order to do so, we need the quantity per year and for our simple example I just do the computation for this (the percentage can easily computed then).

To do so, we use the following cube script:

1. CREATE MEMBER CURRENTCUBE.[Measures].QuantityFullYear
2. AS ([Measures].[Quantity], [Date].[Year].currentmember),
3. VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ;

Opening the cube browser, one can see something like this: For this post it’s only important that the calculated member defined above computes correctly on every level of the date hierarchy: For each year we’re getting the total of the full year no matter what level in the date hierarchy we have on our axis. It’s sufficient to use [Date].[Year].currentmember in the calculation to do the trick. However, in order to understand the following example we have to look at the computation a little bit more precisely.

So let’s take a look at one of the query cells: Before our computation takes place, May 1, 2009 is selected on the date hierarchy so this is the context of our calculation. Because of our attribute relationship, this also results in changes for the other date attributes as shown below: You can verify this easily by defining calculated members that rely on each of the levels (eg.)

1. member QuarterName As [Date].[Quarter].currentmember.name

It might be a little bit surprising that a simple calculation like

1. (
2. [Measures].[Quantity],
3. [Date].[Year].currentmember
4. )

really gives the full year’s value because we’re also in the context of a specific month (May) and quarter (Q2) and even day (1). So one could assume that we would have to write our calculation like this:

1. (
2. [Measures].[Quantity],
3. [Date].[Year].currentmember,
4. [Date].[Quarter].[All],
5. [Date].[Month].[All],
6. [Date].[Day].[All]
7. )

As we saw from our example above, this is not necessary (although it gives the same result). The reason for this is that the reference to a specific member in the Year attribute again changes the context for our computation and in this case this results in all attributes preceding the Year attribute (in our case: Quarter, Month an Day) to be changed to All.

In more detail, the following rules apply for single attribute context changes (in this example the context change happens for the Month attribute): As we can see from the first rule shown here, a context change to a specific member results in all attributes that precede the changed attribute in the attribute relationship to be changed to All. So this does the trick for our computation. It doesn’t matter that we’re actually changing the year to the same value it had before as the context was 2009 and currentmember also gives 2009, so we’re changing from 2009 to 2009. It’s still a Any/all –> specific value change and therefore all preceding attributes are changed to All.

Up till now, this was only the prerequisite for this post. So now, it’s getting more interesting. Let’s assume everybody’s happy with our cube and it is used for some months. Nobody really remembers how our calculated member is defined and everything works correctly.

Then, one user likes to have a calendar week attribute included in the date dimension. Of course this is easily done and now our attribute relationship looks like this: Of course we could also model an attribute relationship between Week and Year (at least for some definitions of the calendar week…) and also define a hierarchy for this. But for our simple example let’s continue without.

So, we only changed the date dimension and deploy our cube because we would expect our calculated member from above to work properly after this changed (hey, we didn’t touch it). So, let’s take a look at the pivot table we used above: As you can clearly see, our calculated member still works fine for the year, quarter and month level but not for the day level of our hierarchy. In order to understand what went wrong here, let’s again take a look at a specific date, eg. May 1, 2009. Instead of giving the full year’s values of 1501, we only get 21 here. The context change to this specific date also results in our week attribute to change (as it depends on the day). In my method for computing the calendar week, it computes to week 18. The following screenshot shows the calendar week together with the day: For the cell of May 1, 2009 our calculated member QuantityFullYear is computed in the following context:

 Attribute Context Day May 1, 2009 Month May Quarter Q2/09 Year 2009 Week 18

Now the expression ([Measures].[Quantity], [Date].[Year].currentmember) is evaluated. Since [Date].[Year].currentmember is now 2009 (based on the context we’re in), we have a context change like in rule 1 above (although it’s again changing to the same value 2009 –> 2009). This forces all attributes that precede the year attribute to change to All. But our week does not precede the year, as it is kind of a branch like shown below (sometimes the visualization of BIDS helper is easier to understand compared to the built-in functionally): So for our computation, the measure Quantity is evaluated in this context:

 Attribute Context Day All Month All Quarter All Year 2009 Week 18

This means, we’re only getting the aggregated quantity of week 18 which can be easily proofed by looking at our last pivot including the week (4+2+6+0+0+3+6=21).

Although we made no changes to the calculation itself, it doesn’t work properly anymore after our change for the attribute relationship. This is just another example that shows, that you really need to take care of your attribute relationships and also need to fully understand the consequences on calculations. Even worse, it is hard to find such problems as our change to the cube happened at a totally different part so nobody expects the calculation to fail afterwards.

I also recommend establishing test queries to assert the functionality of all computations. Such test queries can be run from the ETL process and check, if all computations are still working after loading data into the cube (kind of a unit test idea).

For our problem with the computation, there are at least two possible solutions. In some cases you can simply create the missing attribute relationship. In our case we could create a relationship between the week of the year and the year attribute (assuming the definition of the calendar week allows doing so). Our attribute relationship for the date dimension would look like this then: In this case, the relationship between the week and the year forces the week to its All member when referencing [Date].[Year].currentmember.

If you cannot create such a relationship, we have to force the week to the All member manually in the calculation::

1. CREATE MEMBER CURRENTCUBE.[Measures].QuantityFullYear
2. AS ([Measures].[Quantity], [Date].[Year].currentmember, [Date].[Week].[All]),
3. VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ;

The result for both solutions gives the desired result on every hierarchy level of the date dimension: Teilen auf