Meaningful reporting from change documents (CDHDR, CDPOS) is always a challenge in R/3 system due to its huge volume, execution time & report limitations. The issue becomes more prominent when it’s required to audit the changes been made to sensitive documents on a periodical basis. BI provides an excellent platform to monitor and track the changes been made to the documents thereby enabling the auditors to perform detailed analysis of changes in effective manner.
In this blog, I’m going to explain the data extraction, data model and reporting components involved in producing meaningful analytics from change documents. Here are the typical scenarios:
- Tracking of changes been made to purchasing documents which include price changes, delivery date changes, purchasing group changes etc., for auditing purpose
- Tracking of changes been made to the material master which includes material group, product hierarchy etc.,
Data extraction:
- Tables involved: CDHDR & CDPOS
- Build an extractor to pull the data from tables CDHDR & CDPOS
- SAP delivered standard function module CHANGEDOCUMENT_READ_POSITIONS can be used for extracting the data from CDHDR table.
- Subsequently the respective details can be extracted from table CDPOS. In general this table (CDPOS) table consists of huge amount of data hence it’s important to supply all the key fields to minimize the data load time
- Field OBJECTCLAS in table CDHDR indicates the data relevancy. Examples:
- EINKEBELG represents the data pertaining to Purchasing documents
- BANF represents the data pertaining Purchase Requisitions
- MATERIAL represents the changes pertaining to Material Master
- Field Creation date of the change document (CDHDR-UDATE) can be used for identifying ‘new’ documents thereby it can be utilized for extracting ‘delta’ loads into BW
BW Modeling:
- Suggest building a central DSO which can act like 1st Layer storing all change documents. This DSO includes all fields part of CDHDR & CDPOS tables and stored in as-is format. Ex: Field “Object value (CDHDR-OBJECTID)” stores the value of the object in the raw form
- Building Info cubes specific to the area of interest i.e. 2nd Layer or Reporting Layer. Cubes will include all Info objects part of CDHDR & CDPOS tables and in addition objects are mapped to the standard BW objects
Let’s discuss a sample scenario which includes monitoring the changes pertaining to Purchasing documents. The structure of purchasing document in R/3 is broadly divided into Header data, Item data & Schedule line data. To have the flexibility in reporting suggest building separate Info cubes to hold the changes pertaining to Header, Item & Schedule line data. A multi-provider can be used for combining the individual info cubes. Refer to the below diagram for more details related to the data model.
Purchasing document header: Incorporate a start routine to restrict the documents specific to purchasing document header data
- Object class (CDHDR- OBJECTCLAS) = EINKBELEG
- Table Name (CDPOS-TABNAME) = EKKO (this table represnts purchasing document header data)
Include the following standard objects as part of Info cube thereby the attributes of these objects can be used for reporting
- Purchasing document object, this field can be populated from the field Object value using the formula =CDHDR-OBJECTID +3(10)
Purchasing document Item: Incorporate a start routine to restrict the documents specific to purchasing docuement item data
- Object class (CDHDR- OBJECTCLAS) = EINKBELEG
- Table Name (CDPOS-TABNAME) = EKPO (this table represnts purchasing document item data)
Include the following standard objects as part of the Info cube thereby the attributes of these objects can be used for reporting
- Purchasing document object, this field can be populated from the field Object value using the formula =CDHDR-OBJECTID +3(10)
- Purchasing document item number, this field can be populated from the field Object value using the formula = CDHDR-OBJECTID +13(5)
Purchasing document Schedule line: Incorporate a start routine to restrict the documents specific to purchasing document schedule line data
- Object class (CDHDR- OBJECTCLAS) = EINKBELEG
- Table Name (CDPOS-TABNAME) = EKET (this table represnts purchasing document schedule line data)
Include the following standard objects as part of the Info provider so the existing attributes of these objects can be used for reporting
- Purchasing document object, this field can be populated from the field Object value using the formula =CDHDR-OBJECTID +3(10)
- Purchasing document item number, this field can be populated from the field Object value using the formula = CDHDR-OBJECTID +13(5)
- Purchasing document schedule line, this field can be populated from the field Object value using the formula = CDHDR-OBJECTID +18(4)
Include a key figure named “counter” and update it with a constant value ‘1’ i.e. key figure value gets updated with value '1' for every record. This key figures can be used for tracking the total number of records been changed during the given period of time
Analytics:
A query is normally used for tracking the changes. First step in this process is to decide on which cube the query needs to be developed.
Let’s discuss a sample scenario of tracking changes been made to the delivery dates of purchasing documents. This information is part of purchasing document schedule line data hence the query needs to be developed on top of ‘Purchasing document schedule line’ data. Typical query structure to meet this requirement includes:
Filters:
- Object class (CDHDR-OBJECTCLAS) restrict to EINKEBELG
- Table Name (CDPOS-TABNAME) restrict to EKET.
- Field Name (CDPOS-FNAME) restrict the to ‘EINDT’.
- Field name identifies the object to be tracked. Examples include:
- NETPR for monitoring the Purchase order Net price changes
- EKGRP for monitoring the Purchasing group changes
Variables:
- Creation date of the change document (CDHDR-UDATE)
Output:
- Purchasing document number derived using the formula CDPOS-TABKEY+3(10)
- Purchasing Item Number derived using the formula CDPOS-TABKEY+13(5)
- Purchasing Schedule line number derived using the formula CDPOS-TABKEY+18(4)
- Change documents, unit referenced (CDPOS- UNIT_OLD) this refers to the old delivery date
- Change documents, unit referenced (CDPOS-UNIT_NEW) this refers to the new delivery date
- Counter Key figure to get the count of documents
Suggested Data Model:
- SAP Managed Tags:
1 Comment
