SAP System Setup Training Course
Reveals How To Setup A Free SAP Developer System.
Learn SAP & Practice For Certification. Developer Tips Included:

What Is A SAP BW DSO And ODS?

[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:

  1. 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.
  2. 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.
  3. 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:

How A DSO Is Made

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.

Edit Datastore Object

Open the Settings drop down list, and press on the Edit button marked below.

DSO Settings

A pop up window appears indicating the three types of DataStore objects:

  • Standard
  • Direct Update
  • Write-Optimized

DSO Summary

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.

...

Roula has been an SAP consultant for over 5 years of experience. She has done several end to end implementation projects carrying out SAP BI work, Data Migration and ABAP coding. She also has experience developing a wide range of websites using HTML, PHPI, CSS, mySQL including sites for startup companies and small businesses.
Categories

About the Author:

Pete has been working with SAP technologies for over 10 years. He started out as an ABAP consultant and then moved on to BW where he has worked many different clients covering a wide variety of industries. "I love introducing SAP technology (especially BI) to new clients and showing them how they can go from zero to hero within their business in super fast time". Contact me on twitter @PeterMoxon

  • Laurent THIBERT

    Hi Pete,

    Your explanation on the Write Optimized DSO is a bit uncomplete. The initial staging area for data coming into a BW system is still the PSA even for a Write Optimized DSO.

    The goal of such DSO is mainly to request its data with the Bex tools in order to provide queries at the raw data level.

    Data are going from Write Optimized DSO to standard DSO or cubes with transformation and agregation. Then the data are no more raw.

    A strategy for validating a BW project is to compare data before transformation/agregation versus data after transformation/agregation.

    In 3.x we can generate an export datasource on the PSA. This datasource is mapped to a virtual provider with transfer/update rules. A query can then request the data upon the virtual provider to display their raw version (from the PSA).

    In 7.x the schema is easier help to such Write Optimized DSO which provides the data to the Bex.

    In fact such Write Optimized DSO is a PSA but provider of the Bex.

    Regards,
    Laurent

    • http://www.saptraininghq.com Pete

      Hi Laurent,

      Thanks for the comment. You are right in that the PSA is still the staging area but quite often a write-optimized DSO is still used to stage data before moving it onto other BW objects. I think the main point Roula was making is that write-optimized DSO’s are great for loading data into BW fast to enable the data to be processed further without slowing down the data load process.

      I appreciate the additional information you have added about the 3.x export datasource. It’s all good information.

      The scope in which BW is used is massive and different companies use the tools in many different ways. It’s great to share experiences, tips and trick along the way.

      Pete

  • swathi

    The information on DSO is very useful , understanding and clear.
    Thanks
    Swathi

x

Free SAP Training Course