How to handle calculated measures if no relationships with dimensions

[eo]

Hi,

Using SSAS 2005:

I have two dimensions which has a referenced relationship with fact table (measure group) A. It does however not have any relationship with fact table (measure group) B.
I do not show the actual measures from the measure groups A and B, but rather use them to create MDX calculated measures. When a user view the measure groups sourced from A by one of the two dimensions, all is okay, but when they view the measure groups sourced from B by one of the two dimensions (no relationship) then the total for the measure is "duplicated" for every dimension attribute. I want to show a NULL in these instances as it will be very likely that the cube user will use that number to mean something, is this at all possible??

I view my Cubes through ProClarity 6.2 BTW



EO
Hertfordshire, England

 

[MDXer]

If no relationship exists then the mease will be duplicated as the server doesn't know what values to display. The only way to get results from is to have a defined relationship. can you get any type of relationship?

Fact > Dimension < Fact
or
Fact > Fact > Dimension
?

 

[eo]

Hi MDXer,

I do not want to get results, I simply want to display a NULL as opposed to the duplicated values. NULL will explicitly show the user that measure group B cannot be sliced by the dimensions with which it has no relationships, rather than display anything.

If an indirect link means that there are relationships, then it would be...

Fact (measure group B) > Dim < Fact (measure group A) > Dimension > Dimension (with no known relationship to measure group B)


EO
Hertfordshire, England

 

[MDXer]

You could do this with calculations and MDX script and it would take some time but the downside is you could possibly hose your performance as you would only expose calculated measures to your users and calcs aren't indexed.

We handle this by distributing to users something like the dimension usage chart.

 

[eo]

Thanks for confirming that there is no straigh forward way to do this. I can now go back to the BI Analyst with a clear concience. Is your dimension usage chart a document stipulating which measures can and cannot be sliced with which dimensions?

EO
Hertfordshire, England

 

[MDXer]

It is the same as the dimension usage tab in ssas.