Use 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.
MDX Query Builder only enables you to build datasets against Essbase data sources. For all other OLAP data sources, you must manually create the query.
Understand 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 Dataset and then select MDX Query to launch the New Dataset - MDX Query dialog.
- Enter a name for the dataset.
- 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.
Use 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.
Select 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.
-
Pages — 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.
Add 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 doesn't display if you add a measure to the Slicer/POV axis.
Perform 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.
-
Apply 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.