Leveraging the power of Matlab predictions with SAP Data Warehouse Cloud

Big data, data lake, predictive algorithms, those are words and concepts that we are now all familiar with. However, have you ever seen a use case mixing data coming from SAP Data Warehouse Cloud, classification models from Matlab and dashboards from SAP Analytics Cloud? That’s what we’ll do today! We will transform basic information on car insurance customers into a powerful classification model and visualize everything in a dashboard to leverage all the insights!

 

Table of Contents

• Use Case & Data Introduction
• Schema Creation in SAP Data Warehouse Cloud
• Set the JDBC Connection in Matlab
• Classification Model Creation
• Dashboard Creation in SAP Analytics Cloud
• Conclusion

 

Use Case & Data Introduction

The use case is simple. We have some data about current customers that have a car insurance within our company, and we know if they already had an accident or not. Thanks to a classification model, we’ll be able to know for each new customer if he’s likely to have an accident or not.

 

TRAINING DATA

For each customer, we have the age, the number of children, the car category, and others.

Our target is the last column, “Accident”.

Training Data

APPLICATION DATA

We have the same data for new customers except for the last column which is missing and that we’ll predict using Matlab predictive capabilities.

Application Data

Schema Creation in SAP Data Warehouse Cloud

We’ll start with the creation of an open SQL Schema that will allow the connection between SAP Data Warehouse Cloud and Matlab. In order to do so, go to your space settings > “Schema Access”. Then, “Create an Open SQL Schema”. You’ll be rewarded with a username and a password, keep them preciously.

Make sure to check on the first screen “Do you want the data in this space to be consumable by default” and on the second screen “Consume Space Schema Data”.

Open SQL Schema Creation (1)

Open SQL Schema Creation (2)

The views of the training and application data set must be consumable in SAP Data Warehouse Cloud in order for Matlab to access it.

Make the view consumable

Set the JDBC Connection in Matlab

If you don’t already have it, install the JDBC driver from SAP HANA (https://tools.eu1.hana.ondemand.com/#hanatools).

Once the installation is done, you can now open Matlab, click on “APPS” and then on “Database Explorer”.

Select Database Explorer

Click on “Configure Data Source” and then on “Configure JDBC data source”.

Select Configure JDBC data source

Give it a name, select “Other” for the Vendor and select your driver location.

For the Driver: com.sap.db.jdbc.Driver

For the URL: jdbc:sap://hostname:port/?encrypt=true&validateCertificate=true

While using your SAP Data Warehouse Cloud host name and port. You’re now able to access the data that are in your SAP Data Warehouse Cloud space!

JDBC Driver Parameters

 

Schema Creation in SAP Data Warehouse Cloud

The first step is to import the data from SAP Data Warehouse Cloud to Matlab.

% Open the connection
conn = database('JDBC Connection Name','Schema username','Password');
% Import of the training data
training_data = sqlread(conn,'CONS_STARGATE.ANALYTICS_INSURANCE');
% Import of the application data
my_new_customers = sqlread(conn,'CONS_STARGATE.ANALYTICS_INSURANCE_NEW');

Then, select a “Classification Learner”.

Select Classification Learner

Click on “New Session” and then on “From Workspace”.

Select New Session

Select your training data, then the target “Accident” and uncheck ContractId as it has no impact on the target. Click on “Start Session”.

Model Parameters

Click on “Train”.

Select Train

Once the model is done, click on “Export” and “Generate Function”. It will give you a script that you will be able to use to generate your model whenever you want in the main command window.

Export the script

Thanks to the generated code, I can now easily train my model in the main command window and apply it. The last line writes back in SAP Data Warehouse Cloud.

% This code processes the data into the right shape for training the model
inputTable = training_data;
predictorNames = {'Age', 'DrivingLicenceYears', 'AnnualKilometers', 'Gender', 'Children', 'Profession', 'CustomerType', 'MultipleCars', 'CarCategory', 'Gearbox', 'Fuel'};
predictors = inputTable(:, predictorNames);
response = inputTable.Accident;
isCategoricalPredictor = [false, false, false, true, true, true, true, true, true, true, true];
% Train a regression model
classificationTree = fitctree(...
    predictors, ...
    response, ...
    'SplitCriterion', 'gdi', ...
    'MaxNumSplits', 100, ...
    'Surrogate', 'off', ...
    'ClassNames', {'No'; 'Yes'});
% Create the result struct with predict function
predictorExtractionFcn = @(t) t(:, predictorNames);
treePredictFcn = @(x) predict(classificationTree, x);
trainedClassifier.predictFcn = @(x) treePredictFcn(predictorExtractionFcn(x));
% Add additional fields to the result struct
trainedClassifier.RequiredVariables = {'Age', 'AnnualKilometers', 'CarCategory', 'Children', 'CustomerType', 'DrivingLicenceYears', 'Fuel', 'Gearbox', 'Gender', 'MultipleCars', 'Profession'};
trainedClassifier.ClassificationTree = classificationTree;
trainedClassifier.About = 'This struct is a trained model exported from Classification Learner R2020a.';
trainedClassifier.HowToPredict = sprintf('To make predictions on a new table, T, use: \n  yfit = c.predictFcn(T) \nreplacing ''c'' with the name of the variable that is this struct, e.g. ''trainedModel''. \n \nThe table, T, must contain the variables returned by: \n  c.RequiredVariables \nVariable formats (e.g. matrix/vector, datatype) must match the original training data. \nAdditional variables are ignored. \n \nFor more information, see <a href="matlab:helpview(fullfile(docroot, ''stats'', ''stats.map''), ''appclassification_exportmodeltoworkspace'')">How to predict using an exported model</a>.');
% Perform cross-validation
partitionedModel = crossval(trainedClassifier.ClassificationTree, 'KFold', 5);
% Compute validation predictions
[validationPredictions, validationScores] = kfoldPredict(partitionedModel);
% Compute validation accuracy
validationAccuracy = 1 - kfoldLoss(partitionedModel, 'LossFun', 'ClassifError');
% Apply the model and get the results
results = trainedClassifier.predictFcn(my_new_customers);
% Add a new column to the data my_new_customers with the output of the model
my_new_customers.Result = trainedClassifier.predictFcn(my_new_customers);
% Write back this result in DWC
sqlwrite(conn,'RESULTS',my_new_customers);

 

Dashboard Creation in SAP Analytics Cloud

In SAP Data Warehouse Cloud, to retrieve the “RESULTS” table you created, go to “Data Builder”, select your space > “New SQL View”, and select “Source” to find the table. You can now create an analytical view on top of it to get this result.

This first tab gives an overview of the current customers of our insurance company: number of accidents, repartition of the accidents among our customers and the repartition per dimension (children, car category, gender and others).

In this second tab, we can see the number of potentially new customers that are likely to have an accident. You can easily export the table with the ID of the customers if needed.

SAP Analytics Cloud Dashboard (1)

SAP Analytics Cloud Dashboard (2)

Export of the results if needed

 

Conclusion

In one day, we can easily go from simple and raw data stored in SAP Data Warehouse Cloud to a meaningful classification model created in Matlab leveraged by a beautiful SAP Analytics Cloud dashboard. There are various toolboxes in Matlab that you can use to highlight your story such as Predictive Maintenance, Deep Learning, Reinforcement Learning, Text Analytics and other. Moreover, you can work with images and audios too. Imagine all the advanced scenarios you could build using the best of the three tools!

Virginie Boullery & Yuliya Reich