Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Employee Dimension/Weekly Sales Fact Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 10:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
In our star schema, we have an employee dimension. That dimension includes the employee, the store where the employee works, and the organizational hierarchy above the employee. We also have a Weekly Sales fact table. There is a PK/FK relationship between the dim table and the fact table. That relationship has referential integrity enforcement.

Here is the problem. Our Weekly Sales fact table also includes sales from our external partners. Because those external trading partners are NOT employees, their sales violate referential integrity. What should I do?
Post #1397919
Posted Tuesday, December 18, 2012 10:46 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 2:26 AM
Points: 87, Visits: 228
Some options:
- create a "fake" employee for external partners
- create one "fake" employee for each external partner
- assuming the employee here is a salesperson, create a SellerEntity dimension, grouping employees and external partners

These are very unorthodox, but ultimately their use will depend on your requirements/available time


PMWar
Post #1397928
Posted Tuesday, December 18, 2012 12:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
Okay, let me look into this.
Post #1397975
Posted Tuesday, December 18, 2012 3:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
How would I implement this in SSIS? The majority of my Weekly Sales rows do not match the employee rows.
Post #1398074
Posted Wednesday, December 19, 2012 10:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
Not so unorthodox, I would do pretty much the same thing.

Start by adding the generic vendor record, or specific vendor records to your table. If you can't look up or get specific vendor information use a key of something like -1 as your generic vendor, other than that just run something to populate your table with vendors as you would employees. I also typically have an ID = 0 in every dimension for Unknown. You might need some way to map your vendors to the assigned employee id.

In SSIS, the answer depends on if you are using a generic record for vendors, or specific records.

-For a generic record set your lookup not to fail
-Create a derived column that evaluates the looked up employee id value and gives it a value if it is null or blank. For example ISNULL(LkpEmployeeID)?-1:LkpEmployeeID For your generic vendor this will return a -1 or the actual employee value. Then use this derived column value to load your fact table.

-For a specific set of vendors where you have the ability to look them up.
-Set your employee look up to route no match records to another data stream.
-Look up your vendor information appropriately and grab the employee ID
-Use a Union to bring your data streams back together with the employee and vendor values feeding into the same output column.
-Map to your fact table normally
Post #1398542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse