Customer snapshot

Hi

We have a situation where we are reporting the churn. We have customers each customer is assigned to an account manager. In the beginning of the year the churn targets are assigned to each account manager. now we want to report per account manager the actual churn vs target.

As the customer can change account manager over time but we want to report the churn as per beginning of the year situation. e.g.

Customer A has an account manager X on 2014/01/01 so at that time the churn target was assigned to account manager X based on his customers. But on 2014/03/31 the account manager of customer A has changed to Y and after two months the customer canceled the contract. so in the contract cancellation request we see account manager Y but for the target calculation we want to assign this churn amount to the account manager at the time when targets were assigned i.e to account manager X.

What would be the good model for this type of requirement. Shall I build two customer dimensions one with monthly snapshots and other with current situation.

Thanks in advance

Regards

Harris

How about 2 relationships from one dimension? The first one is static. It is set at create time and never changes. The second one works like a typical type 2.

BoxesAndLines wrote:How about 2 relationships from one dimension?  The first one is static.  It is set at create time and never changes.  The second one works like a typical type 2.

It will not work as the requirement is to assign the account manager who was associated at the time of target setting. In my example if the cancellation request is created after 2014-03-31 the account manager for that customer is Y but at the time of target setting the account manager was X so I need to assign this cancellation to account manager X instead of Y.

So practically we need both overviews, one with the account manager at the time of creation of request and other with the account manager at the time of target setting.

Regards

Harris

Yep, the first FK points to X, the second FK points to Y.

Similar, but slightly different suggestion, to BoxesAndLines' one:
1. Create a (possibly factless) fact table when targets are set. Dims would be Customer, Account Manager, Date, possibly the Target Process Dim (e.g. Name = "1Q2014 Target"; other attributes you may have), possibly "Target Set By" Dim, "Target Approved By" Dim, etc.
2. Create a churn (cancellation event) fact table. Dims would be Customer, Account Manager at time of event, Date, etc.

"Join" the two facts via the Customer Dim to get the churn for the Account Manager assigned when the target was set
Use the Churn fact table to get the churn for the Account Manager assigned at the point of churn

How about capturing target relationship manager and current relationship manager as 2 attributes of the customer dimension. I understand that manager is a dimension by itself and this relationship would work if we have a bridging table. But in this scenario, it looks like at a point in time, the customer and relationship manager relation is known and there should be not a problem having it this way.