“I made a trojan in MDX script!”
Although I deliberately timed this post to be published on my blog on 1st of April, this is far from a joke. And that trojan is a good one, for the benefit of all, as you’ll see soon.
While preparing my presentation about the potential use of Axis() function in calculated measures, I came up to a very interesting discovery: I wrote an MDX expression that allows the use of Axis() function inside dynamic sets in MDX script! Since the use of Axis() function is not supported in dynamic sets, I named the expression Trojan horse for dynamic sets, because of how it tricks the engine and makes Axis() function pass through.
Lets say a few about dynamic sets. Their name suggest they are evaluated dynamically. Furthermore, MSDN definition says they are evaluated everytime they are used in a query. But all that is simply not so. They are actually less powerful (about what I just googled one post from Chris Webb) because they get evaluated only once, before evaluation of cells (where all the fun is usually happening).
Dynamic sets are evaluated much sooner in query execution phases – after (which means in the context of) slicer and subselect. And that brings us to a final conclusion. As opposed to static sets that are static all the time, dynamic sets are just fine-tuned according to slicer and subselect of a query, but are pretty much static when it comes to evaluation of cells on the intersection of axis. In short, they behave just like query defined sets. And I consider them static. Get the idea of problematic name?
I don’t know where I’ve read it, so I made a short search on the net in order to present some proof for that. The best article on that subject that I come upon in a relatively short time is one older post from Mosha (of course). Since I haven’t read it yet and it looks very related to the thing I’m about to show you, I’ll read it now in order not to repeat things here.
Ok, I read it. That thing about subselect is nice.
Now let’s get back to what I ment to present you.
While being active on MSDN SSAS forum, I came up to this thread, where Bryan C. Smith nicely pointed out to me that my advice about the use of Axis() function simply doesn’t hold since it’s not allowed in MDX script. I played with it for a while, but encountered some problems, so I quit and concured with him.
But, being persistant (not to say stubborn) as I am, I took another chance as soon as I saw the first opportunity for that, a time when I was deeply into that issue – preparing a session about Axis() function. And I succeeded! Yes!
If you need a reason for it, I mean, reason why I wanted to do it, just take a look at that thread – making dynamic sets evaluate according to a measure placed on the opposite axis. Isn’t that great possibility? I mean, people ask for it, yet it isn’t supported. So, what do we do? Wait for the next release, for the new Extra Dynamic Sets? Yeah, right.
Why not think of something magical and use it now. This same moment. Why wait? All we need is some kind of expression that will evaluate differently when in MDX script as opposed to query context. But how? What differs those two environment?
When query executes, there are axis. When MDX script is evaluated, we have default measure in context. And iif function is optimized in various program languages not to evaluate on both paths, for performance reasons. Could it be my gate for trojan? Can I somehow make a trojan horse in MDX script that will pass the Axis() function hidden inside various iifs or other constructs and at the same time trigger properly when in context of a query?
Yes! It can be done. Here it is.
Create Dynamic Set CurrentCube.[Query measures evaluated] As
— aka Trojan for Dynamic Sets
iif(Measures.CurrentMember Is Measures.DefaultMember,
iif(IsError(Axis(1).Count),
iif(IsError(Axis(0).Count),
{Measures.CurrentMember},
iif(IsError(Extract(Axis(0), Measures).Count),
{Measures.CurrentMember},
Extract(Axis(0), Measures)
)
),
iif(IsError(Extract(Axis(1), Measures).Count),
{Measures.CurrentMember},
Extract(Axis(1), Measures)
)
),
{Measures.CurrentMember}
)
, Display_Folder = ‘Universal dynamic sets’;
Although it referes to Axis() function, this dynamic set evaluates correctly inside MDX script, yet returns a set of measures that are relevant for the query being executed when triggered from that same query. What does that mean?
If the query doesn’t contain any measure, a default one is returned. If the query contains a measure on slicer, that measure is returned. If a query contains measure in subselect, (only) first measure inside is returned (a default for that subcube). Here’s a possibility for improvement, for eager ones. If a query contains measures on columns, they are returned. If the query contains measures on rows, they are returned. All in all, it collects measures from query, wherever they are.
How is it achieved?
First, let’s get one thing straight. Everything you write in MDX script of a cube needs to be evaluated and checked for errors before it is deployed. Meaning, if you naively reference Axis() inside it, withouth cloaking it properly, you’ll see an error that it is not allowed to use the Axis() function and you won’t be able to deploy. So we need to cloak it somehow. And here’s how.
When that expression is evaluated/checked in MDX script, the first condition (always) evaluates as False. That’s the main trick. Why? The CurrentMember doesn’t exist yet, it exists only in query! Which means that the whole expression results as an empty set ({Measures.CurrentMember} in False part evaluates to null set). SSAS engine doesn’t check the construct in True part (performance optimization, luckily for us) and treats it as a deterministic expression – an empty set. In other words, this {}.
Everything’s clean and fine. We’re in (whisper men in the horse)!
Now, we could have put anything that will evaluate to False in the first condition but we didn’t. We carefully constructed that condition, because we need it to evaluate differently once it is called from a query. CurrentMember is a valid expression in the context of query. On the other hand, current member might or might not be default member. And that means we got ourselves a perfect condition, the one that evaluates either as True or False when triggered from query (which is ok, our condition is therefore not static, but a dynamic, smart one) and the one that is always False (static, which is ok) when evaluated/checked inside MDX script for deployment.
If the set is evaluated in a query, then depending on where the measures are placed in that query, a different path will be chosen. The first condition will be True in most cases. Only in situations when there is some other measure, not a default one, placed in slicer or subselect it will also evaluate to False. How come? Remember that dynamic set is evaluated in context of slicer and subselect, so that first expression catches scenarios when we have something there. In case it is False, we will grab the current member. That means a measure in slicer or the first measure in subselect (since when you make a subselect, the first measure inside becomes a default one automatically for that subcube). And that is exactly what we need.
In other words, the first condition will evaluate as False and take that path only for MDX script evaluation AND for measure in slicer/subselect other than a default one. Two flies, one stroke!
In all other cases the first condition will evaluate as True which means we don’t have measures in slicer or subselect. If that is so, they might be on axis. We test Axis(1) first, then Axis(0). If by some chance there are no measures on either of axes (a query without measures was executed), default measure is implicitely in slicer and that is taken as currentmember.
If measures are on axis, they will be extracted carefully.
That’s basically the operating mode of this trojan.
The explaination above was corrected and enriched 8 hours after the initial post, after a revision and after my memory about all the tips and tricks concerning this issue was refreshed. The key parts that were changed are evaluation paths.
Now, typical dynamic set looks like this.
Create Dynamic Set CurrentCube.[Top 10 Products for slicer measure] As
TopCount(
[Product].[Product].[Product].Members,
10,
Measures.CurrentMember
)
, Display_Folder = ‘My demo sets’;
It operates only on slicer and subselect, as we stated before.
However, this new dynamic set, an improved version of previous example, uses the first measure in trojan no matter where it is (as long as it is not on the same axis – it can be on opposite axis or slicer/subselect).
Create Dynamic Set CurrentCube.[Top 10 Products for first measure] As
TopCount(
[Product].[Product].[Product].Members,
10,
[Query measures evaluated].Item(0).Item(0)
)
, Display_Folder = ‘My demo sets’;
And finally, in order to test all this, you would need to add those definitions into your Adventure Works 2008 database (in the end of MDX script), and fire the following queries (or your variants of it).
SELECT
NON EMPTY
{
[Measures].CurrentMember
}
ON AXIS(0),
NON EMPTY
{
[Top 10 Products for first measure]
}
ON AXIS(1)
FROM
[Adventure Works]
SELECT
NON EMPTY
{
[Measures].[Order Quantity],
[Measures].[Reseller Sales Amount]
}
ON AXIS(0),
NON EMPTY
{
[Top 10 Products for first measure]
}
ON AXIS(1)
FROM
[Adventure Works]
SELECT
NON EMPTY
{
[Measures].[Reseller Sales Amount],
[Measures].[Order Quantity]
}
ON AXIS(0),
NON EMPTY
{
[Top 10 Products for first measure]
}
ON AXIS(1)
FROM
[Adventure Works]
SELECT
NON EMPTY
{
[Measures].CurrentMember
}
ON AXIS(0),
NON EMPTY
{
[Top 10 Products for first measure]
}
ON AXIS(1)
FROM
(SELECT [Measures].[Order Quantity] ON 0 FROM [Adventure Works])
SELECT
NON EMPTY
{
[Date].[Calendar].[All Periods]
}
ON AXIS(0),
NON EMPTY
{
[Top 10 Products for first measure]
}
ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Measures].[Order Quantity])
In case you have any doubts about results, feel free to make another query, the one where you’ll precisely write what do you want, so that you can verify whether Top 10 Products dynamic set calculates correctly or not.
Once you cast your fears aside, feel free to use it in any of your projects in case you need it.
Oh, one more thing. Excel 2007 and OWC are immune to it, they can not profit from this discovery. Only those OLAP clients/tools that generate clean MDX (no fancy stuff in subselect) and respect sets in general as a valid and powerful entity in OLAP (or let you at least edit the MDX if nothing else) are candidates for exploiting this trick. MDX Studio and CubePlayer are some of them known (and available) to me. Any information about other front-ends/tools?
More advanced MDX stuff coming soon :-).