Analyze and Manage Data with MDX

MDX (Multidimensional Expressions) is a powerful data manipulation and querying language.

With MDX, you can:

Query and report against data and metadata in Essbase cubes
Insert data into an Essbase cube
Export data from an Essbase cube

An MDX query is a single MDX statement, having exactly one result set, that applies to a single cube.

An MDX report is a single MDX query, saved in the cube context. You can access MDX reports from Smart View and from the Essbase web interface.

An MDX script is a file, with an .mdx extension, that you can upload and then run from Jobs or in Smart View. Only MDX Insert and Export statements should be used in MDX scripts. To analyze grid data, use MDX reports rather than MDX scripts.

Topics:

Analyze Data with MDX Reports

You can store and render queries in the Essbase web interface using MDX reports. The minimum permission required to create a report is Database Manager.

Defining Layouts using the Ad Hoc Analysis tab may not always be the most efficient way to create a sophisticated report. If you know exactly what you want to query, you can use MDX to create a query to populate the grid.

To create an MDX report:

Log in to the Essbase web interface as a Database Manager or higher role.
On the Applications page, expand an application and select a cube.
Click the Actions menu to the right of the cube name, and select Analyze Data.
In the Analyze view, select the Reports tab and click Create.
Enter a name for the report, and optionally, a description.
In the Query field, enter an MDX query relevant to the current cube. For example:
```
SELECT
  {([West].children)}
ON COLUMNS,
  {([Diet].children)}
ON ROWS
```
The query must contain both row and column axes specifications. In other words, the query syntax must include specifications for both ON COLUMNS and ON ROWS, even if only an empty set {} is specified for one axis.

Because the context of Analyze Data is the active cube, we recommend that you omit the optional cube specification (the FROM clause) from MDX reports. Omitting the FROM clause allows for more flexibility—if the cube is copied or renamed, the report will work in the new cube.

Substitution variables are supported in MDX reports, but not runtime substitution variables. To use runtime substitution variables, save the MDX query as a script, and run it from Smart View using Calculate on the Essbase ribbon.
Click Validate to verify your MDX syntax, and then click Save.
From the Reports panel on the left, select the saved report to render a grid.

To learn more about MDX, see MDX and Writing MDX Queries.

Access to MDX Reports

How you work with reports depends on your cube access.

Users with, at minimum, the application-level role of Database Access can render saved MDX reports created by others. The data a user sees displayed in the report depends on that user's filter access.

In addition to rendering saved reports, Database Access users can export result sets in various formats: HTML, CSV, Excel, and JSON.

Database Access users can also view the MDX query that defines the report, by clicking the Actions menu next to the report name and selecting View.

If you have at least Database Manager role, you can use reports in the same ways that Database Access users can. Additionally, you can edit and delete reports using the Actions menu.

If you are a Service Administrator, you can additionally use the Execute As button to impersonate other users and check their data access. This can be useful for testing filters assigned to various users.

Examples of MDX Reports

The MDX examples in this section demonstrate special types of analyses you can perform, using MDX reports, that are not easily accomplished in the Ad Hoc Analysis view.

The following examples are designed to work on the Sample Basic cube.

Metadata Report

The following example returns only metadata (member names, but no data):

SELECT 
  {[Product].Levels(1).Members} 
ON ROWS, 
  {} 
ON COLUMNS

returning the grid:

Output of report shows Product members 100, 200, 300, 400, and Diet

Attribute Report

The following example uses, on columns, members from an attribute dimension:

SELECT 
 [Product].Children 
ON ROWS, 
 [Ounces].Children 
ON COLUMNS 
WHERE {Sales}

returning the grid:

Output of report shows, on rows, Product members 100, 200, 300, 400, and Diet. On columns, attribute members are shown: Ounces_32, Ounces_20, Ounces_16, and Ounces_12

Filtered Report

The following example uses a slicer (WHERE clause) to limit the query to Cola. Additionally, the Filter function limits the level 0 markets in the query to those that have a negative profit.

SELECT
  { Profit } 
ON COLUMNS,
  Filter( [Market].levels(0).members, Profit < 0) 
ON ROWS
WHERE {Cola}

returning the grid:

Output of report shows, on rows, Market members Oregon, Utah, Nevada, Oklahoma, Louisiana, Ohio, Wisconsin, Missouri, and Iowa. On columns, Profit is shown. The data values are all negative.

UDA Report

The following example shows Product data for Market dimension members that have a user defined attribute (UDA) of "Major Market." A slicer (WHERE clause) limits the query to include only Sales data.

SELECT 
  [Product].Children 
ON ROWS, 
  {Intersect(UDA([Market], "Major Market"), [Market].Children)} 
ON COLUMNS 
WHERE {Sales}

returning the grid:

Output of report shows, on rows, Product members 100, 200, 300, 400, and Diet. On columns, the major markets East and Central are shown.

Insert and Export Data with MDX

In addition to being useful for grid-based analysis, MDX also enables you to copy and update subsets of multidimensional data.

The MDX Insert clause enables you to update the cube with data, using a calculated (non-physical) member that you define using MDX.

The MDX Export clause enables you to save and export query results as data subsets that you can view or import later.

Insert and Export MDX statements can be run as saved MDX scripts.

To learn more about MDX Insert and Export, see MDX Insert Specification and MDX Export Specification.

