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;

        }

    }

}