How to automate training mining model in SSAS using SQL server jobs

Using DMX query we can MANUALLY train using:

INSERT INTO Decision_Tree_8_3_1 ([StudentRetentionModelKey] ,
[SemesterReturningCode] ,
[AffordabilityGap],
CareerGoalCertainty ,
[CertificatePlan] ,
[DistanceFromCampus],
[EDUC108] ,
[EFCAmount],
Gender ,
[HighestMathSAT_SATIM],
[HighestVerbalSAT_SATIV],
[HUNDRED_POINT_SCALE_GPA],
[LateRegistration] ,
[LivingSituation] ,
MATH101 ,
[CollegeGrantAmount],
[CollegeScholarshipAmount],
[PellAmount],
[StaffordLoanAmount],
[TAPAmount],
[TotalFundAvailableAmount],
[VeteranIndicator] ,
[WorkHours] ,
[WRIT100],
[FOUR_POINT_SCALE_GPA],
[FullTimePartTimeIndicator] ,

[MajorCertainty] )
OPENQUERY([Databasename],
‘SELECT StudentRetentionModelKey, [SemesterReturningCode] ,
[AffordabilityGap],
CareerGoalCertainty ,
[CertificatePlan] ,
[DistanceFromCampus],
[EDUC108] ,
[EFCAmount],
Gender ,
[HighestMathSAT_SATIM],
[HighestVerbalSAT_SATIV],
[HUNDRED_POINT_SCALE_GPA],
[LateRegistration] ,
[LivingSituation] ,
MATH101 ,
[CollegeGrantAmount],
[CollegeScholarshipAmount],
[PellAmount],
[StaffordLoanAmount],
[TAPAmount],
[TotalFundAvailableAmount],
[VeteranIndicator] ,
[WorkHours] ,
[WRIT100],

[FOUR_POINT_SCALE_GPA],
[FullTimePartTimeIndicator] ,

[MajorCertainty]
FROM RetentionModeltable
‘ );

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 Query and we can use the same above DMX query.

We will have to:

  1. Provide SSAS server name.
  2. Provide SSAS Database name.
  3. Make sure the in the SQL server Job Step the Run as proxy has Analysis Services Command, Analysis Services Query, and Operating System.
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