Analysis Services MDX Query Designer (Power Pivot)

Cubes are query structures specifically designed for analyzing one or more business performance metrics (such as net profit or gross sales) across different dimensions (time, geography, demographics, and so forth). People who build PivotTables or PivotCharts in Excel often use Analysis Services cubes as a data source.

MDX is the query language for cubes. Behind the scenes, Excel uses MDX to retrieve fields and data values when you choose items from a PivotTable Fields list. You can also build MDX queries manually when importing data from an Analysis Services cube.

If you are using the Power Pivot add-in in Excel 2013, you can build an MDX query using the MDX Query Designer when you import from a multidimensional database running on Analysis Services.

Who should use the MDX Query Designer? You’ll be successful with the MDX query builder if you already have MDX expertise, or you have a predefined query to use or test. If you are not an MDX expert, use Excel to get Analysis Services data. When you connect to a server and database, Excel shows you which cubes are available, and imports all of its measures and dimensions so that you can query any part of cube using the PivotTable Fields list.

  1. Open the Power Pivot window. See Start Power Pivot in Excel 2013 add-in for details.
  2. Click Home > Get External Data > From Database > From Analysis Services or Power Pivot.
  3. In Table Import Wizard, provide the name of an Analysis Services multidimensional server and choose the database. Click Next.
  4. Paste in the MDX query in the MDX Statement window.
  5. Click Validate.
  6. If you get an error, clear the query from the window.
  7. Click Design to open the MDX Query Designer, which you can run in Design or Query mode depending on what you’re used to. The designer provides a list of functions and shows the entire cube structure so that you can choose which measures, KPIs, and dimensions that you can add to the Data Model.
  8. Paste the query into query window. Use the Functions and Metadata that the designer provides to redesign a workable query.
  9. When the query validates, click Finish to retrieve the data. The data and metadata will be imported into a Data Model that you view in Power Pivot. You might need to create relationships to connect this data with other tables in the model. See Create relationships in Diagram View for details.

The MDX Query Designer has two modes: design mode and query mode. Each mode provides a metadata pane from which you can drag members from the selected cubes to build an MDX query that retrieves the data you want to use.

Graphical MDX Query Designer in Design Mode

When you edit an MDX query, the graphical MDX query designer opens in Design mode.

The following figure labels the panes for Design mode.

Relational Query designer

The following table lists the panes in this mode:

Pane

Function

Select Cube button (...) Displays the currently selected cube.
Metadata pane Displays a hierarchical list of measures, Key Performance Indicators (KPIs), and dimensions defined on the selected cube.
Calculated Members pane Displays the currently defined calculated members available for use in the query.
Filter pane Use to choose dimensions and related hierarchies to filter data at the source and limit data returned.
Data pane Displays the column headings for the result set as you drag items from the Metadata pane and the Calculated Members pane. Automatically updates the result set if the AutoExecute button is selected.

You can drag dimensions, measures, and KPIs from the Metadata pane, and calculated members from the Calculated Member pane, onto the Data pane. In the Filter pane, you can select dimensions and related hierarchies, and set filter expressions to limit the data available to query. If the AutoExecute (Relational Query designer icon ) toggle button on the toolbar is selected, the query designer runs the query every time that you drop a metadata object onto the Data pane. You can manually run the query using the Run (Relational Query designer icon ) button on the toolbar.

When you create an MDX query in this mode, the following additional properties are automatically included in the query:

Member Properties MEMBER_CAPTION, MEMBER_UNIQUE_NAME

Cell Properties VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

To specify your own additional properties, you must manually edit the MDX query in Query mode.

Importing from an .mdx query file is not supported.

Graphical MDX Query Designer Toolbar in Design Mode

The query designer toolbar provides buttons to help you design MDX queries using the graphical interface. The following table lists the buttons and their functions.

Button

Description

Edit As Text

Not enabled for this data source type.

Import

Import an existing query from a report definition (.rdl) file on the file system.
Relational Query designer icon Switch to Command Type MDX.
Relational Query designer icon Refresh metadata from the data source.
Relational Query designer icon Display the Calculated Member Builder dialog box.
Relational Query designer icon Toggle between showing and not showing empty cells in the Data pane. (This is the equivalent to using the NON EMPTY clause in MDX).
Relational Query designer icon Automatically run the query and show the result every time a change is made. Results are shown in the Data pane.
Relational Query designer icon Show aggregations in the Data pane.
Relational Query designer icon Delete the selected column in the Data pane from the query.
query parameter icon Display the Query Parameters dialog box. When you specify values for a query parameter, a parameter with the same name is automatically created.
Relational Query designer icon Prepare the query.
Relational Query designer icon Run the query and display the results in the Data pane.
Relational Query designer icon Cancel the query.
Relational Query designer icon Toggle between Design mode and Query mode.

Top of Page Top of Page

Graphical MDX Query Designer in Query Mode

To change the graphical query designer to Query mode, click the Design Mode button on the toolbar.

The following figure labels the panes for Query mode.

Relational Query designer

The following table lists the panes in this mode:

Pane

Function

Select Cube button (...) Displays the currently selected cube.
Metadata/Functions/Templates pane Displays a hierarchical list of measures, KPIs, and dimensions defined on the selected cube.
Query pane Displays the query text.
Result pane Displays the results of running the query.

The Metadata pane displays tabs for Metadata, Functions, and Templates. From the Metadata tab, you can drag dimensions, hierarchies, KPIs, and measures onto the MDX Query pane. From the Functions tab, you can drag functions onto the MDX Query pane. From the Templates tab, you can add MDX templates to the MDX Query pane. When you execute the query, the Result pane displays the results for the MDX query.

You can extend the default MDX query generated in Design mode to include additional member properties and cell properties. When you run the query, these values do not appear in the result set. However, they are passed back with the dataset field collection and you can use these values.

Graphical Query Designer Toolbar in Query Mode

The query designer toolbar provides buttons to help you design MDX queries using the graphical interface.

The toolbar buttons are identical between Design mode and Query mode, but the following buttons are not enabled for Query mode:

  • Edit As Text
  • Add Calculated Member (Relational Query designer icon )
  • Show Empty Cells (Relational Query designer icon )
  • AutoExecute (Relational Query designer icon )
  • Show Aggregations (Relational Query designer icon )

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Power Pivot in Excel 2013