Employee Dimension/Weekly Sales Fact

  • 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?

  • 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

  • Okay, let me look into this.

  • How would I implement this in SSIS? The majority of my Weekly Sales rows do not match the employee rows.

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply