Using Analysis Services DMX Query Designer

When you create a dataset using a Microsoft SQL Server Analysis Services data source, Report Designer displays the Multidimensional Expression (MDX) query designer by default. To switch to the Data Mining Prediction (DMX) query designer, click the Command Type DMX () 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.

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 rowset 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.

The generic query designer () button on the toolbar is not enabled for the Analysis Services data processing extension.

To Create a DMX Dataset

1. On the Data tab, from the Dataset drop-down list, select <New Dataset>.

2. On the Dataset dialog box, from the Data source drop-down list, select New Data Source.

3. On the Data Source dialog box, from the Type drop-down list, select Microsoft SQL Server Analysis Services.

4. Specify a connection string that works with your Analysis Services data source. Contact your database administrator for connection information.
   The following connection string example specifies the Adventure Works DW sample data warehouse on an Analysis Services data source on the local server:

   Data Source=(local);Initial Catalog="Adventure Works DW"
   

5. Set the database credentials in the Data Source dialog box on the Credentials tab.

For more information, see Connecting to a Data Source.

Example: Retrieving Data from a Data Mining Model

1. Install and then publish the AdventureWorks sample reports, and then deploy the Analysis Services cube using the instructions in AdventureWorks Report 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. You can omit the query; you will be creating the query using DMX Query Designer.
4. Click the Command Type DMX () 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. Save the file, right-click the Query Design pane, and select Result to view the rowset. A rowset containing 18484 rows appears in the result view. To switch back to Design mode, right-click the Result pane and select Design.

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. If necessary, you can specify an alias, whether to show the field in the results, whether to group fields together, and criteria for an argument. If you are using Query mode, type the DMX query directly in 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.

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 () button on the toolbar. To view instructions about defining parameters in a DMX query, see How to: Define Parameters in MDX and DMX Query Designer for Analysis Services (Report Designer).

To manage the relationship between report parameters and query parameters, click the Edit Selected () button on the toolbar to open the Dataset dialog box, and click the Parameters tab. To edit or remove a report parameter, from the Report menu, select Report Parameters. For more information, see Working with Parameters in Reporting Services.

Example

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

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 () button on the query designer toolbar.

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

When you switch to Layout 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.

To view instructions about working with report parameters, see:

• How to: Add, Edit, or Delete a Report Parameter (Report Designer)
• How to: Associate a Query Parameter with a Report Parameter (Report Designer)

See Also

Concepts

Defining Report Datasets for Analysis Services Multidimensional and Data Mining Prediction Data
Using Analysis Services MDX Query Designer in Design Mode
Using Analysis Services MDX Query Designer in Query Mode
Query Design Tools in Reporting Services

Other Resources

Working with Data Mining

Help and Information

Getting SQL Server 2005 Assistance