How would you model this data?

[tvaishnav]

I am working on developing start schema data model for payroll hours.

Business Sceneario : An employee can work on a job. For instance a carpenter would be assigned to a job. An employee can work on a equipement. For instance, a mechanic would be assigned to an equipment. Here is how a table looks like :

Tables include:

- Payroll Fact table : Captures hours for each employee, job they worked on OR equipement that they serviced.

- Employee Dimension : Include info like Employee key, names and other details that are irrelevant to the question.

- Equipment Dimension : Equipment ID and other details.

- Job dimension : Job ID and other details.

I need to create a report that slices data by equipment and job both. What is the best modeling practice here?

I can think of these options :

1. Consolidate equipment and job column in fact table as shown below:
   Not sure if this is a good solution. This will create a blank value in both job and equipement slicers. I can hide that value by filtering blank values at slicer level.
2. Create two fact tables. One for equipment and other for job. Problem with this approach is when someone changes job slicer. Equipment will not really change unless I implement some kind of DAX. It might give wrong perception that equipment data is somehow related to the job when it isn't. 

What is the best way to handle cases like these where columns can have NULLs?

[parry2k]

@tvaishnav I will approach it differently but have one question before that, can one row have value in both Job/Equipment or it is always, either you are working on a job or on equipment, again the question is for one record only.

[tvaishnav]

@parry2k  Thank you for taking the time to respond.

No. Either there will be a equipment number of job number. There is no case where both will be included in same row.

[lbendlin]

I wouldn't worry about the NULLs. That's something you can handle in the data model.

Captures hours for each employee, job they worked on OR equipement that they serviced

This is much more concerning. You may have to treat "servicing equipment" as one of the jobs they worked. And you may have to either loosely couple jobs and equipments  (ideally via common dimensions) or keep them as totally separate data domains.