Datawarehouse Design - how to avoid Snowflake structure

zephyr_hex (Megan)
zephyr_hex (Megan) used Ask the Experts™
on
I'm following Kimball Fact - Dimension design principles, but I've encountered a scenario that lends itself to a Snowflake structure, which I'm trying to avoid.

In our ERP, we have a table that contains both customer and vendor information.  Here is a simplified version:

BusinessPartner Table
BP_ID,
BP_Type,  --either Customer or Vendor
BP_Name,
SalesPersonCode,   -- this only occurs on Customer records
AssociatedCustomerID  --on some Vendors, this will be the BP_ID of an associated Customer

So, for example, we have:
BP_ID  |  BP_Type  |  BP_Name  |  SalesPersonID   |  AssociatedCustomerID
1           |      C          | FooBar        |           123                   |   NULL
2           |      V          |  BlueBar      |         NULL                  |   NULL
3           |      V          | ShoeBar       |        NULL                  |    1

Certain accounting transactions will reference the Vendor ID, but these need to be associated with a customer ID in order to tie them to a salesperson.  To help explain the relationship here is the SQL Query I'd use against the ERP table:

SELECT v.BP_ID, v.BP_Name, c.SalesPersonID
FROM BusinessPartner v
INNER JOIN BusinessPartner c ON c.BP_Type = 'C' AND c.BP_ID = v.AssociatedCustomerID
WHERE v.BP_Type = 'V'

note:  this is NOT the query I'd use to load data for the datawarehouse

1.  Should I split this BusinessPartner table up into a Customer Dimension table and Vendor Dimension table or keep them in one Business Partner Dimension table?

2.  How do I structure this so that I'll be able to avoid a Snowflake design but still maintain this association I need?  My guess is that I would omit the association from the dimension (or dimensions) altogether and handle the Salesperson association in the query that loads the GL Fact table (which has yet to be designed).  But I'm not sure.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do you have some reason for keeping Customers and Vendors in the same table?  Granted, the demographic data is structured very similarly for the two, but they are in fact two entirely different things.  From an accounting perspective, a customer is somebody that pays you and a vendor is somebody that you pay.  Customer records tie directly to the income data and vendor records tie directly to the expense data.

Unless there's a compelling reason to do otherwise, I'd split them into separate tables.
Olaf DoschkeSoftware Developer

Commented:
Indeed just recently I recommended having two entities Consumers and Providers with a main entity of Agencies at the thread http://rdsrc.us/EZow9A

You could choose to still have the business partner dimension and additional vendor and customer dimensions. The association of customer and vendor is symmetric, so you'd have mirror facts, but that only means you can look at the data from two more perspectives.

Could you explain what design problems you face with all three dimensions?
Top Expert 2010
Commented:
@Olaf - That thread is a little different in that the discussion concerns normalization, and I'm actually doing the opposite with the Kimball approach.

I hadn't considered using 3 dimensions (Business Partner, Customer and Vendor), and in fact, I'm not sure what the benefit would be to having a Business Partner dimension if I have both a Customer and Vendor dimension.

And I'm still not entirely sure how to handle the Salesperson association on some Vendor records.  I said earlier that I thought maybe I should leave the Salesperson out of the dimension table and include it with the Fact table load, but now I'm pretty sure that's the wrong approach.  It's an attribute, not a calculation, and so it's proper place is in the dimension table.  I'm now thinking that I just include the Salesperson attributes (Name, etc) as columns in a Vendor dimension table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial