[quote]An SAP BW DSO stands for Data Store Object and was previously called an ODS which stands for Operational Data Store. Why has the name changed? Don’t ask me! SAP seem to change the names of there product names more often than I have hot dinners. I am sure they have their reasons .[/quote]
A DSO is a SAP BW object primarily used to store detailed transactional data at a granular (document) level. Unlike multidimensional data storage objects like InfoCubes, DSO’s are designed as a 2-dimensional flat database table that contains key figures and characteristics. Data can be aggregated as well be over-written.
There are 3 types of DataStore Objects that are generally used in BW systems: Standard, Write Optimized, Direct Update. In this article by Roula Sawma she focuses mainly on the architecture of these three types of DSO’s and the main functions of the objects themselves.
Functions of DSOs in a SAP 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 ‘A‘ and 13 characters in source system ‘B‘. BW will harmonize the length of the customer number by creating a DSO with a customer number of 13 characters to ensure the data from both systems can be stored together without any loss of data.
Second unlike InfoCubes, DSOs have the ability to overwrite data, Add or choose not to update at all. This is all dependant on the logic and delta mechanism used in the DSO.
Third, you can use DSO’s for direct reporting 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. This process makes use of 3 tables that make up standard DSO’s:
Activation Queue - This table serves to save DSO 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 can not use this data until it is activated.
Active Data Table - When the data transfer is triggered, the data is first written to this table. This table contains the active data and depending on the transformation and delta process that has been defined will aggregated, add, delete, update records in the DSO. This is the table used for Bex reporting.
Change Log Table - During the activation process and depending on the transformation that has been created, 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 data 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 both the old and new records are written to the active table which will also update existing records. See the picture below:
Architecture of Write-Optimized DSO
The Write-Optimized DSO was primarily designed to be the initial staging area for data coming into a BW system from an outside source system. The idea is to then move this data into another DSO or Cube. You may wonder what the point of this is. It is all down to speed. It is advised to use Write-Optimized data store as a first layer for data, as a temporary storage area because it is designed in such a way as to allow very fast data loading.
Write-Optimized DSO’s consist 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 an active state. Like I mentioned above, this type of DSO is all about speed.
Architecture of Direct Update DSO (3.x ODS)
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?
This is achieved through the Modeling functional area of the BW Workbench – Transaction code RSA1.
- 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 and 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:
- Direct Update
DSO’s are in no way a substitute of InfoCubes in reporting but using them as part of you BW Data Model provides flexibility in data staging, data cleansing and document/detail level reporting.