Types of Data Models

Data Warehouse Design Techniques – Constraints and Indexes
July 28, 2017
Joining Fact Tables
February 20, 2019
Show all

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.[i] 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.


[i] Wikipedia – https://en.wikipedia.org/wiki/Conceptual_model

Jim McHugh
Jim McHugh
An accomplished Sr. IT leader with over 27 years of professional experience applying technology solutions to business challenges. Mr. McHugh has expertise in data modeling, data governance, business intelligence, predictive analytics and data science. His responsibilities include establishing and executing a strategy that ensures the application of data management & analytics to enable an organization to strategically leverage and fully realize the value of their data.

4 Comments

  1. sandeep says:

    Can a FACT table contains Audit Columns like Begin Dt, End Dt and Active Flag? If so in what situation we typically use these audit fields on Fact tables.

    • Jim McHugh says:

      Yes, a FACT table can contain audit columns. In fact, I always add in audit columns to FACT tables. This allows the customer to update (replace) a record and still accurately report on the fact. Take a timesheet system as an example. You may allow employees to adjust their timesheet for up to 2 months before locking their timesheet. If an adjustment is received after the initial timesheet was received and processed in the warehouse you will need to be able to capture (“Type-II”) the change so that you can accurately report on the time as it was before and after the modification by the employee.

  2. Sandeep says:

    If invoice is degenerated dimension
    And use invoice number in fact.
    But how to handle if invoice number has different statuses and active or inactive indicator

    • Jim McHugh says:

      In the situation where you have a degenerate dimension for an invoice and you want to track the status of the invoice, I would suggest that you keep a separate transaction fact table to track the overall status of the invoice. For example. When you initially receive an order you may want to state that the invoice is “OPEN” or “PENDING”. Later after some items are shipped you may want to note that the status is “PARTIALLY SHIPPED”. After all items are shipped you may want to state that the invoice is “PENDING PAYMENT” Finally, once payment is received you can “CLOSE” the invoice. This summary fact would derive statuses from the detailed fact table allowing one to see the high-level view of the invoice without seeing the details. I hope this helps you understand the flexibility dimensional modeling provides.

Leave a Reply to Jim McHugh Cancel reply

Your email address will not be published. Required fields are marked *

Contact