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!
  • Students Click Here

*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.

Students Click Here


Modelling for Measure Swapping

Modelling for Measure Swapping

Modelling for Measure Swapping

My apologies for the thread title.  I'm not really sure how to concisely articulate my question.

This is SSAS 2008 Enterprise.

Anyways, I am working with some financial data.  This particular subject matter area is rather light in comparison to some of my other subject matter areas as far as the quantity of measures we have.  However, my company wishes to add some other "versions" of our measures which would be things like Budgeted Amount.

However, they have requested that they should not be required to swap out the measures in a query tool to view the alternate versions.  

For example, if someone has created a Pivot Table in Excel 2007 with Amount, MTD Amount, and QTD Amount, they would like to simply select "Budgeted Amounts" from a drop-down which would then update the query to return Budgeted Amount, MTD Budgeted Amount and QTD Budgeted Amount.

If possible, they would also like this to be a function of the backend and not the front end report itself.

The only idea I have come up with is to create a new "Version" dimension and utilize some new calculated measures which would return either Actual/Budget/To Be Determined measures based on the current member selected in the new Version dimension.  I would then hide the individual Actual and Budget and other measures.

The downside to this of course is that my entire subject matter area is based off of calculations of varying nested levels.  And of course, what do I do if the user has not selected a member from Version?

So my question is, has anyone worked with a similar situation, and how did you accomplish it?  Did you use something similar to my plan, and how did it work out?  Or did you discover that this is really a job for the front-end tool?  Or maybe I am misguided and SSAS already has a built-in "add ... intelligence wizard" to accomplish this?  (I'm not really an expert at all of the non-vanilla SSAS features).


RE: Modelling for Measure Swapping

I don't think you are really going to find a way that is not going to be mdx based.  You may want to look at how relative time persionds can be accomplished with clacs and dim members.  Here is a link to one http://blogs.conchango.com/christianwade/archive/2006/06/23/MDX-Script_3A00_-Current_2F00_Relative-Period.aspx

Essentially you create a dimension that holds the name for how you want the calc displayed say Actual and Budget are your members.  You would then using mdx script Check the diemnsion for the selected member and have the MDX display the correct measure or calculation.  It is a lot of work but will probably be very close to what you are after.

RE: Modelling for Measure Swapping

Thanks.  That pretty much sounds like what I mentioned above, so it's good to know others would deem it an adequate solution.  The more that I think about it, I think I may want to push back on the requirements and accomplish this on the front-end.  I really don't want to restrict someone to either/or.  If they have multiple sets of measures, they can mix and match Actual vs. Budget (who wouldn't need that?).  And--if I'm already enabling all those base measures, it would be pretty confusing and redundant to have those extra calculations and Version dimension.

RE: Modelling for Measure Swapping

You would hide your base measures and accomplish what you are after in calcs.  By using MDX script you would only need a single calc per measure and when sliced it would be determined what clac to use.  as for mixing and such you could to some degree.

RE: Modelling for Measure Swapping

I know, but what I'm saying is that for a scenario where they ask "how much have I budgeted this month, this quarter and this year for XYZ," then they can readily accomplish that with the calculated measures.

But if they then want to ask "how much have i spent versus how much did I budget this month for XYZ?," then they will be unable to do so.  The only way they could answer that question would be for me to unhide the base measures.  

So in that scenario, they haven't really saved any thing.  Instead of selecting one measure that says "Actual Amount" and one measure that says "Budget Amount," they might select the Version dimension member for "Budget," then select a calculated measure that says "Amount," then select a base measure that says "Actual Amount."


RE: Modelling for Measure Swapping

Hmm, I think I had a brain freeze.  I was so caught up in the original requirement of slicing in the drop-down.  I ran this by the business user, and he mentioned he could drop the version dimension into the columns....so we would see both figures side by side.  That of course makes perfect sense.  It's been a long day wink     

RE: Modelling for Measure Swapping

I guess I took a few ore words to say essential the same thing.  

I understand the long day syndrome, it has been an even longer year.

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!

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