Non-Pivot-Friendly MDX through Excel 2007 [Updated]

I am sooooo glad for Greg Galloway´s tiny little comment to this post.

Because we were looking for a solution to integrate MDX queries into Excel which are not very pivot-table friendly.

Why do you ever want to do something esoteric like that? Suppose you want to compare very specific data-points in your tuple-space with each other (a monthly real margin from one year in one store with a daily plan margin from a region in a completely different year). Then, your pivot table will necessarily cross-join all the individual coordinate components (year/month/day(s) of both years, affected region/store(s), plan & real measures) and come with a solution set that is orders of magnitude too large and cannot be reasonably filtered without going nuts.

That is where you should rather build up a small (remember: SMALL in the sense of aggregated fact data) pivot table, do a drill-though and then save the resulting query table as a nice template for your future experiments.

I have not been able to create that query table by any other means in Excel 2007!

Update:

There is another way for executing and displaying mdx described by Hilmar Buchta which might be more useful when you need full (VBA) control over the connection/command behaviour, e.g., when you first need to check whether the result is empty (and hence would destroy the layouted table structure).