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 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 Output of report shows Product members 100, 200, 300, 400, and Diet](img/rep_metadata.png)
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 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](img/rep_attribute.png)
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. 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.](img/rep_slicer.png)
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. Output of report shows, on rows, Product members 100, 200, 300, 400, and Diet. On columns, the major markets East and Central are shown.](img/rep_uda.png)
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, open the application and open the database (cube).
-
Click Scripts, and click MDX Scripts.
-
Click Create, enter a name for the script and click OK.
-
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.
-
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.
-
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