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;


        }


    }


}


 

Comments (0)