Query the Parameters Used to Create a Mining Model

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

The composition of a mining model is affected not only by the training cases, but by the parameters that were set when the model was created. Therefore, you might find it useful to retrieve the parameter settings of an existing model to better understand the behavior of the model. Retrieving parameters is also useful when documenting a particular version of that model.

To find the parameters that were used when the model was created, you create a query against one of the mining model schema rowsets. These schema rowsets are exposed as a set of system views that you can query easily by using Transact-SQL syntax. This procedure describes how to create a query that returns the parameters that were used to create the specified mining model.

To open a Query window for a schema rowset query

1. In SQL Server Management Studio, open the instance of SQL Server Analysis Services that contains the model you want to query.

2. Right-click the instance name, select New Query, and then select DMX.

   Note

   You can also create a query against a data mining model by using the MDX template.

3. If the instance contains multiple databases, select the database that contains the model you want to query from the Available Databases list in the toolbar.

To return model parameters for an existing mining model

1. In the DMX query pane, type or paste the following text:

   SELECT MINING_PARAMETERS  
   FROM $system.DMSCHEMA_MINING_MODELS  
   WHERE MODEL_NAME = ''  
   

2. In Object Explorer, select the mining model you want, and then drag it into the DMX Query pane, between the single quotation marks.

3. Press F5, or click Execute.

Example

The following code returns a list of the parameters that were used to create the mining model that you build in the Basic Data Mining Tutorial. These parameters include the explicit values for any defaults used by the mining services available from providers on the server.

SELECT MINING_PARAMETERS   
FROM $system.DMSCHEMA_MINING_MODELS  
WHERE MODEL_NAME = 'TM Clustering'  

The code example returns the following parameters for the clustering model:

eExample Results:

MINING_PARAMETERS

CLUSTER_COUNT=10,CLUSTER_SEED=0,CLUSTERING_METHOD=1,MAXIMUM_INPUT_ATTRIBUTES=255,MAXIMUM_STATES=100,MINIMUM_SUPPORT=1,MODELLING_CARDINALITY=10,SAMPLE_SIZE=50000,STOPPING_TOLERANCE=10

See Also

Data Mining Query Tasks and How-tos
Data Mining Queries