Tables in Advanced DSO and their uses

See more in our hbites Advanced DSO

With the advent of HANA, SAP has upgraded its data warehousing solution SAP BW into newer version known as SAP BW on HANA.  With SAP BW on HANA, new concepts of data warehousing modelling and objects has been evolved and Advanced DSOs (ADSOs) forms the backbone of SAP BW on HANA when it comes to data persistency which in sync with recommended LSA++ architecture. The object features for data persistency in earlier SAP BW versions like PSA, Hybrid providers, Infocubes and Datastore Objects have been unified and made simpler with the introduction of Advanced DSOs. We have different templates for creating ADSOs depending upon the functionality we require; however, this article focuses on the different kind of tables involved in an ADSO structure and their use cases.

Typically, an ADSO consists of three kinds of tables and data to these tables are filled and processed depending on ADSO type. Please note, none of these tables are created manually, but its generated by system in the database when the ADSOs are created by developers at UI. The tables are listed below.

1. Inbound Queue.
2. Active data.
3. Change log.

Inbound Queue:

The naming convention of an Inbound queue of ADSO is /BIC/AXXXX1 where XXXX is the technical name of ADSO. Depending on the setting you have configured for ADSO, inbound table has various functions. In general, inbound table is just like new data table/activation queue and contain request transaction number, data packet and data record number.

If ADSO is modelled as

• Data acquisition layer (including corporate memory) – ADSO behave like a write optimized DSO and inbound table stores the source data. We can model this template like a PSA as well (Most detailed level of data as in source).
• Corporate Memory (With reporting capabilities) – Data remains in inbound table and the system does not erase data even after data is activated and moved to active data table. This enables backtracking of data records to source. Remember this configuration comes with additional storage costs.
• Corporate Memory (With compression capabilities) – In this case, inbound specifically behaves like New data table (As in DSOs in SAP BW) and data is deleted after the activation job is complete.
• Datawarehouse layer (With delta calculation) – ADSO behaves like a standard DSO and inbound table acts as new data table. Once the request is activated, data is moved to active data table and change log contains the delta record changes.
• Datawarehouse layer (Data mart) – ADSO exhibits an infocube like behaviors and in this case, inbound table functions like “F-table” of an infocube. Once the data is activated (here it is similar to collapse), the data is moved to active data table and after the activation job, the inbound table is empty.

Active data:

The naming convention of active table of ADSO is /BIC/AXXXX2 where XXXX is the technical name of ADSO. This table constitute the majority of central persistency tables in SAP BW on HANA. Similar to inbound table, active data table also functions in various ways according to the ADSO modelling.

• Corporate Memory (With compression capabilities) – Data is moved to active data table after activation and data is used for reporting.
• Corporate Memory (With reporting capabilities) – Data is moved to active data table after activation and data is used for reporting.
• Datawarehouse layer (With delta calculation) – ADSO behaves like a standard DSO and active table stores the data for reporting purposes. The activation of data load request data moves data from inbound to active data table and change log contains the delta record changes.
• Datawarehouse layer (Data mart) – ADSO exhibits an infocube like behaviors and in this case, active table functions like “E-table” of an infocube, which means, data is compressed and detailed information like TSNs are deleted and data is stored in BW with optimal space consumption. The data is available for reporting as well in this case.

Changelog:

The naming convention of change log table of ADSO is /BIC/AXXXX3 where XXXX is the technical name of ADSO. As the name suggests, it keeps tracks of all the data changes according to the keys defined. The table helps in the following ways.

• When ADSO behaves like a standard DSO and change log contains information on changes happened to data set. This is particularly useful when we have to load the data to multiple data targets within the SAP BW on HANA and change log table is used for delta calculations while loading into upper targets.
• Data in change log table enables deletion data based on the load requests in an ADSO, incase we need to perform any data correction activities.

The below table summarizes the functionalities of each tables.

In addition to the above three tables, two views are also created when an ASO is activated. One view (/BIC/AXXXX6) is available for extraction purposes and another view (/BIC/AXXXX7) is used for reporting purposes. In this case, view data is a union of both available tables, defined by the settings of ADSOs.

Below table shows the technical names of tables generated for an ADSO ZTST_ADS

In order to illustrate the functionality of each table, as an example, we will use an ADSO configured similar to classic DSO and data load transformation is set to “Over-write”. In the example ADSO, we will set the key fields of ADSO as Document number, Document Item and Material.

Configuration and Structure of ADSO

Data in new data table

Please note request is just loaded and not activated.

Note the records highlighted in the above figure. The records are loaded in the most detailed way, which is similar to that in the source. The REQTSN, data package ID and record number for each records are also available along with the data.

Data in active data table

Once the data is activated, the data is moved from Inbound table to active data table. As we can see in the below screen shot, the number of records changed from 8 to 7, based on the key fields defined in the ADSO.

After the activation, the records are over-written and only the latest values for key figures and other characteristics are retained for reporting purposes. Hence the record number 7 and 8 (in the Inbound table) is merged and only one record with exists with plant US04 for the same set of document number, item number and material number combination.

Now let us have a look at change log table on how the data is stored.

Data in Change log table:

Record mode field is important field in change log, which is used for delta administration and overwrite functionality based on key fields. As shown in the figure below, record mode has three values

1. N – New record
2. X – Before Image
3. “Blank” – After Image

As shown in the above figure, the first record (Record number 4) has record mode “N”. However when another record with same set of keys, but different characteristic (Value for plant changed from US01 to US04), the newer record (Record number – 6) replaces the original value and cancels out (Record number – 5). This will ensure that the quantity is calculated correctly (In this case, only latest quantity should be displayed along with latest values for other characteristics) for the same set of key combination. This is inline with expected results for this type of ADSO.

See more in our hbites Advanced DSO