Recursive Fact Table?

Design Challenge

Information Management Magazine, November 2007

Steve Hoberman

To better understand the requirements for an analytics data mart, you and a business user are standing at the white board sketching a high-level data model. The user needs to report on telephone call metrics, and it is a requirement to know how many times each consumer calls on a given day for a given product. It is also a requirement to know whether the call is completely new or a follow-up from an existing call. That is, if Bob calls on Monday with a product complaint and calls Tuesday with more information on this same complaint and then calls again Wednesday with still more information, the user needs to know that Monday is a completely new call and Tuesday and Wednesday’s calls are follow-ups. The high-level dimensional model you and the user came up with is shown in Figure 1. The recursive relationship on call volume captures the rule that each call can be a follow-up from a single new call, and each new call can have many follow-up calls.

The Challenge

This model looks great on a white board, but can a recursive relationship on a fact table actually be physically implemented? Describe how you would physically implement the user’s requirement.

The Response

The responses received for this challenge fit into one of three categories.

Keep the recursion. If your reporting tool (and users) can handle a recursive structure, one solution would be to implement this relationship by storing a parent foreign key in the call volume fact table. Art Trifonov, Oracle consultant, describes this solution and says for the original call the value of the foreign key PARENT_CALL_ID will be the same as CALL_ID, recording the original call as being its own parent. For follow-up calls, the parent foreign key can either contain the ultimate parent key (i.e., both Tuesday’s and Wednesday’s calls will point to Monday’s original call) or the immediate parent (i.e., Tuesday’s call will point to Monday’s original call and Wednesday’s call will point to Tuesday’s call). A number of responses supported this solution, including those of Chan Beauvais, database analyst; Chris Strasser, CIO; and Gordon Everest, professor emeritus.

Model only the current requirement. Many feel that data marts are throwaways. That is, existing only as long as the business questions they answer are still relevant, and, when the business questions change, the data mart can get recreated or replaced. This belief allows us to model the immediate reporting requirement instead of the more flexible recursive relationship shown on the logical model. The immediate requirement is to know how many times each consumer calls on a given day for a given product, and also whether the call is completely new or a follow-up from an existing call. Just storing the measure Call Quantity, and adding to the fact table’s primary key the new data element Initial Call Indicator (Y = Initial Call and N = Follow-up Call) can meet this immediate requirement. A number of responses supported this solution, including those of Roy Guzman, data designer; Philip Kelley, database administrator; and Jeff Lawyer, senior data architect.

Group the calls. If you wanted to avoid recursion and still allow querying on the individual call, you can add a new dimension to the model or add new data elements. Kristina Vogel-An, database architect, suggests a new dimension can be created with two levels. The lowest level is the individual call, and the higher level is a call type with values initial and follow-up. A number of responses supported this solution, including those of Walter Howard, president; Chuck Cohen, engineer; and Richard Ryan, data modeler. A number of Challengers recommended adding new data elements to group calls together. Michael Smith, analyst, suggests adding two new data elements to call volume. “The assumption has to be that the source system collecting the data is already somehow associating call facts. If yes, then I would add two columns to the call volume table. The first would be a group key that would group calls that are associated with one another. The second would be a call order number. This would allow the user to order the calls by when they were received.” Irwin Miller, business intelligence consultant, proposed a similar solution where a complaint sequence number can be added to identify the full chain of calls. If you would like to become a Design Challenger and have the opportunity to submit modeling solutions, please add your email address at http://www.stevehoberman.com/. If you have a challenge you would like our group to tackle, please email me a description of the scenario at me@stevehoberman.com.

Steve Hoberman is one of the world's most well-known data modeling gurus. He understands the human side of data modeling and has evangelized “next generation” techniques. Steve taught his first data modeling class in 1992 and has educated more than 10,000 people about data modeling and business intelligence techniques since then. He has presented at more than 50 international conferences, and his third book, Data Modeling for the Business, is hot off the press.

For more information on related topics, visit the following channels:

• Data Modeling
• DW Administration, Mgmt., Performance
• Data Management