Search This Blog

Sunday, March 14, 2010

Effects of attribute relationship settings on calculations

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:

AS ([Measures].[Quantity], [Date].[Year].currentmember),

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. member QuarterName As [Date].[Quarter]

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


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:


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::

AS ([Measures].[Quantity], [Date].[Year].currentmember, [Date].[Week].[All]),

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



  1. Hi Hilmar,

    I'm a new reader of your blog and it's great. This post is really good. By explaining the principles you described here I can describe why attribute relationships are so important (people usually leave all the attributes related to the key attribute).

    I think that in the case you described, the most accurate solution is to complete the missing attribute relationship between week and year. It will also help in other aspects.

    Thank you again,
    Miky Schreiber.

  2. Awesome! Thanks...

  3. Thanks !
    I did not think of such affect of attr relationships.
    And never meat.

  4. Lovely article... Just one doubt though. While I tried the day ->week->year and day->month->year as you have shown in the second last image I got a warning that cyclic relations are occurring. Did I do something wrong because I dont see that in your image?

  5. Jason Thomas wrote this comment which is not displayed in this list for some reason:
    "Lovely article... Just one doubt though. While I tried the day ->week->year and day->month->year as you have shown in the second last image I got a warning that cyclic relations are occurring. Did I do something wrong because I dont see that in your image?"

    You should create the corresponding user defined hierarchies in this case, so together with the attribute relationship described above you should also have hierarchies day ->week->year and day->month->year.

  6. Hi Hilmar,
    I have a very big problem, it might be small for you. I have searched many times for the cause/solution of this issue, but I couldn't get it.

    My issue is :
    We have a seperate tool to create reports based on SSAS cube. The report which we were created/using, it was working fine till last updation the cube. Recently we have processsed the cube with some minor changes, after that we started getting the error "key cannot be null. parameter name key".

    Please let me if you have any idea on this issue and what will be the solution?

    Thanks in advance

  7. Hi Mallesham, From how you describe the problem, it seems that you have a report parameter named 'key' which is set to not allow null values. If this parameter is visible when rendering the report, you have to enter a value for it. But I suppose you don't see this parameter at all. This might be because the parameter is defined as hidden or internal. I suppose the parameter gets its default value from a dataset which now returns null/nothing.
    In order to fix the problem, you should find this dataset (open report in Business Intelligence Development Studio, open the parameter 'key' and check the Default pane) and examine the query used to get the data (under datasets). It may be hidden. In this case you need to right-click the datasets folder, and then click 'Show Hidden Datasets'.
    Next you will have to find out why this query does not return values anymore (maybe your cube change causes this query to malfunction). Try to run the query directly from the query designer and check its filters and output.