MDX Series Pt 5: Smart View Does More Than You Think

MDX Series

This is post number five in a six-post series. A brief overview of the series:

• Post #1: Connect to Sample.Basic and execute two basic queries
• Post #2: Introduce functions using a single dimension
• Post #3: Introduce multiple dimensions and CrossJoin
• Post #4: Define sets and tuples to add a deeper understanding of how MDX works
• Post #5: Discuss how to avoid almost all frustration using MDX in Smart View
• Post #6: Provide a strategy for refreshable MDX using a macro

It is possible to go years using Essbase and Smart View without using MDX queries. But the option is hidden in plain sight, on the Essbase tab of the ribbon, in an unassuming sub-menu called “Execute Query”:

This brings up an even more unassuming dialog box, giving little indication of all of the powerful things that this option allows:

For more detail on connecting to an application and executing queries, see the first post in this series.

Let’s Talk About the Good

It turns out there are two key ingredients to working with MDX in Smart View. The obvious one is that you need to know MDX syntax. The less obvious one is that you need to know about some of the quirks of Smart View that are specific to MDX.

The really good news is that there is nothing that Smart View does that cannot be worked around. And MDX is really powerful, so knowing that there are no unavoidable problems is a bigger deal than it might seem at first.

Having said that, there are some issues, which can be avoided. Before we talk about why these things lead to a good Smart View MDX experience, let’s simply outline what are the steps that lead to a good experience:

• Write your query in Notepad++, or any text editor.
• Copy and paste the query into the Smart View dialog using a connected worksheet.
• Avoid using the WHERE statement. (A query with WHERE executes, but it can cause the POV dialog to appear.)
• If you are going to reuse the query, save it as a comment in the A1 cell.

The Road More Traveled By

Let’s ignore the recommended steps for a good experience. If we were to jump in and use MDX in Smart View without any idea about what to expect, what would that look like?

Using a sheet connected to Sample.Basic, we select Query > Execute MDX.

The dialog appears, but it is blank.

OK, that’s not ideal. Even a simple query based on the connected database would be great. Still, we can remember that the basic syntax for a query is this:

The Help button does link out to a simple explanation of this, so we are not totally without easy access to a reminder of this information.

Execute a Query

So, we enter a query:

And click Execute:

Oops. We entered “Years” instead of “Year”. Click OK.

The dialog disappears. Everything disappears. We are returned to our worksheet.

Where is the opportunity to fix our query?

There isn’t one.

MDX in a Text Editor

Not providing an opportunity to fix a query is the single-greatest problem with MDX in Smart View right now. To get around it, simply write your query in a text editor, and copy-paste the text into the Smart View.

That simple step means that you will always have access to your query while it is a work-in-progress.

MDX in Notepad++

If you haven’t ever used Notepad++, or another advanced text editor, then you owe it to yourself to try it. There’s a ton of benefits, but the most important ones for me are:

• Work-in-progress documents are always available. You can close and re-open Notepad++ without needing to save everything, and it will be there the next time you open it.
• Brace matching (selecting one open- or close-brace highlights its partner)

• Syntax highlighting!

The last thing is really nice. There are built-in syntax options for common languages like Powershell, SQL and Java. Alas, Essbase is not built-in, but Notepad++ allows custom highlighting, and a quick Google search for “Essbase Notepad++” will bring up resources that allow you to add Essbase highlighting to Notepad++. (That is what has been used throughout this series to create syntax-highlighted images.)

The Dialog

In closing, here are a few observations about the MDX query dialog in Smart View. This information is correct as of Smart View version 11.1.2.5.600:

• The dialog is small and cannot be resized.
• The dialog is blank, and gives no indication of what a valid MDX query looks like.
• The dialog does not offer syntax highlighting, or a syntax validity check.
• The dialog supports Cut-Copy-Paste (shortcuts: Ctrl + X, Ctrl + C, Ctrl + V) but not Select All (shortcut: Ctrl + A).
• Upon execution, if the query fails, an error is displayed, but the query is not preserved.

Working around the Dialog

Of the observations in the last section, the almost-deal-breaker one is that invalid queries display an error, and then do not allow you to fix the problem.

Writing our query in an external text editor works around this problem. It also solves the issue of the small size of the dialog, and provides syntax highlighting (if you use Notepad++ and add the appropriate resource).

It does not directly solve the problem of not having a valid sample query, or examples of functions. However, creating a few of your own sample queries and storing them as an easily-accessed file in Notepad++ can solve that issue.

You can also save your queries in Excel, once they are written and working. I find the easiest way to do this is to add a comment to the A1 cell.

When I store a query this way, I also add at least one comment, using the standard Essbase /*comment syntax*/. The end result is something that reminds me what the query is for, but which I can also copy wholesale from the Excel comment into the MDX query dialog (since comments are ignored, as expected, when the query is executed).

Final Quirk – The WHERE Clause

There is one final oddity. Many of the examples in this series use the WHERE clause, but this is done because it simplified the query a bit, so that we can concentrate on more important things. But Smart View has three unfortunate behaviors when WHERE is used.

The first time it is used, it hides row 1, as if it is going to use the POV toolbox, except it does not actually show the POV toolbox.

If we unhide row 1, we see the second problem, which is that it has duplicated our comment. In fact, it will duplicate any formats or comments applied to the first line.

The second time it is used, it manifests the final behavior, which is that the POV toolbox does appear:

If you prefer the POV toolbox…maybe this is not a problem for you. If you, like me, prefer to avoid the POV toolbox at all costs, then do not use the WHERE clause. You can eliminate the WHERE clause from any query by using the CrossJoin function and shifting the POV dimensions onto the columns.

Up Next

In our final post for the series, we will take a look at a simple macro, which can be combined with the A1-cell-comment strategy in order to make MDX refreshable with one click.

Postscript for Oracle

As a very brief aside, if anyone connected with Smart View development happens to see this, the following simple tweaks would have significant usability benefits:

• Make the MDX query dialog resizable, and larger by default.
• If the Smart View sheet is connected, prefill the dialog with a very simple query that is valid for the connected database. /* Normal comment syntax could be used to indicate the query is just an example. */
• If the query fails, then after the error message, display the query dialog again, allowing the query to be fixed.
• Do not hide the first row and make the POV toolbox come up when a WHERE statement is used. Just honor the current settings of the sheet and only show the POV toolbox if it is already enabled.