Join us for an insightful webinar where we...
Data Warehouse Design
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:
- 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.
- 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.
- 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:
- Week 2: Building a Data Warehouse
- Week 3: Operating a Data Warehouse
- Week 4: Maintaining a Data Warehouse
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.
Experience the Power of WhereScape 3D 9.0.3: New Features and Improvements
We’re thrilled to introduce our latest iteration of WhereScape 3D! Version 9.0.3 brings a host of new features and enhancements designed to make your data warehousing journey smoother, faster, and more efficient. Let’s dive into the details of what you can expect from...
Ahead of the Curve: Future Trends in Data Automation and WhereScape’s Pioneering Solutions
The Evolving Landscape of Data Automation As new technologies emerge and existing tools constantly change and improve, the world of data automation transforms rapidly. Even the most well-versed data teams find themselves disoriented and overwhelmed in the face of...
Investing in Data Automation: A Strategic Approach to Business Growth
Unlocking Growth: The Strategic Advantage of Data Automation Organizations reaping the benefits of data automation stay ahead of industry trends and improve the efficiency of their operations and decision-making. Data automation tools offer a strategic advantage for...
Data + AI Summit 2024: Key Takeaways and Innovations
The Data + AI Summit 2024, hosted by Databricks at the bustling Moscone Center in San Francisco, has concluded with remarkable revelations and forward-looking innovations. Drawing over 16,000 attendees in person and virtually connecting over 60,000 participants from...
WhereScape RED 10.1 is Here: Enhanced Scheduling and Customization
We’re proud to announce the highly anticipated WhereScape RED 10.1 is now available, and it’s packed with exciting new features and enhancements designed to make your data warehousing experience more efficient and enjoyable. Let's take a closer look at what’s new and...
Supercharging Data Integration: The WhereScape and Databricks Advantage
The demand for robust data management systems has never been higher, and Databricks has quickly become a favored choice for cloud-based solutions. Its powerful capabilities make it a top contender for managing large-scale data, but when combined with WhereScape's...
Empowering Customer Success: WhereScape’s Comprehensive Support and Training Resources
Enhancing Operational Success with WhereScape’s Support Systems At WhereScape, we understand that a data warehouse is only useful to the extent that it is understood. In order to drive your organization closer to your key goals and objectives, you need full mastery of...
Revolutionizing Day-to-Day Operations: The Power of Automated Data Integration
The Transformational Role of Automation in Data Management Across industries and business stages, organizations of all types manage data in their daily operations. Whether that data entails patient appointments and reminders in a healthcare clinic, student performance...
Gartner® Insights: Microsoft Fabric as a Unified Data & Analytics Platform
Are you ready to revolutionize your data management strategy with a platform that promises to simplify and enhance your operations? According to a Gartner poll, 43% of respondents believe that the data and analytics ecosystem will significantly influence their choice...
WhereScape and YellowFin Attending World of Data in Munich
We are excited to announce that WhereScape and YellowFin will be attending the World of Data conference in Munich on June 6, 2024. This event will bring together data professionals, industry leaders, and technology enthusiasts from around the globe to explore the...
Related Content
Experience the Power of WhereScape 3D 9.0.3: New Features and Improvements
We’re thrilled to introduce our latest iteration of WhereScape 3D! Version 9.0.3 brings a host of new features and enhancements designed to make your data warehousing journey smoother, faster, and more efficient. Let’s dive into the details of what you can expect from...
Ahead of the Curve: Future Trends in Data Automation and WhereScape’s Pioneering Solutions
The Evolving Landscape of Data Automation As new technologies emerge and existing tools constantly change and improve, the world of data automation transforms rapidly. Even the most well-versed data teams find themselves disoriented and overwhelmed in the face of...
Investing in Data Automation: A Strategic Approach to Business Growth
Unlocking Growth: The Strategic Advantage of Data Automation Organizations reaping the benefits of data automation stay ahead of industry trends and improve the efficiency of their operations and decision-making. Data automation tools offer a strategic advantage for...
Data + AI Summit 2024: Key Takeaways and Innovations
The Data + AI Summit 2024, hosted by Databricks at the bustling Moscone Center in San Francisco, has concluded with remarkable revelations and forward-looking innovations. Drawing over 16,000 attendees in person and virtually connecting over 60,000 participants from...