History tracking in ODS
I have ODS1 and ODS2. ODS1 is overwrite and ODS2 has timestamp in its key so there is no overwrite. ODS1 updates ODS2. I would want ODS2 to be updated by records contained in ODS1 change log only. Afterwards, I want to be able to report on changes to characteristic values listing which characteristics have changed and their original values and new values and also when the characteristics changed. Since change log is not available for reporting, how can I model this reporting requirement. Any suggestions are will be greatly appreciated.
Create a generic datasource based on the change log table of ODS, update other ODS with this datasource and report on the secod ODS. Following are the steps to be followed.
1. RSA1 ->ODS->manage, contents tab, change log button,the header of the next screen shows the name of the change log table.
2. Go to transaction RSO2 to cretae generic datasource. Choose transaction data in first screen.
3. In the next screen, choose an application component where you want to store datasource. At the top right, out of 3 available options, click on "Extraction from view". Give the change log table name in the space provided.
4. Click on save. You will get an information messgae which says you can not transport this datasource, accept it and save as local object. (You need to create it directly in prod. First create it in dev and check if works fine and then create in prod).
5. In the next screen, you can maintain the extractor e.g. choose "fields for selection", "fields to be hidden" etc. Make 0RECORDMODE as selection field if you want to load data only for particular value of 0RECORDMODE.
6. Use this datasource to load second ODS nad report on it.
Hope it helps.