In BW4HANA system, ADSO (Advanced Data Store Object) is the primary persistent object used in data modelling. This blog explains the different tables created when using an ADSO and how a full/delta/initial load updates/fetches data from these different tables.
Below images shows the different tables/views created when we create an ADSO. Given below are three tables that are created for all type of ADSOs.
When creating an ADSO of type inventory, below additional tables are created.
In addition, below 3 views are also generated for all type of ADSOs.
Under the ‘Standard DataStore Object’, we have three options:
Write Change Log
If we use this option, the delta (new, deleted, and changed records) is saved in the change log table and data for delta loads to further targets will be fetched from this change log table.
If we plan for our data source to always deliver the data as a “FULL”, then by setting this indicator, deleted data records can be identified and updated. During activation, the system identifies the records that are in the active table but not in the load request. These records are written to the change log table as reverse images, which can be propagated to further data targets
Unique Data Records
This option is used when the data source always delivers unique records. This allows the activation process to always use “insert” SQL and speeds up the activation time significantly.
Under the ‘Staging Data Store Object’, we have three options:
Inbound Queue Only
There is no change log table concept in this type of ADSO. Full or delta extraction process always fetches the data from the inbound table. This type of ADSO is not suitable for reporting and analysis.
In general, the data is always written to the inbound table. If you choose Compress Data, the data is written to the table for active data (during the compression process) once it arrives in the inbound table. Based upon the semantic key data is aggregated and written to the active data table. In the query, you will then only see the data that has been activated. Request-based deletion of data from the Datastore object is not possible. The data can be deleted selectively.
When this option is selected, after activation of the data load request, the data will still be retained in the inbound table. For loading to further data targets, the data is always extracted from the inbound table. But when a query is executed, the active table is accessed.
Data Mart Data Store Object
This type of ADSO is used for reporting and analysis purpose. The data is always loaded into the inbound table. When we activate the data, the data is updated into the active table and aggregated, then the data is deleted from the inbound table. The change log is not used here.
Full and initial delta extraction fetches the data from the active table to load further to other targets. for a delta extraction, data is fetched from the inbound table.
Direct Update Data Store Object
In this Datastore object type, data is directly written to the active data table and the data is written either using a DTP or an API. We can only do full extraction from this ADSO and data is fetched from the table of active data.
The below table explains which type of ADSO table is used when the data is fetched to update further data targets.
|ADSO Types||Delta load||Full / Initial load||Reporting|
|Standard Datastore Object||Change Log Table||Active Table||Active Table|
|Staging Datastore Object (Compress Data)||Inbound Table||Inbound Table||Not suitable|
|Staging Datastore Object (Reporting enabled)||Inbound Table||Inbound Table||Inbound Table through Active Table|
|Data Mart Data Store Object||Inbound Table||Inbound Table through Active Table||Inbound Table through Active Table|
|Data Store Object for Direct update||Not Possible||Active Table||Active Table|
Based upon the setting we do with above-explained options; these three special properties are enabled.
With this setting enabled, two additional tables are created for the ADSO.
- Validity table
- Reference point table
The ‘validity table’ stores the time interval(s) for which non-cumulative values have been loaded into the Info Provider. The ‘reference point table’ is updated when the data is activated in an Inventory-enabled ADSO (like compression with Marker update of an Inventory cube in legacy BW).
With this setting, write back is possible from connected planning applications (Analysis for Office, Lumira dashboards etc.). You can switch back and forth between load mode and plan mode in this type of ADSO and perform corresponding actions.
Write Interface- Enabled
This setting allows external tools such as Data Services or SAP Cloud Platform Integration (CPI) to write data to the ADSO inbound table directly. As of now, the API for writing data to Datastore objects is not released for other third-party tools.
This property can be used for all types of standard Datastore object and staging Datastore object, but not together with Inventory enabled or Planning enabled.
Know more about Visual BI Solutions SAP BW Services offerings here.