Datawarehouse Design - how to avoid Snowflake structure

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_Type,  --either Customer or Vendor
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.
LVL 44
zephyr_hex (Megan)DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
Indeed just recently I recommended having two entities Consumers and Providers with a main entity of Agencies at the thread

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?
zephyr_hex (Megan)DeveloperAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.