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 in order to ensure drill across from one fact area to another.  Without conformed dimensions the result is independent data marts which wreak havoc with reporting and analysis (and are also inefficient).  If you have 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. 

Some recommend using the FQM as a tool for requirements gathering and indeed for being the first step in developing the dimensional models.  In my opinion, 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  (I’ve yet to see one of these).   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, [1] 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” [2] 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, I have 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 both 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 so 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.  

In my next column, I will explore other types of matrices which can be developed (via spreadsheet or meta data repository) to validate and understand Dimensional models and how these relate to CDM’s, LDM’s, business rules, BI meta data, and other artifacts to ensure that business requirements are satisfied.  

About The Author
Pete Stiglich is a Senior Consultant with EWSolutions with nearly 25 years of IT experience in the fields of Data Modeling, Data Warehousing, Business Intelligence, Meta Data Management, Data Integration,  Customer Relationship Management (CRM),  Customer Data Integration (CDI), Database Design and Administration, Data Quality, and Transaction Processing.  Pete has architected Enterprise Information Management solutions for diverse industries such as Insurance, Credit Card, Medical, Retail, Banking, Manufacturing, Telecom, and Government.

Pete has developed and taught courses on Dimensional Data Modeling, Conceptual Data Modeling, ER/Studio, and SQL. Pete has presented for DAMA at the international and local level, as well as at the 2007 IADQ Conference. Pete’s articles on Data Architecture have been published in Real World Decision Support, DMForum, InfoAdvisors, and the Information and Data Quality Newsletter.  Pete is a listed expert for SearchDataManagement on the topics of data modeling and data warehousing. Pete can be contacted at pstiglich@ewsolutions.com

[1] Ralph Kimball, Margy Ross, Warren Thornthwaite.  Published by John Wiley & Sons. 

[2] Applied Information Science website