December 8, 2010 at 7:36 am
Hello out there,
I have a SQL Server 2008 R2 Cube that allows to analyze the revenues of a company in a performance point services dashboard. There are three measures in the cube: revenue actual year, revenue last year, revenue forecast. These measures will be analyzed by default with the time dimension and the cost center dimension, that contains a hierarchy:
- company branch
--- department
------ cost center number
The measures from last year and actual revenue have the granularity of the cost center level, but the forecast value is just on the department level. Until here it works perfectly, because I defined a relationship in the DSV and in the dimension usage tab of the cube I chose the right granularity. Additional not all the cost centers are contained in the measure, but just the productive ones. This is solved through a member property that is used for the calculation. In the chart I filter the departments by using a named set based on the mentioned member property. This works too.
Now I want to avoid, that drilling down onto other dimensions in the dashboard analytic chart gives the all-member of the measure for the members if a relationship between the measure and the dimension it is not defined (defaul behaviour), explained here: http://hccmsbi.blogspot.com/2007/07/ignoreunrelateddimensions.html
So I set the IgnoreUnrelatedDimension Attribute of the measure group to false and processed. From now on the forecast measure that has not a foreign-key relationship to the cost center dimension, but a relationship in the DSV, shows no values. If I set the IgnoreUnrelatedDimensions attribute back to true it does well. The problem is the named set.
The dashboard designer created the following query:
[font="Courier New"]
SELECT
HIERARCHIZE(
{ [Dim Cost Center].[Hierarchy].[Company Branch].&[Branch1],
[Dim Cost Center].[Hierarchy].[Company Branch].&[Branch2]
}
)
ON COLUMNS,
{ [Measures].[revenue actual],
[Measures].[revenue last year],
[Measures].[revenue forecast]
}
ON ROWS
FROM [Cube]
WHERE ( [DimTime].[MonthYearHierarchy].[Month].&[201011]&[2010]&[11]&[4],
[NamedSetProductives]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR
[/font]
I can't change it, because then I'll loose the interactive functionality of the chart.
Is this behaviour caused by the different granularity? How can I solve the all-member result without using the Ignore
December 21, 2010 at 2:29 am
Hi,
I've found a solution to my problem, although I don't understand the behaviour.
The problem was the different granularity. I defined the forecast measure with a higher granularity (non key) and basically everything worked fine until I used the IgnoreUnrelatedDimension-property. I tried to define the forecast measure on the lowest granularity as possible and now it works.
The IgnoreUnrelatedDimension-property seems not to exclude just the unrelated dimensions, but also the members in the same dimension with another granularity!
Correct me if I am wrong. I don't know why... ??? :unsure:
Wish you all a merry christmas...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply