troubleshooting Question

Datawarehouse Design - how to avoid Snowflake structure

Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)Flag for United States of America asked on
Microsoft SQL ServerDB Reporting Tools
3 Comments2 Solutions222 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros