Duration measure in fact table

Question

I have a fact table with the user reservations with StartDate and EndDate. I am thinking what is the proper way of serving the duration queries in the reports. Should I calculate DurationDays and DurationHours at the Fact ingestion or this is better to be done at the report run time? Or maybe I should have a separate dimension for durations?

Answer 1

You should calculate this during your ETL process or create a calculated column on your fact table. Then, aggregations will be easier and queries will be somewhat faster than calculating the values at runtime.