Technical Articles

April 1, 2019 2 minute read

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 3^rd 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

https://help.sap.com/viewer/107a6e8a38b74ede94c833ca3b7b6f51/2.0.0/en-US/1790df727298492a8c02671d183a5ea6.html

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!

9 Comments

You must be Logged on to comment or reply to a post.

Carlos Weffer

April 9, 2019 at 5:00 am

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

Miguel Peredo Zurcher

Blog Post Author

April 12, 2019 at 12:23 pm

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 ?

Dominik Graus

April 16, 2019 at 9:22 am

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:

CREATE VIEW /BIC/ADGMATLGRP8
   (/BIC/DGMAT,
   CALDAY,
   COLD_STORE_FLAG,
   MATL_GROUP )
AS SELECT
   "/BIC/DGMAT",
    "CALDAY",
    CAST( "COLD_STORE_FLAG" AS NVARCHAR(1) ),
    "MATL_GROUP"
FROM
    ( SELECT "/BIC/DGMAT",
    "CALDAY",
    '' AS COLD_STORE_FLAG,
    "MATL_GROUP" FROM "/BIC/ADGMATLGRP2" )

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

Miguel Peredo Zurcher

Blog Post Author

April 17, 2019 at 6:41 pm

Hello Dominik,

Thanks for the explanation!

Br,

Miguel

Frederic Cincet

April 9, 2019 at 7:08 am

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

Dominik Graus

April 16, 2019 at 9:27 am

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

Steven Huang

September 18, 2019 at 2:09 am

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.

Luc Vanrobays

June 9, 2020 at 4:40 pm

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.

	CREATE VIEW /B28/AECIKZHF8
	   (REQTSN,
	   DATAPAKID,
	   RECORD,
	   /B28/S_ECD3H4G,
	   /B28/S_ECD9SG2,
	   /B28/S_ECDYGNO,
	   /B28/S_ECD0OTQ,
	   /B28/S_ECD50YT,
	   /B28/S_ECDIOK5,
	   /B28/S_ECDRH9Y,
	   /B28/S_ECD3JUT,
	   /B28/S_ECDHNPN,
	   /B28/S_ECD75W5,
	   /B28/S_ECD3F2G,
	   COLD_STORE_FLAG,
	   RECORDMODE,
	   /B28/S_SDATA )
	AS SELECT
	   CAST( "REQTSN" AS NVARCHAR (000023) ),
	    CAST( "DATAPAKID" AS NVARCHAR (000006) ),
	    CAST( "RECORD" AS INTEGER ),
	    "/B28/S_ECD3H4G",
	    "/B28/S_ECD9SG2",
	    "/B28/S_ECDYGNO",
	    "/B28/S_ECD0OTQ",
	    "/B28/S_ECD50YT",
	    "/B28/S_ECDIOK5",
	    "/B28/S_ECDRH9Y",
	    "/B28/S_ECD3JUT",
	    "/B28/S_ECDHNPN",
	    "/B28/S_ECD75W5",
	    "/B28/S_ECD3F2G",
	    CAST( "COLD_STORE_FLAG" AS NVARCHAR(1) ),
	    CAST( "RECORDMODE" AS NVARCHAR (000001) ),
	    "/B28/S_SDATA"
	FROM
	    ( SELECT "REQTSN",
	    "DATAPAKID",
	    "RECORD",
	    "/B28/S_ECD3H4G",
	    "/B28/S_ECD9SG2",
	    "/B28/S_ECDYGNO",
	    "/B28/S_ECD0OTQ",
	    "/B28/S_ECD50YT",
	    "/B28/S_ECDIOK5",
	    "/B28/S_ECDRH9Y",
	    "/B28/S_ECD3JUT",
	    "/B28/S_ECDHNPN",
	    "/B28/S_ECD75W5",
	    "/B28/S_ECD3F2G",
	    '' AS COLD_STORE_FLAG,
	    "RECORDMODE",
	    "/B28/S_SDATA" FROM "/B28/AECIKZHF1"
	WHERE

	    "REQTSN"
	    <=
	    (
	    select
	    max(
	    request_tsn
	    )
	    from
	    (
	    select
	    coalesce(
	    request_tsn,
	    '00000000000000000000000'
	    )
	    as
	    request_tsn
	    from
	    (
	    select
	    max(
	    request_tsn
	    )
	    as
	    request_tsn
	    from
	    rspmrequest
	    where tlogo      = 'ADSO' and
	    datatarget = '/CPMB/ECIKZHF' and
	    storage    = 'AQ' and
	    request_status in ( 'GG', 'GR' ) and
	    request_tsn
	    <
	    (
	    select
	    min(
	    request_tsn
	    )
	    from
	    rspmrequest
	    where tlogo      = 'ADSO' and
	    datatarget = '/CPMB/ECIKZHF' and
	    storage    = 'AQ' and
	    request_status
	    not
	    in
	    (
	    'GG',
	    'GR',
	    'D',
	    'M'
	    )
	    )
	    )
	    union
	    select
	    coalesce(
	    request_tsn,
	    '00000000000000000000000'
	    )
	    as
	    request_tsn
	    from
	    (
	    select
	    max(
	    request_tsn
	    )
	    as
	    request_tsn
	    from
	    rspmrequest
	    where tlogo      = 'ADSO' and
	    datatarget = '/CPMB/ECIKZHF' and
	    storage    = 'AQ' and
	    request_status in ( 'GG', 'GR' ) and
	    not
	    exists
	    (
	    select
	    ''
	    from
	    rspmrequest
	    where tlogo      = 'ADSO' and
	    datatarget = '/CPMB/ECIKZHF' and
	    storage    = 'AQ' and
	    request_status
	    not
	    in
	    ( 'GG', 'GR', 'D', 'M' ) ) ) ) )
	UNION ALL
	    SELECT '00000000000000000000000' AS "REQTSN",
	    '000000' AS "DATAPAKID",
	    0 AS "RECORD",
	    "/B28/S_ECD3H4G",
	    "/B28/S_ECD9SG2",
	    "/B28/S_ECDYGNO",
	    "/B28/S_ECD0OTQ",
	    "/B28/S_ECD50YT",
	    "/B28/S_ECDIOK5",
	    "/B28/S_ECDRH9Y",
	    "/B28/S_ECD3JUT",
	    "/B28/S_ECDHNPN",
	    "/B28/S_ECD75W5",
	    "/B28/S_ECD3F2G",
	    '' AS COLD_STORE_FLAG,
	    ' ' AS "RECORDMODE",
	    "/B28/S_SDATA"
	FROM
	    "/B28/AECIKZHF2" )

Lei Guo

December 10, 2021 at 6:21 am

Nice blog !

Technical Articles

External SAP HANA SQL View with SAP BW/4HANA 2.0

Assigned Tags