Properly showing values for unrelated dimensions (IgnoreUnrelatedDimensions)
SQL Server 2005 | SQL Server 2008
There have already been some posts about unrelated dimension handling, but I think, the topic is worth being visited again. So this is yet another unrelated dimension post (yaudp).
A good summary about this topic can be found in this post by Benny Austin, however there are some drawbacks not being mentioned there and my post today is focused on those drawbacks and how to deal with them.
If you are short on time here is a quick summary of the following text:
Setting IgnoreUnrelatedDimensions to false can make cube browsing easier to understand, but
- Take care with default values in unrelated dimensions: As the dimension is not related, no value will be shown.
- Take special care of dimensions with the IsAggregatable property set to false (no ‘All’ element): If such a dimension is unrelated, setting IgnoreUnrelatedDimensions to false has no influence for this dimension
- Role playing dimensions are considered as different dimensions. If one dimension role is related and another role is unrelated, the setting IgnoreUnrelatedDimensions=false will not show data for the unrelated role(s) of the dimension.
- Calculated measures associated with a fact table do not depend on the IgnoreUnrelatedDimensions setting of the measure group they have been assigned to but only to setting of the measure groups that they are generated from.
If you say ‘yes, I knew all this’ I suggest stop reading here. If not, here is the detailed explanation of the above bullet points.
Setting IgnoreUnrelatedDimensions to false can make cube browsing easier to understand…
For my samples I’m not using Analysis Services (as we have to do some changes to the model) but a very simple OLAP model that looks like this:
Think of the model as being used for a web shop. We want to sell the six products from the fabulous novel “The Deadline” by Tom DeMarco using a web shop and compare our sales with the number of web site visitors for a given day. In order to see some interesting effects of the IgnoreUnrelatedDimensions topic, we have a role playing time dimension DimDate for our web site orders (order data and delivery date).
So, here is the dimension usage screenshot:
As you can see, the measure group for our web site visits is not linked to delivery date or product. Therefore we get the following result if we want to analyze web visits by product:
Most people would find this result confusing. If you are not absolutely aware of the dimension usage, seeing the same number of web visits for every product could lead to a wrong understanding.
A common work around for this behavior is to set the IgnoreUnrelatedDimensions property of the measure group Web Visits to false as shown below:
This causes cell results from unrelated dimensions to disappear as shown in the following screenshot:
This looks much better. So why shouldn’t we always use this?
Take care with default values in unrelated dimensions
The first problem with our design appears if we define a default member other then ‘All’ for the product dimensions. I am a special fan of Quickerstill, so let’s make this the default product:
Please keep in mind that there are other methods of setting the default value (cube script, role security definition) that will have the same effect. Since our web visit measure group is not related to the product, we now get now value returned when we simply use the measure “Number of visits”:
Same happens for the following MDX query:
- select [Measures].[Number Of Visits] on 0
- from [OLAPSample]
The reason is that although we don’t have the product dimension in our pivot table, the default member still acts as a filter and as you can see from the previous screenshot of the pivot table, there is no value for ‘number of visits’ for the product members. The only way to get a value for the Number of visits is now to explicitly set the product dimension to its all member. I’m using MDX here in order not to get confused by side effects caused by the way the built-in pivot component generates the sums:
Take special care of dimensions with the IsAggregatable property set to false…
After looking at the following example I would have expected that removing the ‘All’ member from the dimension would result in no values are shown in any case. Removing the ‘All’ member is simply done by setting IsAggregatable to false:
But now the query above results in the following:
As expected, we don’t see the total any more. As I haven’t expected, the result is exactly the same as if we had set IgnoreUnrelatedDimensions to true (default). So in this case the setting for IgnoreUnrelatedDimensions is ignored.
I would still doubt the need for the default member in many cases. It could be an indicator that the fact table isn’t properly modeled (if we have to exclude certain fact values ‘by default’) or they might be able to be replaced by calculated measures.
Also, the problem with the default member does only exist, if the dimension is not related. In some cases you can fix this. For example, if you have a scenario dimension (actual, plan, forecast etc.) with different fact sources and with a default of ‘actual’ you can link all the fact sources to the matching dimension member (although your actual data source does only contain one scenario).
Unrelated role playing dimensions…
In our sample we have two roles for the time dimension (order date and delivery date). However, web visits are only linked to one of them (order date). Therefore analyzing the web visits by the other (unlinked) role has the same effect as with any other unlinked dimension: The values are suppressed. For the following screenshot, we took the Year from the delivery date dimension:
Regarding the IgnoreUnrelatedDimensions setting, calculated measures are always calculated with the settings of the measure groups used in the calculation. The ASSOCIATED_MEASURE_GROUP attribute has no influence here. For example, let’s simply “copy” the measure “Total Price” from the order measure group to the web visits measure group and vice versa:
- CREATE MEMBER CURRENTCUBE.[Measures].[Total Price (2)]
- AS [Measures].[Total Price],
- FORMAT_STRING = "#,##0.00",
- VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Web Visits' ;
- CREATE MEMBER CURRENTCUBE.[Measures].[Number Of Visits (2)]
- AS [Measures].[Number Of Visits],
- FORMAT_STRING = "#,##0",
- VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Order' ;
The following screenshot shows, that each of our calculated measures (Total Price (2) and Number Of Visits (2)) behaves exactly in the same way like the original measure although they are part of a different measure group:
Setting IgnoreUnrelatedDimensions can be a good way of improving readability of olap results. However, things are getting tricky with unrelated dimensions that are having default members. In some cases you can get rid of the problems by changing the design (splitting fact tables, using calculated measures, relating unrelated dimensions if possible). Especially with non-aggregatable dimensions the behavior becomes strange as IgnoreUnrelatedDimensions doesn’t work at all in this case.