Automation of Data Mining Using Integration Services

Create variables for the Foreach Loop container

Much of the work in this package is done by the variable assignments. You create one set of variables that store the text of the prediction queries, and then you insert the name of the mining models by looking it up in another variable, strModelName. This illustrates a useful technique in Integration Services: updating the contents of a variable by using an expression as the variable definition.

1. 
   With the Foreach Loop selected, create four new variables:
   

strQueryBaseAmount String

strQueryBaseQty String

strPredictAmt String

strPredictQty String

2. 
   For the value of variable strQueryBaseAmount, type the following query.
   

SELECT FLATTENED 'ModelNameHere' as [Model Name],

[ModelNameHere].[Model Region] as [Model and Region],

(SELECT $TIME as NewTime,

Amount as NewValue,

PredictStDev([Amount])as ValueStDev,

PredictVariance([Amount]) as ValueVariance

FROM PredictTimeSeries([ModelNameHere].[Amount],10) )

AS Predictions

FROM [ModelNameHere]

Important: The query here is formatted for readability, but the query will not work if you copy and paste these statements into the variable as is. You must copy the statement into a text editor first and remove all line breaks. Unfortunately the Integration Services editors do not detect line breaks or raise any errors while you are editing the task, but when you run the package, you will get an error. So be sure to remove the line breaks first!

3. 
   For the value of variable strQueryBaseQty, type the following query after removing the line breaks.
   

SELECT FLATTENED 'ModelNameHere' as [Model Name],

[ModelNameHere].[Model Region] as [Model and Region],

(SELECT $TIME as NewTime,

Quantity as NewValue,

PredictStDev([Quantity])as ValueStDev,

PredictVariance([Quantity]) as ValueVariance

FROM PredictTimeSeries([ModelNameHere].[Amount],10) )

AS Predictions

FROM [ModelNameHere]

Notice the placeholder, ModelNameHere, in this procedure. This placeholder will be replaced with a valid model name, which the package gets from the variable strModelName.

The next steps explain how to create an expression that updates the query text each time the loop is executed.

4. 
   In the Variables window, select the variable strPredictQty, and then open or select the Properties window to see the extended properties of the variable.
   
5. 
   Locate Evaluate as Expression and set the value to True.
   
6. 
   Locate Expression and type or paste in the following expression.
   

REPLACE( @[User::strQueryBaseQty] , "ModelNameHere", @[User::strModelName2] )

4. 
   Repeat this process for the variable strPredictAmt, using the following expression.
   

REPLACE( @[User::strQueryBaseAmount] , "ModelNameHere", @[User::strModelName2] )

Create the Data Mining Query task (Predict Amt)

Now that you’ve configured the variables, most of the work is done. All you have to do is create a pair of Data Mining Query tasks. Each task gets the updated query string out of the variable that you just created, runs the query, and then saves the predictions to the specified output:

1. 
   Drop a new Data Mining Query task into the Foreach Loop container. Name it Predict Amt.
   
2. 
   In the Data Mining Query Task Editor, on the Mining Model tab, for Connection, choose the name of the Analysis Services instance that hosts your models. For example:.ForecastingModels.
   
3. 
   For Mining structure, choose Forecasting.
   
4. 
   On the Output tab, for Connection, choose the instance of the database engine where you will store the results. For example, .DM_Reporting.
   
5. 
   For Output table, select or type the table name, tmpPredictionResults. Choose the option Drop and re-create the output table. (Note: If this package has never been run, you must type the name of the table, and the task will then create it. However, if you are rerunning the package, the table already exists, so you must drop and then rebuild it.)
   
6. 
   On the Query tab, for Build query, you can paste in the base query temporarily. It will be replaced with the contents of a variable. After you run the package once, you should see the text of the base query.
   
7. 
   With the Predict Amt task selected, open the Properties pane and locate the Expressions property.
   
8. 
   Expand the list of expressions and add the variable @[User::strPredictAmt] as the value for the QueryString property. You can also select the value from a list by clicking the Browse (...) button.
   

Create the second Data Mining Query task (Predict Qty)

Repeat the steps just described for a query that does the same thing, only with Quantity as the predictable column:

1. 
   Create a new Data Mining Query task named Predict Qty.
   
2. 
   Repeat steps 2-6 from the previous procedure exactly as described.
   
3. 
   With the Predict Qty task selected, open the Properties pane and locate the Expressions property.
   
4. 
   Expand the list of expressions and add the variable @[User::strPredictQty] as the value for the QueryString property.
   

After you run the package once, the DMX statement contains blank brackets, like these.

SELECT FLATTENED '' as [Model Name],

[].[Model Region] as [Model and Region],

(SELECT $TIME as NewTime, Amount as NewValue,

PredictStDev([Amount])as ValueStDev,

PredictVariance([Amount]) as ValueVariance

FROM PredictTimeSeries([].[Amount],10) )

AS Predictions

FROM []

These brackets will be populated by a variable that supplies the model name at run time.

To summarize all the variable activity at run time:

• 
  The package gets a variable with a list of models.
  
• 
  The loop gets the name of one model from the list.
  
• 
  The loop gets a prediction query from a variable, inserts the model name, and writes out a new prediction query.
  
• 
  The query task executes the updated prediction query.
  

Note that these prediction queries all write their results to the same temporary table, which is dropped and then rebuilt during each loop. Therefore, you need to add a Data Flow task in between, which moves the results to the archive table and also adds some metadata.