Data Modeling Issue?  [Solved]

Any topic that doesn't fit in elsewhere
Posts: 4
Joined: Fri Aug 02, 2019 11:01 am

Data Modeling Issue?

Postby Deehambone » Fri Feb 28, 2020 6:37 am

I've got what I suspect to be a data modeling issue that I'm not sure how to handle. I'll try to simplify and summarize the issue below. I'm not sure if this is the proper place for this post, but any help on this particular issue or feedback on other forums I should consult or books I should reference would be helpful.

I'm dealing with trying to track job performance on construction jobs. This performance is measured by estimating the number of hours it will take to complete a job, then comparing it to the actual number of hours it took to complete. Additionally for each job estimate, there are "job code" categories that hours are distributed to. It is easier to estimate hours by job code than it is for the overall jobs. Data is taken through timecards.

I've included some sample data in the files attached. There are two tables I'm dealing with and I've included both the raw data for each table and the cleaned up data as it lives in PowerBI after using PowerQuery. One table is called Job Hours and the other is called Timecard Data.

The “Timecard Data Images.png” shows the raw and cleaned Timecard Data table.
The “Job Hours Data Images.png“ shows the raw and cleaned Job Hours table.

The issue is, I'd like to be able to use the Job Hours table as a "lookup table" (dimension) for the Timecard Data "data table" (fact). I want to create views where a user selects a job in the Job Hours table and is shown the hours estimated for that job for each job code category, and the hours used for each category split by each employee. I see two issues with this, one which I have fixed. The one I fixed is the Timecard Data not including the job codes granularity. I can easily add this and have provided an image of the tables where I have done this.

The “Timecard Data Update Images.png” shows the updated timecard data including job codes (just an example showing I can add job code granularity to the Timecard Data table).

The issue I need help with is that in the Job Hours table, the hours are split into different categories ("job code" column in the image), making this table not have a primary key. I need these job codes in this table, because the user wants to be able to compare job code estimated hours vs actual hours for each job and see which person logged time to each job code.

I am trying to find a fix for this issue. I have considered merging the job number and job code columns in the Job Hours Data table to create a primary key and doing the same in the Timecard Data Update table to be able to relate these two tables, but I don't know if that is the best approach.

Any thoughts or input about potential solutions to this problem would be greatly appreciated. I seem to even be having trouble if the issue is the granularity in the exported data, the model, etc.
Timecard Data Images.png
Timecard Data Images.png (81.54 KiB) Viewed 8938 times
Job Hours Data Images.png
Job Hours Data Images.png (55.57 KiB) Viewed 8938 times
Timecard Data Update Images.png
Timecard Data Update Images.png (53.87 KiB) Viewed 8938 times

User avatar
Jason Cockington
Posts: 6
Joined: Thu Jan 16, 2020 8:46 am

Re: Data Modeling Issue?  [Solved]

Postby Jason Cockington » Fri Mar 06, 2020 9:20 am

Hi Dee,

My recommendation would be:
- You definitely need a primary key in your Job Hours table that can link to the same key in your Timecard table, so that you can filter your Timecard data by Job Hours.
- create a unique ID for your Job's (concatenate Job # with the first (number) part of Job Code)
- place this index column in both your Timecard Data table as well as in your Job Hours Lookup table.
- create an Employee lookup table containing Employee Name and an Index if you have duplicate names

Load your tables into PowerBI and set up your relationships:
- link Employee table to Timecard table (1 to many)
- link your Job Hours to Timecard (1 to many)

With this in place, you'll be able to filter your Timecard data based on both Employee and Job.
To achieve your desired outcome ("I want to create views where a user selects a job in the Job Hours table and is shown the hours estimated for that job for each job code category, and the hours used for each category split by each employee."), I would recommend:
- placing a slicer on your report for Job Name
- Place a matrix with Job Code & Employee on Rows, and Sum of Actual Hours on Values. Drill down to show division by employee.
- Place a matrix with Job Code on rows and Sum of Estimated Hours and Actual Hours on values.

Slicing by Job Name will then give you a nice breakdown between Estimated vs Actual Hours, and Job Code Actual Hours by Employee.

I have attached a workbook for your reference.

You'll note that i have included a calendar table in my workbook allowing you to explore employee time commitment over the year. If you'd like to build your own calendar table, check out Matt Allington's awesome blog article: ... wer-query/
PPivot furum.pbix
(124.34 KiB) Downloaded 58 times
Jason Cockington is Self Service BI Trainer and Consultant with Excelerator BI

Posts: 4
Joined: Fri Aug 02, 2019 11:01 am

Re: Data Modeling Issue?

Postby Deehambone » Sat Apr 25, 2020 7:13 am

Thanks so much for the reply Jason! This gives me a working solution!

Return to “General”

Who is online

Users browsing this forum: No registered users and 1 guest