ABC Analysis with SAP HANA PAL

ABC Analysis with SAP HANA PAL

Details

You will use the SAP HANA Predictive Analytics Library (PAL) to create and run an algorithm to analysis a set of data and separate them in groups (also known as ABC analysis or Selective Inventory Control).

For that, you will use a SAP HANA multi-tenant database container (MDC) instance available on the SAP Cloud Platform trial account where each tenant databases in the same system share the same system resources (memory and CPU cores) but each tenant database is fully isolated with its own database users, catalog, repository, persistence (data files and log files) and services.

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 where “A” items are very important for an organization, “B” items are of medium importance and “C” items are of the least importance.

An example of ABC classification is as follows:

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

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

“C” items – 50% of the items (customers or accounts) represents 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.

Step 5: Create a schema and load input data

Next, you need to create the table and load the data set that will be used during this tutorial.

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

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_PREDICTIVE"."V_ABC_INPUT" AS T
INNER JOIN "TUTORIAL_PREDICTIVE"."PAL_ABC_RESULT_TBL" AS R
ON T."SOMEOBJECT" = R."SOMEOBJECT"
ORDER BY 1, 3 desc;

Secondly, using SAP HANA SQL window functions, you can compute the percentages of the total revenue falling into each group and compare to the 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_PREDICTIVE"."V_ABC_INPUT" AS T
INNER JOIN "TUTORIAL_PREDICTIVE"."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.

Congratulations!! You have run your first PAL algorithm!

Final note: the SAP HANA MDC instance is yours and you can use it as well outside of this tutorial.

Just remember that it is stopped automatically after 12 hours, and then deleted if not re-started during 7 days.

You will get email notifications before the instance is scheduled for deletion, so that you can go to your SAP Cloud Platform Trial account to start this instance and to prevent it from being removed.