Skip to Content
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

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!

/
8 Comments
You must be Logged on to comment or reply to a post.
  • 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:

        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

         

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

     

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