Customer and reseller Dimensions
4 posters
Page 1 of 1
Customer and reseller Dimensions
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
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
RE:Customer and reseller Dimensions
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
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
Re: Customer and reseller Dimensions
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
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
Re: Customer and reseller Dimensions
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.
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.
Re: Customer and reseller Dimensions
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
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
Re: Customer and reseller Dimensions
Hi ngalemmo
Can you please give your recommendation and suggestions on this.
Regards
Harris
Can you please give your recommendation and suggestions on this.
Regards
Harris
grahan007- Posts : 18
Join date : 2009-05-26
Re: Customer and reseller Dimensions
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 : 364
Join date : 2014-01-06
Location : London
Re: Customer and reseller Dimensions
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
Re: Customer and reseller Dimensions
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.
Re: Customer and reseller Dimensions
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
Re: Customer and reseller Dimensions
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 : 364
Join date : 2014-01-06
Location : London
Re: Customer and reseller Dimensions
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.
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.
Re: Customer and reseller Dimensions
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
Similar topics
» Customer and Account dimensions
» 1 or 2 Dimensions - Customer & Shipping Address
» Bridge table - two customer-related dimensions
» Additional customer attributes or new dimensions - when is a dimension too wide?
» In Sales system how to design dimensions where city describes Order and Customer
» 1 or 2 Dimensions - Customer & Shipping Address
» Bridge table - two customer-related dimensions
» Additional customer attributes or new dimensions - when is a dimension too wide?
» In Sales system how to design dimensions where city describes Order and Customer
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|