Timestamp as key field on ODS

[Former Member]

Hello Gurus,

I have defined as key, 0date and 0time fields on my ODS so that I get unique records. However, on loading data to the ODS, I still get duplicate 0date and 0time entries. I am populating the 0date with sysdatum and 0time with system local time at runtime within the update rules. However, for some reason, most of the records are being populated with the same 0date and 0time value. What is the best method for ensuring a unique timestamp for each record and if this cannot be achieved, can someone tell me how to generate a unique sequence?

[Former Member]

Hello Rue,

If you have mapped 0date and 0time with system date and time in update rules, all the records of a request will contain same value in these felds. You need to have some other key in the ODS to idetify the records uniquely. Time stamp can be used to store and track history. Is that something you are looking for or want to use time stamp for some other purpose?

Regards,

Praveen

[Former Member]

Praveen,

Yes, I am trying to use timestamp to track history. I want every record to be updated into my ODS and I was hoping timestamp would give me a unique record. What else can I do?

[Former Member]

Hello Rue,

In order to use time stamp for tracking history, you need to use time stamp in addition to the actual key fields of ODS. With time stamp, you can prevent old data from getting over-written with new records with same key combination and hence can maintain the entry.

Please keep your existing key fields in ODS and add time stamp as additional key field and it should work.

Regards,

Praveen

[Former Member]

Praveen,

Thats what I have done but I still get duplicate entries. I have document number plus the timestamp as key on my ODS. But because the timestamp is populating the same value, if I have the document no appearing more than once in a data load, I am ending up having duplicate key because the timestamp is not providing me with a unique time for each record in the load. I don't know if there is another way to make it unique for each record. Thanks.

[Former Member]

Hello Rue,

Time stamp method implemeted in this way can be used to diffrentiate between records with same key from different data loads. Because here what we are getting as time stamp is the update time of the records and it will be same for all reords in a particular data load. But if you want to get unique records from the same load, you need to identify some other field apart from existing key and need to include that in key section.

Regards,

Praveen

[Former Member]

Praveen,

Thanks. Do you know if I can generate a sequence number at time of loading to provide me with a unique key? If so, how can I populate the sequence at time of update of the ODS.

[Former Member]

Hi Rue,

Often when source system data is differentiated based on the time-stamp,for the purpose of having history data,the key field of timestamp in the ODS is based on one of the date/time fields present in the base table in R/3.

Using sy-datum and system time is not advisable as..if u reconstruct any loaded request at any time,the timestamps on the records will surely change to the new 'current' date and time.The ODS data then will not match up with R/3 records.

Are there suitable date/time fields available in your base tables?

cheers,

Vishvesh

[Former Member]

Rue,

What is your datasource for this load?

Regards,

Praveen

[Former Member]

Vishvesh,

Many thanks. My source system is non-SAP and it is not providing dates field for things like last change date, time etc. I was hoping I could model a unique key in BW based on either sequence number or date and time.

[Former Member]

Hello Rue,

Did you check if you can get unique records by combining multiple fields and using them as key. If that is possible, that would be good a neat solution.

As far as generating the sequence is concerned, if we include code in start routine, it will run for each data package and may create same sequence number for same key combination coming from different data packages.

I was thinking of creating some random numbers and use in the key section to make records unique. you can use function module QF05_RANDOM_INTEGER to generate random numbers. Put value of parameter RAN_INT_MAX as the maximum possible value to minimise the chances of getting duplicate values. Include a counter in key section and in its update routine call this FM.

Please let me know your thoughts on this.

Regards,

Praveen