Skip to Content
Technical Articles
Author's profile photo Miguel Peredo Zurcher

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

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!

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Carlos Weffer
      Carlos Weffer

      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

       

      Author's profile photo Miguel Peredo Zurcher
      Miguel Peredo Zurcher
      Blog Post Author

      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 ?

       

       

      Author's profile photo Dominik Graus
      Dominik Graus

      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

       

      Author's profile photo Miguel Peredo Zurcher
      Miguel Peredo Zurcher
      Blog Post Author

      Hello Dominik,

      Thanks for the explanation!

      Br,

      Miguel

      Author's profile photo Frédéric Cincet
      Frédéric Cincet

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

      Author's profile photo Dominik Graus
      Dominik Graus

      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

      Author's profile photo Steven Huang
      Steven Huang

      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.

       

      Author's profile photo Luc Vanrobays
      Luc Vanrobays

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