Should I use the surrogate key?

View previous topic View next topic Go down

Should I use the surrogate key?

Post  imani_technology on Tue Nov 06, 2012 3:31 pm

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?

imani_technology

Posts : 3
Join date : 2012-11-06

View user profile

Back to top Go down

Re: Should I use the surrogate key?

Post  ngalemmo on Tue Nov 06, 2012 7:49 pm

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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Thank you for responsding

Post  imani_technology on Tue Nov 06, 2012 8:30 pm

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?

imani_technology

Posts : 3
Join date : 2012-11-06

View user profile

Back to top Go down

Re: Should I use the surrogate key?

Post  ngalemmo on Wed Nov 07, 2012 1:24 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Should I use the surrogate key?

Post  imani_technology on Wed Nov 07, 2012 6:21 pm

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

imani_technology

Posts : 3
Join date : 2012-11-06

View user profile

Back to top Go down

Re: Should I use the surrogate key?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum