Automatic Generation of CREATE MINING MODEL statements
I noticed through answering newsgroup and forum posts that we always want to know model definitions when discussing a problem. Luckily we have this great language called DMX that makes it very easy and simple to describe a model. Unluckily, our tools that we use to create models use an XML DDL that is very complex and, for the most part, not intelligible to human readers.
To solve this problem I sat down last night and wrote up a little stored procedure to create a CREATE MINING MODEL statement from any server model. All you have to do after building and deploying the stored procedure is execute the query
SELECT DMXtract.CreateMiningModelStatement() FROM [TreeModel]
To build this code you will have to add a reference to Microsoft.AnalysisServices.AdomdServer. For more information on how to deploy stored procedures see this article in Books Online.
Below is the code for CreateMiningModelStatement. One unforeseen benefit I discovered writing this code was that the parameters collection contains all parameters even if they weren’t set by the user. This makes it useful to determine how the algorithms automatically set specific parameter values on your behalf.
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdServer;
namespace DMXtract
{
public class DMXtract
{
[SafeToPrepare(true)]
public string CreateMiningModelStatement()
{
// Return empty string on prepare
if (Context.ExecuteForPrepare)
return "";
// Return the create statement for the current model
return CreateMiningModelStatement(Context.CurrentMiningModel);
}
private string CreateMiningModelStatement(MiningModel model)
{
int count = 0;
// Add the CREATE syntax
string statement = "CREATE MINING MODEL [" + model.Name + "]";
statement += "\r\n(\r\n";
// Add the columns
statement += ListModelColumns(model.Columns,1);
// Add the algorithm
statement += "\r\n) USING ";
statement += model.Algorithm;
// Add model parameters
if (model.Parameters.Count > 0)
{
statement += "(";
foreach(MiningParameter param in model.Parameters)
{
if (param.Name == "" | param.Value == "")
continue;
if (count > 0)
statement += ", ";
statement += param.Name + "=" + param.Value;
count++;
}
statement += ")";
}
// Add Drillthrough
if (model.AllowDrillThrough)
statement += "\r\nWITH DRILLTHROUGH";
return statement;
}
private string ListModelColumns(MiningModelColumnCollection columns, int indent)
{
int count = 0;
string statement = "";
// Iterate columns in collection
foreach (MiningModelColumn modelcol in columns)
{
if (count > 0)
statement = statement + ",\r\n";
// Indent as indicated
for (int i = 0; i < indent; i++)
statement += " ";
statement += "[" + modelcol.Name + "]\t";
MiningStructureColumn structcol = modelcol.StructureColumn;
if (modelcol.Type != MiningColumnType.Table)
{
// Append scalar column
statement += " " + modelcol.Type.ToString().ToUpper();
if (modelcol.Distribution != MiningColumnDistribution.Missing)
statement += " " + modelcol.Distribution.ToString().ToUpper();
if (modelcol.Flags != "")
statement += " " + modelcol.Flags;
statement += " " + modelcol.Content.ToString().ToUpper();
// Add prediction flags
if (modelcol.IsPredictable)
{
if (modelcol.IsInput)
statement += " PREDICT";
else
statement += " PREDICT_ONLY";
}
}
else
{
// Append nested table column
statement += " TABLE";
// Add prediction flags
if (modelcol.IsPredictable)
{
if (modelcol.IsInput)
statement += " PREDICT";
else
statement += " PREDICT_ONLY";
}
statement += "\r\n";
for (int i = 0; i < indent; i++)
statement += " ";
statement += "(\r\n";
// Append nested columns
statement += ListModelColumns(modelcol.Columns, indent + 1);
// Close nested table definition
statement += "\r\n";
for (int i = 0; i < indent; i++)
statement += " ";
statement += ")";
}
count++;
}
return statement;
}
}
}