Skip to content

Commit 9d55cea

Browse files
committed
adding tutorials 9 and 10
1 parent 3b8ec3b commit 9d55cea

31 files changed

Lines changed: 624 additions & 0 deletions
45.9 KB
Loading
40.9 KB
Loading
65.1 KB
Loading
61.1 KB
Loading
51.4 KB
Loading
40.1 KB
Loading
74.6 KB
Loading
76.7 KB
Loading
Lines changed: 108 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,108 @@
1+
DROP TABLE DUMMYDATA;
2+
3+
CREATE TABLE DUMMYDATA (
4+
accountnr nvarchar(11) default NULL,
5+
location varchar(100) default NULL,
6+
total_amount double default NULL
7+
);
8+
9+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('82540748399','Netherlands',385);
10+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('99692723699','Morocco',992);
11+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('98804020299','Israel',176);
12+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('24364537999','Estonia',41);
13+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('97592208699','South Africa',308);
14+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('55783852899','Niger',332);
15+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('12338489699','Malaysia',582);
16+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('66941296899','Angola',332);
17+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78341775899','Japan',778);
18+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('30205810399','Indonesia',665);
19+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('35766575899','Madagascar',616);
20+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('23270858999','Malta',354);
21+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('91578369899','Bahrain',231);
22+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('76401095499','Central African Republic',412);
23+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('00913274699','Namibia',71);
24+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('50694975399','South Georgia and The South Sandwich Islands',176);
25+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('01488053499','Solomon Islands',878);
26+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('96932672899','Northern Mariana Islands',748);
27+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('91282172899','Liberia',122);
28+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('79787992099','Jordan',800);
29+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('67397439699','El Salvador',539);
30+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('63992562599','Macedonia',57);
31+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('43356492199','San Marino',696);
32+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('51670856799','Sao Tome and Principe',640);
33+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('19947726599','Ukraine',270);
34+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('99834072799','Ukraine',806);
35+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('31917565199','French Guiana',27);
36+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('90613141899','Ireland',369);
37+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('75361130899','Guinea',929);
38+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('39763551799','Jersey',385);
39+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('94627738599','Equatorial Guinea',615);
40+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('57356613099','Sao Tome and Principe',354);
41+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('34330084699','Azerbaijan',949);
42+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('02988459599','French Polynesia',418);
43+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('79935287199','Korea, South',15);
44+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('06403930099','Vanuatu',781);
45+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('41841311499','Belarus',820);
46+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('34791974399','Uzbekistan',984);
47+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('24750820299','Ethiopia',791);
48+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('68989297999','Anguilla',589);
49+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('37448014599','Niger',42);
50+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('41325924899','Isle of Man',233);
51+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('83463321899','United Arab Emirates',535);
52+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('87960002399','Albania',800);
53+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('42469535999','Georgia',783);
54+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('18539856499','Djibouti',716);
55+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('32610094899','Cameroon',482);
56+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78884234499','Guyana',912);
57+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('93904093399','Mauritania',495);
58+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('81519284299','Martinique',387);
59+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('94861577199','Grenada',89);
60+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('23701374899','Sri Lanka',161);
61+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('10334180899','Gambia',473);
62+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('63747550799','Ukraine',375);
63+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('66384175199','Tokelau',946);
64+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('70471211599','Tokelau',45);
65+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('94537787299','Bosnia and Herzegovina',956);
66+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('41888115799','Thailand',101);
67+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('84851632199','Bonaire, Sint Eustatius and Saba',957);
68+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('12369169799','Romania',323);
69+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('10320338499','Sudan',128);
70+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('23973062599','Philippines',558);
71+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('80234682699','Grenada',212);
72+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('92435601999','Norway',305);
73+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('05213022999','Samoa',714);
74+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('34385740899','Guam',413);
75+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('93686420399','Niger',957);
76+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('39548974899','Aruba',89);
77+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('14873205199','Guinea-Bissau',485);
78+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('70546477699','Tanzania',364);
79+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('09430444999','Seychelles',323);
80+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('58371537199','Cocos (Keeling) Islands',733);
81+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('25609325299','Tajikistan',167);
82+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('85253147299','Ghana',70);
83+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('90265537999','Peru',96);
84+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('87529168299','Bolivia',642);
85+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('65449829999','Norfolk Island',566);
86+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('75845729799','Jamaica',568);
87+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('04342023699','Germany',5);
88+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('66802523699','Colombia',899);
89+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78554702399','Senegal',774);
90+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('57575869099','Tunisia',26);
91+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('09291636899','Burundi',655);
92+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('68688029599','Argentina',790);
93+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('54674462999','Zambia',684);
94+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('07752768499','Bolivia',454);
95+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('63610114499','Samoa',78);
96+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('80322453199','Tajikistan',47);
97+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('01611711299','Latvia',821);
98+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('05742930199','Curaçao',637);
99+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('78351684399','United States Minor Outlying Islands',969);
100+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('28553427199','Ethiopia',524);
101+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('99534559099','Ukraine',94);
102+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('75215421499','Uganda',958);
103+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('37984032999','Bermuda',193);
104+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('20604544399','Tanzania',961);
105+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('29957102299','Samoa',4);
106+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('76457446499','Lebanon',72);
107+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('67280243199','Antarctica',503);
108+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('01625239599','Yemen',902);
Lines changed: 241 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,241 @@
1+
---
2+
title: ABC Analysis with SAP HANA
3+
description: Using SAP HANA Predictive Analytics Library for ABC Analysis
4+
tags: [ tutorial>beginner, products>sap-hana, products>sap-hana-cloud-platform ]
5+
---
6+
7+
## Prerequisites
8+
- **Proficiency:** Beginner
9+
- **Tutorials:**
10+
- [Internet of Things (IoT) made easy](http://go.sap.com/developer/tutorials/teched-2016-9.html)
11+
- Your SAP HANA MDC database is created and running in SAP HANA Cloud Platform account and you have Developer access to it
12+
13+
## Next Steps
14+
- 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)
15+
16+
## Details
17+
### You will learn
18+
19+
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).
20+
21+
### Time to Complete
22+
**10 Min**.
23+
24+
---
25+
26+
[ACCORDION-BEGIN [Step 1: ](Open your Databases and Schemas tab in HCP)]
27+
28+
In your SAP HCP Trial account go to `Databases & Schemas` and check you have database of the system type `HANA MDC (<trial>)` created.
29+
30+
![Databases and Schemas](hanapalabc01.jpg)
31+
32+
[DONE]
33+
[ACCORDION-END]
34+
35+
[ACCORDION-BEGIN [Step 2: ](Verify database state)]
36+
37+
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.
38+
39+
![Database Overview screen](hanapalabc02.jpg)
40+
41+
[DONE]
42+
[ACCORDION-END]
43+
44+
[ACCORDION-BEGIN [Step 3: ](Open the Catalog tool)]
45+
46+
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
47+
48+
![SAP HANA Web-based Development Workbench](hanapalabc03.jpg)
49+
50+
Then in the Catalog tool click on `SQL` icon to open a new SQL Console. Type the following code:
51+
52+
```SQL
53+
SELECT * FROM "SYS"."AFL_FUNCTIONS" WHERE AREA_NAME = 'AFLPAL'
54+
ORDER BY FUNCTION_NAME;
55+
```
56+
57+
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.
58+
59+
![SAP HANA SQL Console](hanapalabc04.jpg)
60+
61+
[DONE]
62+
[ACCORDION-END]
63+
64+
[ACCORDION-BEGIN [Step 4: ](Introduction to "PAL")]
65+
66+
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.
67+
68+
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.
69+
70+
An example of ABC classification is as follows:
71+
72+
- “A” items – 20% of the items (customers) accounts for 70% of the revenue.
73+
- “B” items – 30% of the items (customers) accounts for 20% of the revenue.
74+
- “C” items – 50% of the items (customers) accounts for 10% of the revenue.
75+
76+
In this tutorial you will classify customers into ABC groups accordingly to the revenue they generate based on some dummy data.
77+
78+
[DONE]
79+
[ACCORDION-END]
80+
81+
[ACCORDION-BEGIN [Step 5: ](Create a new schema)]
82+
83+
First create a new schema `TUTORIAL_PAL_ABC` and a table with some dummy data.
84+
85+
```sql
86+
--SET SCHEMA FOR THE TUTORIAL
87+
CREATE SCHEMA "TUTORIAL_PAL_ABC";
88+
SET SCHEMA "TUTORIAL_PAL_ABC";
89+
90+
CREATE COLUMN TABLE DUMMYDATA (
91+
accountnr varchar(11) default NULL,
92+
location varchar(100) default NULL,
93+
total_amount double default NULL
94+
);
95+
```
96+
97+
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
98+
99+
```sql
100+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('51670856799','Sao Tome and Principe',640);
101+
INSERT INTO DUMMYDATA (accountnr,location,total_amount) VALUES ('19947726599','Ukraine',270);
102+
-- and so on...
103+
```
104+
105+
Commit inserts and verify all records has been loaded.
106+
107+
```sql
108+
COMMIT;
109+
SELECT COUNT(*) FROM "TUTORIAL_PAL_ABC"."DUMMYDATA";
110+
```
111+
112+
![Results of data load](hanapalabc05.jpg)
113+
114+
And create a view that will be the input to the ABC algorithm.
115+
116+
```sql
117+
CREATE VIEW "TUTORIAL_PAL_ABC"."V_ABC_INPUT" as (SELECT ACCOUNTNR as SOMEOBJECT, TOTAL_AMOUNT as SOMEAMOUNT from "DUMMYDATA");
118+
119+
SELECT COUNT(*) FROM "TUTORIAL_PAL_ABC"."V_ABC_INPUT";
120+
--Should return the 100 records
121+
```
122+
123+
[DONE]
124+
[ACCORDION-END]
125+
126+
[ACCORDION-BEGIN [Step 6: ](Create the PAL procedure)]
127+
128+
To use PAL functions, you must do the following:
129+
130+
- Generate a procedure that wraps the PAL function.
131+
- Call the procedure, for example, from an SQLScript procedure.
132+
133+
>You can find more details in `SAP HANA Predictive Analysis Library (PAL) Reference` document at http://help.sap.com/hana_platform.
134+
135+
Use the following code to generate the wrap procedure:
136+
137+
```sql
138+
--Prepare Table Types
139+
DROP TYPE PAL_ABC_DATA_VIEW_T;
140+
CREATE TYPE PAL_ABC_DATA_VIEW_T AS TABLE ("SOMEOBJECT" NVARCHAR(80), "SOMEAMOUNT" DOUBLE);
141+
DROP TYPE PAL_CONTROL_T;
142+
CREATE TYPE PAL_CONTROL_T AS TABLE("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE, "strArgs" VARCHAR(100));
143+
DROP TYPE PAL_ABC_RESULT_T;
144+
CREATE TYPE PAL_ABC_RESULT_T AS TABLE("ABC" NVARCHAR(10), "SOMEOBJECT" NVARCHAR(80));
145+
146+
--Create the proc using the wrapper
147+
---Define and populate parameters table
148+
DROP TABLE PAL_ABC_PDATA_TBL;
149+
CREATE COLUMN TABLE PAL_ABC_PDATA_TBL(
150+
"POSITION" INT,
151+
"SCHEMA_NAME" NVARCHAR(256),
152+
"TYPE_NAME" NVARCHAR(256),
153+
"PARAMETER_TYPE" VARCHAR(7)
154+
);
155+
INSERT INTO PAL_ABC_PDATA_TBL VALUES (1,'TUTORIAL_PAL_ABC','PAL_ABC_DATA_VIEW_T', 'in');
156+
INSERT INTO PAL_ABC_PDATA_TBL VALUES (2,'TUTORIAL_PAL_ABC','PAL_CONTROL_T', 'in');
157+
INSERT INTO PAL_ABC_PDATA_TBL VALUES (3,'TUTORIAL_PAL_ABC','PAL_ABC_RESULT_T','out');
158+
159+
---Call the wrapper procedure to generate our ABC calculation procedure
160+
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('TUTORIAL_PAL_ABC', 'PAL_ABC_CALCULATE');
161+
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE ('AFLPAL', 'ABC', 'TUTORIAL_PAL_ABC', 'PAL_ABC_CALCULATE', "TUTORIAL_PAL_ABC"."PAL_ABC_PDATA_TBL");
162+
```
163+
164+
You should see following objects create in the `TUTORIAL_PAL_ABC` schema:
165+
166+
![Catalog Explorer](hanapalabc06.jpg)
167+
168+
[DONE]
169+
[ACCORDION-END]
170+
171+
[ACCORDION-BEGIN [Step 7: ](Run the PAL procedure)]
172+
173+
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.
174+
175+
```sql
176+
--Prepare and execute the procedure with ABC Analysis
177+
--Prepare parameters table
178+
DROP TABLE "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL";
179+
CREATE COLUMN TABLE "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL"
180+
("Name" NVARCHAR(100) NOT NULL,
181+
"intArgs" INTEGER,
182+
"doubleArgs" DECIMAL(15, 2),
183+
"strArgs" NVARCHAR(100),
184+
PRIMARY KEY ("Name"));
185+
186+
TRUNCATE TABLE "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL";
187+
INSERT INTO "TUTORIAL_PAL_ABC"."PAL_CONTROL_TBL"
188+
SELECT 'THREAD_NUMBER',5,null,null from DUMMY UNION ALL
189+
SELECT 'PERCENT_A',null,0.70,null from DUMMY UNION ALL
190+
SELECT 'PERCENT_B',null,0.20,null from DUMMY UNION ALL
191+
SELECT 'PERCENT_C',null,0.10,null from DUMMY;
192+
193+
---Execute the procedure
194+
DROP TABLE "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL";
195+
CREATE COLUMN TABLE "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL"
196+
("ABC" NVARCHAR(10) NOT NULL,
197+
"SOMEOBJECT" NVARCHAR(100) NOT NULL,
198+
PRIMARY KEY ("ABC", "SOMEOBJECT"));
199+
TRUNCATE TABLE "TUTORIAL_PAL_ABC"."PAL_ABC_RESULT_TBL";
200+
201+
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;
202+
```
203+
204+
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.
205+
206+
[DONE]
207+
[ACCORDION-END]
208+
209+
[ACCORDION-BEGIN [Step 8: ](Check your results)]
210+
211+
Check the results. Firstly, check what group each account has been assigned to.
212+
213+
```sql
214+
SELECT "ABC" AS ABC, T."SOMEOBJECT" AS COMPANY, "SOMEAMOUNT" AS GROSS_AMOUNT
215+
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"
216+
ORDER BY 1, 3 desc;
217+
```
218+
219+
![ABC Groups assigned to accounts](hanapalabc07.jpg)
220+
221+
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.
222+
223+
```sql
224+
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
225+
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"
226+
GROUP BY "ABC"
227+
ORDER BY 1;
228+
```
229+
230+
![ABC Groups calculation verification](hanapalabc08.jpg)
231+
232+
In real-world distribution you would expect group A has a smaller count, but this is randomly generated data.
233+
234+
You have run your first PAL algorithm.
235+
236+
[DONE]
237+
[ACCORDION-END]
238+
239+
240+
## Next Steps
241+
- 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)

0 commit comments

Comments
 (0)