FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Recursive Fact Table?

Design Challenge

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.


Figure 1: The Challenge

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 has worked as a business intelligence and data management practitioner and trainer since 1990. He is a Certified Business Intelligence Professional (CBIP), having achieved mastery level certification in data analysis and design. He is a popular and frequent presenter at industry conferences, both nationally and internationally.  Hoberman is a columnist and frequent contributor to industry publications, as well as the author of  Data Modeler's Workbench and Data Modeling Made Simple (available for purchase through the DM Review bookstore). He is the founder of the Design Challenges group, inventor of the Data Model Scorecard and a recognized innovator and thought leader in the field of data modeling. He can be reached at me@stevehoberman.com.

Graeme Simsion's latest book is out! Data Modeling Theory and Practice. Here's a link where you can read more about the book and purchase it at a discounted price.

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



Industry Vendors