The Conformance Matrix

Conformed dimensions are the linchpins of dimensional models. This post summarizes their use, and describes how to document them in matrix form.

Conformed dimensions: a refresher

Metrics describing different processes can be compared if they are stored in stars which share common dimensions. As I have discussed previously, these compound metrics often turn out to be among the most valuable from a business perspective.

Image by Patrick Hosley
Licensed under CC 2.0

The common dimensions do not have to be physically shared tables.  Each star may reside in a separate database.

As long as the common dimensions, such as "customer" or "product" have the same structure and content, they are said to conform.

Using conformed dimensions, we are able to compare measurements stored in different star schemas through a process called drilling across.

Planning conformance

Conformed dimensions are therefore the linchpins of the dimensional model. They ensure that each star works on its own, and also works with other stars.

If conformed dimensions are planned in advance, you can implement one star at a time without worrying about incompatibility issues.

This is the core idea behind Ralph Kimball's bus architecture. Conformed dimensions are designed as part of an up-front architecture project. Then, they serve as a semantic "bus."  Like cards plug into the backplane of a PC, fact tables plug into this dimensional bus.

The concept is also important in other architectures. For W.H. Inmon's Corporate Information Factory, conformance allows process comparison within data marts as well as across data marts.

Without conformed dimensions, subject areas become stovepipes. The opportunity to build cross-process metrics is lost. Worse yet, users also develop distrust in the individual data marts.  Their thinking is that  if sales and inventory cannot be compared, there must be something wrong with the data.

Documenting conformance

The conformance plan is a central feature of your dimensional model, so of course it must be documented.

Conformed dimensions are best documented in a matrix format, as in the diagram below.

Image from Star Schema: The Complete Reference by Chris Adamson
 (c) 2010 McGraw-Hill.  Used by permission.

The rows of this diagram correspond to fact tables, and the columns are dimensions.  Where you see a checkmark, it indicates that the fact table makes use of the associated dimension.

The matrix makes it easy to identify compatibility across fact tables. When two fact tables have a checkmark in the same column, that dimension cab be used as the basis for comparing the processes (aka drilling across).

Notice that conformed dimensions are depicted with associated "levels."  Salesperson, for example, has three successive levels of conformity: regions, territories, and individual salespeople. (This topic has been covered previously.)

It is possible that a degenerate dimension (a dimension attribute stored within a fact table) may be a conforming dimension. These attributes should also appear on the conformance matrix. In the picture above, order_line may be a degenerate dimension.

Variations on the conformance matrix

The small conformance matrix above focuses on a subject area (sales).  Conformance across subject areas can also be illustrated using a matrix, albeit a larger one.

An enterprise level conformance matrix is a valuable tool. It is a blueprint that can guide incremental implementation. It also helps break down proprietary attitudes toward data among different groups within your business.  One look at the matrix, for example, and it becomes clear that "customer" touches several parts of the business.

Conformance matrices can be produced at different levels of summarization.  A more summarized matrix may contain one row per data mart, rather that one per fact table. This may help guide project planning, or simply make an enterprise level matrix easier to digest.

Similarly, the conformance matrix can be used to map individual fact to dimensions. Architects use this kind of matrix when they are having trouble identifying discrete fact table. Performing affinity analysis on this kind of matrix reveals facts that share dimensionality. These may be candidates for inclusion in a single star.

Support this blog

Pick up a copy of Star Schema: The Complete Reference and you will be helping support this blog. Chapter 5 is completely dedicated to conformed dimensions.

A lot of information on conformed dimensions also appears in this blog:

• Multiple Stars and Conformed Dimensions (Aug 15, 2011) introduces the concept of conformed dimensions.

• Conformed Dimensions (Nov 15, 2011) discusses different ways dimensions can conform, and introduces the concept of "levels" of conformance.

• Three Ways to Drill Across (Dec 23, 2011) explains how to combine facts from different stars using conformed dimensions.

• Kimball's Approach is Top-Down (May 19, 2010) shows how conformed dimensions play a central role in Ralph Kimball's data warehouse architecture.

There is also a category label for posts referencing conformed dimensions.

Matrix Falling image by Patrick Hosley
Licensed under CC 2.0

Conformance matrix illustration is from
Star Schema: The Complete Reference by Chris Adamson,  

 Copyright (c) 2010 by McGraw-Hill. Used by permission.

Using A Hierarchy Bridge WIthout A Many-to-many Relationship

In this post, I describe what you can do to eliminate the many-to-many relationship when using a hierarchy bridge.

The Hierarchy Bridge

A recent post on Recursive Hierarchies and Bridge Tables described how to support a recursive hierarchy in a dimensional model.

The business benefits of this kind of solution can be extremely valuable.

In the example, a special bridge table was used to navigate the relationships between companies and their subsidiaries. When connected to a fact table, this allowed us to roll up transactions via the recursive hierarchy.

Here is what the solution looked like.  Click the picture for a larger version.  For full details, refer to the original post.

Many-to-many is legal, but you may need to resolve it anyway.

You be wondering about the many-to-many relationship -- the one between the fact table and the bridge table.

This kind of relationship is perfectly valid in a relational database.  Unfortunately, some of our software products may not accept this kind of relationship, making it necessary to eliminate it.

I've written about this before in the context of a multi-valued dimension.  The solution in that case was to resolve the many-to-many relationship by creating a single-column table that consisted solely of a surrogate key.

