Design Challenge #30 - Recursive fact table?
|
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.
|
To read the response to this design challenge, please click
here