LookupCube & Excel's "Select Multiple Items"

Hey,

I would first like to thank you for your articles. While I still consider I understand pretty much nothing to MDX, the ability to work by mimicking your examples has helped me quite a bit.

This is the firs thread of a few where I'm going to ask questions that may be a bit irrelevant (in which case I apologize in advance), but that I keep stumbling against. As it is, working on Multidimensional Analysis in general, I know generally exactly what I want to do, just not how to implement it properly, so needless to say I find myself struggling quite a bit.

Here's the first one...

I'm going to simplify my actual scenario so it is easier to understand. In short I have a couple fact tables:

One that tells me how many users visit different websites on a daily basis (therefore two dimensions: website and time)
One that tells me how many orders I've had on each website, on a daily basis as well, then breaks those orders into a third dimension that we shall call "scenario", for simplicity.

The idea is to come up with an OLAP cube that can give me a "Conversion Rate" - that is, for a given website group, time range and scenario selection, I might have 200 orders, for the same website group and time range I had 10,000 visits, therefore my conversion rate is 200/10,000 = 2%

Easy enough, right - conceptually?

So, obviously the problem I face is that I have no visit distribution long my scenario dimension. In this case this is fine because regardless of the scenario selection the resulting conversion rate does make sense. Consider for instance that the scenario may be "type of product bought"
If I select (All) scenarios I get the answer to the question "out of all the visits, what's the percentage of people who bought anything", if I select a specific scenario (product type), I get the answer to the question "out of all the visit, what is the percentage of people who bought this specific product type". Allows me to answer something like "1% of all the visitors on a specific site buy this type of product"

My first implementation of this used the LookupCube function, just discovered and understood reading your article. Basically I have a Calculated Member defined as:

[Measures].[Conversion Rate] AS...

[Measures].[Order Count]/LookupCube("session_data","([Measures].[session Count]," + [Time].CurrentMember.UniqueName + "," + [Website].CurrentMember.UniqueName + ")")

Works perfectly... and happy as a kid who just found a bag of candy I started working on the Excel report to display the data, chart it, etc.
Until I selected the Time dimension at the page level - I have data in there for 5 years and I only wanted to see the last two years, so I checked "Select multiple items" and crossed 2004 and 2005, to get #VALUE in the conversion rate fields (and a lot of other cryptic characters in the back of my mind!).

I eventually traced this down to the following: Excel creates a session-based named set for my time selection... therefore, the LookupCube call looked like: LookupCube("session_data","([Measures].[session Count],[Time].[XLS_QYZ],[Website].[All])")

[XLS_QYZ] being the dynamically defined named set which obviously means nothing at all in the Lookup call unless I can transfer the definition along my query – couldn’t figure this one out!
Is there any workaround to this, using the LookupCube function?

I eventually found an alternate solution that works perfectly regardless of Excel's MDX re-formatting, using a calculated cell formula. Basically I create a Virtual Cube made of both the order_data and session_data cubes and redefine how the session count measure is done for the children of the Scenario dimension. This looks like this:

Subcube: {[Measures].[Session Count]}, DESCENDANTS([Scenario].[All])
Condition (actualy should be useless but... as I mentioned, I still understand very little!): CalculationPassValue([Measures].[Session Count],0) = NULL
Calculation Value: CalculationPassValue(([Scenario].[All],[Measures].[Session Count]),0)

So really I quite answered my question, but there a slight problem with this as well. For one thing I was obliged to rename a slew of dimensions because the formulas where too long and MSAS would give me a parsing error (my "real" case has about 79 dimensions...). In addition, I find it a bit sad that the LookupCube function is, in my scenario (I want to deliver reports through Excel), utterly useless. The problem I'm having with the calculated cell solution is the object of my next post (to separate the issues clearly)

Am I missing something? Is there a way to use LookupCube and allow it to pass forward session-based named set definitions?

Thanks for your help,

Seb