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
Architecture of Standard ODS /DSO (7.x)
“ODS Objects consist of three tables as shown in the Architecture graphic below” – Refered from SAP Docs:
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.
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
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.
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)
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.
Figure 6: Activation Queue
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
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)
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.
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:
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 ……….
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
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
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.
Good blog for basic understanding of delta.
Keep blogging.
KJ!!!
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.
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
Raj,
Two of your images were broken, can you repair it ? thank you !