Are you struggling to keep up with the...
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.
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...
WhereScape’s Supported Platforms: Accelerating Data Solutions Across the Board
The Future of Data Warehouse Automation with WhereScape Data warehouse automation represents a transformative shift in how businesses manage and utilize their data. WhereScape is at the forefront of this movement, offering tools that automate code generation,...
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...
Brief Insights from Gartner® Latest Report on Data Fabric and Data Mesh
In the rapidly evolving world of data management, distinguishing between the myriad of strategies and technologies can be daunting. The latest Gartner® report, "How Are Organizations Overcoming Issues to Start Their Data Fabric or Mesh?" provides critical insights...
ETL vs ELT: What are the Differences?
In data management, the debate between ETL and ELT strategies is at the forefront for organizations aiming to refine their approach to handling vast amounts of data. Each method, ETL vs ELT, offers a unique pathway for transferring raw data into a warehouse, where it...
Embracing the Future of Data Management Recap: Insights from Mike Ferguson
In our recent webinar, "Embrace the Future of Data Management with Automated Cloud Data Warehousing," we had the privilege of diving into the transformative world of cloud data warehousing and highlighting the pivotal role of automation. Guided by our own Brad Kloth,...
How to Hire and Retain Data Warehouse Developers
The projected data warehouse developer job growth rate is 21% from 2018-2028, with about 284,100 new jobs for data warehouse developers projected over the next decade, according to Zippia. This surge in demand for data warehouse talent is being felt across businesses...
8 Reasons to Make the Switch to ELT Automation
Extraction, loading, and transformation (ELT) processes have been in existence for almost 30 years. It has been a programming skill set mandatory for those responsible for the creation of analytical environments and their maintenance because ELT automation works....
Related Content
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...
WhereScape’s Supported Platforms: Accelerating Data Solutions Across the Board
The Future of Data Warehouse Automation with WhereScape Data warehouse automation represents a transformative shift in how businesses manage and utilize their data. WhereScape is at the forefront of this movement, offering tools that automate code generation,...
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....