Denormalizing your world
Posted at 5/29/2008 04:11:00 AM
How many tables are in your data warehouse? 500? 1000? 5000? More? One data warehouse that I know of has approximately 500,000 tables!
Is it any wonder that business people can not use a data warehouse? Even most data marts are more complex than the business activity they represent. The paradox here is that if you try to list all of the real world entities that constitute a business information environment it is very hard to find more than fifty or sixty and a good case could be made for as few as twenty.
Where then do all of these tables come from and what do they mean? Most of them are the result of optimizing data structures to improve the performance of a computer. Good computer performance is gained at the expense of good business performance—that seems like a poor tradeoff.
Normalizing data structures and metadata rather than normalizing data is the first step in making information accessible to business users. A data structure that never repeated a metadata value, including repeats in the form of contrived names to represent the same thing in different contexts and levels of aggregation, would be the most understandable structure for business information users. This means one table for all customer data regardless of source or usage, providing a single customer view. The same goes for product, service, order, invoice, etc.
In an environment where one table represents one entity the average business user could easily navigate through the tables and BI for the masses would become a more realistic concept.
Is it any wonder that business people can not use a data warehouse? Even most data marts are more complex than the business activity they represent. The paradox here is that if you try to list all of the real world entities that constitute a business information environment it is very hard to find more than fifty or sixty and a good case could be made for as few as twenty.
Where then do all of these tables come from and what do they mean? Most of them are the result of optimizing data structures to improve the performance of a computer. Good computer performance is gained at the expense of good business performance—that seems like a poor tradeoff.
Normalizing data structures and metadata rather than normalizing data is the first step in making information accessible to business users. A data structure that never repeated a metadata value, including repeats in the form of contrived names to represent the same thing in different contexts and levels of aggregation, would be the most understandable structure for business information users. This means one table for all customer data regardless of source or usage, providing a single customer view. The same goes for product, service, order, invoice, etc.
In an environment where one table represents one entity the average business user could easily navigate through the tables and BI for the masses would become a more realistic concept.
