. Dimensional Data Modeling Fact Qualifier Matrix - EWSOLUTIONS

Teaching Data Management Since 1998

Data Management University

Request a free consultation with a DMU Expert

Search DMU Library

Dimensional Data Modeling Fact Qualifier Matrix

01 October, 2008 | Pete Stiglich | Data Warehousing

Fact Qualifier Matrix capabilities can be expanded to improve the value of conformed dimensions in business intelligence and analytics systems


The venerable Fact Qualifier Matrix (FQM) has been a tool long used for demonstrating for dimensional models how fact and dimension tables intersect to ensure that dimensional models are conformed. A key principle of dimensional modeling is that dimensions must be conformed to ensure drill across capabilities from one fact area to another. Without conformed dimensions, the result is a collection of inefficient independent data marts that wreak havoc with reporting and analysis.

If there are two distinct customer dimensions (with differing definition and granularity) analysts may assume the definition for one when working with the other. The Fact Qualifier Matrix consists of rows listing fact tables and columns listing dimensions such as below.


Figure 1: Classic Fact Qualifier Matrix

While this is a useful and valuable tool for demonstrating that dimensions conform across facts (if applicable – not every dimension can be used in more than one fact area), it does not demonstrate how business requirements can be satisfied by the dimensional models.

Basic Uses of the Fact Qualifier Matrix (FQM)

Some experts recommend using the FQM as a tool for requirements gathering and indeed as the first step in developing the dimensional models. However, the FQM is a tool only to validate conformity after the first draft of the models have been developed (and revised as the model is revised), unless you have a very simple business model.

Developing the dimensional model with business and physical representations should be the last step in a phased modeling approach that starts with the Conceptual Data Model (CDM). The CDM models the business from a data perspective and is application and technology neutral. Serious issues can arise when a CDM is not developed before the dimensional model. For example, a relationship that appears to be one-to-many, may in fact be many-to-many under certain business conditions.

Resolving many-to-many problems in a dimensional model is a special challenge, usually requiring a bridge table or an allocation method (see The Data Warehousing Lifecycle Toolkit, for more information on these topics) – both of which increase complexity.

A normalized, relational model – the Logical Data Model (LDM), is usually developed after the CDM and before the dimensional model. The LDM “conforms to the rules of information science” and fully explores the subjects being modeled. The LDM often serves as the foundation for a normalized data warehouse or data staging area, and is the basis from which dimensional models are derived.

The FQM is not detailed enough to demonstrate how the Dimensional model will answer business questions. To this purpose, EWSolutions has developed an extension of the FQM, the Expanded Fact Qualifier Matrix (E-FQM) which ties business questions to the facts and dimensions, and demonstrates how these questions can be satisfied using the dimensional models.


Figure 2: Expanded Fact Qualifier Matrix (E-FQM)


Now, instead of the rows consisting of fact tables, rows describe business questions and which facts/dimensions are needed to satisfy the question. Facts and dimensions are laid out in columns with a different coloring scheme. The last column “Description of Resolution” describes how the user would resolve the question. It is important to be verbose here and to add any particular notes that will help the user understand the model better relative to the question.

For example, the Customer Account fact table utilizes an allocation method which divides the measures pertaining to a loan account across all the customers on a loan. In addition to being a validation tool, the E-FQM becomes a training / reference tool.

The emphasis of the E-FQM is demonstrating how the model satisfies business questions, and should be reviewed in conjunction with the model during design reviews. It is important to note that the traditional Fact Qualifier Matrix is still a required deliverable for demonstrating dimension conformity.


The Fact Qualifier Matrix is an important component of dimensional modeling in data warehouse and business intelligence environment development. However, traditional FQM cannot meet all the needs of complex business users, so data architects should consider using an expanded Fact Qualifier Matrix to support robust dimensional modeling.



View Comments

Leave a Reply

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

Request a free consultation with a DMU Expert

The First Steps in Building a World Class Data Management Program

Date : 24 May 2018, Time : 1:00 PM, USA/Chicago
Presenter:David Marco
Registration Opens December 11, 2017.

During this webinar international speaker and bestselling author, David Marco will walk us through the key first steps needed in building a world-class data management program.

View Our Data Management Moments

Watch our 2 - 3 minute Data Management Moment teaching videos to learn the very finest data management best practices.

WordPress Image Lightbox