April 17, 2012

How Does DSO Maintains Delta Records in Change Log Table


Hello Everyone,

In this post I would like to explain how DSO manages change log table to give proper delta records to the Info Provider(DSO/Info Cube) and the importance of orecordmode.

DSO has three tables i) Activation Queue/ New Table ii) Active table and iii) Change Log table.

DSO uses Change Log table to Manage Delta Records.

In DSO you can set whether Key Figures should get Overwrite/Summation based on Key Fields.

Now let’s take an example where today we got one sales order with quantity 50

111          50

When it gets loaded to the DSO and activated, we will get the below records in Active Table and Change Log table (there will be no data in Activation Queue/New Table upon activation)

Active Table:

111          50

Change Log Table

111          50   N (0recordmode - New Image)

When this record loaded to the cube, it will be as below (Info Cube is always Additive)

111          50

As this record is loaded to the CUBE, Delta Time stamp for this source and target will get update in ROOSPRMSC table with all the details.

Now tomorrow we got same sales order with quantity 70. So this record has to get change in DSO and CUBE. As DSO is overwrite, new value will get overwrite in place of old value so we will get correct value.

What about CUBE? Cube is always additive so if I load this new record to CUBE then the quantity will become 120 which is wrong.

This is the place where 0recordmode comes into picture. It maintains the images for changed and new records so that records will correctly uploaded to the cube.

Now as the quantity is changed to 70, we will get below records in active and change log table of DSO upon activation.

Active Table

111            70 (overwrite)

Change Log Table

111            -50        (X -- Before Image)
111             70    ( '  ' -- After Image) symbol for after image is 'Space'

So if we load this record to the target as a delta the above two records from change log table will get loaded to the CUBE.

Now what happens?

111            50 (already Existing)
111           -50
111            70
------------------
111            70 (Which is expected)

Hope this gives you clear idea...

You can refer my document for all the details on 0recordmode

7 comments:

  1. can anyone clarify which i dont understand how the system understand internally for a particular record ?
    for eg: if the 1800th record qty value is "70" then next time qty value become "100" so how the system identifies 1800th record has to overwrite
    is there any unique identification (like sid) please let me know
    thanks for ur blog!
    which is very helpful

    ReplyDelete
    Replies
    1. it's based on the Primary key's of each Record

      Delete
  2. Its really excellent information you posted on this blog that gives the clear architecture of delta loads via change log table. I believe most of the people do not know how the change log table maintain the delta and pushes the delta from DSO to Cube. Again Thanks for this great info.

    ReplyDelete
  3. Good Article on how delta's are maintained in Change log.
    How do you handle deletion in ECC and how does it flow through to DSO?

    ReplyDelete
  4. Excellent article. Thanks for posting this gem.

    ReplyDelete
  5. Is there possibility to delete selected records in active data table of dso?

    ReplyDelete
  6. I have scenario where there are before and after image in cube,which is same as change log.can any one explain what is exactly happening?DSO is standard DSO

    ReplyDelete