Scoring data in ADAPA via Web Services using SSIS 2012 (SQL Server Integration Services)

An important application of Big Data is the use of data in real-time. How to detect changes in databases and immediately make the predictions translating the data as useful information sequentially becomes more and more essential. ADAPA® is a real-time scoring engine. Here is a simple way to utilize ADAPA® to do real-time prediction - Connecting SQL Server Integration Service (SSIS) with ADAPA® via Web Services API. This way allows ADAPA® immediately generating the predictions when any data is changed from Microsoft SQL Server.



The process will be:

  • A model in PMML format is uploaded in ADAPA® scoring engine
  • A record in Microsoft SQL Server is added or changed
  • A Web Service call is automatically triggered to connect ADAPA®
  • ADAPA® responses a new prediction
  • A predictive score is inserted or updated back to Microsoft SQL Server




Before the MS SQL server configuration, we have two steps related to the configuration of certificate and the model uploading on ADAPA®:

Step 1: Installing the Zementis Security Certificate

First, we need to have the "Zementis Security Certificate" installed as a trusted 3rd party root certification authority. To install the certificate, follow the instructions posted HERE. Follow the instructions on how to install a certificate on a server with Microsoft Management Console (MMC). Instead of importing the certificate to the "Personal" folder under "Certificates", choose instead "Third-Party Root Certification Authorities". See figure below:



Step 2: Uploading PMML models into ADAPA®

In the example below, we uploaded several models via ADAPA® Console. Those models are available in the PMML Examples page of the Zementis website. For the model Iris_NN.xml, it is the respective PMML exports of a neural network model built to solve the Iris classification problem. The figure below shows the ADAPA® Console after some models have been uploaded.



With first two steps done, we provide a how-to video to demonstrate how easy it is to score data from a MSSQL database with the ADAPA® predictive analytics engine by using a Microsoft SSIS, SQL Agent and Triggers.

Step 3: Installing SSIS package, configuring the SQL Agent, and writing the Functions

Using a SQL Agent to integrate SSIS with MSSQL and ADAPA® dramatically reduces complexity and enables rich levels of transformations and execution of advanced predictive models based on the Predictive Model Markup Language (PMML) industry standard in ADAPA®.

This self-contained example showcases how to remotely execute a predictive model in ADAPA® through its Web Service API. The key advantage of using SSIS to access ADAPA® is that further level of data and control flow actions can be taken to cleanse the data or take additional actions based upon the results returned from ADAPA® while also automatically saving the score request back into the MSSQL database.

This video covers:

  1. Installing the SSIS package into MSSQL
  2. Configuring the SQL Agent to run the SSIS package
  3. Writing functions for scoring, updating and inserting data


The SSIS package we demonstrate in the video could be download by clicking HERE. (This is a zip file that you will need to unzip.). Remember to replace "" and "yourADAPApassword" in "main.cs" by the e-mail and password used to access the ADAPA® Console.

client.ClientCredentials.UserName.UserName = "";

client.ClientCredentials.UserName.Password = "yourADAPApassword";




Article is closed for comments.
Powered by Zendesk