We help IT Professionals succeed at work.
Get Started

Datawarehouse Design - how to avoid Snowflake structure

219 Views
Last Modified: 2016-03-10
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
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE