DROP TABLE DUMMYDATA;

CREATE TABLE DUMMYDATA (

accountnr nvarchar(11) default NULL,

location varchar(100) default NULL,

total_amount double default NULL

);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('82540748399','Netherlands',385);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('99692723699','Morocco',992);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('98804020299','Israel',176);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('24364537999','Estonia',41);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('97592208699','South Africa',308);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('55783852899','Niger',332);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('12338489699','Malaysia',582);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('66941296899','Angola',332);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78341775899','Japan',778);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('30205810399','Indonesia',665);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('35766575899','Madagascar',616);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('23270858999','Malta',354);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('91578369899','Bahrain',231);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('76401095499','Central African Republic',412);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('00913274699','Namibia',71);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('50694975399','South Georgia and The South Sandwich Islands',176);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('01488053499','Solomon Islands',878);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('96932672899','Northern Mariana Islands',748);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('91282172899','Liberia',122);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('79787992099','Jordan',800);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('67397439699','El Salvador',539);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('63992562599','Macedonia',57);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('43356492199','San Marino',696);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('51670856799','Sao Tome and Principe',640);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('19947726599','Ukraine',270);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('99834072799','Ukraine',806);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('31917565199','French Guiana',27);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('90613141899','Ireland',369);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('75361130899','Guinea',929);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('39763551799','Jersey',385);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('94627738599','Equatorial Guinea',615);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('57356613099','Sao Tome and Principe',354);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('34330084699','Azerbaijan',949);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('02988459599','French Polynesia',418);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('79935287199','Korea, South',15);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('06403930099','Vanuatu',781);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('41841311499','Belarus',820);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('34791974399','Uzbekistan',984);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('24750820299','Ethiopia',791);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('68989297999','Anguilla',589);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('37448014599','Niger',42);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('41325924899','Isle of Man',233);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('83463321899','United Arab Emirates',535);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('87960002399','Albania',800);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('42469535999','Georgia',783);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('18539856499','Djibouti',716);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('32610094899','Cameroon',482);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78884234499','Guyana',912);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('93904093399','Mauritania',495);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('81519284299','Martinique',387);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('94861577199','Grenada',89);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('23701374899','Sri Lanka',161);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('10334180899','Gambia',473);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('63747550799','Ukraine',375);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('66384175199','Tokelau',946);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('70471211599','Tokelau',45);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('94537787299','Bosnia and Herzegovina',956);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('41888115799','Thailand',101);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('84851632199','Bonaire, Sint Eustatius and Saba',957);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('12369169799','Romania',323);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('10320338499','Sudan',128);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('23973062599','Philippines',558);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('80234682699','Grenada',212);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('92435601999','Norway',305);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('05213022999','Samoa',714);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('34385740899','Guam',413);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('93686420399','Niger',957);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('39548974899','Aruba',89);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('14873205199','Guinea-Bissau',485);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('70546477699','Tanzania',364);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('09430444999','Seychelles',323);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('58371537199','Cocos (Keeling) Islands',733);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('25609325299','Tajikistan',167);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('85253147299','Ghana',70);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('90265537999','Peru',96);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('87529168299','Bolivia',642);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('65449829999','Norfolk Island',566);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('75845729799','Jamaica',568);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('04342023699','Germany',5);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('66802523699','Colombia',899);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78554702399','Senegal',774);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('57575869099','Tunisia',26);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('09291636899','Burundi',655);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('68688029599','Argentina',790);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('54674462999','Zambia',684);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('07752768499','Bolivia',454);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('63610114499','Samoa',78);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('80322453199','Tajikistan',47);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('01611711299','Latvia',821);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('05742930199','Curaçao',637);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78351684399','United States Minor Outlying Islands',969);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('28553427199','Ethiopia',524);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('99534559099','Ukraine',94);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('75215421499','Uganda',958);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('37984032999','Bermuda',193);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('20604544399','Tanzania',961);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('29957102299','Samoa',4);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('76457446499','Lebanon',72);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('67280243199','Antarctica',503);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('01625239599','Yemen',902);

title: ABC Analysis with SAP HANA

description: Using SAP HANA Predictive Analytics Library for ABC Analysis

tags: [ tutorial>beginner, products>sap-hana, products>sap-hana-cloud-platform ]

- **Proficiency:** Beginner

- **Tutorials:**

- [Internet of Things (IoT) made easy](http://go.sap.com/developer/tutorials/teched-2016-9.html)

- Your SAP HANA MDC database is created and running in SAP HANA Cloud Platform account and you have Developer access to it

- Select a tutorial from the [Tutorial Navigator](http://go.sap.com/developer/tutorial-navigator.html) or the [Tutorial Catalog](http://go.sap.com/developer/tutorials.html)

You will use the SAP HANA Predictive Analytics Library to create and run an algorithm to do analysis of data in separate groups (also known as "ABC" analysis).

**10 Min**.

[ACCORDION-BEGIN [Step 1: ](Open your Databases and Schemas tab in HCP)]

In your SAP HCP Trial account go to `Databases & Schemas` and check you have database of the system type `HANA MDC (<trial>)` created.


[DONE]

[ACCORDION-END]

[ACCORDION-BEGIN [Step 2: ](Verify database state)]

The database should be in the state `STARTED`. Click on `SAP HANA Web-based Development Workbench` and logon using your developer user. For brevity, you are using the user `SYSTEM`, but ideally a separate user should be used.


[DONE]

[ACCORDION-END]

[ACCORDION-BEGIN [Step 3: ](Open the Catalog tool)]

On the SAP HANA Web-based Development Workbench screen click `Catalog` to open Catalog tool, which allows you to create, edit, execute and manage SQL catalog artifacts in the SAP HANA database


Then in the Catalog tool click on `SQL` icon to open a new SQL Console. Type the following code:

SELECT * FROM "SYS"."AFL_FUNCTIONS" WHERE AREA_NAME = 'AFLPAL'

ORDER BY FUNCTION_NAME;

If Predictive Analytics Libary (PAL) is installed in the SAP HANA instance, then the query will return the list of PAL predictive analytics algorithms available in the library. You should be able to find `ABC` among them and this is the one you are going to use in this tutorial.


[DONE]

[ACCORDION-END]

[ACCORDION-BEGIN [Step 4: ](Introduction to "PAL")]

The **Predictive Analysis Library (PAL)** defines functions that can be called from within SQLScript procedures to perform analytic algorithms. PAL includes classic and universal predictive analysis algorithms in different data-mining categories, like Clustering, Classification, Regression, Association, Social Network Analysis etc.

ABC Analysis algorithm is used to classify objects (such as customers or products) based on a particular measure (such as revenue or profit). It suggests that inventories of an organization are not of equal value, thus can be grouped into three categories (A, B, and C) by their estimated importance. “A” items are very important for an organization. “B” items are of medium importance. “C” items are of the least importance.

An example of ABC classification is as follows:

- “A” items – 20% of the items (customers) accounts for 70% of the revenue.

- “B” items – 30% of the items (customers) accounts for 20% of the revenue.

- “C” items – 50% of the items (customers) accounts for 10% of the revenue.

In this tutorial you will classify customers into ABC groups accordingly to the revenue they generate based on some dummy data.

[DONE]

[ACCORDION-END]

[ACCORDION-BEGIN [Step 5: ](Create a new schema)]

First create a new schema `TUTORIAL_PAL_ABC` and a table with some dummy data.

CREATE SCHEMA "TUTORIAL_PAL_ABC";

SET SCHEMA "TUTORIAL_PAL_ABC";

CREATE COLUMN TABLE DUMMYDATA (

accountnr varchar(11) default NULL,

location varchar(100) default NULL,

total_amount double default NULL

);

Next load 100 records with randomly generated data from the file [pal_abc_data.txt](pal_abc_data.txt) - **This is actually an SQL file and should be treated as such**, where you find INSERT statements like

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('51670856799','Sao Tome and Principe',640);

INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('19947726599','Ukraine',270);

Commit inserts and verify all records has been loaded.

COMMIT;

SELECT COUNT(*) FROM "TUTORIAL_PAL_ABC"."DUMMYDATA";


And create a view that will be the input to the ABC algorithm.

CREATE VIEW "TUTORIAL_PAL_ABC"."V_ABC_INPUT" as (SELECT ACCOUNTNR as SOMEOBJECT, TOTAL_AMOUNT as SOMEAMOUNT from "DUMMYDATA");

SELECT COUNT(*) FROM "TUTORIAL_PAL_ABC"."V_ABC_INPUT";

[DONE]

[ACCORDION-END]

[ACCORDION-BEGIN [Step 6: ](Create the PAL procedure)]

To use PAL functions, you must do the following:

- Generate a procedure that wraps the PAL function.

- Call the procedure, for example, from an SQLScript procedure.

Use the following code to generate the wrap procedure:

DROP TYPE PAL_ABC_DATA_VIEW_T;

CREATE TYPE PAL_ABC_DATA_VIEW_T AS TABLE ("SOMEOBJECT" NVARCHAR(80), "SOMEAMOUNT" DOUBLE);

DROP TYPE PAL_CONTROL_T;

CREATE TYPE PAL_CONTROL_T AS TABLE("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE, "strArgs" VARCHAR(100));

DROP TYPE PAL_ABC_RESULT_T;

CREATE TYPE PAL_ABC_RESULT_T AS TABLE("ABC" NVARCHAR(10), "SOMEOBJECT" NVARCHAR(80));

DROP TABLE PAL_ABC_PDATA_TBL;

CREATE COLUMN TABLE PAL_ABC_PDATA_TBL(

"POSITION" INT,

"SCHEMA_NAME" NVARCHAR(256),

"TYPE_NAME" NVARCHAR(256),

"PARAMETER_TYPE" VARCHAR(7)

);

INSERT INTO PAL_ABC_PDATA_TBL VALUES (1,'TUTORIAL_PAL_ABC','PAL_ABC_DATA_VIEW_T', 'in');

INSERT INTO PAL_ABC_PDATA_TBL VALUES (2,'TUTORIAL_PAL_ABC','PAL_CONTROL_T', 'in');

INSERT INTO PAL_ABC_PDATA_TBL VALUES (3,'TUTORIAL_PAL_ABC','PAL_ABC_RESULT_T','out');

CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('TUTORIAL_PAL_ABC', 'PAL_ABC_CALCULATE');

CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE ('AFLPAL', 'ABC', 'TUTORIAL_PAL_ABC', 'PAL_ABC_CALCULATE', "TUTORIAL_PAL_ABC"."PAL_ABC_PDATA_TBL");

You should see following objects create in the `TUTORIAL_PAL_ABC` schema:


[DONE]

[ACCORDION-END]

[ACCORDION-BEGIN [Step 7: ](Run the PAL procedure)]

Now execute the procedure with the desired allocation of A, B, and C groups and desired nr of parallel calculation threads. In this example you will have `70%-20%-10%` distribution and 5 threads.

DROP TABLE "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL";

CREATE COLUMN TABLE "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL"

("Name" NVARCHAR(100) NOT NULL,

"intArgs" INTEGER,

"doubleArgs" DECIMAL(15, 2),

"strArgs" NVARCHAR(100),

PRIMARY KEY ("Name"));

TRUNCATE TABLE "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL";

INSERT INTO "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL"

SELECT 'THREAD_NUMBER',5,null,null from DUMMY UNION ALL

SELECT 'PERCENT_A',null,0.70,null from DUMMY UNION ALL

SELECT 'PERCENT_B',null,0.20,null from DUMMY UNION ALL

SELECT 'PERCENT_C',null,0.10,null from DUMMY;

DROP TABLE "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL";

CREATE COLUMN TABLE "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL"

("ABC" NVARCHAR(10) NOT NULL,

"SOMEOBJECT" NVARCHAR(100) NOT NULL,

PRIMARY KEY ("ABC", "SOMEOBJECT"));

TRUNCATE TABLE "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL";

CALL "TUTORIAL_PAL_ABC"."PAL_ABC_CALCULATE"("TUTORIAL_PAL_ABC"."V_ABC_INPUT","TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL","TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL") WITH OVERVIEW;

As a result the table `"TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL"` should be populated with the results of the ABC Analysis with account numbers assigned to particular A, B, or C group based on their revenue.

[DONE]

[ACCORDION-END]

[ACCORDION-BEGIN [Step 8: ](Check your results)]

Check the results. Firstly, check what group each account has been assigned to.

SELECT "ABC" AS ABC, T."SOMEOBJECT" AS COMPANY, "SOMEAMOUNT" AS GROSS_AMOUNT

FROM "TUTORIAL_PAL_ABC"."V_ABC_INPUT" AS T INNER JOIN "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL" AS R ON T."SOMEOBJECT" = R."SOMEOBJECT"

ORDER BY 1, 3 desc;


Secondly, using SAP HANA's SQL window functions you can calculate percentages of the total revenue falling into particular groups and compare to desired `70-20-10` distribution.

SELECT "ABC" AS ABC, count(*) AS NR_OF_ACCOUNTS, sum("SOMEAMOUNT") AS GROSS_AMOUNT, round(sum("SOMEAMOUNT") / (sum(sum("SOMEAMOUNT") ) over ()),2) AS PT_SHARE

FROM "TUTORIAL_PAL_ABC"."V_ABC_INPUT" AS T INNER JOIN "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL" AS R ON T."SOMEOBJECT" = R."SOMEOBJECT"

GROUP BY "ABC"

ORDER BY 1;


In real-world distribution you would expect group A has a smaller count, but this is randomly generated data.

You have run your first PAL algorithm.

[DONE]

[ACCORDION-END]

- Select a tutorial from the [Tutorial Navigator](http://go.sap.com/developer/tutorial-navigator.html) or the [Tutorial Catalog](http://go.sap.com/developer/tutorials.html)