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.

Essbase - Streamlining Data Management for Strategic Insights

Click New at the bottom left.

EPM Essbase - Powering Dynamic Financial Analysis

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.

Hyperion Essbase - Unleashing Business Intelligence Potential

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.

Cutting-edge Financial Analysis - Hyperion Essbase the Dynamic Solution

Click the elipses to the right of the Query property.

Streamlined Data Management - Unlocking Insights with Hyperion Essbase

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.