SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Modeling
+
-
Spatial Processing
+
-
Predictive Analysis

Run ABC Analysis using HANA PAL - Part 2

In previous article Run ABC Analysis using HANA PAL - Part 1 we explained what ABC analysis is and generated data for ABC analysis.
Now, we will generate PAL procedure and do an ABC Analysis using that.

Note: In article How to work with HANA PAL Library, we explained the steps required to generate and call PAL procedure. If you have skipped that article, please go through it once again.

Generate PAL Procedure

Go to HANA Studio and open SQL Console. Copy the below script and execute it. Make sure you have changed “YOUR_SCHEMA” to your actual schema name.
            SET SCHEMA "YOUR_SCHEMA"; 
             
            --------------------------------------------------------------------------------
            -- CREATE INPUT TABLE TYPE
            --------------------------------------------------------------------------------
            --DROP TYPE TT_INPUT_TABLE;
            CREATE TYPE TT_INPUT_TABLE AS TABLE ("COMPANY_NAME" VARCHAR(100), 
            "TURNOVER" DOUBLE);
             
            --------------------------------------------------------------------------------
            -- CREATE PARAMETER TABLE TYPE
            --------------------------------------------------------------------------------
            --DROP TYPE TT_PARAM_TABLE;
            CREATE TYPE TT_PARAM_TABLE AS TABLE("Name" VARCHAR(100), "intArgs" INT, 
            "doubleArgs" DOUBLE, "strArgs" VARCHAR(100));
             
            --------------------------------------------------------------------------------
            -- CREATE OUTPUT TABLE TYPE
            --------------------------------------------------------------------------------
            --DROP TYPE TT_OUTPUT_TABLE;
            CREATE TYPE TT_OUTPUT_TABLE AS TABLE("ABC" VARCHAR(10), 
            "COMPANY_NAME" VARCHAR(100)); 
             
            --------------------------------------------------------------------------------
            -- CREATE SIGNATURE TABLE
            --------------------------------------------------------------------------------
            --DROP TABLE SIGNATURE_TABLE;
            CREATE COLUMN TABLE SIGNATURE_TABLE(
                            "POSITION" INT,
                            "SCHEMA_NAME" NVARCHAR(256),
                            "TYPE_NAME" NVARCHAR(256),
                            "PARAMETER_TYPE" VARCHAR(7)
            );
            INSERT INTO SIGNATURE_TABLE VALUES (1,'SAP_HANA_TUTORIAL','TT_INPUT_TABLE', 'IN'); 
            INSERT INTO SIGNATURE_TABLE VALUES (2,'SAP_HANA_TUTORIAL','TT_PARAM_TABLE', 'IN'); 
            INSERT INTO SIGNATURE_TABLE VALUES (3,'SAP_HANA_TUTORIAL','TT_OUTPUT_TABLE','OUT');
             
            --------------------------------------------------------------------------------
            -- GENERATE PROCEDURE
            --------------------------------------------------------------------------------
            --CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('YOUR_SCHEMA', 'PAL_ABC_ANALYSIS_PROC');
            CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'ABC', 'YOUR_SCHEMA', 'PAL_ABC_ANALYSIS_PROC', SIGNATURE_TABLE);


Call the PAL Procedure

Go to HANA Studio and open SQL Console. Copy the below script and execute it. Make sure you have changed YOUR_SCHEMA to your actual schema name.

            SET SCHEMA YOUR_SCHEMA
             
            --------------------------------------------------------------------------------
            -- CREATE PARAMETER TABLE
            --------------------------------------------------------------------------------
            CREATE LOCAL TEMPORARY COLUMN TABLE #PARAM_TABLE LIKE TT_PARAM_TABLE;
            INSERT INTO #PARAM_TABLE VALUES ('THREAD_NUMBER', 1, NULL, NULL);
            INSERT INTO #PARAM_TABLE VALUES ('PERCENT_A', NULL, 0.8, NULL);
            INSERT INTO #PARAM_TABLE VALUES ('PERCENT_B', NULL, 0.15, NULL);
            INSERT INTO #PARAM_TABLE VALUES ('PERCENT_C', NULL, 0.05, NULL);
             
             
            --------------------------------------------------------------------------------
            -- CREATE OUTPUT TABLE
            --------------------------------------------------------------------------------
            --DROP TABLE OUTPUT_TABLE;
            CREATE COLUMN TABLE OUTPUT_TABLE LIKE TT_OUTPUT_TABLE;
             
             
            --------------------------------------------------------------------------------
            -- CALL PROCEDURE TO RUN ABC ANALYSIS
            --------------------------------------------------------------------------------
            CALL PAL_ABC_ANALYSIS_PROC(COMPANIES, #PARAM_TABLE, OUTPUT_TABLE) WITH OVERVIEW;
             
             
            --------------------------------------------------------------------------------
            -- SEE THE ABC ANALYSIS REPORT FROM OUTPUT TABLE
            --------------------------------------------------------------------------------
            SELECT * FROM OUTPUT_TABLE;
             
The output should be similar to below.

ABC Analysis generate PAL procedure

Conclusion:
Using HANA PAL Library, we created a procedure which provides the ABC analysis for any set of data. This procedure can further be called on any set of data to do ABC analysis. In real time scenario, we normally create a UI using this procedure to show the result in more meaningful way as below.

ABC Analysis generate PAL procedure


Support us by sharing this article.



Explore More
Close X
Close X

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy