Data Definition Queries (Data Mining)

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

For data mining, the category data definition query means DMX statements or XMLA commands that do the following:

• Create, alter, or manipulate data mining objects, such as a model.

• Define the source of data to be used in training or for prediction.

• Export or import mining models and mining structures.

Creating Data Definition Queries

• Data Definition Queries in SQL Server Data Tools

• Data Definition Queries in SQL Server Management Studio

Scripting Data Definition Statements

Scripting Data Definition Statements

Creating Data Definition Queries

You can create data definition queries (statements) by using the Prediction Query Builder in SQL Server Data Tools and SQL Server Management Studio, or by using the DMX Query window in SQL Server Management Studio. Data definition statements in DMX are part of the Analysis Services data definition language (DDL).

For information about the syntax of specific data definition statements, see Data Mining Extensions (DMX) Reference.

Data Definition Queries in SQL Server Data Tools

The Data Mining Wizard is the preferred tool in SQL Server Data Tools for creating and modifying mining models and mining structures, and for defining the data sources that are used in prediction queries and for training.

However, if you want to know what statements are being sent to the server by the wizard to create data structures or mining models, you can use SQL Server Profiler to capture the data definition statements. For more information, see Use SQL Server Profiler to Monitor Analysis Services.

To view the statements used for defining data sources used for training or prediction, you can use the SQL View in the Prediction Query Builder. Sometimes it can be helpful to build basic queries for training and testing models by using Prediction Query Builder, to establish the correct syntax. You can then switch to SQL View and manually edit the query. For more information, see Manually Edit a Prediction Query.

Data Definition Queries in SQL Server Management Studio

For data mining objects, you can use data definition queries to perform the following actions:

• Create specific types of models, such as a clustering model or decision tree model, by using CREATE MINING MODEL (DMX).

• Alter an existing mining structure by adding a model or by changing the columns, by using ALTER MINING STRUCTURE (DMX). Note that you cannot alter a mining model by using DMX; you only add new models to an existing structure.

• Make a copy of a mining model and then alter it, by using SELECT INTO (DMX).

• Define the data set used for training a model, by using INSERT INTO (DMX) together with a data source query such as OPENROWSET.

SQL Server Management Studio provides query templates that can help you create data definition queries. For more information, see Use Analysis Services Templates in SQL Server Management Studio.

In general, the templates that are provided for SQL Server Analysis Services in SQL Server Management Studio contain only the general syntax definition, which you must customize, either by typing in the Query window, or by using the dialog box provided for entering parameters.

For an example of how to enter parameters using the interface, see Create a Singleton Prediction Query from a Template.

Scripting Data Definition Statements

SQL Server Analysis Services provides multiple scripting and programming languages that you can use to create or alter data mining objects, or to define data sources. Although DMX is designed for expediting data mining tasks, you can also use both XMLA and AMO to manipulate objects in scripts or in custom code.

The Data Mining Add-in for Excel also includes many query templates, and provides the Advanced Query Editor, which helps you compose complex DMX statements. You can build a query interactively and then switch to SQL View to capture the DMX statement.

Exporting and Importing Models

You can use data definition statements in DMX to export the definition of a model and its required structure and data sources, and then import that definition into a different server. Using export and import is the fastest and easiest way to move data mining models and mining structures between instances of SQL Server Analysis Services. For more information, see Management of Data Mining Solutions and Objects.

Related Tasks

The following table provides links to tasks that are related to data definition queries.

See Also

Data Mining Wizard (Analysis Services - Data Mining)