Search This Blog

Sunday, April 25, 2010

Pie chart with ‘others’ category (collected data)

SQL Server 2005 | SQL Server 2008

Pie chart with too many categories don’t make much sense. The following screenshot shows the order count from the AdventureWorks OLAP database by subcategory (no selection on date here for this example) as a pie chart:

image_thumb4

Fortunately, SQL Server 2008 Reporting Services adds a feature to collect all slices below a certain threshold (either as a fixed value or as a percentage) as shown below:

image_thumb3

To get this result you have to check the custom attributes (properties) of the chart series (either by selecting the pie itself or by choosing the chart series in the property box picker)

image_thumb6

In our example I collected all slices with a value of less than 3% to one single slice with the name ‘Other’. You can even show the other values as an exploded pie chart (although I think it’s more confusing).

In cases where you want to show just a certain number of slices (instead of using a threshold) or if you are using Reporting Services 2005 which doesn’t support the collected slice, you may do the collection by MDX:

WITH
SET SelectedSubCategories AS
TopCount
(
Order
(
[Product].[Subcategory].[Subcategory]
,[Measures].[Order Count]
,DESC
)
,10
)
SET OtherSubCategories AS
[Product].[Subcategory].[Subcategory] - SelectedSubCategories
MEMBER [Product].[Subcategory].[Other] AS
Aggregate(OtherSubCategories)
SELECT
[Measures].[Order Count] ON 0
,NON EMPTY
{
SelectedSubCategories
,[Product].[Subcategory].[Other]
} ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE;

The idea is quite simple. First, you create a set with the number of slices you want to see (I called it SelectedSubCategories here). Then you can simply get all other categories using a set minus operation (I called it OtherSubCategories here). Finally you create the ‘Other’ member in the dimension as an aggregate of the last set.

image_thumb7

Of course, you can even make the parameter for the number of slices a report parameter so the user can choose how many slices are shown in the diagram.

Sunday, April 4, 2010

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:

image_thumb1

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:

image_thumb3

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:

image_thumb8

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:

image_thumb5

This causes cell results from unrelated dimensions to disappear as shown in the following screenshot:

image_thumb9

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:

image_thumb151

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

image_thumb161

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:

image_thumb17 select [Measures].[Number Of Visits] on 0,
[Product].[Product].allmembers on 1
from [OLAPSample]

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:

image_thumb91

But now the query above results in the following:

image_thumb10 select [Measures].[Number Of Visits] on 0,
[Product].[Product].allmembers on 1
from [OLAPSample]

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:

image_thumb11

Calculated measures…

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' ;

image_thumb16

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:

image_thumb15

Conclusion

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.