As we know that Oracle released Essbase Cloud Service in March 2017. Since Essbase is in Cloud, we are able to use MDX to query and manipulate the multidimensional data from cloud now.  I think it might be a good time to discuss MDX.

 

Let’s do a quick intro about MDX. Just in case you are not quite familiar with it.

 

MDX stands for Multidimensional Expressions, which is a query language for online analytical processing (OLAP) using a database management system. You may have heard of SQL. SQL is a query language for relational databases. MDX is a query language for multidimensional database, like Essbase. On top of that MDX is also a calculation language, with syntax similar to spreadsheet formulas. By using MDX, we can query multidimensional data, build dimensions and cubes, create calculated members, define security restrictions, etc.

 

Back to the on-premise Essbase, we can use MDX through:

• MDX Script Editor in Hyperion Essbase Administration Services(EAS) console
• MaxL Shell
• Smart View

 

The MDX Script Editor in EAS:

 

When Essbase comes to cloud, unfortunately, EAS and MaxL Shell are not available for the cloud version. But we are still able to use Smart View to do MDX query. So now let’s check some examples.

 

The basic syntax of an MDX query is using SELECT, FROM, WHERE.

• SELECT: select Sets.
• FROM: define Cube.
• WHERE: filter Data.

 

Some quick notes and tips for writing MDX queries.

• Tuples are a single member from one or more dimension.
• Sets are multiple members from the same dimension or a group of multiple cells. Sets are enclosed in curly braces {}.
• An axis is a specification determining the layout of query results from a database
• Square brackets are required if the name has a space or is a reserved word [].

 

Comparing with SQL, SQL expects data to be in a row and column format. Data is returned in a two-dimensional tabular format. MDX queries can return data in more than two dimensions, which is called a Cellset. Cellsets are typically flattened into two dimensions for display purpose.

 

With the basic MDX knowledge, let’s link to Essbase Cloud Application through Smart View and do some simple MDX queries.

 

Link to Essbase application.

 

Update the POV and get sample data.

 

In order to make sure our MDX query works, we will delete the data from the Excel.

 

Go to Query, and the Execute MDX. This is where we are able to write and execute MDX queries.

 

Since in Smart View, we cannot validate MDX statements, it is better to validate the MDX statements from other MDX editors, then copy and paste to here. We are going to select the same combination as we were using earlier. Then Execute the MDX query.

 

Now we see that Measure is showing on column and Calendar is on row. Also, we get the same data as we saw earlier, which means our query works.

 

Let’s try another query. This time we would like to have Measure, Purchase organization, Product and Supplier on column, and Jan—16 on row.

 

We get the result as we expected.

 

Hope you have some basic ideas on what is MDX query and how to use MDX query in Smart View. In the future blogs, I will discuss more advanced ideas and tricks on MDX. See you next time.