Employee Dimension and Employee "Profile" Dimension?

I am building a DimEmployee. We have a Point of Sale system that some employees have multiple accounts on, one for each security level they may be authorized for. So an employee could have one account on the POS system (for general cashiering) or two (for elevated privileges).

I'm thinking of 2 tables, one DimEmployee and one DimEmployee_POS_Profiles and linking them by both a unique payroll id and by the Surrogate Key from DimEmployee (as employee_FK in the POS_Profiles table). In this way, DimEmployee grain would be one row for every employee (except for Type 2 SCD rows). DimEmployee_POS_Profiles grain would be one row per employee per POS account that they have.

This seems to work okay, until I start thinking about Type 2 SCDs in the POS_Profiles 'dimension' (mini?). I can envision rolling Type 2's in the main, DimEmployee table over to the POS_Profile dimension, but I hit a wall when I think about Type 2 SCD's in the POS_Profile table.

How are others dealing with DimEmployee and then other specific use-cases that involve Employees, but may be better suited in yet another table? Is this solution totally off the wall wrong?

Thank you as always.
Brad

What you have is one employee participating in two different roles. I would store just the number of employees and then relate to facts for the required role. Otherwise, how do you answer how many employees do I have? You also get into multiple copies of the same information such as name, address, phone number. These values could, and likely are, different for the same employee in your system.

I'm not sure I get what you're saying.

If you're saying one DimEmployee table, how do you lookup the Surrogate Key for that role?

I think what BoxesAndLines is suggesting is keep the employee and security role separate - let the fact retain the relationship.

In other words, two dimensions
DIM_Employee
DIM_Role (just the security roles, not the relationship with each employee. e.g. Cashier, Manager etc).

The dimensions are linked by the relevant facts. If needed, you can have a lookup table in your ETL spaces that maps an account to an employee and role.

Once you have the employee and role for a transaction, the surrogate key lookup is straight forward.

What is being proposed is clearer to me now, but what if one employee has more than one role? The lookup value coming in from the fact table could either be my cashier role code, or my Supervisor role code.

Is my grain of my DimEmployee then, 'one row per employee per role'?

[Update] I'm re-reading your posts and drawing out the model being proposed. I will post back if I have more questions, I don't want to waste your time!

An employee can have more than one security role, but can a single POS transaction have more than 1 role associated with it? Think about the business activity, the employee signs into the cash register (using one of their accounts depending on role). Then the employee processes customer purchases. Each purchase should be associated with the role the employee signed in with.

The same employee signs in the next day with a different role. Purchases recorded on the second day will be recorded with the new role. (same employee).

Actually I was proposing 1 employee dimension. The role is defined by the relationship to the fact table. There are different strategies for managing the natural key lookups with the easiest being carrying the natural keys in the dimension. If you have two roles applicable to the fact, then you will have two relationships (e.g. CashierEmployee_Dim_FK, SupervisorEmployee_Dim_FK). Now if you need to know which employees participate in which roles, you'll need a new fact table. If you want to know how many employees, or current address, name, etc, it's a simple dimension query.

When I posted this topic, this was my original model, but seems complicated to manage SCD Type 2's across both tables (potentially):



LAndrews (and maybe BoxesandLines) seems to be proposing something like this:


Am I way off here?

The incoming fact rows only have one column with an account_id from the POS (could be low privilege, could be a higher privilege). Just need to map it to one human being, if it does map to one, and hopefully not lose the context that in any given fact row, they were the lower role or the higher role.

I would do a 'role' playing dimension, but the account_id only shows up in one column.

Thank you all again.

Brad