Customer Dim, Vendor Dim, or combined?
5 posters
Page 1 of 1
Customer Dim, Vendor Dim, or combined?
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!
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!
Re: Customer Dim, Vendor Dim, or combined?
What is the relationship between a Customer and a Vendor? One to one, one to many, many to many?
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Customer Dim, Vendor Dim, or combined?
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.
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
Re: Customer Dim, Vendor Dim or combined?
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
Thanks
2 dimensions - make the key negative for one
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
Re: Customer Dim, Vendor Dim, or combined?
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.
mark.tan- Posts : 14
Join date : 2009-02-04
Re: Customer Dim, Vendor Dim, or combined?
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
Any other thoughts would (as always) be appreciated, but thanks for all the input so far.
Jim
Similar topics
» Merging customer data from disparate sources to create a master customer dimension
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» Date and Time Dimension Combined or Separate
» How to handle situations when there is a combined primary key in the source system?
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» Date and Time Dimension Combined or Separate
» How to handle situations when there is a combined primary key in the source system?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|