Skip to Content

Objective: Apply all data intensive logic  in database layer and only  store the result in  BW .

In this blog, I would discuss how can we load data from HANA models/Database Procedure  to BW DataSource based on Function Module.

Scenario 1 : 

You have a HANA model which gives you  every day snapshot of open order / delivered order in real time . You want to store the data somewhere  to see trend over time.  Reporting security is implemented in BW side and you want to reuse that .You also want to use Master Data / Text available in BW .


Scenario 2: 

You have BW on HANA and HANA native in same database , and there is hybrid data model which uses data from both BW Schema and HANA native schema.  If the tables are small , you might pull the data by  some way and build your model in BW . However, if the tables are big and requirement is not straight forward, a calculation view can be handy and comes with great performance benefit by using Input Parameter.  We can also model very complex requirement by using  stored  procedure or Script Based Calcuation view.  But , for some reasons we want data to be persisted in BW , like business wants Key and Text side by side for variable help values which still does not work well in native HANA .



In our case, we have two large tables ( ~ 1 B records )  , one in BW Schema , another in HANA schema along with some moderate size tables.  I need to build a data model on top of these tables to get the output and then store it for some time . Because of the data volume, I created Calculation View with Input Parameter to filter these two big tables as early as possible before implementing business logic.  Now, for the above mentioned reasons, I want to store the data in BW DSO and then have a BEx query on top of it.

There are multiple ways how HANA models can be consumed in BW , viz. Composite Provider , Analytic Index, Virtual Provider , OpenODS views , External View ( to be accessed within ABAP ) , Database proxy procedure etc.  I used function module based extractor with database proxy procedure to have flexibility in data loading.  Function Module would call HANA models/Procedure  repetitively  with different input parameters and store the data in PSA.

Implementation: 

We can access any Stored Procedure in HANA ( created from HANA studio) within ABAP via Proxy Procedure. We can access this Proxy procedure within our Function Module via automatically generated interface.  Below figures shows  overall design.

                                /wp-content/uploads/2016/01/capture_869244.png

                                                                                      Figure1: Design diagram

Step 1 :  Create Analytic / Calculation View ( with Input Parameter ) . Without Input Parameter filter, it is much easier to expose them via External View.

How to Consume HANA View in ABAP Using External View and Open SQL

Step 2 : Create Stored Procedure in HANA to access Objects created in Step 1.  Here my procedure select all fields from Calculation View ZSCN_BILLING , but you can add more fields, calculation if you want.

procedure.PNG

Test the procedure once to see if it is returning correct data.

test.PNG

Step 3 :  Create Proxy Procedure for Stored Procedure created in Step 2 . This has to be done from ABAP perspective of Eclipse Modeling tool

Login to Eclipse tool , open a ABAP project.  Navigate through the package where you want to create Database proxy procedure. “Package” here means Transport package, for this demo I choose $TMP , as I do not want to transport it. Right click on DB Procedure Proxy folder and Select New Database Procedure Proxy

proxy1.PNG    proxy2.PNG

Name your Proxy Procedure, select your Database procedure ( Use Ctrl + space key to get a list ) and enter name for the interface to be created.

proxy3.PNG

Click Next and then Finish, if everything is fine Proxy Procedure would be created. You can change some of the data types( like C to N  )  as well  if required and activate the Proxy Procedure.  Once activated, this can be called from ABAP ( here we would call this proxy procedure inside our function module ) .

proxy4.PNG

More detail regarding Proxy Procedure can be found here. ProxyProcedure

Step 4:  Create a Function Module based DataSource in BW and implement your logic within the function module .

Function module based extractor gets called repeatedly and load data to PSA table.  In the first call, it initializes all parameters and then in every subsequent call it fetches the data.  When it cannot fetches any more data, it raises an exception NO_MORE_DATA and your info package gets finished.  FM based extractor does all these things by using a cursor .  In our case, we have to achieve all these via custom coding.

First I created a list of Input Parameter ( list of Customer)  which would be passed while calling the Proxy Procedure. By default it would fetch all the customer; we can also use InfoPackage selection.

In the second step, we would loop through this list of parameter and call our Proxy procedure in loop.

Once we complete our Input Parameter list, in the next call , we would raise NO_MORE_DATA to send messageto InfoPackage that data load completed.

Extractor Checker ( RSA3) without Selection :

Ext1.PNG

With Customer Selection :

Ext2.PNG

Ext3.PNG

For complete code, Click Here

Please share your feedback and ideas for improvement.

Regards

Anindya

To report this post you need to login first.

16 Comments

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

  1. Jino Jose

    Hi ,

    I hope this requirement can be done using open ods view. you could create and open ods view which consumes the HANA model and generate the data model on top of the open ods view. you can also add selections on the generated datasource which also gives the flexibility to data load. With this approach we don’t need db procedure and function module.

    Is there any reason for not considering the above approach?.

    Thanks,

    Jino.

    (0) 
    1. Anindya Bose Post author

      Hi Jino

      Thanks for the feedback.  Open ODS had a limitation with Input Parameter , not sure if still it is the case in latest support pack.  And for my model Input Parameter is must, because my calculation view is joining two table with billion records each . If , input parameter was not there, I could easily consume it as External View ( easier  way ).

      Second reason is, in this demo my procedure is not doing anything apart from merely selecting the data.  But in actual scenario , I have some other calculations which are easy to do in procedure than in ABAP, e.g. RANK() and DENSE_RANK() function.

      Hope this answers your question.

      Regards

      Anindya

      (0) 
    1. Anindya Bose Post author

      Hi Nan

      Original link is not working I just saw. If I have a saved copy of the PDF, I shall update that .

      Regards

      Anindya

      (0) 
  2. Roman Bukarev

    I would guess the same thing can be done with extraction from a Composite Provider, built on top of a HANA Calc View. Composite Provider allows use of input parameters.

    Would the data source based approach have any extra benefits?

    (0) 
    1. Anindya Bose Post author

      Hi Roman

      Please refer to my reply to Jino Jose .

      Apart from additional calculation in procedure, FM based extraction gives me advantage  to call the same procedure around 800 times with different parameters .  My base tables has billion+ records, so use of input parameter was kind of mandatory for me.  Current version does support using a composite provider on top of HANA model, but anyway I need to use a FM ( to check what parameter  to call the procedure with for a given day ) .  So, in a nutshell, I could avoid using procedure and proxy procedure and replace them with composite provider , but then I would not be able to calculate rank .

      Please let me know if that answers your question .

      Thanks and Regards

      (0) 
  3. martinolivia23@gmail.com olivia

    What is SAP BW?

    SAP NetWeaver Business Warehouse (SAP NetWeaver BW) or also called SAP BW in short, is the name of the Business Intelligence, analytical, reporting and Data Warehousing solution produced by SAP.

    Serving as a powerful Enterprise Data Warehouse application platform BW provides flexible reporting and analysis tools. It is a packaged, comprehensive business intelligence product centered around a data warehouse that is optimized for (but not limited to) the R/3 environment from SAP.

    What is SAP BW on HANA?

    SAP BW on HANA is nothing but SAPÔÇÖs existing NetWeaver BW data warehouse, running on SAP HANA.
    SAP now supports SAP HANA as…Read More: http://www.saphanacentral.com/p/introduction.html

    (0) 
  4. Anindya Bose Post author

    Hola Miledy

    Sorry for late reply .

    Proxy Procedure has been deprecated . ┬áPlease explore AMDP ┬á( ABAP Managed Database Procedure ) if you need Input Parameter to be passed . ┬á If your model does not have IP, you can simple use “External View” features.

     

     

    Anindya

    (0) 

Leave a Reply