March 24, 2012 at 8:49 am
I had someone hand off a schema for a "Dimensional Model".
I looked at it and I did not see anything that closely resembled a fact table.
Almost all of the tables are using natural keys as the primary key as opposed to a Surrogate Key.
The article listed below list some of the reasons why you should not use natural keys.
I just need a clear and concise quick argument as to what they have done is wrong.
Your input would be greatly appreciated.
http://msdn.microsoft.com/en-us/library/aa905979(v=sql.80).aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 25, 2012 at 1:01 pm
Welsh Corgi (3/24/2012)
I just need a clear and concise quick argument as to what they have done is wrong.
Surrogate Keys add a level of abstraction that isolates and protects the Data Warehouse for whatever may happen to the Natural a.k.a. Business Keys in the source OLTP systems.
Things that WILL happen to the Natural Keys on the source OLTP systems are:
- Somebody decides to re-code Natural Keys for an entity like "customers"
- Different source OLTP systems use or start to use different Natural Keys to identify the very same item.
- Regulations or market or industry standards force to change some Natural Keys over time.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 25, 2012 at 1:07 pm
PaulB-TheOneAndOnly (3/25/2012)
Welsh Corgi (3/24/2012)
I just need a clear and concise quick argument as to what they have done is wrong.Surrogate Keys add a level of abstraction that isolates and protects the Data Warehouse for whatever may happen to the Natural a.k.a. Business Keys in the source OLTP systems.
Things that WILL happen to the Natural Keys on the source OLTP systems are:
- Somebody decides to re-code Natural Keys for an entity like "customers"
- Different source OLTP systems use or start to use different Natural Keys to identify the very same item.
- Regulations or market or industry standards force to change some Natural Keys over time.
Well said.:-D
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 25, 2012 at 1:24 pm
PaulB-TheOneAndOnly (3/25/2012)
Welsh Corgi (3/24/2012)
I just need a clear and concise quick argument as to what they have done is wrong.Surrogate Keys add a level of abstraction that isolates and protects the Data Warehouse for whatever may happen to the Natural a.k.a. Business Keys in the source OLTP systems.
Things that WILL happen to the Natural Keys on the source OLTP systems are:
- Somebody decides to re-code Natural Keys for an entity like "customers"
- Different source OLTP systems use or start to use different Natural Keys to identify the very same item.
- Regulations or market or industry standards force to change some Natural Keys over time.
Over and above that, you will find that query performance (for data extraction) will benefit from the use of surrogate keys...specifically reffering to the case where you have multiple joins and composite business keys.
The whole premise of Data Warehousing is to do some extra work upfront that will make extraction easier and faster. Surrogate keys are a key element of that process.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply