Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

AS2005, granularity and end users

Posted by thomasivarssonmalmo on July 1, 2007

This problem first appeared to me in a project where we have actual sales values on the month level and budget values on a higher level, quarter. In this project we have one measure group for actuals and one for budget in the same cube and we share most dimensions between the measure groups.
Below the quarter level we could see repeting values appear on the month members even if the measure group only have budget values for quarters as the leaf level.
It was nice to see that the Adventure Works cube have the same relation between sales tagets and the date.calendar dimension. You can check this relation and the granularity attribute in the dimension usage tab in the cube editor.
You can run this MDX select statement in management studio or ProClarity Professional 6.3, on the Adventure Works cube and see the problem:

SELECT { [Employee].[Employee Department].DEFAULTMEMBER } ON COLUMNS , { [Date].[Calendar].[Calendar Year].&[2001], [Date].[Calendar].[Calendar Semester].&[2001]&[2], [Date].[Calendar].[Calendar Quarter].&[2001]&[3], [Date].[Calendar].[Calendar Quarter].&[2001]&[4], [Date].[Calendar].[Month].&[2001]&[10], [Date].[Calendar].[Month].&[2001]&[11], [Date].[Calendar].[Month].&[2001]&[12] } ON ROWS FROM [Sales Targets] WHERE ( [Measures].[Sales Amount Quota] )

I had an idea that the property IgnoreUnrelatedDimensions, for the measure group in the cube editor, would help with this problem but it did not. I would need a property like IgnoreUnrelatedDimensionLevels but it does not exist in SSAS2005.

What finally helped was adding a MDX script in the calculations tab in the cube editor. Here the cube will set the levels on Calendar.Month and below to null instead of showing values that end users do not understand.

Scope ( Descendants([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month],SELF_AND_AFTER), [Measures].[Sales Amount Quota] ) ; This = Null ; End Scope ;



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: