Modelling a Customer / Account Manager relationship

View previous topic View next topic Go down

Modelling a Customer / Account Manager relationship

Post  jimlad on Tue Mar 22, 2011 8:23 am

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.


jimlad

Posts : 4
Join date : 2011-03-22

View user profile

Back to top Go down

Re: Modelling a Customer / Account Manager relationship

Post  BoxesAndLines on Tue Mar 22, 2011 9:45 am

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.

avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modelling a Customer / Account Manager relationship

Post  jimlad on Tue Mar 22, 2011 9:57 am

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.

jimlad

Posts : 4
Join date : 2011-03-22

View user profile

Back to top Go down

Re: Modelling a Customer / Account Manager relationship

Post  ngalemmo on Wed Mar 23, 2011 12:23 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Modelling a Customer / Account Manager relationship

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum