Learn about a custom solution in SAP NetWeaver BW that helps developers and technical analysts track employee movements and changes within SAP ERP Human Capital Management, including how to use DataStore objects to capture this information. The employee movements provide HR end users with visibility to transfers that take place within the organization (e.g., between departments or relocations to different countries). These departments or countries can have different systems or software to house employee data, but a custom method enables analysis on all employee movements in SAP NetWeaver BW.
Key Concept
A change log table keeps the history of changes that have happened to the semantic key of a DataStore object. By using a change log table, previous characteristics of employees can be compared with current characteristics to track employee movements.
There is no standard SAP ERP Central Component (SAP ECC) report or SAP NetWeaver BW business content available to capture employee movements, so we developed a complete custom solution that you can implement across SAP NetWeaver BW projects.
We have carried out a similar solution for five clients. Scenarios for which the solution is useful are as follows:
End-to-End Data Flow
Let’s look at what objects are required for our solution and how the data flows from each object. This section explains the whole concept through one example.
Figure 1 shows that data is extracted from 0EMPLOYEE, which is populated from SAP ECC by extractor 0EMPLOYEE_ATTR. 0EMPLOYEE is a standard InfoObject that contains many employees characteristics defined by SAP by default. You can define additional custom characteristics in SAP NetWeaver BW. You add them in 0EMPLOYEE, and map them with DataSource 0EMPLOYEE_ATTR.
Meanwhile, 0EMPLOYEE_ATTR is a standard DataSource in SAP ECC, which needs to be activated and moved to the SAP NetWeaver BW system to get 0EMPLOYEE characteristics. If custom characteristics are required (not provided by SAP by default), then this DataSource needs enhanced fields, and you need to write logic via transaction code CMOD.
In this example in Figure 1, we track the movement of the following employee objects:
- Personnel area
- Organizational unit
- Position
- Employee group
- Pay grade
- Personnel sub-area
- Company code
Figure 1
Data flow diagram
There are two DataStore objects (DSOs): A Level 1 DSO (ZHR_O01) captures the changes in a change log, and a Level 2 DSO (ZHR_O02) reads from the Level 1 DSO change log table to record movements, as explained by the example in Figure 2.
Figure 2
Level 1 DSO change log table
Data in Figure 2 shows how the changes to employee characteristics behave in the Level 1 DSO tables. DSOs consist of three tables: new, active, and change log. This is a standard SAP NetWeaver BW concept.
An active table consists of current records, and a change log table consists of all the changes that have happened to that record. In the example in Figure 2, when data is loaded for the first time, the active table and change log table have same record.
During the next load, if the characteristics of 0Employee E1 change, then the active table contains the changed record, and in the change log table, two additional entries are generated:
- RECORDMODE = BLANK, which is an active image representing a current employee record
- RECORDMODE = X, which is a before image representing the previous characteristics of 0EMPLOYEE E1
Now data from the Level 1 change log table is sent to Level 2 DSO through a data transfer process (DTP) and changed in the Level 2 DSO transformation end routine. In the end routine of the Level 2 DSO, there is an SAP-defined internal table called RESULT_PACKAGE, in which both after and before images from the Level 1 DSO change log table are automatically present.
Below is step-by-step logic for the ABAP code that needs to be applied in the end routine. Here, you separate after and before images into two internal tables (see step 1), and compare both tables to get the movements (step 4). We provide sample code for these steps in Figure 3 and in this file you can download.
- Step 1. Separate out the after images (i.e., having REC_NUM = 1) and before images (i.e., having REC_NUM = -1) from RESULT_PACKAGE into IT_CURRENT and IT_PREV
- Step 2. Start looping through IT_CURRENT
- Step 3. Read IT_PREV based on EMPLOYEE
- Step 4. Compare each field in both tables, and append a new record in the result package for each change (e.g., if there are 10 fields to compare, and only three have changed, then only three records need to be appended)
Figure 3
Sample code to insert in the end routine of transformation ZHR_O01 > ZHR_O02
Level 1 DSO Requirements
This section defines the Level 1 DSO properties and structure. Based on the properties in Table 1 and structure in Table 2, create this DSO in the SAP NetWeaver BW system.
Table 1
Level 1 DSO properties
If characteristics that need to be tracked are different from those in Table 2, then replace them with data fields with those characteristics:
Table 2
Level 1 DSO structure
Create a transformation between 0EMPLOYEE and ZHR_O01 with the mapping shown in Table 3.
Table 3
Transformation mapping between 0EMPLOYEE and ZHR_O01
Now move data from 0EMPLOYEE to ZHR_O01 using a DTP. Table 4 shows what data needs to be filtered, as all data is not required.
Table 4
DTP properties for data to be filtered
A DTP filter of 0DATETO = 31.12.9999 in Table 4 is required because 0EMPLOYEE has time-dependent data for all the characteristics in the Level 1 DSO. This filter ensures that only the current, valid employee record as of each day is loaded to the Level 1 DSO.
Level 2 DSO Requirements
This section defines the Level 2 DSO properties and structure. Based on the properties in Table 5 and structure in Table 6, create this DSO in the SAP NetWeaver BW system.
Table 5
Level 2 DSO properties
Table 6
Level 2 DSO structure
Create a transformation between ZHR_O01 and ZHR_O02 with the mapping shown in Table 7. All data fields in the Level 2 DSO (ZHR_O02) are calculated using end routine logic given in the section after Table 8.
Table 7
Transformation mapping between ZHR_O01 and ZHR_O02. Note that the blank fields will be populated by ABAP code in the end routine, thus they are blank now
Now move data from ZHR_O01 to ZHR_O02 using a DTP. Table 8 shows the properties of the DTP.
Table 8
DTP properties
Isha Gupta
Isha Gupta is an SAP BI consultant in Accenture within its India Delivery Center. She is a subject matter expert with almost five years of experience in BI data model design, development, and reporting. She is a certified solution consultant in SAP NetWeaver 2004 Business Intelligence. She is proficient in various SAP BusinessObjects tools. Isha also has been involved in various SAP BI capability development activities within Accenture, including management of IDC SAP BI reusable assets, thought leadership, planning, and executing various client demos.
You may contact the author at isha.guptas@accenture.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
Vipul Goyal
Vipul Goyal is a Senior SAP Analytics Consultant for Accenture, mainly focused on SAP analytics technologies like SAP HANA, BW, and BusinessObjects. He has SAP implementation experience in the retail, construction, oil and gas, and telecommunications industries. Vipul is certified in SAP HANA and SAP BW and has led many successful SAP analytics implementations for a variety of clients.
You may contact the author at gemini.vipul@gmail.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
