How to create mining model in SSAS dynamically using SQL server jobs

Using DMX query we can MANUALLY create using:

CREATE MINING MODEL Decision_Tree_8_3_1 (
[StudentRetentionModelKey] LONG KEY,
[SemesterReturningCode] LONG DISCRETE Predict_only ,
[AffordabilityGap] LONG continuous,
CareerGoalCertainty long discrete,
[CertificatePlan] long discrete,
[DistanceFromCampus] LONG continuous,
[EDUC108] long discrete,
[EFCAmount] LONG continuous,
Gender TEXT DISCRETE,
[HighestMathSAT_SATIM] LONG continuous,
[HighestVerbalSAT_SATIV] LONG continuous,
[HUNDRED_POINT_SCALE_GPA] LONG continuous,
[LateRegistration] long discrete,
[LivingSituation] long discrete,
MATH101 LONG DISCRETE,
[CollegeGrantAmount] LONG continuous,
[CollegeScholarshipAmount] LONG continuous,
[PellAmount] LONG continuous,
[StaffordLoanAmount] LONG continuous,
[TAPAmount] LONG continuous,
[TotalFundAvailableAmount] LONG continuous,
[VeteranIndicator] long discrete,
[WorkHours] long discrete,
[WRIT100] long discrete,
[FOUR_POINT_SCALE_GPA] long continuous,
[FullTimePartTimeIndicator] long discrete,
[MajorCertainty] long discrete
)
USING Microsoft_Decision_Trees (COMPLEXITY_PENALTY = 0.8 , SCORE_METHOD = 3 , SPLIT_METHOD = 3) ;

For executing the above DMX query, open SSMS and connect to Analysis Server and execute the above DMX query.

Now if we want to automate this using SQL server job, how to go about it?

Create job step of the type SQL Server Analysis Command and the command is:

