Customer Dim, Vendor Dim, or combined?

View previous topic View next topic Go down

Customer Dim, Vendor Dim, or combined?

Post  jpayton on Fri May 08, 2009 10:33 pm

Hi All,

Just considering a design approach for a new project. The situation will quite frequently exist where a Customer is also a Vendor, and we will need to look at a summarized 'account'. So the question is, should we create separate customer and vendor dimensions, or a single table that is an Account? The dimension tables will not in fact be too large, so I'm not worried about having slow performance; more of the the issue is efficiency in creating reports that combine information from both the Accounts Receivable and Accounts Payable TRX fact tables.

Comments would be appreciated... thanks!

jpayton

Posts : 10
Join date : 2009-05-08
Location : Ontario, Canada

View user profile http://www.dynamicintelligence.ca

Back to top Go down

Re: Customer Dim, Vendor Dim, or combined?

Post  BoxesAndLines on Sat May 09, 2009 12:54 pm

What is the relationship between a Customer and a Vendor? One to one, one to many, many to many?
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Customer Dim, Vendor Dim, or combined?

Post  VHF on Mon May 11, 2009 4:48 pm

I think somewhere that Kimball warns against trying to make a dimension too generic--for example, combining vendors and customers into a single dimension. One reason to avoid this scenario is there are usually customer-specific attributes and vendor-specific attributes. Having a combined dimension for both types of entities results in lots of null values (or a value such as 'NA') for attributes that don't apply.

However, in your particular case--where a trading partner is frequently both a customer and a vendor--a single 'customer-vendor' (or 'account' or 'trading partner') dimension doesn't sound out of line and would facilitate combined reporting.

Watch out for ETL issues loading your combined dimension if customer and vendor information comes from separate tables in the source system, and make sure you have a plan for handling attributes that don't apply to a given entity.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Customer Dim, Vendor Dim or combined?

Post  jpayton on Tue May 12, 2009 7:33 am

Thanks for the input. The relationship is a one-to-one (or so it seems so far). I definitely see the value of not making the dimension too generic; I was really thinking about the benefit of connecting the AR with the AP easily. Any other ideas? Am I maybe overthinking this, and trying to look for too much simplicity at report design-time by making the back-end too complex?

Thanks

jpayton

Posts : 10
Join date : 2009-05-08
Location : Ontario, Canada

View user profile http://www.dynamicintelligence.ca

Back to top Go down

2 dimensions - make the key negative for one

Post  Jeff Smith on Thu May 14, 2009 11:17 am

I assume that you were thinking of puting the Vendor and Customer dimension keys into the same column on the combine AR/AP table. You could create 2 seperate dimension tables with the surrogate values of one dimension table negatives. You could then create a union view of the like columns from both dimensions to join to the AP/AR fact table.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Customer Dim, Vendor Dim, or combined?

Post  mark.tan on Fri May 15, 2009 3:23 am

VHF wrote:I think somewhere that Kimball warns against trying to make a dimension too generic--for example, combining vendors and customers into a single dimension. One reason to avoid this scenario is there are usually customer-specific attributes and vendor-specific attributes. Having a combined dimension for both types of entities results in lots of null values (or a value such as 'NA') for attributes that don't apply.

However, in your particular case--where a trading partner is frequently both a customer and a vendor--a single 'customer-vendor' (or 'account' or 'trading partner') dimension doesn't sound out of line and would facilitate combined reporting.

Watch out for ETL issues loading your combined dimension if customer and vendor information comes from separate tables in the source system, and make sure you have a plan for handling attributes that don't apply to a given entity.

I totally agreed with VHF analysis. On top of that, if you are using some form of semantic layer to design your dimension model, you could role play your "Account" dimension to be "Customer" or "Vendor" to connect to the AR/AP fact tables accordingly. You should have a complete and standardize view when you try to combine information from AR/AP fact tables.
avatar
mark.tan

Posts : 14
Join date : 2009-02-04

View user profile

Back to top Go down

Re: Customer Dim, Vendor Dim, or combined?

Post  jpayton on Mon May 18, 2009 1:55 pm

Thanks for all the input. I think the combined dimension might work out quite well in this case, likely with separate columns for customer & vendor IDs. What I might do is have a generic 'Account' dimension with shared attributes (city, state, etc), and possibly a mini-dimension (sorry if wrong terminology) with either AR or AP attributes (such as Terms, Ship via).

Any other thoughts would (as always) be appreciated, but thanks for all the input so far.

Jim

jpayton

Posts : 10
Join date : 2009-05-08
Location : Ontario, Canada

View user profile http://www.dynamicintelligence.ca

Back to top Go down

Re: Customer Dim, Vendor Dim, or combined?

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