DSO or Data Store Object is a SAP BW object primarily used to store detailed transactional data on an atomic level. Unlike multidimensional data storage InfoCubes, it’s designed as a 2-dimensional flat database table that contains key fields and data fields.
Data is normally held in a DSO for several years. There are 3 types of DataStore Objects: Standard, Write Optimized and Direct Update. In our article, we’ll focus mainly on the architecture of these three types and main functions of the DataStore objects.
Functions of DSOs in a Data Warehouse:
First DSOs are mainly used to harmonize data before it is stored in multidimensional InfoCubes in aggregated forms. An example of harmonization would be: say a customer number has 10 characters in a source system and 13 characters in another source system. BI will harmonize the length of the customer number by creating a DSO with a customer number of 13 characters.
Second unlike InfoCubes,DSOs have the ability to overwrite data, Add or choose not to update at all.
Third you can do direct reporting on DSOs but normal reporting scenarios involve a drill down from an InfoCube to a DSO.
Architecture of Standard DSO
The standard DataStore object is filled with data during the extraction and load process of the BW system.
Standard DSO object consists of 3 tables:
- Activation Queue: This table serves to save DataStore object data records that are to be updated, but that have not yet been activated. The data is deleted after the records have been activated.BEx reporting uses this table.
- Active Data table:When the data transfer is triggered, the data is first written to this table. This table contains the active data.
- Change log table: During the activation process, changes are stored in the Change log table. Here, you can find the complete history of the changes since the content of the change log is not automatically deleted. The targets such as DataStore objects or InfoCubes are updated from the change log table if the delta method is triggered on the DSO.
Note that upon activation, the data arrives in the change log from the activation queue so that we’ll have both the old and new records and the data is written to the active table which will have its records updated. See the picture below:
Architecture of Write-Optimized DSO
Write-Optimized DSO was primarily designed to be the initial staging of the source system data in order for it to be transferred to a standard DSO or an InfoCube. It is advised to use Write-Optimized data store as a first layer for data, as a temporary storage area.
The Write-Optimized DSO consists of only one table: the active data table. The key benefit of using it is that the data is immediately available for further processing in active version. So all-in-all you save the activation time.
Architecture of Direct Update DSO
This is nothing but the previous 3.x transactional ODS. It consists of only one table: the active table. Direct update DSO ensures that the data is available quickly, and can be written to the DSO by several users at the same time.
Where do we define the type of the DSO in BW workbench?
Go to transaction code: rsa1, then choose the Modeling functional area of the Data Warehouse Workbench.
Choose InfoProvider, the tree will be displayed on the right hand side of the screen.
Choose the appropriate InfoArea in which you want to create the DSO → In its context menu choose Create DataStore Object. In the screen that follows enter the technical name of the DSO and a description→press the create button that is highlighted in red.
Open the Settings drop down list, and press on the Edit button marked below.
A pop up window appears indicating the three types of DataStore objects: Standard, Direct Update and Write-Optimized.
DSOs are in no way a substitute of InfoCubes in reporting but using them provides flexibility in accessing the data which is stored in a cleansed, detailed format. In addition to having the overwrite capability in manipulating the new data.