<Create xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
<ParentObject>
<DatabaseID>StudentRetentionModels</DatabaseID>
</ParentObject>
<ObjectDefinition>
<MiningStructure xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:ddl2=”http://schemas.microsoft.com/analysisservices/2003/engine/2&#8243; xmlns:ddl2_2=”http://schemas.microsoft.com/analysisservices/2003/engine/2/2&#8243; xmlns:ddl100_100=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100&#8243; xmlns:ddl200=”http://schemas.microsoft.com/analysisservices/2010/engine/200&#8243; xmlns:ddl200_200=”http://schemas.microsoft.com/analysisservices/2010/engine/200/200&#8243; xmlns:ddl300=”http://schemas.microsoft.com/analysisservices/2011/engine/300&#8243; xmlns:ddl300_300=”http://schemas.microsoft.com/analysisservices/2011/engine/300/300&#8243; xmlns:ddl400=”http://schemas.microsoft.com/analysisservices/2012/engine/400&#8243; xmlns:ddl400_400=”http://schemas.microsoft.com/analysisservices/2012/engine/400/400″&gt;
<ID>Decision_Tree_8_3_1_Structure</ID>
<Name>Decision_Tree_8_3_1_Structure</Name>
<Language>1033</Language>
<Collation>Latin1_General_CI_AS</Collation>
<Columns>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>StudentRetentionModelKey</ID>
<Name>StudentRetentionModelKey</Name>
<IsKey>true</IsKey>
<Type>Long</Type>
<Content>Key</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>SemesterReturningCode</ID>
<Name>SemesterReturningCode</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>AffordabilityGap</ID>
<Name>AffordabilityGap</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>CareerGoalCertainty</ID>
<Name>CareerGoalCertainty</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>CertificatePlan</ID>
<Name>CertificatePlan</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>DistanceFromCampus</ID>
<Name>DistanceFromCampus</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>EDUC108</ID>
<Name>EDUC108</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>EFCAmount</ID>
<Name>EFCAmount</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>Gender</ID>
<Name>Gender</Name>
<Type>Text</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>HighestMathSAT_SATIM</ID>
<Name>HighestMathSAT_SATIM</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>HighestVerbalSAT_SATIV</ID>
<Name>HighestVerbalSAT_SATIV</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>HUNDRED_POINT_SCALE_GPA</ID>
<Name>HUNDRED_POINT_SCALE_GPA</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>LateRegistration</ID>
<Name>LateRegistration</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>LivingSituation</ID>
<Name>LivingSituation</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>MATH101</ID>
<Name>MATH101</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>CollegeGrantAmount</ID>
<Name>CollegeGrantAmount</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>CollegeScholarshipAmount</ID>
<Name>CollegeScholarshipAmount</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>PellAmount</ID>
<Name>PellAmount</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>StaffordLoanAmount</ID>
<Name>StaffordLoanAmount</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>TAPAmount</ID>
<Name>TAPAmount</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>TotalFundAvailableAmount</ID>
<Name>TotalFundAvailableAmount</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>VeteranIndicator</ID>
<Name>VeteranIndicator</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>WorkHours</ID>
<Name>WorkHours</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>WRIT100</ID>
<Name>WRIT100</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>FOUR_POINT_SCALE_GPA</ID>
<Name>FOUR_POINT_SCALE_GPA</Name>
<Type>Long</Type>
<Content>Continuous</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>FullTimePartTimeIndicator</ID>
<Name>FullTimePartTimeIndicator</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>StudentLocationCode</ID>
<Name>StudentLocationCode</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
<Column xsi:type=”ScalarMiningStructureColumn”>
<ID>MajorCertainty</ID>
<Name>MajorCertainty</Name>
<Type>Long</Type>
<Content>Discrete</Content>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>-1</DataSize>
<Trimming>Left</Trimming>
</KeyColumn>
</KeyColumns>
</Column>
</Columns>
<MiningModels>
<MiningModel>
<ID>Decision_Tree_8_3_1</ID>
<Name>Decision_Tree_8_3_1</Name>
<Algorithm>Microsoft_Decision_Trees</Algorithm>
<AlgorithmParameters>
<AlgorithmParameter>
<Name>COMPLEXITY_PENALTY</Name>
<Value xsi:type=”xsd:double”>0.8</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>SCORE_METHOD</Name>
<Value xsi:type=”xsd:int”>3</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>SPLIT_METHOD</Name>
<Value xsi:type=”xsd:int”>3</Value>
</AlgorithmParameter>
</AlgorithmParameters>
<Columns>
<Column>
<ID>StudentRetentionModelKey</ID>
<Name>StudentRetentionModelKey</Name>
<SourceColumnID>StudentRetentionModelKey</SourceColumnID>
<Usage>Key</Usage>
</Column>
<Column>
<ID>SemesterReturningCode</ID>
<Name>SemesterReturningCode</Name>
<SourceColumnID>SemesterReturningCode</SourceColumnID>
<Usage>PredictOnly</Usage>
</Column>
<Column>
<ID>AffordabilityGap</ID>
<Name>AffordabilityGap</Name>
<SourceColumnID>AffordabilityGap</SourceColumnID>
</Column>
<Column>
<ID>CareerGoalCertainty</ID>
<Name>CareerGoalCertainty</Name>
<SourceColumnID>CareerGoalCertainty</SourceColumnID>
</Column>
<Column>
<ID>CertificatePlan</ID>
<Name>CertificatePlan</Name>
<SourceColumnID>CertificatePlan</SourceColumnID>
</Column>
<Column>
<ID>DistanceFromCampus</ID>
<Name>DistanceFromCampus</Name>
<SourceColumnID>DistanceFromCampus</SourceColumnID>
</Column>
<Column>
<ID>EDUC108</ID>
<Name>EDUC108</Name>
<SourceColumnID>EDUC108</SourceColumnID>
</Column>
<Column>
<ID>EFCAmount</ID>
<Name>EFCAmount</Name>
<SourceColumnID>EFCAmount</SourceColumnID>
</Column>
<Column>
<ID>Gender</ID>
<Name>Gender</Name>
<SourceColumnID>Gender</SourceColumnID>
</Column>
<Column>
<ID>HighestMathSAT_SATIM</ID>
<Name>HighestMathSAT_SATIM</Name>
<SourceColumnID>HighestMathSAT_SATIM</SourceColumnID>
</Column>
<Column>
<ID>HighestVerbalSAT_SATIV</ID>
<Name>HighestVerbalSAT_SATIV</Name>
<SourceColumnID>HighestVerbalSAT_SATIV</SourceColumnID>
</Column>
<Column>
<ID>HUNDRED_POINT_SCALE_GPA</ID>
<Name>HUNDRED_POINT_SCALE_GPA</Name>
<SourceColumnID>HUNDRED_POINT_SCALE_GPA</SourceColumnID>
</Column>
<Column>
<ID>LateRegistration</ID>
<Name>LateRegistration</Name>
<SourceColumnID>LateRegistration</SourceColumnID>
</Column>
<Column>
<ID>LivingSituation</ID>
<Name>LivingSituation</Name>
<SourceColumnID>LivingSituation</SourceColumnID>
</Column>
<Column>
<ID>MATH101</ID>
<Name>MATH101</Name>
<SourceColumnID>MATH101</SourceColumnID>
</Column>
<Column>
<ID>CollegeGrantAmount</ID>
<Name>CollegeGrantAmount</Name>
<SourceColumnID>CollegeGrantAmount</SourceColumnID>
</Column>
<Column>
<ID>CollegeScholarshipAmount</ID>
<Name>CollegeScholarshipAmount</Name>
<SourceColumnID>CollegeScholarshipAmount</SourceColumnID>
</Column>
<Column>
<ID>PellAmount</ID>
<Name>PellAmount</Name>
<SourceColumnID>PellAmount</SourceColumnID>
</Column>
<Column>
<ID>StaffordLoanAmount</ID>
<Name>StaffordLoanAmount</Name>
<SourceColumnID>StaffordLoanAmount</SourceColumnID>
</Column>
<Column>
<ID>TAPAmount</ID>
<Name>TAPAmount</Name>
<SourceColumnID>TAPAmount</SourceColumnID>
</Column>
<Column>
<ID>TotalFundAvailableAmount</ID>
<Name>TotalFundAvailableAmount</Name>
<SourceColumnID>TotalFundAvailableAmount</SourceColumnID>
</Column>
<Column>
<ID>VeteranIndicator</ID>
<Name>VeteranIndicator</Name>
<SourceColumnID>VeteranIndicator</SourceColumnID>
</Column>
<Column>
<ID>WorkHours</ID>
<Name>WorkHours</Name>
<SourceColumnID>WorkHours</SourceColumnID>
</Column>
<Column>
<ID>WRIT100</ID>
<Name>WRIT100</Name>
<SourceColumnID>WRIT100</SourceColumnID>
</Column>
<Column>
<ID>FOUR_POINT_SCALE_GPA</ID>
<Name>FOUR_POINT_SCALE_GPA</Name>
<SourceColumnID>FOUR_POINT_SCALE_GPA</SourceColumnID>
</Column>
<Column>
<ID>FullTimePartTimeIndicator</ID>
<Name>FullTimePartTimeIndicator</Name>
<SourceColumnID>FullTimePartTimeIndicator</SourceColumnID>
</Column>

<Column>
<ID>MajorCertainty</ID>
<Name>MajorCertainty</Name>
<SourceColumnID>MajorCertainty</SourceColumnID>
</Column>
</Columns>
<Language>1033</Language>
<Collation>Latin1_General_CI_AS</Collation>
</MiningModel>
</MiningModels>
<ErrorConfiguration>
<KeyErrorLimit>-1</KeyErrorLimit>
<KeyErrorAction>DiscardRecord</KeyErrorAction>
<KeyNotFound>IgnoreError</KeyNotFound>
</ErrorConfiguration>
</MiningStructure>
</ObjectDefinition>
</Create>

NOTE:

  1. Make sure you prefix or suffix with structure while naming structure, so as to name it differently from model, else you may get error:Error (Data mining): The ‘Decision_Tree_8_3_1’ name does not uniquely identify the mining model or mining structure. Try to qualify this name by prefixing it with ‘MINING STRUCTURE’ or ‘MINING MODEL’.
  2. Make sure the in the SQL server Job Step the Run as proxy has Analysis Services Command, Analysis Services Query, and Operating System.
  3. We can generate the above Analysis Services Command, by right clicking on existing model and  selecting:

Script Mining Structure as => Create to

In SSMS connect to Analysis Server and the cube.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s