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 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.
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
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.
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).
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 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.
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 :
This way it makes it more simpler to have a reporting solution through mixed modelling approach by consuming this view for external access in a calculation view (I might come up with another blog post explaining a use case of the same). Thus we can expose this flag to control whether or not should the cold storage data be accessed based on the flag value.
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. 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.
No comments:
Post a Comment