Monday, August 31, 2015

MDX Monday - Running MDX in Smart View

I've been a bit rough on Oracle for their MDX output -- I think with good reason. Trying to use it in a production batch environment is challenging. But there is some good news. Most of us have a tool at our disposal that gives beautiful MDX output: Smart View!

Simply right click on a connected database in the Connections Panel and select Execute MDX.

Then paste your query into the dialog box and click Execute. There's no need to include the database name specification or the semi-colon.

Here are the results. I got a neatly formatted Excel sheet and Smart View even put the where clause members into my POV! Very nice.

What happens when I use the calculated columns query from this post?

It works! Unfortunately you lose the calculated fields when you refresh the sheet.

It would be great if we could print member properties too. If we could do that, then we could do things like export dimensions in a parent child format directly into Excel. Let's try it. (This is the first query in the post with a PROPERTY_EXPR addition which should print each Market member's parent.)

<sad face> Smart View appears to ignore the properties returned in an MDX query. We don't get an error but we also don't get what we want. Hopefully there will be a future enhancement to Smart View that will add this functionality.

No comments: