SECTION IV
Subject Area, Logical, and Physical Data Models
Section IV introduces the data model pyramid, then explores the three different levels of models: subject area, logical, and physical. A subject area model (SAM) represents a business need. It is a very broad view, containing only the basic and critical concepts for a given scope. The logical data model (LDM) represents a detailed business solution, capturing the business requirements without complicating the model with implementation concerns such as software and hardware. The physical data model (PDM) represents a detailed technical solution. It loses some of the precision of the LDM, but this loss usually comes with gains in performance and usability within a given hardware and software set.
In addition to these three levels of detail, there are also two different modeling mindsets: relational and dimensional. Relational modeling is the process of capturing how the business works, while dimensional modeling is the process of capturing what the business is monitoring or measuring. Relational modeling captures how the business works, and captures business rules, such as “A Customer must have at least one Account”, or a “Product must have a Product Short Name”. Dimensional modeling captures what the business uses to measure how well it is performing. For example, examining sales at a day level and then, after getting the answer, looking at sales at a month or year level, or a product or brand level, or a city or country level. Dimensional modeling is all about playing with numbers by summarizing or aggregating data such as Sales Value Amount.
The table on the facing page summarizes these three model levels and two mindsets, leading to six different types of models.
|
| Mindset | |
|
| Relational | Dimensional |
Types of models | SAM | Key concepts and their business rules, such as a “Customer can place many Orders.” | Key concepts focused around one or more measures, such as “I want to see Gross Sales Value Amount by Customer.” |
LDM | All data elements required for a given application or business process, neatly organized into entities according to strict business rules and independent of technology, such as “Customer Last Name and Customer Shoe Size depend completely on Customer Identifier.” | All data elements required for a given reporting application, focused on measures and independent of technology, such as “I want to see Gross Sales Value Amount by Customer and view the Customer’s first and last name.” | |
PDM | The LDM modified for a specific technology, such as database or access software. For example, “To improve retrieval speed, we need a non-unique index on Customer Last Name.” | The LDM modified for a specific technology, such as database or access software. For example, “Because there is a need to view Gross Sales Value Amount at a Day level, and then by Month and Year, we should consider combining all calendar data elements into a single table.” | |
The purpose and characteristics of each of these six models are explained in detail in this section, along with specific instructions for creating them in PowerDesigner.
Chapter 15 goes into detail on the subject area model, discussing the variations along with how to build and manage this type of model in PowerDesigner.
Chapter 16 focuses on the relational and dimensional logical data model.
Chapter 17 focuses on the physical data model, going through the different techniques for building an effective design, such as denormalization and partitioning. Slowly Changing Dimensions (SCDs) are also discussed in this chapter.
Chapter 18 focuses on managing model dependencies, and describes the PowerDesigner model generation process.
The Data Model Pyramid
Many practitioners of data modeling use a pyramid to illustrate the different types of models and the relationships between them. This particular shape is well suited to the task for two reasons:
· It supports the principle of ‘layers of models’
· As we move down the layers, everything grows – the number of models, their complexity, and the number of objects included
The pyramid consists of four layers, providing increasing detail as you move towards the base. PowerDesigner connects models in adjacent layers using ‘Link and Sync’ connections; they can also be connected manually via mappings made using the Mapping Editor.
Figure IV.1 The data model pyramid
For Subject Area Models, see Chapter 15. For Logical Data Models, see Chapter 16. For Physical Data Models, see Chapter 17.