Data Warehouse Design

| July 10, 2017

Over this series of four posts, I explore the keys to a successful data warehouse, starting with data warehouse design. The topics for future posts are: build, operation, and maintenance.

Designing a Data Warehouse

In the design of a data warehouse and, indeed, over the entire warehouse journey, the most important principle to keep in mind is that what you are delivering is a cross-functional, long-life foundation for data provision and decision support. Let’s parse that out:

  1. Cross-functional may, in some cases, extend to cover the entire enterprise; in other cases, it may address only the needs of the different departments in a business unit. In either case, the data warehouse must be able to satisfy the unique needs of multiple decision support projects from diverse groups of business users.
  2. Long-life means that these projects will arise and mutate over many years; they cannot be known in advance when you first design your initial data warehouse.
  3. As a foundation for data provision, the data warehouse must provide consistent, reconciled, legally-binding data to its various business clients, to ensure that decisions made are reliable, auditable, and non-contradictory.

These characteristics have long bedeviled the design (and subsequent phases) of the process of delivering a data warehouse. If you have delivered a data mart, for example, you will know that you must meet the specific and (reasonably) well-known data needs of a single department, usually as quickly as possible. If you delivered input to a data lake, you will understand that flexibility of use—often called schema-on-read—is of paramount importance. Data marts and lakes are projects; a data warehouse is a process consisting of multiple projects, which must work together toward the goals outlined above.

Enterprise Data Model

At the heart of this process is a vision of what the data in the warehouse should ultimately look like. This vision is best described by an enterprise data model (EDM), consisting of the high-level entities that a business must track: customers, products, orders, and so on. (In Bill Inmon’s terminology, these are called subject areas.) In the past, EDMs were often built from scratch, a process that provided data modelers with jobs for life, but frustrated the business users who were drawn into drawn-out definitional debates rather than seeing the results they needed.

Today, many EDMs are customized from standard industry models—a much faster and easier process. After all, how different are the characteristics of customers, for example, across various banks? In many more cases, an EDM already exists within the organization as a result of previous data warehouse or other enterprise-wide undertakings, such as master data management, for example.

Data Warehouse Challenges

The major design challenge for today’s data warehouses is defining and refining the logical (and ultimately physical) structure of the relational tables of the data warehouse. Ultimately, a good design must take into account the limitations of the source systems, the challenges in joining data from multiple sources, and the possibility of changes in both business needs and source system structures over time. These are topics for later posts focusing on build and maintenance concerns. For now, we focus on how to achieve the “ideal structure” to facilitate loading, updating and using data warehouse tables.

3NF

The traditional design approach recommends mapping high-level entities to “loosely normalized” tables—based initially on third normal form (3NF), but relaxed sufficiently to ease population and querying performance issues that arise from strict 3NF implementation. The goal of this approach is to create a cross-enterprise, functionally neutral set of data that can be used for a wide variety of query, analysis, and reporting needs.

3NF was originally designed to ensure insert/update/delete consistency of operational databases, as was thus suboptimal for data warehousing. Ralph Kimball proposed an alternative approach in the 1990s known as dimensional or star-schema models. While widely used—mainly because it promises faster delivery of decision support projects—its design is also suboptimal for a data warehouse, being highly optimized for slice-and-dice analysis, and driven by the specific business needs of a particular department. In essence, it is more suitable for a data mart than a data warehouse.

Data Vault

In the early 2000s, Dan Linstedt defined the Data Vault Model, a hybrid of the normalized and star schema forms above, which better balances the generality of loosely normalized and the delivery speed of dimensional models. The Data Vault is a detail-oriented, history-tracking, specially linked set of normalized tables designed to support multiple functional business areas. The model consists of three specialized types of entities/tables: hubs based on rarely changed business keys, links that describe associations or transactions between business keys, and satellites that hold all temporal and descriptive attributes of business keys and their associations. Version 2.0, introduced in 2013, consisting of a data model, methodology, and systems architecture, provides a design basis for data warehouses that emphasizes core data quality, consistency, and agility to support enterprise-wide data provision needs.

With design sorted, it’s time to move to the build phase. That is the topic of the next post.

You can find the other blog posts in this series here:


Dr. Barry Devlin is among the foremost authorities on business insight and one of the founders of data warehousing, having published the first architectural paper on the topic in 1988. Barry is founder and principal of 9sight Consulting. A regular blogger, writer and commentator on information and its use, Barry is based in Cape Town, South Africa and operates worldwide. 

Why Data Automation is the Keystone of Digital Transformation

In today's fast-paced digital landscape, businesses must adapt quickly to stay competitive. Data automation has emerged as a critical component of digital transformation, enabling organizations to save time, streamline operations, gain valuable insights, enhance...

What is a Cloud Data Warehouse?

A cloud data warehouse is an advanced database service managed and hosted over the internet by a third-party cloud provider. Unlike traditional on-premises databases that require physical infrastructure and hands-on maintenance, cloud data warehouses offer a more...

A Webinar Recap: Exploring Data Automation Levels with Kent Graziano

Our most recent webinar, "The Future of Data Warehousing: Understanding Automation Levels," hosted by Patrick O'Halloran, Solutions Architect, and esteemed guest speaker Kent Graziano dove into the transformative world of data warehouse automation. They discussed its...

Overcoming Challenges with AI Hallucinations

Conversing with your digital assistant on your smartphone, using facial recognition for security, traveling in autonomous vehicles, or browsing recommended products based on your search history - there is no denying AI is embedded in many aspects of our lives. AI has...

Navigating Data Governance with WhereScape 3D

Properly managing and organizing data allows businesses to not only understand crucial patterns and trends, but also to leverage that data in strategic ways that grow revenue over time. Data drives decision-making and paves the way for innovation when used properly....

Deep Dive into WhereScape RED: Features and Benefits

Transforming a business’s various databases and files into actionable insights and reports is crucial, but incredibly time-consuming with traditional tools. Fortunately, with data warehouse automation tools like WhereScape RED, organizations can take advantage of a...

Related Content

What is a Cloud Data Warehouse?

What is a Cloud Data Warehouse?

A cloud data warehouse is an advanced database service managed and hosted over the internet by a third-party cloud provider. Unlike traditional on-premises databases that require physical infrastructure and hands-on maintenance, cloud data warehouses offer a more...

Why Data Automation is the Keystone of Digital Transformation

Why Data Automation is the Keystone of Digital Transformation

In today's fast-paced digital landscape, businesses must adapt quickly to stay competitive. Data automation has emerged as a critical component of digital transformation, enabling organizations to save time, streamline operations, gain valuable insights, enhance...

What is a Cloud Data Warehouse?

What is a Cloud Data Warehouse?

A cloud data warehouse is an advanced database service managed and hosted over the internet by a third-party cloud provider. Unlike traditional on-premises databases that require physical infrastructure and hands-on maintenance, cloud data warehouses offer a more...