INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to handle calculated measures if no relationships with dimensions

How to handle calculated measures if no relationships with dimensions

(OP)
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

RE: How to handle calculated measures if no relationships with dimensions

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
?

RE: How to handle calculated measures if no relationships with dimensions

(OP)
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

RE: How to handle calculated measures if no relationships with dimensions

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.

RE: How to handle calculated measures if no relationships with dimensions

(OP)
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

RE: How to handle calculated measures if no relationships with dimensions

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close