I am building a data warehouse using the famous facts/dimensions star scheme. Currently implementing employee performance data.
I have two sources:
fact: spent time, billed amount dimension: Employee, Date, Customer, type-of-time (billed, not-billed, internal, service, driving-time ...)
fact: worked hours dimension: Employee, Date
What would be the "correct" approach:
A. Add the Worked hours as separate fact?
B. Just have a "time spent" fact and add the worked hours as a dimension to the type-of-time dimension?
The goal is to create a dashboard with info such as billed time vs worked time, amount of not-billed time etc.
As suggested by the OP, here my comment as answer:
This depends on your structure / aggregation level of the facts. Example: Employee A works 9.5 hours on March 2nd 2020. He Works 4 hours for Customer X and 5 hours for Customer Y. Additionally, he has a 30 minute break (whis is not billed). Your ticketing system most certainly contains 2 rows - one per customer. The Clocking System has only one aggregated row, since it does not distinguish between the customers. Therefore in this case I would suggest to create a separate fact table for the worked hours.