Panorama Business Intelligence

It is well known that MDX is the standard language for querying OLAP servers… Today MDX is become the de-facto standard for querying most OLAP servers, such as Microsoft Analysis-Services, SAP BW, Hyperion etc.

In that case you might probably think that supporting one OLAP platform’s MDX means you’ve covered them all…. Is that the case?

nope!

The main objective of standardization is to make this happen (supporting all OLAP providers with zero effort). However, each implementer of the standard might interpret the standard “slightly” different.

How much does “slightly” mean?

Let’s look at an example for such a “slight” difference between Microsoft and SAP BW as an example. Think about the “Filter” MDX function;

Suppose you’re analyzing the sales amount of year 1997 across the various states in the US, such as California (CA), Oregon (OR) and Washington (WA):

1997

——

CA 70
OR 40
WA 20

Now, if you want to see only states which sold more than $30K. The MDX statement produced by your BI tool (such as Panorama NovaView) should be something like:

Filter (

{CA,OR,WA} ,

Year.1997 > 30

)

The filter function accepts 2 arguments: the first is a set of members – in our case the states, and the second is a numeric expression.

The filter iterates the members of the set, and check for each member if the numeric expression returns positive. If not – the member is filtered out from the set.

In our case, filter iterates the states and leaves only states which sold more than 30 in 1997 (that leaves CA and OR in our case):

1997

——

CA 70

OR 40

Now, suppose you want to see the same data, but divided to gender – Male (M) and Female (F):

1997

—–

M

CA 31

OR 17

WA 12

F

CA 29

OR 23

WA 8

Have you noticed that this is the same data? If you sum up M and F you’ll get the same results as before (e.g. for CA 31+29 = 50).

What if we apply the same filter we used before on this new table (or “cross-tab”, as we call it)?

We can easily see that the only number above 30 is CA in the context of Male (31). Let’s mark it as (M, CA) – which is called a “Tuple”.

Now, in the Microsoft world, if we used the same filter function we used before, we would get only this tuple – (M, CA).

However, SAP BW has a “slightly” different interpretation of the “Filter” function:

It ignores context!

In other words: it’s not aware of the Male/Female we nested before our states, and calculates the filter condition according to the sales amount seen in the first table.

That means that we’ll see CA and OR in the context of both Male and Female – so we’ll see many values lesser than 30, which we wanted to filter out.

SAP BW rationale states that if you want the filter condition to be calculated in the context of a certain “dimension” (in our case the gender dimension), you should explicitly add the dimension’s current-member to the condition:

Filter (

{CA,OR,WA} ,

(Year.1997, Gender.CurrentMember) > 30

)

This will order BW to calculate the values of Male, then of Female, and produce correct results.

Ok – you say – but that’s not *that* complicated.

Maybe so, but that’s just one small example. The above solution works only for a small portion of the cases. In other cases you would use the “Sum” function in order to add a member to context, or replacing “Count of Filter” with “Sum of IIF” – and other things you wouldn’t want to know about. Plus, you need a pretty “rough” mechanism to even understand which dimensions are found in your context! You would go through all that effort just to overcome the “slight” difference of “Filter”….

And let me tell you something, this is just one tiny example… there are many other functions and so many differences….

So this is my life, I learn and optimize our MDX support to be able to work across platforms. Hope to get some feedback on this note.