Technical Articles
External SAP HANA SQL View with SAP BW/4HANA 2.0
Hello!
Now that BW/4 2.0 has been released, and I had some time I decided to try out some of the new features.
In this blog I’m focusing on this new feature: “Openness: SQL View for Look-ups on DataStoreObjects,” page 28 from here: https://www.sap.com/documents/2019/02/b079bb3f-3a7d-0010-87a3-c30de2ffd8ff.html
Before the 2.0 release, there was the note:
https://launchpad.support.sap.com/#/notes/1682131 – SAP BW tables in SAP HANA Information Views and ABAP CDS Views not supported
In a nutshell, “it is not supported to use an “active data” table of an SAP BW (a)DSO or a P/Q-table of an SAP BW InfoObject to build SAP HANA Attribute/Analytic/and Calculation Views by hand via the SAP HANA Modeler.”
From time to time I’ve noticed this happening in some systems I’ve worked with, and I understand the reasoning behind, but please keep in mind that is not allowed
For me the motivators for needing to use the ADSO views/tables were mainly:
- Mixed modeling scenarios. Do some joins operations combining the data in the ADSO and some Hana view or similar situations.
- Extracting to 3rd party tools
- HANA Transformations (lookups)
Now with release 2.0 of BW/4, we have some views that are “legal” to use, and also remember that for exposing semantically rich views to SQL tools, you can use the RS2HANA framework, which also provides authorizations, this is achieved setting the “External HANA view” in a composite, more info here: https://help.sap.com/viewer/107a6e8a38b74ede94c833ca3b7b6f51/1.0.1/en-US/6633d851345c4770bd4e523701b9f5b0.html
The documentation I’ve found so far about this new views is the following:
https://launchpad.support.sap.com/#/notes/2723506 – External SAP HANA SQL View with SAP BW/4HANA 2.0
and
In this documentation, I only found a reference to a view: /<namespace>/A<name of the DataStore object>8
In my case, my ADSO technical name is ZFIGL
And I can see that three views got generated on the ABAP side:
/BIC/AZFIGL6 View for Extraction from DataStore ZFIGL
/BIC/AZFIGL7 View for Reporting for DataStore ZFIGL
/BIC/AZFIGL8 View for external Access for DataStore ZFIGL
The views ending in 6 and 7 are generated in BW/4 1.0 (At least with SP10), but the one ending in 8 is the new one for BW/4 2.0
My wish for this blog in that everybody who is interested in this topic, put in the comments his questions, experiences, etc, with this new view, and at some time we create a wiki for this.
Cheers!
Hi Miguel
Thanks for your blog about aDSO new views in BW/4HANA 2.0.
Do you know what table or tables is /BIC/A<dso_name>8 based on?
Both views ending in 6 and 7 generated in BW/4 1.0 are based on Inbound Table [/BIC/A<dso_name>1] .
I would expect view 8 to based on Active Table [/BIC/A<dso_name>2 ]. Ideally it should include any external table used for DTO purposes, too. eg. OU<dso_name>
Regards, Carlos
I think it will depend on type of the DSO.
For the settings “Standard DSO” and “Write change log enabled” /BIC/A<dso_name>8 is based on: [/BIC/A<dso_name>1].
See screenshot below.
This looks like a bug to me, it does not make sense to have to the inbound as the base.
Can you find any logic there ?
Hi Miguel,
Thanks for your blog!
This is not a bug but very misleading indeed. Let me explain:
The views /*/A*8 must be created in ABAP DDIC. Otherwise they would not be available for consumption in routines in transformations, for example.
Unfortunately, the functionality of the ABAP DDIC is pretty limited when it comes to defining database views and it would not be possible to create the views with pure ABAP DDIC view semantics. Therefore, the view is created in ABAP DDIC as shown in your screenshot but its database definition differs from that.
You can see the actual view definition on the database via menu Utilities > Database object > Display.
My sample ADSO is also a 'standard' ADSO with activation and option 'write changelog'. The External SAP HANA SQL View is defined as shown here:
As expected it reads from the active data table of the ADSO.
Therefore, you must never activate the views in ABAP DDIC. This would overwrite the database view and lead to wrong results. Activation must always take place in BW Modeling Tools or via report RSDG_ADSO_ACTIVATE.
Regards,
Dominik
Hello Dominik,
Thanks for the explanation!
Br,
Miguel
"Amount in external and internal format". At last !! I had some hard times to handle this in mixed scenario.
Awaited feature.
Thanks for the news.
Hi Frédéric,
Nice feature indeed. 🙂
For ADSOs with non-cumulative key figures we also expose the reference points. This allows for rather easy extraction of stock snapshots with excellent performance if you don't need the full feature set including validity slices. I plan to release a blog on that topic soon.
Regards,
Dominik
hi miguel :
nice blog !
when we using this “ external SAP HANA SQL view” in the customization CV ,when preview the CV data ,also show the Error “SAP DBTech jdbc:[478] user defined function runtime error : search table error: curency/unit ” , XXXXX."TCURX" -invalid table .
thanks.
Awesome blog,
by the way to easily check aDSO external view underlying tables,
one can use abap function module DB_GET_VIEW_DEFINITION_RAW > List > Text tab separated.
Nice blog !