Home > MDX Challenges > MDX #13 – Use Crossjoin() to Get the number of cars each customer owned

MDX #13 – Use Crossjoin() to Get the number of cars each customer owned

In a previous post, MDX #11 – How to get number of cars each customer owned using Properties() function?, I used the Properties() function to get the number of cars each customer owned.

There must be another way

Is that the only way? We (programmers/developers), with a trademark of being curious, never stopped thinking that “there must be another way”.

Here is another way using the Crossjoin() function. Its purpose is the same as in TSQL, to produce cross product of one or more sets (tables). Unlike in TSQL where cross join is sparsely used, Crossjoin() is one of the most used functions in MDX.


Comparing to the result from my MDX #11 post, the above screen shot shows the same returns, except that the number of cars owned no longer has a proper column name.

Although this query worked, I have more questions to answer now.

If Crossjoin() has the same purpose as in TSQL, why the result didn’t show the combination of each customer with all the possible numbers of cars?

If it does, the result wouldn’t be correct. The MDX engine must know something so it would not mechanically create every possible combinations. This “something” that the MDX engine does very well is commonly referred to as the “Auto Exists” feature.   

  • “Auto Exists” applies whenever multiple hierarchies from the same dimension are referenced.
  • When “Auto Exists” is at work, the resulting set is filtered to only the non-empty sets.

There are not many articles about the “Auto Exists” feature. My only explanation for the lack of more information is that the feature is so “intuitive” that most experts feel no need to mention it.

Are we using the [Number of Cars Owned] as an attribute hierarchy?

Yes, it is used as an attribute hierarchy in the above query rather than a member property as in MDX #11.

Is the * a short hand version of Crossjoin()?

Yes. Check out the MSDN MDX reference here: http://technet.microsoft.com/en-us/library/ms144816

Categories: MDX Challenges Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: