Automation of Data Mining Using Integration Services

Create package and variables (ProcessEmptyModels.dtsx)

1. 
   Create a new Integration Services package and name it ProcessEmptyModels.dtsx.
   
2. 
   With the package background selected, add a user variable, objModelsList. This variable will have package scope and will be used to store the list of models that are available on the server.
   

Add a Data Mining Query task (Execute DMX Query)

1. 
   Create a new Data Mining Query task and name it Execute DMX Query.
   
2. 
   In the Data Mining Query Task Editor, on the Mining Model tab, specify the Analysis Services database that contains the time series mining models.
   
3. 
   For Mining Structure, choose Forecasting.
   
4. 
   Click the Query tab. Here, instead of creating a prediction query, paste in the following text of a content query. A content query returns metadata about the model and data already stored in the model in the form of summary statistics.
   

SELECT MODEL_NAME, IS_POPULATED, LAST_PROCESSED, TRAINING_SET_SIZE

FROM $system.DM_SCHEMA_MINING_MODELS

Not all of these columns are needed for processing, but you can add the columns now and update the information later.

5. 
   On the Output tab, for Connection, select the relational database where you will store the results. For this solution, it is /DM_Reporting.
   
6. 
   For Output table, type a temporary table name (in this solution, tmpProcessingStatus) and then select the option Drop and re-create the output table.
   

Create Execute SQL task (List Unprocessed Models)

1. 
   Add a new Execute SQL Task, and name it List Unprocessed Models. Connect it to the previous task.
   
2. 
   In the Execute SQL Task Editor, for Connection, use an OLE DB connection, and then choose the server name: for example, /DM_Reporting.
   
3. 
   For Result set, select Full result set.
   
4. 
   For SQLSourceType, select Direct input.
   
5. 
   For SQL Statement, type the following query text.
   

SELECT MODEL_NAME FROM tmpProcessingStatus

6. 
   On the Result Set tab, assign the columns in the result set to variables. There is only one column in the result set, so you assign the variable, User::objModelList, to ResultSet 0 (zero).
   

Create a Foreach Loop container (Foreach Model in Variable)

By now you should be pretty comfortable with using the combination of an Execute SQL task and a Foreach Loop container.

1. 
   Create a new Foreach Loop container and name it Foreach Model in Variable. Connect it to the previous task.
   
2. 
   With the Foreach Loop container selected, open the Variables window, and then add three variables scoped to the Foreach Loop container. The latter two variables work together: you store the processing command template in one variable, and then you use an Integration Services expression to alter the text and save the changes to the second variable:
   

strModelName1 String

strXMLAProcess1 String

strXMLAProcess2 String

3. 
   In the Foreach Loop Editor, set the enumerator type to ForEach ADO enumerator.
   
4. 
   In the Enumerator configuration pane, set ADO object source variable to User::objModelList.
   
5. 
   Set Enumeration mode to Rows in first table only.
   
6. 
   In the Variables mapping pane, assign the variable, User::strModelName1, to Index 1. This means that each row of the single-column table returned by the query will be fed into the variable.
   

Add an Analysis Services Processing task to the Foreach Loop (Process Current Model)

The editor for this task requires that you first connect to an Analysis Services database and then choose from a list of objects that can be processed. However, because you need to automate this task, you can’t use the interface to choose the objects to process. So how do you iterate through a list of objects for processing?

The solution is to use an expression to alter the contents of the property, ProcessingCommand. You use the variable, strXMLAProcess1, which you set up earlier, to store the basic XMLA for processing a model, but you insert a placeholder that you can modify later when you read the variable. You alter the command using an expression and write the new XMLA out to a second variable, strXMLAProcess2.

1. 
   Drag a new Analysis Services Processing task into the Foreach Loop container you just created. Name it Process Current Model.
   
2. 
   With the Foreach Loop selected, open the Variables window, and then select the variable User::strXMLAProcess2.
   
3. 
   In the Properties pane, select Evaluate as expression and set it to True.
   
4. 
   For the value of the variable, type or build this expression.
   

REPLACE( @[User::strXMLAProcess1] , "ModelNameHere", @[User::strModelName1] )

5. 
   In the Analysis Services Processing Task Editor, click Expressions, and then expand the list of expressions.
   
6. 
   Select ProcessingCommand and then type the variable name as follows:
   

@[User::strXMLAProcess2]

Another way to train the model would be to add a processing task within the same Foreach Loop that you used to create the model. However, there are good reasons to build and process the models in separate packages. For example:

• 
  Processing can be time-consuming, and it depends on connections to source data.
  
• 
  It is easier to debug problems when model creation and processing are in separate packages.
  

Moreover, the Data Mining Query task that is provided in the Control Flow can be used to execute many different types of queries against an Analysis Services data source. You can use schema rowset queries within this task to get information about other Analysis Services objects, including cubes and tabular models, or even run Data Mining Extensions (DMX) DDL statements. (In contrast, the Data Mining Query Transformation component, available in the Data Flow, can only be used to create predictions against an existing mining model.)

The final step in this phase is to add a task that updates the status of your mining models. You can now execute this package as before.