Skip to Content
Author's profile photo Anindya Bose

SAP BW Extractor ( DataSource) based on HANA Model

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

Assigned Tags

      23 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Bhanu Prakash
      Bhanu Prakash

      Nice document

      Author's profile photo Krishnendu Roy
      Krishnendu Roy

      Good way....Thanks for sharing this document... 🙂

      Author's profile photo Jino Jose
      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.

      Author's profile photo Anindya Bose
      Anindya Bose
      Blog 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

      Author's profile photo Koushik Paul
      Koushik Paul

      Nice job! Anindya

      Author's profile photo Koushik Paul
      Koushik Paul

      Really nice.

      Author's profile photo Former Member
      Former Member

      thanks for share!

      but link:ProxyProcedure,can't open.

      Author's profile photo Anindya Bose
      Anindya Bose
      Blog 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

      Author's profile photo Roman Bukarev
      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?

      Author's profile photo Anindya Bose
      Anindya Bose
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hola Anindya Bose

      I was just looking for this scenario, but I have problems creating the proxy, I do not see the option.

       

      Author's profile photo Former Member
      Former Member

      Hello  Anindya Bose

      and not see the logical for call procedure from abap

      Author's profile photo Anindya Bose
      Anindya Bose
      Blog 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

      Author's profile photo Ibrahim Tamayo Blanco
      Ibrahim Tamayo Blanco

      Hello Anindya,

      I am looking for the easiest way to do the same: Creating a persistency from HANA view with Input parameters. You mentioned the AMDP instead the deprecated Proxy Procedure,  do you have any example?  Where do you set the AMDP code? in a transformation? Please explain.

      Another question is: using the ODP_HANA source system can pass these parameters?

      I have done a lot of searches online and nothing found related to that.

      thanks in advance.

      Ibrahim

      Author's profile photo Anindya Bose
      Anindya Bose
      Blog Post Author

      Hi Ibrahim

       

      What we are using for large set of data is Function Module based extractor.  It is like same generic extractor for DataSource but instead of using cursor, I am calling same HANA model again and again with set of parameters. For example, let's say we have to run an HANA model for 20K customer, but instead of calling all at once I use AMDP/ADBC inside FM with 500 customers each time.  So, I can get all my data after 40th call.  ( 500X40 = 20000)

       

      If you data volume is less, you can also try Composite Provider based on HANA model. Then you can create transformation from Composite Provider to Target. CP does support Input Parameter and you can also run multiple DTPs in parallel ( with diff filter) 

      Author's profile photo Ibrahim Tamayo Blanco
      Ibrahim Tamayo Blanco

      Thank you for your answer!

      Author's profile photo Luxottica Consulant
      Luxottica Consulant

      Nice document, but what if I need to extract data in BW using delta mode?

      How is possible use the delta?

       

      Author's profile photo Manu PJ
      Manu PJ

      Hi

       

      Can you please do another blog without the proxy thing, to create a datasource based on HANA cal view. Appreciate it. Cheers.

      Author's profile photo Anindya Bose
      Anindya Bose
      Blog Post Author

      Rajesh - instead of Proxy Procedure you can use either AMDP Class or ADBC interface .  AMDP is easy but ADBC gives us better performance .

       

      Regards

      Anindya

      Author's profile photo Dilip Tatipaka
      Dilip Tatipaka

      Hi Anindya,

       

      We have a calculation view built on the Native HANA system and we want to leverage the same Hana View in the SAP BW Datasource. Unfortunately we are unable to see the Input Parameter defined in the Calculation view. Is this an limitation?

       

      Author's profile photo Anindya Bose
      Anindya Bose
      Blog Post Author

      For AMDP and ADBC you have no issue with Input Parameter as those essentially send SQL Query.  Based on your data volume, you can also try Composite Provider(HPCR) built on top of HANA view. That is is the easiest way to expose HANA models to BW when they exist in the same system.

      Author's profile photo Sree Ram
      Sree Ram

      I am trying to load data from HANA CV to ADSO via Composite provider.

      CV has mandatory date parameter. But DTP input value is not passing to Hana CV.

      Is there any setting we need to maintain to acheive this?

      Author's profile photo Dilip Tatipaka
      Dilip Tatipaka

      Hi Anindya,

       

      In my scenario, the HANA view is in different system and BW is different system. My concern is in the fields section in the datasource is not displaying Input parameters that are defined in HANA view