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 Power Pivot in Excel, 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.
-
Open the Power Pivot window.
-
Click Home > Get External Data > From Database > From Analysis Services or Power Pivot.
-
In Table Import Wizard, provide the name of an Analysis Services multidimensional server and choose the database. Click Next.
-
Paste in the MDX query in the MDX Statement window.
-
Click Validate.
-
If you get an error, clear the query from the window.
-
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.
-
Paste the query into query window. Use the Functions and Metadata that the designer provides to redesign a workable query.
-
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.
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 (
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. |
|
|
Switch to Command Type MDX. |
|
|
Refresh metadata from the data source. |
|
|
Display the Calculated Member Builder dialog box. |
|
|
Toggle between showing and not showing empty cells in the Data pane. (This is the equivalent to using the NON EMPTY clause in MDX). |
|
|
Automatically run the query and show the result every time a change is made. Results are shown in the Data pane. |
|
|
Show aggregations in the Data pane. |
|
|
Delete the selected column in the Data pane from the query. |
|
|
Display the Query Parameters dialog box. When you specify values for a query parameter, a parameter with the same name is automatically created. |
|
|
Prepare the query. |
|
|
Run the query and display the results in the Data pane. |
|
|
Cancel the query. |
|
|
Toggle between Design mode and Query mode. |
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.
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 (
-
Show Empty Cells (
-
AutoExecute (
-
Show Aggregations (
