Forum Discussion
jjkmd
Helper I
Helper IManage changing job classifications over time.
Paycor is our payroll service, and we can output a report that has information on each employee for each payroll date. At it's most basic it will show Payroll Date : Name : Job Title : Pay for...
Well, should you then not keep a history of Job Title changes for each and every Name? You should.
So, you have to have another fact table that will store job titles for all dates and all employees. Connect this to your Dates table.
By the way, you need the following dimensions: Dates, Employees (for Names), JobTitles. And two fact tables: Payroll (Date,Name,Pay), JobTitleHistory (Date,Name,JobTitle).
Join dimensions to facts with 1:many relationships and you're OK to build your DAX.
I'd also suggest not to use Name as the id field but EmployeeID (which will not change even if the employee's name does).
The key to building good and easy to manage models is to reach the star schema. The above will give you one.
Best
Darek
So, you have to have another fact table that will store job titles for all dates and all employees. Connect this to your Dates table.
By the way, you need the following dimensions: Dates, Employees (for Names), JobTitles. And two fact tables: Payroll (Date,Name,Pay), JobTitleHistory (Date,Name,JobTitle).
Join dimensions to facts with 1:many relationships and you're OK to build your DAX.
I'd also suggest not to use Name as the id field but EmployeeID (which will not change even if the employee's name does).
The key to building good and easy to manage models is to reach the star schema. The above will give you one.
Best
Darek
jjkmdSo, i'm creating and maintaining a table like this?
Employee number Employee name Date Position 1 John Doe 1/1/2019 Clerk 2 Peter Parker 1/1/2019 Clerk 3 Daisy May 1/1/2019 Reception 4 Lexy Ann 1/1/2019 Reception 5 Jack Black 1/1/2019 Manager 1 John Doe 1/2/2019 Clerk 2 Peter Parker 1/2/2019 Clerk 3 Daisy May 1/2/2019 Reception 4 Lexy Ann 1/2/2019 Reception 5 Jack Black 1/2/2019 Manager 1 John Doe 1/3/2019 Clerk 2 Peter Parker 1/3/2019 Clerk 3 Daisy May 1/3/2019 Reception 4 Lexy Ann 1/3/2019 Reception 5 Jack Black 1/3/2019 Manager 1 John Doe 1/4/2019 Clerk 2 Peter Parker 1/4/2019 Clerk 3 Daisy May 1/4/2019 Reception 4 Lexy Ann 1/4/2019 Reception 5 Jack Black 1/4/2019 Manager 1 John Doe 1/5/2019 Clerk 2 Peter Parker 1/5/2019 Clerk 3 Daisy May 1/5/2019 Reception 4 Lexy Ann 1/5/2019 Reception 5 Jack Black 1/5/2019 Manager 1 John Doe 1/6/2019 Clerk 2 Peter Parker 1/6/2019 Clerk 3 Daisy May 1/6/2019 Reception 4 Lexy Ann 1/6/2019 Reception 5 Jack Black 1/6/2019 Manager