Hi There,
We have to create key during ETL.
This can be done by surrogate key generator.
However,
Fact table will be loaded with more than 100 millions of rows everyday.
Please advice (TELCO project) what might be the better solution and why:
1. Will the simple key like incremtal key is fine enough for such a large data load.
or
2. Should I go for some other Key generation mechanism ie Timestamp + (BillID + ItemID)
What is the better solution and why?
(Considering that all the load is in one batch.)
I look forward for your answers.
Thanks,
Munish
Key generator for large volume
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I would stick with a simple meaningless counter where the seed value is the current maximum value in the target table. I wouldn't get fancy concatenating intelligent fields to derive a dumb surrogate, it would just take longer. You can use the surrogate key stage or you can also generate a key in a transformer stage as shown in the FAQ forum.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
By the time you are ready for very large keys to be generated you will have version 8 or later, in which the Surrogate Key Generator stage is equipped to generate uint64 values.
That's 18446744073709551616 distinct key values. Enough for you?
That's 18446744073709551616 distinct key values. Enough for you?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: