Panorama Business Intelligence

At Panorama we have a saying that “MDX is like chess – knowing the rules doesn’t mean you know hoe to play”.

Don’t believe it? Okay, let’s play!

If you don’t know much about MDX, these are the rules of your game:

1. Your pawns are called “Members”. Each member has a name, e.g. [Harry Potter] (get used to the square brackets limiting names in MDX).
a. Each member may also have a value depending on the current members on other dimensions. E.g. the value of [Harry Potter] from the “Customer” dimension might be “2″, indicating the two tooth pastes Harry bought, i.e. the current member of dimension “Product” is “Tooth Paste”, and the “Measure” is “Product Count” rather than “Money payed”.
b. You may have a neutral member, called: [Measures].CurrentMember.

2. A group of members is called “Set”. Sets may have names, e.g. [Set A]. Sets may contain members or other sets, by wrapping them with curly brackets, using the following syntax: “{[Member1], [Member2], [Set1]}”. The empty set syntax is “{}”

3. You may generate a new set by iterating a set and performing operation on each of its members, using this syntax: “Generate ([Set], Operation ([Set].Current))”. E.g. if [Set] contains “{[USA], [Canada]}”, then “Generate ([Set], [Set].Current.Children)” will generate a new set with the states which are the children of each country, namely “{[California], [Washington], …, [British Columbia], [Yukon], …}”

4. Filter a set by a certain condition: “Filter ([Set], Condition)”. E.g. “Filter ([Customers], [Customers].Current > 2)” will filter out [Harry Potter], as its value is “2″, and the filter leaves only customers whose value is larger than “2″.

5. Instant-If syntax: “Iif (Condition, True-Expression, False-Expression)” means that if condition is met, then True-Expression is returned, otherwise, False-Expression is returned. E.g. what would return: “Iif ([Harry Potter] = 2, 1, 0)”? That’s right, since [Harry Potter] has a value “2″, the condition is met, and “1″ is returned, being the true-expression.

6. Use StrToSet(”…”) to convert a string into a set. E.g. StrToSet(”{[M1]}”) will convert the string to a set with single member [M1].

Memorize these rules, as they will be erased from this blog within 5 seconds…

Just kidding… Now the challenge:
You have one condition and 2 sets, [True Set] and [False Set]. You should return [True Set] if the condition is met, otherwise return [False Set].

Have a clue…? Recognize any rules with true/false expressions? … For example … rule #5…?

Well, that’s easy, you say. You propose:
“Iif (Condition, [True Set], [False Set])”

Good for you, it’s just that Instant-If shouldn’t return sets, only numbers, strings, or members.

Excuse me? You tried it and it worked?

You tried it on Yukon, right? Yukon (apart from being a territory in Canada), is the nickname of Microsoft Analysis Services (AS) 2005. However, it won’t work on the older version, AS 2000.

Any solution…? Like bypass the Instant-If/set situation by some conversion…? As mentioned in rule … #6?

Huray! You propose using StrToSet(…). But how…?

That’s right: StrToSet ( Iif (Condition, “[True Set]“, “[False Set]“))
The instant-if returns a string, and whatever string is returned, is immediately converted to a set.

That’s a jolly good answer, but still, there are MDX dialects which don’t support StrToSet, like SAP BW. Besides, what if the sets are huge? Conversion might be time & memory consuming.

Any other ideas…? Other rules with conditions…? Like … rule #4?

Great! You propose: Filter ([True Set], Condition)
You claim that if the condition is met – all members of [True Set] will pass the filter.

Alas, it’s very time consuming! Applying the condition on *all* members? Think of an improvement for the condition to be calculated only once (you’re in the right track, though).

Tough, huh…? Any rules you haven’t used …? For example … rule #3?

Ok, you suggest using “Generate” (by elimination, huh?). But do you know how?

Suppose you use “Generate” to generate [True Set] all at once, but only if the condition is met.
“Generate” is capable of generating [True Set] all at once, but it doesn’t do conditions. It iterates sets.

How can we benefit from that?

Let’s look at this phrase: Generate ([Set], [True Set])

This will generate [True Set] for each member in [Set]. So, if [Set] has only one member – [True Set] will be generated once. If set is empty – [True Set] won’t be generated.

Hey, this is exactly what we need. Can you smell the solution coming…?
We should control the number of members in the set: 1 for generating [True Set], 0 otherwise – depending on our condition. And what would be better for that purpose than our beloved … Filter?

Now we’re really close:
Generate (Filter ([Set], Condition), [True Set])

We use Filter to control the number of members in [Set]: 1 member for generating [True Set]; 0 members for generating an empty set.

But what is this [Set]?

Okay, it should contain only one member, but we don’t really care which. In other words, it should be neutral.

Rings a bell…? Any forgotten sub-rule…? Like … #1b?

Eureka! Using a set containing the neutral member { [Measures].CurrentMember }, The full answer is:
Generate (Filter ( { [Measures].CurrentMember }, Condition), [True Set])

This will generate [True Set] only once, and only if condition is met.

And what about [False Set]?

Answer: Lest use the opposite condition “Not Condition”:
Generate (Filter ( { [Measures].CurrentMember }, NOT Condition), [False Set])

Finally, how do we combine these 2 together?

Well, that’s easy: since both phrases generate sets (the first generates [True Set] or empty set, the second generates [False Set] or empty set);
And since a set may contain other sets, let’s wrap them with curly brackets:

{
Generate (Filter ( { [Measures].CurrentMember }, Condition), [True Set])
,
Generate (Filter ( { [Measures].CurrentMember }, NOT Condition), [False Set])
}

Whenever condition is met – [True Set] will be generated be the first phrase. Since the second phrase uses opposite condition – it will necessarily not be met, and an empty set will be generated consequently, leaving only [True Set]. If the condition is not met – only [False Set] will be generated, as you can figure.

Check Mate!!!