How to Write Back #MSSQL Data with #Statistica IDP

OK, I finally figured out how to configure Statistica to write back my results from data mining to the database using IDP.

Database Connection

The database is a Microsoft SQL Server 2012 database. After some testing, I came up with the best query options to be:

According to the Statistica Online help, the cursor should be “Batch Optimistic” in most cases – however, the best option depends on your database (especially, if the database supports batch optimistic drivers at all). In our case, Microsoft SQL Server 2012 supports the cursor type.

The Statistica online help states that:

If you intend to write information back to the input database via the Rapid Deployment of Models options for computing predicted values and classifications (and other statistics), review the options described below carefully to select the appropriate Cursor Type and Lock Type consistent with that operation. Specifically, choose a Lock Type other than Read Only and either a Server Side Dynamic or Keyset cursor, or a Client Side Static cursor. Generally, a Lock Type of Batch Optimistic will be most efficient (updates are sent to the remote database in batches rather than one record at a time.) However, not all database environments will support this option.

 Project Setup

The setup of the project is as follows:

A neural network is trained (in our case with vehicles sold in 2011) from an IDP source (on the top of the Statistica Data Miner Workspace). The PMML script of the model is deployed / exported to disk:

Make sure to select the “Saves PMML code” option to True, otherwise the file will not be written.

To process the test data (in this case, all vehicles sold in 2012), a “Rapid Deployment” node (see Rapid Deployment of Statistic Models Overview) is used. That node builds the model using the saved PMML script and processes the test data from another IDP source.

The “Selection of variables” option defines if the variable selection should come from the test dataset or from the PMML file.

Writing Back the Data

To write back the data, the options on the “Save results” tab must be configured:

Enable the option “Writing back to input data” and assign the prediction variables to the database columns using the “Assign” button:

Once the variables has been assigned, the Rapid Deployment node writes the data back to the database on the next run.

One thought on “How to Write Back #MSSQL Data with #Statistica IDP

  1. Just received an email from a StatSoft employee. They liked the post and realized that the topic is missing in their documentation. They will fix it.

Leave a Reply

Your email address will not be published. Required fields are marked *