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.
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.
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 message to InfoPackage that data load completed.
Extractor Checker ( RSA3) without Selection :
With Customer Selection :
Source: scn.sap.com
No comments:
Post a Comment