Creating an Essbase MDX Script

MDX 9 | Applied OLAP

MDX is a query language for multidimensional databases that can be used to execute grid retrievals. MDX expressions can also be used to define formulas on Essbase aggregate storage databases, to query metadata, to qualify member names, and to describe data or outline subsets for conditional triggers and other Essbase functionality. It is a powerful way of quickly getting what you need from an Essbase cube in exactly the way you need it.

In this post, I’ll show to create an Essbase MDX script in Dodeca. In the next post, I’ll show how to use this MDX script in a Workbook Script to create a Dodeca report that adapts automatically to changing Essbase hierarchies.

Click on the Admin Menu then select Essbase Scripts.

MDX 1 | Applied OLAP

Click New at the bottom left.

MDX 2 | Applied OLAP
Type CHILDREN_OF_PRODUCT_ROWS in the ID field. The Name field will automatically be populated.

Then select MDX as the QueryType in the dropdown box.

Then press the OK button.
MDX 3 | Applied OLAP
Select the Sample.Basic EssbaseConnection from the dropdown in the EssbaseConnectionID.

 

Note: This step is only necessary in order to test the script. Once the script is tested, you can remove the EssbaseConnectionID and the script will inherit the EssbaseConnectionID from the view on which it is used.
MDX 4 | Applied OLAP

Click the elipses to the right of the Query property.

MDX 5 | Applied OLAP
Enter the following query:

 

SELECT {} ON COLUMNS,
HIERARCHIZE({[[T.Product]], Children([[T.Product]])}, POST) ON ROWS

 

This MDX Query will return the Product token member and its children in a view. The hierarchize function tells Essbase to put the parent after the children.

Once you have entered the query, press the Edit TestTokens button.
MDX 6 | Applied OLAP

Type Colas in the [T.Product] definition text box and then press the OK button.

MDX 7 | Applied OLAP

Press the Test Query button at the bottom of the editor.

MDX 8 | Applied OLAP

Check the Show as Member List checkbox and then press the OK button.

MDX 9 | Applied OLAP

Click Commit at the bottom left.

MDX 10 | Applied OLAP

The Essbase script is now complete. This script will now be available to use in any Workbook Script within the current tenant.