Customer and reseller Dimensions

View previous topic View next topic Go down

Customer and reseller Dimensions

Post  grahan007 on Tue Apr 01, 2014 7:30 am

Hi

Another question about the dimensional design.

We have a table in source system called accounts which contains customer, resellers and employee accounts. Now I am making two dimensions DimCustomer and DimReseller out of this source table. As per requirement both DimCustomer and DimReseller are Type-2 dimensions. One of the attribute of DimCustomer is reseller because we sell directly to customers and also through resellers.

Keeping in mind that both DimCustomer and DImReseller are type-2 dimensions what would be the best design of DimCustomer where we do need reseller information.

Cheers

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

RE:Customer and reseller Dimensions

Post  hkandpal on Tue Apr 01, 2014 11:25 am

Hi,

what are the attributes that you want to capture for the Customer and reseller. Are the common, can a customer be a reseller or vice versa ?

thanks

Himanshu

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  grahan007 on Tue Apr 01, 2014 2:21 pm

Hi Himanshu

Thanks for your reply. Yes a customer can be a reseller and most of the attributes are same between customer and reseller.

Cheers
Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  ngalemmo on Wed Apr 02, 2014 7:06 pm

I would just implement a single customer dimension and include an attribute to indicate the customer is a reseller, employee, or not.  I do not see a reason you would want to create multiple dimension tables.  The differences in attributes should be minor, and reseller related attributes would simply be null or blank for customers that are not resellers.

Having two dimension tables introduces a lot of potential problems.  For example, a sales fact table would need two FKs, one to customer and the other to reseller.  They would be mutually exclusive which make queries difficult.  To perform a query for all sales would require a union of two queries, one for sales to customers and another for sales to resellers.  Such complexity is unnecessary.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer and reseller Dimensions

Post  grahan007 on Thu Apr 03, 2014 2:50 am

Thanks ngalemmo for your suggestion. Indeed you are right there is no need to make it complex.

So I would go for a single account dimension then where one attribute will be account type i.e. customer, reseller, employee etc. I will include a self referencing Resellerkey which will be populated if the account is a customer account and customer has a reseller. In other cases it will be not applicable.

Can you please advise me how to design ETL flow for such a dimension because it is a type 2 dimension means in case of any type 2 change to a reseller account will create a new reseller surrogate key and that change will trigger a type 2 change to all the customers who have that reseller.

Any example design or script will be very helpful.

Cheers
Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  grahan007 on Mon Apr 07, 2014 3:02 am

Hi ngalemmo

Can you please give your recommendation and suggestions on this.

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  nick_white on Mon Apr 07, 2014 6:48 am

The reference to the Reseller on the Customer Dim should be the business/natural key and not the Surrogate Key. This removes the need to update customers when details of the reseller change

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  grahan007 on Mon Apr 07, 2014 7:09 am

nick_white wrote:The reference to the Reseller on the Customer Dim should be the business/natural key and not the Surrogate Key. This removes the need to update customers when details of the reseller change

If I use the business/natural key for the reseller then it will point to the most current situation of that reseller where as the business in interested in the status of reseller when a certain transaction was done by a customer.

I might be missing something out?

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  ngalemmo on Mon Apr 07, 2014 9:45 am

grahan007 wrote:Hi

One of the attribute of DimCustomer is reseller because we sell directly to customers and also through resellers.

Harris

I think I may have misread your original post. Can you clarify the statement above?

Are you saying, if you sell to a reseller you also know and track the final consumer as well? Or are you saying you have customers, some are resellers and others are consumers?

If it is the former, do you know the final consumer at the time of order (i.e. a drop-ship arrangement with resellers)? Or is there a significant time gap?

If both a reseller and consumer can be party to the transaction, then have the two dimensions and carry both keys in the fact. For consumer only transactions, have a 'not applicable' reseller row that a fact can reference.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer and reseller Dimensions

Post  grahan007 on Mon Apr 07, 2014 10:12 am

ngalemmo wrote:

Are you saying, if you sell to a reseller you also know and track the final consumer as well?  Or are you saying you have customers, some are resellers and others are consumers?

Hi

Your first assumption is correct. We always know the final customer/consumer either we sold directly or via reseller/partner.
To clarify more, we are working on account cancellation process, suppose a customer send a request to cancel the account we have customer information in our Accounts table as following:

AccountID
AccountName
AccountType
ResellerID (Which is foreign key to AccountID for the same table)

Suppose we have following records in accounts table

AccountID | AccountName | AccountType | ResellerID
123          | ABC Ltd         | Customer      | 456
456          | XYZ Ltd         | Reseller        | NULL

Business wants to know when there is a cancellation request who is the reseller of that account so they can track the performance of the resellers.

I hope this will be clear explanation.

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  nick_white on Mon Apr 07, 2014 11:06 am

grahan007 wrote:
nick_white wrote:The reference to the Reseller on the Customer Dim should be the business/natural key and not the Surrogate Key. This removes the need to update customers when details of the reseller change

If I use the business/natural key for the reseller then it will point to the most current situation of that reseller where as the business in interested in the status of reseller when a certain transaction was done by a customer.

I might be missing something out?

Regards

Harris

If you are interested in the status of the reseller when a certain transaction was done then why not add the Reseller as an FK on the Fact table(s) you are using to track these transactions? Obviously this assumes the Reseller Dim is a Type 2 SCD

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

Post  ngalemmo on Mon Apr 07, 2014 12:06 pm

As Nick stated, reseller should be a dimension of the fact, not a FK of the customer dimension.

This is particularly important if the customer/reseller relationship changes over time. I would imagine you would want old sales to reference the reseller responsible for the sale, not the current reseller the customer is working with.

If the reseller comes in on the sales feed, I wouldn't put it on the customer at all. If you need to derive the reseller when you load sales, then its ok to have the reseller FK on customer so that the ETL process can use it to put it in the fact table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer and reseller Dimensions

Post  grahan007 on Tue Apr 08, 2014 4:18 am

ngalemmo wrote:As Nick stated, reseller should be a dimension of the fact, not a FK of the customer dimension.

This is particularly important if the customer/reseller relationship changes over time.  I would imagine you would want old sales to reference the reseller responsible for the sale, not the current reseller the customer is working with.

If the reseller comes in on the sales feed, I wouldn't put it on the customer at all.  If you need to derive the reseller when you load sales, then its ok to have the reseller FK on customer so that the ETL process can use it to put it in the fact table.

Thanks guys for your help. Much appreciated.

Regards
Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer and reseller Dimensions

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