Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Assymetric Selects in MDX

Posted by thomasivarssonmalmo on June 24, 2007

After a few months away from AS2005 cubes and MDX, because I have been working mainly with Reporting Services projects, I thought of improving my MDX skills. Also my copy of "MDX Solutions"is falling apart so why not start with translating the code samples in that book to useful scripts with the Adventure Works cube. On page 18 in that book I found a MDX select that I changed to:
 
Select {([Date].[Calendar].[Calendar Year].&[2002],[Measures].[Internet Sales Amount]),
([Date].[Calendar].[Month].&[2002]&[7],[Measures].[Internet Order Quantity]),
([Date].[Calendar].[Calendar Quarter].&[2003]&[3],[Measures].[Internet Tax Amount]),
([Date].[Calendar].[Month].&[2002]&[1],[Measures].[Internet Freight Cost])} On Columns,
[Product].[Product Model Categories].[Category] On Rows
From [Adventure Works]

 
The interesting thing with this script is that you get what the book calls an assymetric result. Each measure will not be repeated for every time member. When you click on members in a client tool like ProClarity Professional 6.3 you will never get this result because ProClarity will always do crossjoins on dimension members that you put on rows or columns. It is the same with Excel 2007.
 
Assymetric results is new to me. I have created calculated members to produce this results earlier and I had no idea that I could write queries to achive this. Perhaps I have been to lazy and never thought of that most SSAS2005 clients do crossjoins of everything you put on an axis and ignores the other possiblity.
 
When I pasted the Select into ProClarity Professional it created the same assymetrical result as in management studio.
 
Should not these clients be able to switch on or off crossjoin because most reports are assymetrical?
 
Advertisements

2 Responses to “Assymetric Selects in MDX”

  1. Chris said

    A few client tools do in fact support assymetric sets, but you have to look hard for them. I agree it’s a very useful feature! You might also be interested in checking out the following bit of functionality in the AS Stored Procedure Project:
    http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=AsymmetricSet&referringTitle=Home
     

  2. Thomas said

    Hello Chris. Thank’s for the comment. I am really amazed that this is not a feature in ProClarity or Excel because it will make SSAS2005 better for reporting. I will have a look at the project.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: