Skip to Content
Technical Articles
Author's profile photo Abhishek Hazra

View for external Access (The ‘8’ View) for aDSOs in BW/4HANA 2.0 Mixed modelling

Hello there!

Context

In this blog post, I would focus on some of the key aspects of the generated view for external access  or the external SAP HANA SQL View for aDSOs in BW/4HANA 2.0 by a use case scenario. The view is generated with naming convention as following : /BIC (namespace)/A<technical name of the aDSO>8.

We are all well familiar with the below generated tables & views of aDSOs : (depending on the type of particular aDSO they are relevant)

/BIC/A<technical name of the aDSO>1 : Inbound Table for aDSO.

/BIC/A<technical name of the aDSO>2 : Active Data Table for aDSO.

/BIC/A<technical name of the aDSO>3 : Change Log Table for aDSO.

And the generated views :
/BIC/A<technical name of the aDSO>6 : View for Extraction from aDSO.

/BIC/A<technical name of the aDSO>7 : View for Reporting for aDSO.

You can read more about them here.

Now with BW/4HANA 2.0, we have another view generated with name : /BIC/A<technical name of the aDSO>8 : View for external Access for aDSO.

It is recommended that the view for external access to be used in mixed modelling scenarios or exposing data to 3rd party consumers, while on the contrary, the usages of the database tables directly are discouraged & not supported by SAP. However, direct access to these tables is said to be supported in routines scripted in HANA being used in transformations (Reference note : 1682131).

More information regarding the features of this ‘8’ view can be found in SAP note : 2723506 or here : External SAP HANA SQL View, so I will not repeat all the features in this blog post. Rather we’ll focus on two important facades of the same :

1. New controller technical flag : “COLD_STORE_FLAG”.

2. The internal & external formats of amount type keyfigures.

There’s another interesting feature for aDSOs with non-cumulative key figures – the marker for non-cumulative keyfigures. The reference points are exposed with an additional column with naming convention as following : “<Column Name>_NCUM_REFP“. More details about this can be found in above mentioned SAP help portal link.

Note : All newly created aDSOs in BW/4HANA 2.0, will have this ‘8’ view generated by default. For pre-existing aDSOs prior to the introduction of this feature, a reactivation is needed in order to have this view generated. This can be achieved by the program : RSDG_ADSO_ACTIVATE as well as manual activation of the same.

Use Case

For this blog post, I have a standard aDSO with technical name : SALESADSO, which contains some sales data for 2019 & 2020.

The view for external Access for DataStore SALESADSO is generated as : /BIC/ASALESADSO8.

At the initial stage, no data tiering is set except for Hot Data.

Now let’s have a look at the definition of the generated ‘8’ view in database catalog. We can find it under the DB owned schema.

The definition looks somewhat like below :

CREATE VIEW "<DB Owned Schema>"."/BIC/ASALESADSO8" ( "BILL_NUM",
	 "BILL_ITEM",
	 "BILL_DATE",
	 "COLD_STORE_FLAG",
	 "SALESORG",
	 "PLANT",
	 "COMP_CODE",
	 "CO_AREA",
	 "DISTR_CHAN",
	 "CALDAY",
	 "CALMONTH",
	 "CALYEAR",
	 "BILL_TYPE",
	 "MATERIAL",
	 "QUANTITY",
	 "AMOUNT__EXT",
	 "AMOUNT__INT",
	 "CURRENCY",
	 "UNIT" ) AS SELECT
	 "BILL_NUM" ,
	 "BILL_ITEM" ,
	 "BILL_DATE" ,
	 CAST( "COLD_STORE_FLAG" AS NVARCHAR(1) ) ,
	 "SALESORG" ,
	 "PLANT" ,
	 "COMP_CODE" ,
	 "CO_AREA" ,
	 "DISTR_CHAN" ,
	 "CALDAY" ,
	 "CALMONTH" ,
	 "CALYEAR" ,
	 "BILL_TYPE" ,
	 "MATERIAL" ,
	 "QUANTITY" ,
	 CAST( CONVERT_CURRENCY( amount => "AMOUNT",
	 source_unit => "CURRENCY",
	 target_unit => "CURRENCY",
	 steps => 'shift',
	 schema => '<DB Owned Schema>',
	 client => '<Client ID>' ) AS DECIMAL (000017,
	 000002) ) ,
	 "AMOUNT" ,
	 "CURRENCY" ,
	 "UNIT" 
FROM ( SELECT
	 "BILL_NUM" ,
	 "BILL_ITEM" ,
	 "BILL_DATE" ,
	 '' AS COLD_STORE_FLAG ,
	 "SALESORG" ,
	 "PLANT" ,
	 "COMP_CODE" ,
	 "CO_AREA" ,
	 "DISTR_CHAN" ,
	 "CALDAY" ,
	 "CALMONTH" ,
	 "CALYEAR" ,
	 "BILL_TYPE" ,
	 "MATERIAL" ,
	 "QUANTITY" ,
	 "AMOUNT" ,
	 "CURRENCY" ,
	 "UNIT" 
	FROM "/BIC/ASALESADSO2" ) WITH READ ONLY

Firstly, notice that there are two Amount keyfigures in the outer most SELECT as : “AMOUNT__EXT” & “AMOUNT__INT” even though I just have just one Amount keyfigure in the aDSO as : 0AMOUNT These two generated Amount keyfigures represent the external & internal format of 0AMOUNT based on the client specific currency settings with reference to TCURX table (More information regarding internal & external formats of Amount keyfigures can be found in SAP note : 1240163).

The derivation of external formal (AMOUNT_EXT) can be seen in the second select in above shown DDL statement. We usually choose the internal format (AMOUNT_INT in this context) which is the format we can see from the active table of aDSO too, but it may vary according to specific technical need or consumer requirement. They would not differ unless otherwise maintained with specific decimal shifts or specific currency settings as described in the note I mentioned earlier.

Now coming to the custom field in the generated view : the much awaited ‘COLD_STORE_FLAG‘. From the definition we can see it’s generated with blank value like : ‘ ‘ AS COLD_STORE_FLAG.

Here comes the relevance of the aDSO being created with data tiering properties selected only to access temperature tier containing the HOT data as stated in the beginning and that’s the reason the innermost SELECT query in the DDL statement can be seen to read data only from the Active Data Table of aDSO SALESADSO (/BIC/ASALESADSO2). So, we can conclude from the observations until here that for the HOT data residing in HANA standard nodes, the COLD_STORE_FLAG holds value as ‘ ‘ (blank or empty string, not whitespace).

Now let’s experiment a little more with this data temperature access controller flag by changing the Data Tiering Properties of the aDSO.

Make sure you have a valid external Cold Storage Connection set up ( For more information regarding this, refer to : Configuring SAP IQ as a Cold Store ) to move the data around prior to this step. I have a time characteristic as key field of the aDSO ie. 0BILL_DATE, which is my partition specifier field. I have maintained Static Partitions on 0BILL_DATE (Details on maintenance of the partitions & using them with DTO, refer to the SAP help link for Creating Partitions & note : 2044468).

After reactivating the aDSO with the external Cold Storage tiering enabled, let’s check the DDL of the regenerated ‘8’ view once again from database catalog. Now DDL creation statement looks like below :

CREATE VIEW "<DB Owned Schema>"."/BIC/ASALESADSO8" ( "BILL_NUM",
	 "BILL_ITEM",
	 "BILL_DATE",
	 "COLD_STORE_FLAG",
	 "SALESORG",
	 "PLANT",
	 "COMP_CODE",
	 "CO_AREA",
	 "DISTR_CHAN",
	 "CALDAY",
	 "CALMONTH",
	 "CALYEAR",
	 "BILL_TYPE",
	 "MATERIAL",
	 "QUANTITY",
	 "AMOUNT__EXT",
	 "AMOUNT__INT",
	 "CURRENCY",
	 "UNIT" ) AS SELECT
	 "BILL_NUM" ,
	 "BILL_ITEM" ,
	 "BILL_DATE" ,
	 CAST( "COLD_STORE_FLAG" AS NVARCHAR(1) ) ,
	 "SALESORG" ,
	 "PLANT" ,
	 "COMP_CODE" ,
	 "CO_AREA" ,
	 "DISTR_CHAN" ,
	 "CALDAY" ,
	 "CALMONTH" ,
	 "CALYEAR" ,
	 "BILL_TYPE" ,
	 "MATERIAL" ,
	 "QUANTITY" ,
	 CAST( CONVERT_CURRENCY( amount => "AMOUNT",
	 source_unit => "CURRENCY",
	 target_unit => "CURRENCY",
	 steps => 'shift',
	 schema => '<DB Owned Schema>',
	 client => '<Client ID>' ) AS DECIMAL (000017,
	 000002) ) ,
	 "AMOUNT" ,
	 "CURRENCY" ,
	 "UNIT" 
FROM ( SELECT
	 "BILL_NUM" ,
	 "BILL_ITEM" ,
	 "BILL_DATE" ,
	 '' AS COLD_STORE_FLAG ,
	 "SALESORG" ,
	 "PLANT" ,
	 "COMP_CODE" ,
	 "CO_AREA" ,
	 "DISTR_CHAN" ,
	 "CALDAY" ,
	 "CALMONTH" ,
	 "CALYEAR" ,
	 "BILL_TYPE" ,
	 "MATERIAL" ,
	 "QUANTITY" ,
	 "AMOUNT" ,
	 "CURRENCY" ,
	 "UNIT" 
	FROM "/BIC/ASALESADSO2" 
	UNION ALL SELECT
	 CAST( "BILL_NUM" AS NVARCHAR(10)) ,
	 CAST( "BILL_ITEM" AS NVARCHAR(6)) ,
	 CAST( "BILL_DATE" AS NVARCHAR(8)) ,
	 'X' AS COLD_STORE_FLAG ,
	 CAST( "SALESORG" AS NVARCHAR(4)) ,
	 CAST( "PLANT" AS NVARCHAR(4)) ,
	 CAST( "COMP_CODE" AS NVARCHAR(4)) ,
	 CAST( "CO_AREA" AS NVARCHAR(4)) ,
	 CAST( "DISTR_CHAN" AS NVARCHAR(2)) ,
	 CAST( "CALDAY" AS NVARCHAR(8)) ,
	 CAST( "CALMONTH" AS NVARCHAR(6)) ,
	 CAST( "CALYEAR" AS NVARCHAR(4)) ,
	 CAST( "BILL_TYPE" AS NVARCHAR(4)) ,
	 CAST( "MATERIAL" AS NVARCHAR(18)) ,
	 "QUANTITY" ,
	 "AMOUNT" ,
	 CAST( "CURRENCY" AS NVARCHAR(5)) ,
	 CAST( "UNIT" AS NVARCHAR(3)) 
	FROM "/BIC/OUSALESADSO" ) WITH READ ONLY

 

Notice the very important change in the DDL sttement here – now it invokes a union of the previously used Active Data Table of SALESADSO with the virtual table : /BIC/OUSALESADSO generated in the DB owned schema. We can see three new virtual tables generated in the above schema upon activation of the aDSO with Cold Storage Access enablement in DTO.

 

The last one is what is used in the ‘8’ view & it secures that the data moved to Cold Storage successfully is accessed correctly. The ‘OU’ virtual table is formed by (defined in the external cold storage DB) an inner join of other two generated virtual tables /BIC/ONSALESADSO & /BIC/ORSALESADSO on column : ARCHREQTSN with a restriction on REQSTAT = ‘7’ (status indicating successfully completed archiving request).

The second interesting observation is that the COLD_STORE_FLAG value is set to ‘X‘ in the select from /BIC/OUSALESADSO, meaning the cold storage data. This way it is easily distinguished from the Hot Data, where the flag was set as ‘ ‘ (blank).

We can test it easily by setting up a rule & move some of the data to external Cold Storage. For SALESADSO, I set up & executed a rule & moved all the data prior to the year 2020 to Cold Storage.

Now if I preview the data of the generated ‘8’ view on 0CALYEAR level, it looks like below :

Thus we can expose this flag to control whether or not should the cold storage data be accessed based on the flag value. I hope to come up with another blog post, where I explain how to use this view for external access in BW mixed modelling scenario with proper pruning technique.

Another important thing to notice is the typecasting of characteristic fields in the select from ‘/BIC/OU‘ virtual table. The reason for this is in the external Cold Storage (in this context IQ system), the characteristic fields are created with increased length (For more details follow SAP note : 2592044). Observation : Even though the note says it’s created with 4 times the length of the field in HANA, in actual case it’s thrice the length. However, the measures are not impacted. Below is a side by side comparison of the characteristic columns between the HANA based Active Table & the IQ based Virtual Table.

So, to make the union on similar data types of the unique columns the typecasting is handled by the generated ‘8’ view.

These are my collective experience so far with the generated view for external access or the ‘8’ view of aDSOs in BW/4HANA 2.0. I hope this blog post can be used as a space for sharing feedback/comments on the topic or sharing the experiences gained by other members of the community regarding the same 🙂

Cheers!
Abhi

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo David Gallego
      David Gallego

      Hello Abhishek,

      Many thanks for this nice blog.

      We recently upgrade to BW/4HANA 2.0.

      We have a lot of HANA Calc views whose projection nodes are reporting views from aDSOs (/BIC/A<technical name of the aDSO>7 : View for Reporting for aDSO) .

      My approach is to replace /BIC/A<technical name of the aDSO>7 : View for Reporting for aDSO with new external SQL View /BIC/A<technical name of the aDSO>8.

      Do you think we need to replace in all calc views? Or only in cases where aDSOs are DTO enable?

      Waiting for your next blog!

      Many thanks and best regards.

      David

       

       

      Author's profile photo Abhishek Hazra
      Abhishek Hazra
      Blog Post Author

      Hi David,

      Thanks for your kind feedback 🙂
      I would recommend that you replace all the '7' views by '8' views in your calculation views based on the discussions we have had with SAP developer team after our BW/4HANA 2.0 migration. The use of '8' views were encouraged not only from the flexibility point of the added functionality for accessability of the cold storage data over the reporting '7' views, but also from the discussion it seemed that they can't foresee the future of '7' views considering the '8' views already serve the purpose with added benefits. So, my advice, since you're already on BW/4HANA 2.0, would be to replace your '7' views with '8' views in calculation views. We did the same 🙂

      But you need to beware that since the definition of '8' views contain the cold store virtual table (the 'OU' tables) in the union for the ones which have DTO in action, you need to be careful with the connections to cold storage being created for each query no matter which data is requested. This is explained in another blog post where I also have explained the controlling of the cold store flag access in optimal way within mixed modelling architecture, hope you'd like to have a look if needed:)

      Warm Regards,
      Abhi

      Author's profile photo Frank Schneider
      Frank Schneider

      Hello Abhi,

      thanks for the great blog.

      Did you write a new blog for "BW mixed modelling scenario with proper pruning technique" ? That's very interesting for us, because we are in a Proof of Concept for the DTO with IQ and have unfortunately problems with queries. Specifically, we access a calculationview via an HCPR that uses, among other things, an ADSO whose cold data is partially stored in the IQ via DTO, and the performance is really bad even for data that is not stored in the IQ.

      Thank

      Frank

       

      Author's profile photo David Gallego
      David Gallego

      Hello Abhi,

      Many thanks for your recommendation. I will ask all developers to make this replacement: replace all the '7' views by '8' views in our calculation views.

      I also checked your blog about Enabling cold store data access using view for external access (The ‘8’ View) for aDSOs in BW reports with mixed modelling scenario, and your different approaches. We will discuss internally about the best solution for it. I will let you know.

      You helped me very much. Appreciate.

      Best regards.

      David

      Author's profile photo Ritesh Paul
      Ritesh Paul

      Hi Abhi,

      I have noticed if I replace the system generated ADSO under Bw2HANA schema with /BIC/A<technical name of the aDSO>8 or 7, I am losing all text column /description. Do I need to join back all text tables separately in mixed model scenario? Please suggest, the best practice here.

       

      Thanks,

      Ritesh Paul

      Author's profile photo Abhishek Hazra
      Abhishek Hazra
      Blog Post Author

      That's right Ritesh, the aDSO 7 or 8 view are not extended to the info object level tables, it will only show you the values fot the infoobject/fields used in the aDSO. If you want to have the texts as well, you need to use the generated external HANA view for the aDSO, which can be found bw2hana package.

      Best Regards,
      Abhi

      Author's profile photo Ritesh Paul
      Ritesh Paul

      Thanks Abhi. We actually have system generated ADSO view, currently used mixed modelling scenario. However , it has severe performance issue, AO report takes 8-10 minutes to get refreshed. so I am planning to replace it with aDSO 7 or 8 view. Let me know if you have any suggestion or feedback, from performance perspective.

      Author's profile photo Abhishek Hazra
      Abhishek Hazra
      Blog Post Author

      Agreed, that’s exactly the experience I had with usage of the generated HANA view for the BW providers couple of years back, because it has to compile & do all those extra joins to text & hierarchies in the runtime. In mixed modelling I would rather prefer using the ‘8’ views (it’s suggested not to build solutions around ‘7’ views, I have mentioned in the above blog why) to using the generated calculation views.
      Talking about mixed modelling performance cases, I have had better performance experience with creation of calculation views using the 8 views & then create a composite provider consuming the calculation view instead of using the generated views. In the composite provider level you can assign infobjects to all the fields from the calculation views & you can access all your texts information in reporting layer automatically from the masterdata of the infoobjects.

      Best Regards,

      Abhi

      Author's profile photo Ritesh Paul
      Ritesh Paul

      Thank you very much, appreciate your help here.