Question

I've been reading through "Super Charge your Data Warehouse," and have learned a great deal. Still, I find myself struggling with a key element of this. I understand that Hubs contain of a number sequence, a business key, load date, and a record source. As I've come to understand it, hubs act are representative of real world assets. For example, an itinerary of products may be logged as business keys that are itemized by IDs assigned to them by a CRM system. Understandably, these numbers serve a purpose in satellite tables, condensing historical data into one place. This, however, fails to explain just why hubs are given these IDs. As Hubs lend themselves to a substantial amount of variability, changing the satellite table at any given time. Herein lies my confusion.

Am I to understand that these numbers are there in order to itemize records from stratified groups in order to give a juxtaposed view of different records?

If so, does this not complicate the view if it is possible for the same ID to be used for multiple items reflecting real-world assets?

And if this is the case, doesn't normalization of a proper record fly out the window if the same ID is also a primary key?

Answer 1

A satellite getting its key from a Hub sequence?

Comments

I am aware of how a Hub's numbers is recognized as the foreign key inside links and satellites. But this doesn't explain why an entirely separate key is needed if the primary key already exists. If production assigns an ID and I can rewrite it and assign it any value I choose, then production becomes irrelevant. Or, am I understanding correctly that using the production key as the ID works just as well?

Answer 2

Hubs should rely on surrogate keys in the event of conflicts a database may have with natural keys. From Wikipedia: "In some circumstances the natural key that uniquely identifies a tuple in a relation may be cumbersome to use for software development. For example, it may involve multiple columns or large text fields. In such cases, a surrogate key can be used instead as the primary key."

From what you mentioned, using the ID assigned by the CRM might be best for avoiding confusion. If can confidently identify values as unique, then the there should be not reason to suspect conflict from varying sources.

Answer 3

Using a surrogate from the source as the hub business key may not be the best way to go. Business keys should be taken naturally; a surrogate should not be necessary in most cases. It may seem like the simple solution, using a surrogate, but I prefer to avoid this method when possible. I don't suggest this without reason. If a surrogate is used, the numbers could be replicated during upgrades. If the original numbers are abandoned, the workload increases if new keys must be generated for use in a new system.