Surrogate Key Generation for Orders/Transactions
3 posters
Page 1 of 1
Surrogate Key Generation for Orders/Transactions
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
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
DWModeler- Posts : 7
Join date : 2010-09-23
Re: Surrogate Key Generation for Orders/Transactions
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.
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.
Re: Surrogate Key Generation for Orders/Transactions
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
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
DWModeler- Posts : 7
Join date : 2010-09-23
Re: Surrogate Key Generation for Orders/Transactions
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.DWModeler wrote: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.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Test Data Generation
» SK generation in SQL Server 2005/2008
» Adding Facts to Transactions
» Transactions with Mixed Grain
» How to create transactions SCD2 from many SCD tables
» SK generation in SQL Server 2005/2008
» Adding Facts to Transactions
» Transactions with Mixed Grain
» How to create transactions SCD2 from many SCD tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum