MDX Series Pt 1: Your First Essbase MDX Query

Getting Started

Alright, you’ve decided to write an MDX query–wait, maybe you haven’t decided that. What can an MDX query do, you ask?

MDX stands for multi-dimensional expression, and you can think of it as SQL for Hyperion Essbase and Planning. Actually, the MDX standard was created by Microsoft, and it used in Essbase-type databases across the industry, but we mostly care because it is available for use in Essbase.

Within Essbase, MDX has three uses: first (and most importantly for this series), it is a querying language the works for ASO and BSO databases–that is, for all Essbase databases; also, it is the member formula language for ASO and finally it is used for ASO in other ways, such as the MaxL “execute allocation” command tree.

Who can this benefit? Anyone. MDX is often used by administrators because it is powerful and flexible, but power users and regular users alike can benefit from the ability to quickly and easily create grids that would otherwise require many standard Essbase operations (Zoom In, Zoom Out, Keep Only, Remove Only, etc.)

5 SIGNS YOU’VE OUTGROWN SPREADSHEETS

MDX or SQL?

Before we jump into how to write an MDX query, let’s take one minute to consider why Essbase is SQL-like, but not SQL. SQL is, by far, the dominant query language for relational databases. But Essbase is not a relational database, it is an “analytical processing” database, which is optimized for multi-dimensional data.

There is a native concept of hierarchy in Essbase, so that drilling up and down is possible, and so that the sum of children is correctly reflected in the parent. In a relational database, this would require multiple tables connected in a star schema pattern, and even then there will be challenges writing the queries to retrieve data at different places in the hierarchy.

So, in short, Essbase is a very different type of database than SQL is used for. We need a language that supports hierarchies, and is aware of the multidimensional nature of Essbase data. MDX borrows some SQL syntax as a starting point, but then it goes its own way.

The First Query

Let’s start with a basic connection to the Sample.Basic application. If you connect to that application in Smart View, the default view of the grid looks like this:

(click to enlarge)

This is a very basic grid. There are five dimensions. Market is on columns, Year is on rows, and Product, Measures, and Scenario are on the POV.

Let’s use MDX to create this same view. To use MDX, you must make an Essbase connection to the application in Smart View, and then use Execute Query on the Essbase tab of the ribbon. We’ll walk through those steps.

Note: As a prerequisite, this procedure requires that you have configured Shared Connections in Smart View, which is typically done using instructions provided by your system administrator. Additionally, you must be provisioned to use the Sample.Basic application. Your administrator can assist with both of these requirements.

Open a blank workbook.
Open the Panel.

(click to enlarge)

Open Shared Connections

(click to enlarge)

Use the drop-down to select Essbase.

(click to enlarge)

Expand the server (usually EssbaseCluster-1), then expand Sample, right-click on Basic, and choose Connect.

(click to enlarge)

Right-click again and select Ad hoc analysis.

(click to enlarge)

The basic grid appears.

(click to enlarge)

So that we can be sure that our MDX retrieve works, let’s delete the Measures member and the number value.

(click to enlarge)

To execute an MDX statement, go to the Essbase tab of the ribbon, then use the drop-down for Query and select Execute MDX.

(click to enlarge)

A dialog appears. Now we need our MDX statement, which is covered in the next step.

(click to enlarge)

The MDX statement that is equivalent to the original grid is written like this:

(click to enlarge)

Type or copy-paste the MDX statement and click Execute. The result is the same as the original grid.

(click to enlarge)

It Worked, but How?

There’s a number of things that stand out immediately about the syntax of the statement we just used. There are keywords, just like SQL. The keywords here are “SELECT” and “WHERE” as well as the keyword-phrases “ON COLUMNS” and “ON ROWS”. Additionally, we see that member names are surround by [square brackets], stand-alone members are surrounded by {curly brackets}, and lists of members from different dimensions are surrounded by (parentheses).

So far, so good. You can try swapping members out, for different grid views, but our toolbox is pretty limited right now. Still, we know enough to create a second valid query:

(click to enlarge)

Actually, that example sneaks in one fact that we did not know, which is that we need to add the (parentheses) when we put two dimensions on the columns and that we still need use the {curly brackets}. We’ll discuss why in more detail later in the series.

Up Next

That’s it for the first post in the series. The next post will address how functions work, so that we can begin to leverage the hierarchy-aware nature of MDX to create queries that zoom in exactly where we need them to.