CODEWITHSUNDEEP
sql-serverpowerbidata-warehousebusiness-intelligence
Daniel
Daniel

Reputation: 426

Data Warehouse / BI modeling: Fact or Dimension?

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.

Upvotes: 2

Views: 147

Answers (1)

Tyron78
Tyron78

Reputation: 4187

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.

Upvotes: 1

Related Questions