Querying a Linear Regression Model (Analysis Services - Data Mining)

By querying the data mining schema rowset, you can find metadata about the model. This might include when the model was created, when the model was last processed, the name of the mining structure that the model is based on, and the name of the column designated as the predictable attribute. You can also return the parameters that were used when the model was first created.

SELECT MINING_PARAMETERS 
FROM $system.DMSCHEMA_MINING_MODELS
WHERE MODEL_NAME = 'Call Center Regression'

Sample results:

Back to top

The following query returns the mining model content for the linear regression model. The query returns the contents of the node that contains the regression formula. You can also use this query in a decision trees model if one of the nodes contains a regression formula.

Each variable and coefficient is stored in a separate row of the nested NODE_DISTRIBUTION table. If you want to view the complete regression formula, use the Microsoft Tree Viewer, click the (All) node, and open the Mining Legend.

SELECT FLATTENED NODE_DISTRIBUTION as t
FROM [Call Center Regression].CONTENT

Expected results:

In comparison, in the Mining Legend, the regression formula appears as follows:

Service Grade = 0.070+0.001*(Average Time Per Issue-79.842)-0.0004*(Total Operators-10.692)

In the Mining Legend, some numbers might be rounded; however, the NODE_DISTRIBUTION table and the Mining Legend essentially contain the same values.

The values in the VALUETYPE column tell you what kind of information is contained in each row, which is useful if you are processing the results programmatically. The following table shows the value types that are output for a linear regression formula.

For more information about the meaning of each value type for regression models, see Mining Model Content for Linear Regression Models (Analysis Services - Data Mining).

Back to top

By using the VALUETYPE enumeration, you can return only the coefficient for the regression equation, as shown in the following query:

SELECT FLATTENED MODEL_NAME,
    (SELECT ATTRIBUTE_VALUE, VALUETYPE
     FROM NODE_DISTRIBUTION
     WHERE VALUETYPE = 11) 
AS t
FROM [Call Center Regression].CONTENT

This query returns two rows, one from the mining model content, and the row from the nested table that contains the coefficient. The ATTRIBUTE_NAME column is not included here because it is always blank for the coefficient.

The easiest way to create a singleton query on a regression model is by using the Singleton Query Input dialog box. For example, you can build the following DMX query by selecting the appropriate regression model, choosing Singleton Query, and then typing 10 as the value for Total Operators.

SELECT
  Predict([Call Center Regression].[Service Grade])
FROM
  [Call Center Regression]
NATURAL PREDICTION JOIN
(SELECT 10 AS [Total Operators]) AS t

Sample results:

Back to top

You can use many of the standard prediction functions with linear regression models. The following example illustrates how to add some descriptive statistics to the prediction query results.

SELECT
  Predict([Call Center Regression].[Service Grade]) as [Predicted Service],
  PredictStdev([Call Center Regression].[Service Grade]) as [Standard Deviation]
FROM
  [Call Center Regression]
NATURAL PREDICTION JOIN
(SELECT 10 AS [Total Operators]) AS t

Sample results:

Back to top