Using MDX Query Builder
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.
Understanding the MDX Query Builder Process
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:
- On the toolbar, click New Data Set and then select MDX Query to launch the New Data Set - MDX Query dialog.
- Enter a name for the data set.
- Select a data source.
- Launch MDX Query Builder.
- Select an Essbase cube for the query.
- Select dimensions and measures by dragging and dropping them to the Columns, Rows, Slicer/POV, and the Pages axes.
- (Optional) Use actions to modify the query.
- (Optional) Apply filters.
- Set the query options and save the query.
Using the Select Cube Dialog
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.
Selecting Dimensions and Measures
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.
Adding Dimension Members to the Slicer/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.
Performing MDX Query Actions
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.
-
Applying MDX Query Filters
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.
- To create a filter, click the down-arrow button to the right of a dimension in the Columns, Rows, or Pages axes to display it in the Filters area. You create the filter by selecting the desired dimension member as shown below.
Selecting MDX Query Options and Saving MDX Queries
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.