Data Definition Queries (Data Mining)
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 (SSDT) 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 more information, see Scripting Language Reference.
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 (SSDT) 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 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
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 Analysis Services. For more information, see Management of Data Mining Solutions and Objects.
Warning
If your model is based on data from a cube data srouce, you cannot use DMX to export the model, and should use backup and restore instead.
Related Tasks
The following table provides links to tasks that are related to data definition queries.
Work with templates for DMX queries. |
Use Analysis Services Templates in SQL Server Management Studio |
Design queries of all kinds, using Prediction Query Builder. |
Create a Prediction Query Using the Prediction Query Builder |
Capture query definitions by using SQL Server Profiler, and use traces to monitor Analysis Services. |
|
Learn more about the scripting languages and programming languages provided for Analysis Services. |
|
Learn how to manage models in SQL Server Management Studio and SQL Server Data Tools (SSDT). |
|
Learn more about OPENROWSET and other ways to query external data. |