Use MDX Query Builder to build MDX basic queries without having to code them. MDX Query Builder enables you to add dimensions to columns, rows, pages, and point of view axes and preview the query results.
Note:
MDX Query Builder only enables you to build data sets against Essbase data sources. For all other OLAP data sources, you must manually create the query.
You create MDX queries either by manually entering the MDX query or by using MDX Query Builder to build the query.
To use MDX Query Builder to build a MDX query:
In the Select Cube dialog, select the Essbase cube that you want to use to build the MDX query.
The MDX data source connection that you selected previously drives which Essbase cubes are available for selection.
You build MDX queries by selecting dimensions for the Columns, Rows, Slicer/POV, and Pages axes.
Account dimension members are listed individually by member name. All other dimension members are represented by generation name as shown below.
You can drag dimension generations and individual measures from the Account dimension to the Columns, Rows, Slicer/POV, and Pages axes.
Build the query by dragging dimension members or measures from the Dimensions panel to one of the following axes areas:
Columns— Axis (0) of the query
Rows — Axis (1) of the query
Slicer/POV— The slicer axis enables you to limit a query to only a specific slice of the Essbase cube. This represents the optional WHERE clause of a query.
Axis — Axis (2) of the query
You can nest dimension members in the Columns and Rows axes, but you can only add a single dimension to the Slider/POV axis.
When you add a dimension to the Slicer/POV axis, the Member Selection dialog launches.
You can only select one dimension member for this axis. Simply select the dimension in the Member Selection dialog, and then click OK.
The Member Selection dialog does not display if you add a measure to the Slicer/POV axis.
The MDX Query Builder toolbar contains the following buttons for modifying the MDX query:
Click Swap Rows and Columns to flip dimensions between columns and rows.
Click Actions to display the following menu items for selection:
Select Cube - Selects a different Essbase cube for the query.
Set Alias Table - Selects the alias table used for dimension display names. Alias names are used for display only and are not used in the query.
Auto Refresh - Displays the results as dimension members are placed in the Columns, Rows, Slicer/POV, and Pages axes and automatically refreshes the MDX query syntax.
Clear Results - Clears the results and removes member selections from all of the axes and any filters added to the query.
Show Empty Columns - Displays columns that do not contain data.
Show Empty Rows - Displays rows that do not contain data.
Show Query - Displays the MDX query syntax resulting from how the dimensions are placed in the Columns, Rows, Slicer/POV, and Pages axes.
You can create filters for dimensions on the Columns, Rows, and Pages axes in MDX Query Builder to further streamline your MDX query.
You can create multiple filters for a query, but you can only create one filter for each Columns, Rows, or Pages axis.
Use the Options dialog to select the dimension properties to include in the query for each of the dimensions in the Columns, Rows, and Pages axes.
Once you have built the query, click Save to display the Options dialog as shown below.
By default, none of the properties are selected.
The dimension properties are as follows:
Member Alias — Dimension member alias names as listed in the Essbase outline.
Ancestor Names — Ancestor dimension names as listed in the Essbase outline.
Level Number — Dimension level numbers as listed in the Essbase outline.
Gen Number — Generation number of the dimensions as listed in the Essbase outline.
For example, if you select the Member Alias and Level Number properties for Columns, the MDX query results are as follows:
SELECT NON EMPTY Hierarchize([Market].Generations(2).Members) PROPERTIES MEMBER_ALIAS,LEVEL_NUMBER ON Axis(0), NON EMPTY CROSSJOIN(Hierarchize([Product].Generations(3).Members), {[Accounts].[Margin],[Accounts].[Sales],[Accounts].[Total_Expenses]})ON Axis(1), NON EMPTY [Year].Generations(3).Members ON Axis(2) FROM Demo.Basic
For more information on Essbase dimension properties, see: Oracle Essbase Database Administrator's Guide.
After you select options for the MDX query, click OK to return to the New Data Set - MDX Query dialog and review the MDX query output as shown below.
Click OK to return to the data model editor, and save your changes.
Note:
If you modify a MDX query after you save it in BI Publisher, Oracle recommends that you manually change the syntax and not use MDX Query Builder to do so.