Data Warehouse / BI modeling: Fact or Dimension?

user_7458464 0
Viewed 124

I am building a data warehouse using the famous facts/dimensions star scheme. Currently implementing employee performance data.

I have two sources:

  1. The ticket system where I get 
fact: spent time, billed amount 

dimension: Employee, Date, Customer, type-of-time (billed, not-billed, internal, service, driving-time ...)
  1. The time clocking system, where i get
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.

powerbi business-intelligence data-warehouse sql-server
1 Answers

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.

user_61674400
Related