Run MDX Scripts

Use MDX scripts when you need to execute Insert or Export data operations.

For analysis of grid data, use MDX reports. See Analyze Data with MDX Reports.

To use MDX scripts, select a workflow:

Write, Upload, and Run an MDX Script

Use this workflow to write MDX scripts in a text editor and upload them to Essbase.

Write the MDX script in a text editor, and save it with an .mdx extension.
Upload the MDX script to the application or cube directory under Files in the Essbase web interface.
Run the MDX script from Jobs or from Smart View, using Calculate on the Essbase ribbon.

Write an MDX Script in the Script Editor and Run It

Use this workflow to write MDX scripts in a script editor on the cube, and run them from Jobs.

On the Applications page, expand an application and cube.
From the cube's Actions menu, click Inspect.
Click Scripts, and then click MDX Scripts.
Click + to open a script editor.
Write the MDX script. A member tree and function list can help you.
Validate and save the script, then close the script editor.
Run the MDX script from Jobs (see Run MDX), or if using Smart View, using Calculate on the Essbase ribbon.

Create an MDX Script in Cube Designer and Run it

Use this workflow to create MDX scripts using an application workbook, and run them from Jobs.

In an application workbook, create an MDX worksheet. See Work with MDX Worksheets in Cube Designer.
Add a file name in the File Name field.
Indicate, in the Execute MDX field, whether to execute the MDX at the time the cube is created. Valid entries are Yes and No.
Add the MDX script below the Script line.
Save the application workbook.
Build the cube. See Create an Application and Cube in Cube Designer.
Run the MDX script from Jobs, or if using Smart View, using Calculate on the Essbase ribbon.

Guidelines for MDX Scripts

Use the following guidelines when working with MDX scripts.

Use MDX scripts to perform Insert or Export data operations.
For grid analysis, use MDX reports instead of MDX scripts.
MDX scripts can optionally include runtime substitution variables.
- To be usable within Smart View, MDX scripts with runtime substitution variables must use the XML syntax within the SET RUNTIMESUBVARS calculation command, including <RTSV_HINT>.
- To set a runtime substitution variable so that it calculates only the visible slice of data in Smart View, set the value of the runtime substitution variable to POV, and set the data type to member.
- When run from the Essbase web interface, your MDX scripts may use substitution variables, but not runtime substitution variables. To use runtime substitution variables in MDX scripts, you must run the scripts from Smart View, using Calculate on the Essbase ribbon.

Use Substitution Variables

Examples of MDX Scripts

The following are examples of MDX scripts you can run on the Sample Basic cube, either from Jobs or in Smart View.

MDX Insert

You can save this .mdx script and run it from Jobs or from the Calculate dialog in Smart View.

INSERT "([Measures].[Payroll])" TO "([Measures].[Revised_Payroll])"
INTO [Sample].[Basic]
FROM (
       SELECT 
            {[Measures].[Payroll]} ON COLUMNS,
                {Crossjoin
                  (Crossjoin(Descendants([Year]), 
                   Crossjoin(Descendants([Scenario]),
                  Descendants([Product]))),
                  Descendants([Market]))} ON ROWS
       FROM [Sample].[Basic]
);

The above example assumes you have previously added a Revised_Payroll measure to Sample Basic.

MDX Export

You can save this .mdx script and run it from Jobs or from the Calculate dialog in Smart View.

EXPORT INTO FILE "sample01" OVERWRITE
SELECT 
 {[Mar],[Apr]} 
ON COLUMNS,
 Crossjoin({[New York]},
  Crossjoin({[Actual],[Budget]},
     {[Opening Inventory],[Ending Inventory]})) 
ON ROWS
FROM [Sample].[Basic]
WHERE ([100-10])

After you run the script, the following export file, sample01.txt, is saved in the cube directory of the file catalog:

Market,Scenario,Measures,Mar,Apr
New York,Actual,Opening Inventory,2041,2108
New York,Actual,Ending Inventory,2108,2250
New York,Budget,Opening Inventory,1980,2040
New York,Budget,Ending Inventory,2040,2170

MDX Export Using Runtime Substitution Variable

You can save this .mdx script and run it from the Calculate dialog in Smart View.

SET RUNTIMESUBVARS
{
 States = "Massachusetts"<RTSV_HINT><svLaunch>
                    <description>US States</description>
                    <type>member</type>
                    <allowMissing>false</allowMissing>
                    <dimension>Market</dimension>
                    <choice>multiple</choice>
                    </svLaunch></RTSV_HINT>;
};
EXPORT INTO FILE "sample002" OVERWRITE
SELECT
 {[Mar],[Apr]}
ON COLUMNS,
 Crossjoin({&States}, Crossjoin({[Actual],[Budget]},
 {[Opening Inventory],[Ending Inventory]}))
ON ROWS
FROM [Sample].[Basic]
WHERE ([100-10])

After you run the script, the following export file, sample002.txt, is saved in the cube directory of the file catalog:

Market,Scenario,Measures,Mar,Apr
Massachusetts,Actual,Opening Inventory,-54,-348
Massachusetts,Actual,Ending Inventory,-348,-663
Massachusetts,Budget,Opening Inventory,-160,-520
Massachusetts,Budget,Ending Inventory,-520,-910