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.

                                                                                      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.

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

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

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.

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 ) .

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 :

With Customer Selection :

For complete code, Click Here

Please share your feedback and ideas for improvement.

Regards

Anindya