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 Wednesdays 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.
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 users requirement.
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 Tuesdays and Wednesdays calls will point to Mondays original call) or the immediate parent (i.e., Tuesdays call will point to Mondays original call and Wednesdays call will point to Tuesdays 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 tables 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.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access