Modelling a Customer / Account Manager relationship

Hi, Hopefully someone can help with this problem I have.

I have a simple Customer dimension and Orders Fact. I need to add into this model the Account Manager of the customer.

In my business a new customer is assigned two different account managers, typically the first account manager will have primary responsibility for the customer during the first three months, theresfter the second account manager will be responsible. I use the terms 'primarily' responsible as this is still shared but the primary account manager has the bulk of the responsibilty. As well as being assigned two account managers, each customer is assigned an account management type, indicating which account manager has the main responsibility at that point in time. Both account managers are active at the same time as specific analyses is done on this, although only one account management type is active. All this should be tracked using SCD Type 2.

There are various options in my head.
1) Add the Account Management Type, and both account managers as attributes on the Customer dimension. The Orders dimension links via the CustomerKey back to the Customer dimension to get the current account manager and type. This is useful here as it is important to ensure that analyses can be done on the number of customers per account manager.

2) Create an AccountManager dimension and add both account managers to the Orders Fact. Would I also store the account management type in the Fact? I'd also lose the ability to see how many customers are assigned to each account manager/type unless I introduce a bridge table of some sort.

I've pretty much talked myself out of option 2 whilst writing this but any feedback would be greatly appreciated.

Thanks.

Why do you need a type? Just add Primary_Account_Manager and Secondary_Account_Manager columns. If the limit of your analysis is who is the account manager then adding these to the customer dimension is fine. Be sure to add an index on these columns.

Hi B&L, I need the type to determine who is currently in charge of the customer. One of the main reasons we need this type is to determine who is paid the commission from the revenue this customer generates.

You have two things you are trying to track: who gets commission and who is responsible for the customer.

From the order line point of view, you capture who gets commission. So you have an FK to the account manager who receives commission.

You need another fact to track who is responsible for the customer and when. This would have customer, account manager, relatationship type, and effective date range as dimensions.