Customer Dim, Vendor Dim, or combined?

[jpayton]

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!

[BoxesAndLines]

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

[VHF]

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.

[jpayton]

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

[Jeff Smith]

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.

[mark.tan]

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.

[jpayton]

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

[Sponsored content]