Ragged Dimensions and Excel 2007

If you have ragged dimensions, i.e. some paths from the top node to a leaf node differ in length, and you are using Excel 2007 as a front end, then… bad news! It will not work.

A client tool can get the ragged dimensions from the server if it uses MDX Compatibility=2 in its connection string. See MSDN: Working with Ragged Hierarchies.
But Excel 2007 forces the connection string to contain MDX Compatibilty=1. There is a trick suggested here to force it to use MDX Compatibility=2, alas this has severe side effects. The trick is to put the following in the connection string:

Extended Properties="MDX Compatibility=2";MDX Compatibility=2;

In my tests it displays the All level somewhere in the middle of the hierarchy, shows the same node at different levels, etcetera. My advice: do not do this, as the Excel pivot table clearly does not know how to handle ragged dimensions.
In exceptional cases you could get away with a work around suggested by Chris here.

Comments »

No comments yet.

Name (required)
E-mail (required - never shown publicly)
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post