0

I have a source with information about TICKETS and about TIME_ENTRIES.

Initially I wanted to model TICKETS as a fact table with measures like first_response_time, resolve-time, and with dimensions like Requester/company, Agent/-group, start-date, resolve-date, type, sla-category, status.

Until ... I came to realise that I also wanted to report on the 'time-spent-on-ticket' measure, having a need to start updating the fact-rows, every time my source adds TIME_ENTRIES.

My TICKETS fact then starts to behave more like a dimension.

What is the correct modelling solution here?

Bart Jonk
  • 365
  • 3
  • 14

1 Answers1

0

The standard approach is to identify your measures and then define the grain for each measure. If measures have the same grain you can, but don’t have to, put them in the same fact table. If measures don’t have the the same grain you can’t put them in the same fact table.

Does that help?

NickW
  • 8,430
  • 2
  • 6
  • 19
  • Not sure... I guess the grain of the ticket fact is one per ticket created. I can slice them per requester, sla-category, type etc. I can even add some accumulating snapshot columns for 'time in state'. The grain of the time-entries is different. One per entry. So yes, these will need to be put in different fact table. This second table will have the tickets as a dimension. I guess tickets are both a dimension and a fact. – Bart Jonk Feb 17 '22 at 15:21