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 then 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.
Here is the high level dimensional model you and the user came up with:
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 user’s requirement.