Modelling for Measure Swapping 1

[RiverGuy]

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

Thanks

 

[MDXer]

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 [URL unfurl="true"]http://blogs.conchango.com/christianwade/archive/2006/06/23/MDX-Script_3A00_-Current_2F00_Relative-Period.aspx[/url]

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.

 

[RiverGuy]

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.

 

[MDXer]

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.

 

[RiverGuy]

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

 

[RiverGuy]

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

 

[MDXer]

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.