Skip to Content

Long time ago I was sitting back thinking about the architecture of ODS and how invaluble it is from its inception with BW 2.0 in the BW Data Warehouse Layer (DWH).

This basically motivated me to write this blog with the necessary tech details of ODS – Well call it Data Store Object (DSO) as of NW04s or BI 7.0

“An Operational Data Store object (ODS object) is used to store consolidated and cleansed data (transaction data or master data for example) on a document level (atomic level)”

– Refered from SAP Docs.It describes a consolidated dataset from one or more Info Sources / transformations (7.0) as illustrated below in Fig.1.

In this blog we will look at the Standard Data Store Object. We have other types namely Data Store Object with Direct Update (Transactional ODS in 3.x) and Write Optimized Data Store new with BI 7.x which contains only Active data table used to manage huge data loads for instance – Here is the link from Help portal

Write optimised DSO 

Architecture of Standard ODS /DSO (7.x)

“ODS Objects consist of three tables as shown in the Architecture graphic below” – Refered from SAP Docs:

image

Figure 1: ODS Architecture – Extracted from SAP Docs

TIP: The new data status is written to the table with active data in parallel to writing to the change log taking the advantage of parallel processes which can be customized globally or at the object level in the system

Lets go through a Scenario

In this example we will take the Master data object material and plant (0MAT_PLANT compounded with 0PLANT) with a few attributes for the demonstration purpose. Now define a ODS / DSO as below where material and plant is a key and the corresponding attributes as data fields.

!https://weblogs.sdn.sap.com/weblogs/images/251696177/Fig2.jpg|height=315|alt=image|width=448|src=https://weblogs.sdn.sap.com/weblogs/images/251696177/Fig2.jpg|border=0!

Figure 2: ODS / DSO definition

Lets create a flat file data source or an info source with 3.x in this example to simplify the scenario with all the info objects we have defined in ODS structure

image

Figure 3: Info source definition

Lets check the flat file records, remember that the key fields are plant and material and we have a duplicate record as in the below Fig.4. The ‘Unique Data Records’option is unchecked which means it can expect duplicate records.

image

Figure 4: Flat file Records

Check the monitor entries and we see that 3 records are transferred to update rules and two records are loaded in to NEWDATA table as we haven’t activated the request yet. This is because we have a duplicate record for the key in the ODS which gets overwritten (Check the first two records in Fig 4)

image

Figure 5: Monitor Entries

Now check the data in the NEWDATA / ACTIVATION QUEUE table, we have only two records as the duplicate records gets overwritten with the most recent record i.e. record 2 in PSA got overwritten as it has got the same key material and plant.

image

Figure 6: Activation Queue

image

image

image

image

Figure 11: Monitor

Look at the new data table (Activation Queue) and we will have 3 records that are updated as seen in the monitor

image

Figure 12: Activation Queue

How the Change log works?

We will check the change log table to see how the deltas are handled. The highlighted records are from the first request that is uniquely identified by technical key (Request Number, Data packet number, Partition value of PSA and Data record number)

image 

Figure 13: Change log Table 1

With the second load i.e. the second request the change log table puts the before and after Image for the relevant records (the non highlighted part from the Fig.13)

In the above example Material (1) and Plant (1) has the before image with record mode “x”(row 3 in the above Fig)
And all the key figures will be have the “-” sign as we have opted to overwrite option and the characteristics will be overwritten always.

image

Figure 14: Change log Table 2

The after image ” ” which reflects the change in the data record (Check row 4 in the above fig). We have changed the characteristic Profit center with SE from SECOND and the Key figure Processing Time is changed from 1 to 2.
A new record (last row in the above Fig) is added is with the Status “N” as it’s a new record.

Summary

This gives us an overview of the standard ODS object and how the change log works. The various record modes available:

!https://weblogs.sdn.sap.com/weblogs/images/251696177/Fig15.jpg|height=198|alt=image|width=333|src=https://weblogs.sdn.sap.com/weblogs/images/251696177/Fig15.jpg|border=0!

Figure 15: Record Modes

Check the note

399739

about the details of the Record Mode. The record mode(s) that a particular data source uses for the delta mechanism largely depends on the type of the extractor. Check the table

RODELTM

about the BW Delta Process methods with the record modes available as well our well known table

ROOSOURCE

for the extractor specific delta method.

For Instance LO Cockpit extractors use ‘ABR’ delta method that supplies After-Image, Before-Image, New Image and Reverse Image. Extractors in HR and Activity based costing uses the delta method ‘ADD’ i.e. with record mode ‘A’ and FI-GL,AR,AP extractors are based on delta method ‘AIE’ i.e. record mode space ‘ ‘ After image. The list goes on ……….

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Suneel S
    Hey Raj

    This gives a good information on ODS details.
    What about the ODS setting unique records how does it work?
    I think its not dealt in detail here

    Regards,
    Suneel

    (0) 
    1. Raj Alluri Post author
      Suneel,

      Yes The “unique Data record option” means that the data target will not expect any duplicate records from the source for the key combination defined in the ODS defination as in the above example – Material and Plant as they are key in the ODS.

      Thanks,
      Raj

      (0) 
  2. Sudheer Bethanabotla
    Hi –

    I guess I have this basic question but wanted to know the answer. When you have the same key field combination but changes in keyfigures…during the activation, ODS writes a record with -ve values for the keyfigures along with the new record into the change log table right? My question is, does the ODS check against the active table or the change log table to perform this task? In other words, how would it know that this particular key combination already exists and hence has to create a record with old values (change in sign) and add the new record.

    (0) 
  3. HemaVardhan Gillella
    Raj,
    I have quetion regarding change log table deletion.
    We have huge change log tables and not sending data to cubes.
    But in future we want to build a summarized cubes based on the various reporting requiremetns.
    So is it ok to delete the change log tables?

    Thanks,
    Reddy.

    (0) 
    1. Sebastian Drews
      yes should be fine, as you can build the cubes from the active tables only. From the time you did the initial load to the cubes you should keep the change log to enable a delta load.
      (0) 
  4. C C
    How would the changelog look like if say the Material 75, plant 1 item didnt change in the next load but was still present in the flat file load?

    The active table would overwrite 75 1 with the same unchanged 75 1, but how would the changelog look?

    Would there be a 75 1 -key figure  X
                     75 1 key figure 

    (0) 

Leave a Reply