Skip to Content

Not sure this information is already announced in other blog or discussion. If you already know how to use HANA Stored Procedures (SP) for BPC, this may not apply to you.

Let me explain how we can overcome performance issue of BPC with HANA stored procedures.

The project was for 1. 4-month rolling forecasting 2. Yearly planning 3. Long range planning 4. Operating Income Simulation by LE condition. Industy was petrochemical and the company is located in Korea.

During the design phase, the customer strongly asked to do performance tests within isolated environment with PA data, we had 1 million records as sample and performed a test by using BPC Script, ABAP, and HANA Stored Procedure. The result was below.

Let’s say 10 sec as a base line, which is BPC Script logic. ABAP got 7 and HANA SP got 1. Not that complex just assumed simple, we believe the gap would get larger in proportion of complexity of logic.

Here is how we set the link between HANA SP and SAP BPC.

1. Once you finished HANA SP, you need to add DB proxy.

– Assume you already know how to make SP, you need to make out-put parameter same as the module you created in BPC.

– In HANA studio, you need to install ABAP software, it make you enable to create DB proxy.

ABAP_02.jpg

– Do not forget Do Synchronize and then Activation

2. Once you created DB proxy, you need to make BADI.

– Assume you already know how to manipulate BPC BADI

– Create Filter

BADI.jpg

– Create Implementation

BADI_Implementation.jpg

3. Activate the BADI implementation.

4. Create LGF file in BPC Admin

Logic Scripot_01.jpg

You need to follow the steps above to avoid validation errors.

Except the performance benefit, you can get the advantage as follow.

1. Debug stored procedure – Whether you implement Classic or not, which means model supports single figure or not(if you implement Embedded model), you can build internal table as multi-key figure(just like PA table CE1XXXX). And you can verify the logic you built in debug mode. It is easier!!

2. All ECC tables you need, you can access from SP after getting data to DSO. –  All LE events, you can design the integration between LE and FI/CO data for BPC planning. For example, you can design MRP explosion for the planning with ECC tables.

3. And I believe ANSI-SQL is more widely known and easy to maintenance.

If anybody knows another way to run HANA SP’s with SAP BPC, plese let me know. ^^;;

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Christian Sass

    Hi Seo,

    I really appreciate your post. Since I’m searching possiblities on how to integrate HANA performance to SAP BPC Standard Model.

    How would you handle UJR_WRITE_BACK since you cannot pass entire tables as Imput Parameter to a stored procedure. Would you write data to the Data Base to consume it afterwards in a SP?

    It would be great if you could share more examples of HANA to BPC Standard integration

    Thanx!

    (0) 
    1. YH Seo Post author

      Sorry I cannot fully understand what you asked. But guess you are asking how it write data to db. Based on the my understanding,

      We pass VERSION as the main since it determine time range, and pass others, like plant, product and so on. Then the SP calculate all what should be with only SELECT statement(not Update, Delete, Create and so on), then the output parameter contains all data(including ‘0’ as to clean up data), it passes to BADI, and BADI get them into the cube.

      Then I guess it contacts UJR_WRITE_BACK function automatically, we have never touched it.

      Not sure this is the answer you want, but let me know if you need to know more on this.

      And…more…

      The current project I am working, we adopted little bit different way. Have you heard about AMDP? We currently write logic to AMDP in HANA(Abap project, but we write SQL), and the package handle UJR_WRITE_BACK. Funny thing is that, the mechanism is almost same, but it works differently. Not sure you are interested or related to this way, but hope you get help on this.

      Regards,

      YH Seo

      (0) 
  2. Andy Xu

    Hi YH Seo,

    This is not a HANA store procedure. Your article is misleading.

    By looking at the example, this is just a standard BADI, with underlining HANA DB.

    How is this a store procedure?

    Store procedure in HANA uses SQLscript, and it can be called via AMDP.

    Andy

    (0) 
    1. YH Seo Post author

      Hi Andy,

      You can put SQL scirpt which is developed in Stored Procedure into AMDP, as I know AMDP is the tool which is synched up in ABAP. The advantage using AMDP, you can check it is correctly designed and developed in SE24. And the SQL script can be developed and worked with ABAP, which may cover disadvantage of SQL.

      The article I wrote is all about SP which works with BADI, you are right. But you can also expand the skill and knowledge to AMDP. Hope you get an idea on this.

      Regards,

      YH Seo

      (0) 
      1. Andy Xu

        Hi YH,

        Please show me where is the SP in your article?

        As I’ve said, store procedure must be in SQLScript, that runs on HANA DB itself.

        Andy

        (0) 
  3. Karan Shah

    Hi YH Seo,

    This is an informative post showcasing how BPC calculations can be actually pushed to the HANA layer. I am trying to do something similar and have a few questions specifically related to the Stored Procedure.

    Then the SP calculate all what should be with only SELECT statement(not Update, Delete, Create and so on)

    You mention in your post that the SP will calculate all with the Select Statement. I am trying to understand in a little more detail as to what tables are you using for the SELECT statement. Also, are you using any temp variable/tables in your stored procedure while performing the calculations. Any other information on the details of your stored procedure would also be helpful.

    (0) 
    1. YH Seo Post author

      Hi Karan,

      Sorry it is too late to answer as to your question.

      The reason we used only SELECT statement was the limitation of license the customer had. You can use other statement such as update, delete, create and so on with full authorization if you have appropriate license.

      Using BADi, as you know the destination is defined, the cube in this case. The source was DSO, ADSO and cube(analytic views). Therefore it is mandatory to get data from ECC to DSO, then read data from SP and then calculate data by your logic and change structure to put them to your model(BPC cube)

      Another way, you can use AMDP, install related ABAP software to your HANA Studio, then create ABAP project. You can make CLASS and then write SQL Script as the method. In this way, it is same limitation under the license, but you can combine a lot of ABAP function and easy to control CBO with RFC.

      There are pros and cons between BADi way and AMDP. BADi is to close standard but more ristiction than AMDP. But think it is easier from the implementation standpoint. AMDP, you might need to understand CLASS, METHOD, and ABAP for RFC, and SQL, but it is better to automation from the report standpoint and control CBO, means cover business requirement more than BADi.

      PS. if you have further question reg. this contents, you can reach me at esterr@naver.com . thx.

      Regards,

      YH Seo

      (0) 

Leave a Reply