Surrogate Key Generation for Orders/Transactions

[DWModeler]

Hi,

We get a large number (millions) of transactions/orders on a daily basis into the DW. These orders carry a numeric id today but it is likely to change to a large char id in future. I'd like to inject surrogate key to identify these orders in the DW. Can Informatica key generator handle such a large volume to assign keys? If an order comes again due to some updates, it should carry previously generated/assigned surrogate key. Can Informatica skip assigning surrogate keys to repeating orders?

Any other suggestions to generate surrogate keys?

Thanks

[ngalemmo]

Assuming the order ID is a degenerate dimension value, you can just store it in a string. But, if the order ID doesn't have any real value and all you are trying to do is separate one order from another, a surrogate value works fine.

It's been a while since I've used INFA (5-6 years). I recall it used an Integer data type for sequences, which gives you 4 billion values. It should be enough for 2 or so years. If you are not trying to use it to reference back to an order it should be fine. You could check to see if a BIGINT sequence is available... that will be good for a few millennia.

[DWModeler]

Order id is not just degenrate dim - there are other contextual data sets that hang off of orders and order id is referred to in those.

Can INFA manage duplicate / repeating order ids in the data stream and generate surrogate key for new orders only while reusing previously generated key for older / existing orders?

Thanks

[hang]

Order id is not just degenrate dim - there are other contextual data sets that hang off of orders and order id is referred to in those.

Why Order id is not DD if the fact table stores order transaction and other contextual data will provide the dimension context around the order fact table.

[Sponsored content]