Applies to: SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Summary: This article is a walkthrough that illustrates how to build multiple related data models by using the tools that are provided with Microsoft SQL Server Integration Services. In this walkthrough, you will learn how to automatically build and process multiple data mining models based on a single mining structure, how to create predictions from all related models, and how to save the results to a relational database for further analysis. Finally, you view and compare the predictions, historical trends, and model statistics in SQL Server Reporting Services reports.
This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.
Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.
This article is a walkthrough that illustrates how to use the data mining tools that are provided with Microsoft SQL Server Integration Services. If you are an experienced data miner, you probably already use the tools provided in Business Intelligence Development Studio or the Data Mining Client Add-in for Microsoft Excel for building or browsing mining models. However, Integration Services helps you automate many processes.
This solution also introduces the concept of ensemble models for data mining, which are sets of multiple related models. For most data mining projects, you need to create several models, analyze the differences, and compare outputs before you can select a best model to use operationally. Integration Services provides a framework within which you can easily generate and manage ensemble models.
• Configure the Integration Services components that are provided for data mining.
• Automatically build and update mining models by using Integration Services.
• Store mining model parameters and prediction results in the database engine.
• Integrate reporting requirements in the model design workflow.
Note that these are just a few of the ways that you can use Integration Services to incorporate data mining into analytic and data handling workflows. Hopefully these examples will help you get more mileage out of existing installations of Integration Services and SQL Server Analysis Services.
Automating the Creation of Data Mining Models
This scenario positions you as an analyst who has been tasked with creating some projections based on past sales data. You are unsure about how to configure the time series algorithm for best results (ARIMA? ARTXP? What hints to provide?). Moreover, you know that the modeling process typically involves building several models and testing different scenarios.
Rather than build variations on the model ad hoc, you decide to automatically generate multiple related models, varying the parameters systematically for each model. This way you can easily create many models, each using a different combination of periodicity hints and algorithm type. After you have created and processed all the models, you will put the historical data plus the predictions for each model into a series of reports to see which models provide good results.
This walkthrough demonstrates these features:
• Automatically building multiple mining models using parameters stored in SQL Server
• Generating bulk predictions for each model and storing them in SQL Server