Fact Qualifier Matrix capabilities can be expanded to improve the value of conformed dimensions in business intelligence and analytics systems
Introduction
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) in a data warehouse, 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. It is considered an important part of a data warehouse or data mart metadata.
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.
Conclusion
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.
