Putting Conceptual Modeling into Practice

Conceptual database modeling is a high-level approach to designing a database that focuses on capturing business entities and their relationships. This approach allows designers to develop a deeper understanding of the data, making it easier to identify potential issues or inconsistencies in the design. It can also make the database more flexible and adaptable to future changes and make it easier to understand and use for people who are unfamiliar with it. As a result, conceptual database modeling can help make a database more effective and efficient at supporting the needs of an organization.

In this chapter, we will generate a conceptual model from scratch using Kimball’s dimensional modeling (DM) technique. This approach brings the data team together with business experts to ensure that database designs reflect the reality of business processes from inception, thereby saving costly rework down the line.

However, conceptual models...

Embarking on conceptual design

Out of all the modeling types, conceptual captures and displays the least amount of detail. This makes conceptual modeling ideal for getting acquainted with a database landscape at a high level and for designing one from scratch. Designing data models is an art honed over many iterations, but where do you begin if you are new to modeling?

Dimensional modeling

In the early 2000s, Ralph Kimball and Margy Ross published the groundbreaking book The Data Warehouse Toolkit, which has persisted for decades as the authoritative blueprint for constructing database designs. Many of the terms, concepts, and techniques described in later chapters of this book trace their origins to The Data Warehouse Toolkit—whose latest edition fittingly carries the subtitle, The definitive guide to dimensional modeling.

To be clear, Kimball’s approach is not the only way to go about creating a conceptual model. The agile-based Business Event Analysis and...

Modeling in reverse

Real-world enterprise operational systems and data warehouses can reach heights of great complexity, especially if they have evolved over many years and absorbed untold numbers of dubious design decisions. Even the standing members of the data team may not be able to explain every detail, let alone a new hire or a business analyst. Fortunately, conceptual modeling can help simplify and visualize existing database landscapes as quickly as it does when starting new ones.

Database modeling tools allow users to take schema definitions as DDL and ingest them to generate a corresponding diagram—a process known as reverse engineering. Since all the table structures and constraints are already defined in the database, generating an ER diagram and visualizing the database landscape can be done in seconds. Because reverse engineering uses Snowflake object DDL, it produces physical models. As physical models contain the highest level of detail of all modeling types...

Summary

In this chapter, we have seen how a conceptual model and its accompanying diagram make it easy to visualize an organizational business model and validate it with domain experts against the intended business operations that it is meant to support.

To get started in conceptual modeling, we used Kimball’s DM methodology, which has been used for decades to guide the design of database and warehouse architectures. Kimball uses a four-step method to initiate discussions between the data team and business teams, and domain experts to identify the business processes that an organization engages in.

Once business processes have been identified, we determine their grain or lowest level of detail pertinent to our business. Describing the grain will also help us discover the core dimensions of our business. Plotting dimensions and business processes on a chart, known as the bus matrix, produces an elegant cross-section of our business and lets us transfer its details easily...

Further reading

Kimball and company popularized and significantly contributed to DM and data warehousing. Their books are still relevant today and offer great overviews of concepts and fundamental terminology. For an agile alternative to Kimball, you should look at the BEAM method, described in Agile Data Warehouse Design:

• Kimball, Ralph, and Margy Ross. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. John Wiley and Sons, 2013.
• Kimball, Ralph, et al. The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence Remastered Collection. John Wiley and Sons, 2015.
• Corr, Lawrence, and Jim Stagnitto. Agile Data Warehouse Design: Collaborative Dimensional Modeling, From Whiteboard to Star Schema. DecisionOne Consulting, 2011.