FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Do you recommend keeping the same surrogate keys for a row in the ODS and in the data warehouse or can you assign different surrogate keys by data store?

Q:

Question: Do you recommend keeping the same surrogate keys for a row in the ODS and in the data warehouse or can you assign different surrogate keys by data store?

 

A:

Chuck Kelley's Answer:

Without having the answers to a lot of questions, I would probably use different surrogate keys. The reason is that depending on your ODS purpose, you may combine data items or break items apart when you build your data warehouse. If you are positive that you always (this is a key word) have a one-to-one relationship between a row in the ODS and a rows in the data warehouse, then you could use the same. I think that it may be too much of a chance of error later on.

Les Barbusinski's Answer: It depends. The structure and content of the ODS and data warehouse (a.k.a. historical data store) are fundamentally different. The former is usually denormalized to provide optimum performance for operational reporting processes, while the latter is usually normalized. This means the level of granularity may be different, and some entities in the ODS may not have an analog in the data warehouse because of denormalization. In such situations, it doesn't make sense to maintain identical surrogate keys. For those ODS entities that do correspond to something in the data warehouse, it's okay to maintain identical surrogate keys ... but it may not be necessary if there's already a "natural key" that can be used for matching. Hope this helps.


Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.

Les Barbusinski is vice president of technology and co-founder of Digital Symmetry, LLC, a consulting firm that specializes in the design and development of data warehousing and business intelligence solutions. He has more than 20 years of experience in data warehouse and operational systems development and provides hands-on expertise in data warehouse design, development and project management. Les can be reached at dwexpert@dsym.com.

For more information on related topics, visit the following channels:



Industry Vendors