Surrogate Key Generation for Orders/Transactions

View previous topic View next topic Go down

Surrogate Key Generation for Orders/Transactions

Post  DWModeler on Thu Jan 12, 2012 4:58 pm

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

DWModeler

Posts : 7
Join date : 2010-09-23

View user profile

Back to top Go down

Re: Surrogate Key Generation for Orders/Transactions

Post  ngalemmo on Thu Jan 12, 2012 10:56 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Surrogate Key Generation for Orders/Transactions

Post  DWModeler on Fri Jan 13, 2012 6:26 pm

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

DWModeler

Posts : 7
Join date : 2010-09-23

View user profile

Back to top Go down

Re: Surrogate Key Generation for Orders/Transactions

Post  hang on Fri Jan 13, 2012 6:49 pm

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.
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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Surrogate Key Generation for Orders/Transactions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum