Should I use the surrogate key?

[imani_technology]

Here is the scenario. I have a fact table that looks like the following:

factEmployeeWorkHours
PK ID
LastName
FirstName
Hours

The fact table joins to a dimension table that looks like the following:

dimOrganization
PK ID
EmployeeLastName
EmployeeFirstName
Store
StoreManager
RegionalManager
VP
Region
State

But there is a problem. The Employee can be assigned to more than one store and therefore can have more than one store manager, region, etc. If I join the fact table to the dim table during ETL to get the surrogate key/PK from the dim table, I will have multiple PKs per fact table row. I know that Kimball wants to have an FK in the fact table that joins the PK in the dimension table, but is that prudent in this case?

[ngalemmo]

Why do you have employee in an organization dimension? Why do you have name in the fact table?

Is it safe to assume the hours are being posted for an employee, for a particular day, at a particular location? Why are none of those dimensions?

Keys are always surrogate, but that's not your problem. Your dimensions are wrong.

[imani_technology]

This is the dimension table in question:

CREATE TABLE [dbo].[dimEmployee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[VP] [nvarchar](255) NULL,
[RSM] [nvarchar](255) NULL,
[RAM_BM] [nvarchar](255) NULL,
[Role] [varchar](255) NULL,
[ADP_Code] [nvarchar](50) NULL,
[Aesthie_First_Name] [nvarchar](50) NULL,
[Aesthie_Last_Name] [nvarchar](50) NULL,
[Country] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[Market] [nchar](255) NULL,
[Sub_Market] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[State_2] [nchar](255) NULL,
[Store] [nvarchar](255) NULL,
[TradingPartnerID] [nvarchar](50) NULL,
[DoorNum] [nvarchar](50) NULL,
[InsertDate] [smalldatetime] NULL,
[LastUpdateDate] [smalldatetime] NULL

This is the fact table:

CREATE TABLE [dbo].[factAesthetician_Hours](
[Aesthetician_HoursID] [int] IDENTITY(1,1) NOT NULL,
[Hours] [decimal](18, 3) NULL,
[StoreID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Date_DID] [int] NOT NULL,
[InsertDate] [smalldatetime] NULL,
[LastUpdateDate] [smalldatetime] NULL

You're probably right that these to tables are designed improperly. What should I do?

[ngalemmo]

I would break out geography from the person.You should have an employee dimension that describes the employee, and a location dimension that tells you where it happened. It you want to keep track of what locations an employee is assigned to, use another fact table.

[imani_technology]

Thank you for the advice. We are looking into this right now.

[Sponsored content]