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
Nice document
Good way....Thanks for sharing this document... 🙂
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.
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
Nice job! Anindya
Really nice.
thanks for share!
but link:ProxyProcedure,can't open.
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
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?
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
Hola Anindya Bose
I was just looking for this scenario, but I have problems creating the proxy, I do not see the option.
Hello Anindya Bose
and not see the logical for call procedure from abap
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
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
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)
Thank you for your answer!
Nice document, but what if I need to extract data in BW using delta mode?
How is possible use the delta?
Hi
Can you please do another blog without the proxy thing, to create a datasource based on HANA cal view. Appreciate it. Cheers.
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
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?
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.
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?
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