The DAs (data architects) and DBAs (database administrators) that are employed on data warehousing projects have spent the last 20 years learning and implementing the principles of normalized data design laid down by the inventor of the relational database, Dr. Codd. These normalized database design principles mandate that no data point should ever be duplicated and that a database table should never contain a field that is not a unique attribute of the key of that table. These principles yield a database design that is optimized for transactional systems and for ease of maintenance and update. The principles of successful data warehouse end-user access database design, however, are the antithesis of Dr. Codd's normalized database design principles. When designing a data warehouse or data mart that users are going to "touch," or use for direct access for query and analysis purposes, there is widespread replication of data and rampant denormalization. When designing a database for end-user utilization, there is only one litmus test: ease of use.

If the addition of a replicated data point or a derived metric will make the system easier to use, then it is added. For example, when designing an aggregation data set, all members of a hierarchy are included, along with all descriptive elements for all key values. For instance, an aggregation for sales by day would also include columns for week, month, quarter and year. This allows users to easily roll up to a higher level of the hierarchy. In this way, one aggregation table can serve the needs of users who need daily, monthly, quarterly or annual totals. By including additional hierarchies, such as sales geography, the table becomes even more powerful, allowing roll ups to any level of multiple hierarchies. (See Figure 1.) This is a key strategy to minimize the number of aggregation tables required to meet the needs of the users.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access