Ahmad Fattahi

Predictive Analytics with Excel Data Mining Add-In

Blog Post created by Ahmad Fattahi Employee on May 20, 2011

Let's do some data mining! One of the powerful set of features coming with MS SQL Server is data mining. To be more accurate, it comes as part of the SQL Server Analysis Services (SSAS). It comes with SQL Server Enterprise or Data Center. Using that you can build models on top of your data (e.g. a decision tree) and visualize the results for valuable insight into your data. In short, you can turn data into information.

 

For an easy start, we can start with the Excel client built to work with SSAS in a much more handy way. When installed, it adds a "Data Mining" tab to MS Excel making it available to mine data on the Excel sheets as well as external sources.

 

8422.Data-Mining-Tab.jpg

 

There are multiple features and functionalities in the package.

 

 4377.Data-Mining-Tab-_2D00_-Features.jpg

 

To start off with an easy and cool example, let's try to forecast where a PI tag will go next. For that matter, I used PI DataLink to grab the past 5 hours worth of sampled values of the tag Sinusoid on the Excel sheet and called the columns "Timestamp" and "Value".

 

 4834.Sinusoid-Values.jpg

 

Then I clicked on the button "Forecast" from the Data Mining tab in MS Excel. It starts a wizard allowing you to quickly build the forecasting model. All we need to do is to specify the range of the cells containing the timestamps and values (with possibly the column headers) and click Next.

 

 2350.Cells-Range.jpg

 

On the next window we are building the model by defining the inputs (both columns in this case) and indicating the first column as the time stamp. Click Next.

 

 4885.Indicating-inputs.jpg

 

Just accept the defaults on the next window and click Finish. The tool builds the model (using SSAS in the back end) and plots the existing data as well as some (5 by default) steps of prediction.

 

 7610.Prediction-Result.jpg

 

This is how easy it is! By choosing more steps of prediction you can extend the prediction further into the future. Obviously, the farther you get, the less trustworthy the prediction becomes. Also, by choosing the Model tab on the prediction window you can see the details of the predictive model. It can handle more than one independent variable as well.

 

This way, using our PI System, we can easily turn the insight into action by looking into the future!

 

 

Outcomes