SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Employee Dimension/Weekly Sales Fact


Employee Dimension/Weekly Sales Fact

Author
Message
imani_technology
imani_technology
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 777
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?
PMwar
PMwar
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 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
imani_technology
imani_technology
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 777
Okay, let me look into this.
imani_technology
imani_technology
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 777
How would I implement this in SSIS? The majority of my Weekly Sales rows do not match the employee rows.
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4136 Visits: 2629
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search