With a hierarchy bridge, we can resolve the many-to-many without adding one of those silly single-column tables.

Additional table not required

In the recursive hierarchy above, we already have what we need: the company table itself.

Notice that in the picture above, the company table represents the "superior" to which we are aggregating transactions. The bridge links it to facts that describe the activities of subordinates.

To resolve the many-to-many, we will insert a second alias for the company table between the fact table and the bridge.  This alias represents the subordinate.

Now the picture looks like this:

An added benefit of this approach is that we can include information about both the superior and the subordinates in our query results.

Learn more

There are several posts describing bridge tables in this blog.  This post has mentioned two of them:

• Recursive Hierarchies and Bridge Tables (May 2, 2012) introduced the recursive hierarchy bridge, including the many-to-many that is eliminated in this post.

• Bridge Tables And Many-to-many Relationships (April 25, 2011) described how to eliminate many-to-many relationships involving a different kind of bridge table -- one that handles multi-valued dimensions. 

• Use the category keyword bridges to find more posts.

And of course, you can help support this blog by picking up a copy of Star Schema: The Complete Reference.  It devotes two chapters and 30 illustrations to the topic of bridge tables.

Bridge photo by Branwebs via Creative Commons

Recursive Hierarchies And Bridge Tables

This post introduces the hierarchy bridge table, which can be used to navigate recursive hierarchies.

High Trestle Bridge at Night
Photo (c) Carol Bodensteiner

Bridge tables are a popular topic among readers of this blog. Perhaps that's because they can be a bit confusing, yet also enable powerful analysis.

Previously, I've focused on the two simplest uses for bridge tables:  the attribute bridge and the dimension bridge.  See links at the end of this post for more on these.

In this post, I'll tackle a third and final use: how a bridge can aid in the navigation of a recursive hierarchy.

Recursive relationships

In the real world, recursive relationships are common. Employees report to other employees, parts are made of other parts, and so forth.

In an E-R model, a recursive relationship might look like this:

Companies may own other companies.  That dotted relationship line is sometimes called a "pigs ear."  It links each company to the company that owns it.

You can read this relationship in two directions:  each company may have a parent company; each company may be the parent of one or more companies.

Looking up and looking down

From a business perspective, this information may be very useful.  For example, suppose you work for a business whose sells things to companies. You would like to be able to answer questions like these:

1. What are January order dollars from to Company XYZ and its subsidiaries?
2. Show me January orders from Company ABC and any companies above it.

The first question "looks down" from company XYZ.  The second question "looks up" from company ABC.

In both cases, we want to use the hierarchy to aggregate details about orders.

The hierarchy bridge

In a dimensional model, the idea is to support the study of facts (such as "order dollars") in the context of this hierarchy. This can be done using a bridge table, as shown here:

The table in the center is a hierarchy bridge.  This kind of table can be a bit confusing.  First, consider its content.

Each row contains a pair of company keys.  Here are the rules for populating the bridge table:

1. For each company, the bridge contains rows associating it with each of its subordinates.
2. Each company also has 1 row associating it with itself.

With respect to item 1, note each company is linked to all subordinates, not just the direct ones. (That's why the keys have the prefixes "superior" and "subordinate" rather than "parent" and "child".)

Using the bridge

In the picture above, notice that the fact table contains a company_key.  This represents the company from which each order is taken.  

Normally, we would join it to the company table to study "orders by company."

In this case, however, the bridge is inserted between the fact table and the company table. The bridge acts like a multiplexer--linking each company (on the right) to transactions with any and all of its subordinates (on the left).

This allows us to select a single company on the right (say Company XYZ) and aggregate all orders at or beneath that company.  That's called "looking down." It lets us answer question 1.

We can also use this hierarchy to "look up," as in question 2.  We simply reverse the joins--link company to the subordinate key, and the superior key to the fact table.  Then we can select a single company (say Company ABC) and look upward.

Powerful but dangerous

Of course, the danger of the bridge table should be readily apparent.  As you can see from the diagram above, if we forget to select a single parent company, we can easily double count the orders, triple count them, or worse.

For this reason, writing queries that involve the hierarchy must be governed carefully.  Developers or users with little experience can study the facts without using the bridge.  This will be safe, but they will not be able to leverage the hierarchy.

Other kinds of bridge tables

The hierarchy bridge described in this post is one of three kinds of bridge tables.  I've written previously about the other two kinds:

• Resolve Repeating Attributes With a Bridge Table (1/28/11) shows how a bridge can support a repeating attribute, such as a product that has multiple colors or a company that has multiple industry classifications.

• Bridge To Multi-Valued Dimensions (2/9/2011) shows how a bridge can link a single fact to multiple rows in a dimension.  Examples include orders with multiple salespeople, claims with multiple parties, etc.

More on the hierarchy bridge

When it comes to recursive hierarchies, this post only scratches the surface.  If you have questions, send them in.

In the mean time, there's a whole chapter on this topic in my book, Star Schema: The Complete Reference.  That would be Chapter 10:  "Recursive Hierarchies and Bridges." It's a long chapter--the longest one in the book.

And don't forget, when you use the links on this page to buy it from Amazon, you are helping to support this blog.

Photo credit: High Trestle Bridge at Night

Copyright (c) Carol Bodensteiner

Used by permission, with many thanks!