• Article

Using the Analysis Services DMX Query Designer (Reporting Services)

When you create a dataset using a SQL Server Analysis Services data source, Report Designer displays the Multidimensional Expression (MDX) query designer if it detects a valid cube. If no cube is detected, but a data mining model is available, Report Designer displays the Data Mining Extensions (DMX) query designer. To switch between the MDX and DMX designers, click the Command Type DMX (Change to DMX query language view) button on the toolbar. Use the DMX Query Designer to interactively build a DMX query using graphical elements. To use DMX Query Designer, the data source that you specify must already have a data mining model that provides the data. Query results are converted to a flattened rowset for use in the report.

Note

You must train your model before designing your report. For more information, see Data Mining Projects (Analysis Services - Data Mining).

Design Mode

The DMX query designer opens in Design mode. Design mode includes a graphical design surface used for selecting a single data mining model and input table, and a grid used for specifying the prediction query. There are two other modes in DMX query designer: Query mode and Result mode. In Query mode, the grid from Design mode is replaced with a Query pane, which you can use to type DMX queries. In Result mode, the result set returned by the query appears in a data grid.

To change modes for the DMX query designer, right-click on the query design surface and select Design, Query, or Result. For more information, see Analysis Services DMX Query Designer User Interface and How to: Retrieve Data from a Data Mining Model (DMX).

Designing a Prediction Query

The Query Design pane of the Design mode contains two windows: Mining Model and Select Input Table. Use the Mining Model window to select the mining model to use in your query. Use the Select Input Table window to select the table on which to base your predictions. If you want to use a singleton query instead of an input table, right-click in the Query Design pane and select Singleton Query. The Select Input Table window is replaced with a Singleton Query Input window.

In Design mode, drag fields from the Mining Model and Select Input Table windows to the Field column in the Grid pane. You can also fill in the remaining columns to specify an alias, to show the field in the results, to group fields together, and to specify an operator to restrict the field value to a given criteria or argument. If you are in Query mode, build the DMX query by dragging fields to the Query pane.

For more information about building DMX prediction queries using the DMX query designer, see Using the Prediction Query Builder to Create DMX Prediction Queries.

The following example shows how to create a report dataset by using the DMX query designer.

Example: Retrieving Data from a Data Mining Model

The Reporting Services samples include a project that deploys two mining models based on the SQL Server sample database AdventureWorksDW. For more information, see Reporting Services Samples.

  1. Install and then publish the AdventureWorks sample reports, and then deploy the Analysis Services cube. For more information, see Reporting Services Samples.

  2. Open the AdventureWorks Sample Reports project, and then add an empty report definition (.rdl) file to the project.

  3. Create a new dataset using the AdventureWorksAS shared data source. In the Dataset Properties dialog, click Query Designer. The MDX Analysis Services query designer opens in Design mode.

  4. Click the Command Type DMX (Change to DMX query language view) button on the toolbar.

  5. Click Yes to switch to the DMX Query Designer.

  6. Click Select Model, expand Targeted Mailing, and then choose TM Decision Tree. Click OK.

  7. Click Select Case Table, scroll to and then select vTargetMail (dbo). Click OK.

  8. In the Grid pane, click Source and then select TM Decision Tree mining model. Bike Buyer appears in the Field column.

  9. On the next line, click Source and then select vTargetMail Table. CustomerKey appears in the Field column.

  10. Right-click the Query Design pane, and select Result to view the result set. A result set containing 18484 rows appears in the result view. To switch back to Design mode, right-click the Result pane and select Design.

Using Parameters

You can pass report parameters to a DMX query parameter. To do this, you must add a parameter to your DMX query, define the query parameters in the Query Parameters dialog box, and then modify the associated report parameters. To define a query parameter, click the Query Parameters (Icon for the Query Parameters dialog box) button on the toolbar. To view instructions about defining parameters in a DMX query, see How to: Define Parameters in the MDX Query Designer for Analysis Services.

For more information about how to manage the relationship between report parameters and query parameters, see How to: Associate a Query Parameter with a Report Parameter. For more information about parameters, see Adding Parameters to Your Report.

Example Query with Parameters

The following query retrieves report data indicating which customers are likely to purchase a bicycle, and the probability that they will do so.

SELECT
  t.FirstName, t.LastName,
  (Predict ([Bike Buyer])) as [PredictedValue],
  (PredictProbability([Bike Buyer])) as [Probability]
From
  [TM Decision Tree]
PREDICTION JOIN
  OPENQUERY([Adventure Works DW],
    'SELECT
      [FirstName],
      [LastName],
      [CustomerKey],
      [MaritalStatus],
      [Gender],
      [YearlyIncome],
      [TotalChildren],
      [NumberChildrenAtHome],
      [HouseOwnerFlag],
      [NumberCarsOwned],
      [CommuteDistance]
    FROM
      [dbo].[DimCustomer]
    ') AS t
ON
  [TM Decision Tree].[Marital Status] = t.[MaritalStatus] AND
  [TM Decision Tree].[Gender] = t.[Gender] AND
  [TM Decision Tree].[Yearly Income] = t.[YearlyIncome] AND
  [TM Decision Tree].[Total Children] = t.[TotalChildren] AND
  [TM Decision Tree].[Number Children At Home] = t.[NumberChildrenAtHome] 
AND
  [TM Decision Tree].[House Owner Flag] = t.[HouseOwnerFlag] AND
  [TM Decision Tree].[Number Cars Owned] = t.[NumberCarsOwned] AND
  [TM Decision Tree].[Commute Distance] = t.[CommuteDistance]
WHERE
 (Predict ([Bike Buyer]))=@Buyer AND
 (PredictProbability([Bike Buyer]))>@Probability

Note

This example uses the DimCustomer table as an input table. This is for illustration only. In the AdventureWorks database, the DimCustomer table was used to train the model used in this example. Ordinarily, you would use an input table that was not previously used for training.

In this example, after you create the query, you must define the query parameters using the Query Parameters dialog box. To do this, click the Query Parameters (Icon for the Query Parameters dialog box) button on the query designer toolbar.

Add the parameters as follows. Each parameter must also have a default value.

Parameter

Default Value

Buyer

1

Probability

.75

Note

The parameters specified in the Query Parameters dialog box must be the same as the parameters in the query, without the at (@) symbol.

When you switch to Design view to create a report, new report parameters are created from the query parameters. The report parameters are presented to the user when the report is run. You can update the report parameters to provide a list of values from which the user can choose, specify a default value, or change other report parameter properties.

For more information about working with report parameters, see: