Putting Logical Modeling into Practice

In the previous chapter, we observed data teams working with business teams to create a high-level conceptual model representing an organization’s main entities and relationships. While a conceptual model helps to understand the overall structure and requirements of the data without going into excessive detail, the next stage in the modeling process requires us to go further and develop a detailed model to be used as a blueprint for moving to a physical database design.

To complete the logical model, the data team will have to collaborate with domain experts from the business once again to expand the list of entities, attributes, and relationships that will be used in the database, as well as the data types and constraints for each element. Just as the conceptual model held bidirectional benefits in both developing a fresh design and simplifying an existing one, a logical model is not merely a stepping stone in the modeling process.

...

Expanding from conceptual to logical modeling

In the previous chapter, we used Kimball’s four-step methodology to develop a bus matrix and create a conceptual model based on the recorded information. Details that informed the bus matrix were gathered through workshops and discussions between the data team and experts on the business side, who could elucidate the business’s operational model and create a functional artifact—the conceptual diagram.

The following diagram shows the conceptual model as it looked at the end of the exercise:

Figure 8.1 – The final conceptual diagram from Chapter 7

Despite the obvious deficiencies, such as missing attributes and Snowflake-specific object properties, the conceptual diagram did not attempt to add any contextual detail on the functional relationships between the entities, such as subtypes and many-to-many associations.

To uncover the relevant fields that should be added to our model...

Adding attributes

The most important detail in a dimension is its unique identifier. This determines what constitutes a unique instance of each entity in our business. Examples of unique identifiers include things such as serial numbers for parts and employee IDs for a company’s human capital. Domain experts from each business area can confirm these and other necessary details. We will use the CUSTOMER dimension as an example to identify the relevant details and incorporate them into the model.

Suppose we sat down with the head of the sales team to learn about how our organization identifies customers and their relevant attributes. The domain expert explains that besides typical customer attributes such as name and address, our organization is also interested in tracking the account balance and identifying its market segment. The sales team also explains how customers are grouped into regions based on their NATION attribute, maintained in a separate LOCATION dimension. Besides...

Cementing the relationships

With attributes and PK and FK relationships established in the dimensions, we again turn to the business experts to help flesh out the fact tables and determine their grain.

Building from the conceptual model, we ensure that all relationships have a defined granularity and optionality and that the data teams understand their business context. Mistakes made in the fact table definition are the costliest to reconcile and could result in costly readjustment, so extra care should be taken. Work with domain experts to ensure that the fact tables capture the true atomic grain of the information recorded by business operations and that the optionality is correctly understood.

In the following example, the logistics team confirms that our warehouse separates parts by the supplier to facilitate returns and inventory tracking. In short, the warehouse can store many parts from many suppliers. Role names are also verified and documented at this point for non-obvious...

Summary

In this chapter, we continued the modeling journey by taking the conceptual design from the previous chapter and expanding it to logical by adding structural details and technical details that closely resemble the physical model we will create and deploy in later chapters.

Continuing to work with business experts from our organization, we identified the attributes that will be used to capture crucial business master data and transactions. We defined each business entity’s identifiers, attributes and measures, and data types to do this.

Once attributes have been set in place, we reviewed the relationships between entities to understand the nuances of their associations to architect them in such a way as to fit the needs of the business and simplify their maintenance.

We started by identifying the M:M relationships. These relationships require an associative table to capture the interactions between the entities involved but are not considered entities themselves...