Types of Data Models

A conceptual model is a representation of a system, made of the composition of concepts which are used to help people know, understand, or simulate a subject the model represents. In dimensional modeling this is the most abstract level of modeling. Here we are only worried about the entities (dimensions) and their relationship to each other (facts). No attributes are required in the conceptual model as we are trying to work with the customer who is not an expert in databases or data warehousing but they do understand how the entities are related in their business. The conceptual model will help us to ensure we have captured the full list of dimensions and facts. If we are missing an entity or a relationship the customer will be able to provide us with that information.

Conceptual Model

Now that we have completed a successful conceptual data model, we need to create a logical model. A logical model is a fully attributed conceptual model. The attributes are fully spelled out with no abbreviations. High-level data types (string, number, date, etc.) are provided at this point but no details of the physical implementation are provided in the logical model. The logical model will give the customer a more detailed idea of how the dimensions and facts will work. This model is geared to the business user who understands, in detail, how the business works and the reporting that is required from the warehouse.

Logical Model

Finally, we need to create a physical data model. This is a logical model with the appropriate abbreviations, where necessary, and the appropriate physical data structure attributes defined (datatypes, primary & foreign keys, partitioning, storage location, etc.) for the fact and dimension tables. This model is used primarily by the database administrators and the application developers and should only be shared with the customer as needed or requested.

Physical Model

As you can see, each level of data modeling is targeted for different users. Each model provides the appropriate the level of detail for the user to understand the data structures we are trying to build for them based on the requirements provided.