History tracking in ODS

[Former Member]

Hello Gurus,

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.

[Former Member · Accepted solution]

Hello Rue,

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.

Regards,

Praveen

[Former Member · Accepted solution]

Praveen,

Magnificent, this solved my problem. Thanks very much.

[Former Member · Accepted solution]

Hello guys,

I'm trying to follow your suggestion but I'm experiencing some troubles. I succesfully managed to create DataSource (through RSO2) which is connected to the change log table of ODS1. When I use this DataSource to populate ODS2, the record with ORECORDMODE = 'x' are provided with value 0 instead of the reverse value. Eg:

CUSTOMER;COORDER;ITEM_NUM;RECORDMODE;NET_PRICE;CALDAY;

BBB;100;2;N;2000;10.01.2006;

BBB;100;2;X;-2000;10.01.2006;

BBB;100;2;' ';1500;12.01.2006;

BBB;100;2;X;-1500;12.01.2006;

BBB;100;2;' ';3000;20.01.2006;

so I can easily see that order 100, line item 2, was 2000 on jan 10th, changed to 1500 on jan 12th and then to 3000 (which is actual value) on jan 20th. What I need to do is KEEP all of that data, so I use a DataSource reading directly from Changelog. If I check the PSA of the DataSource I find the same data.

The problem is that when I use e DTP to load data from DataSource to another ODS (where I explicitally add 0STORNO to map 0RECORDMODE values), that is what I see:

CUSTOMER;COORDER;ITEM_NUM;0STORNO;NET_PRICE;CALDAY;

BBB;100;2;N;2000;10.01.2006;

BBB;100;2;X;0;10.01.2006;

BBB;100;2;' ';1500;12.01.2006;

BBB;100;2;X;0;12.01.2006;

BBB;100;2;' ';3000;20.01.2006;

that is, every BeforeImage is correctly copied except for the numeric values like NET_PRICE.

Just to make the situation clearer, I'm on SAP Netweaver 2004s BI, the delta process used by the DataSource is AIE (I cannot change that) and I already tried to load data in Delta or Full mode.

Any help would be greatly appreciated.

[Former Member]

Rue

Create the ODS1 change log as a datasource in your

BI system Transaction RSO2. Then update ODS2 from New

Datasource-> infosource -> ODS2.

Cheers

George

[Former Member]

George,

Thanks for your response. How do I select that I want data contained in the change log table of the source ODS. I dont want to load data that is contained in the active table of the source ODS. The option you gave me seem to just select on the source ODS without allowing me to select the specific change log table of the source ODS. Any ideas?