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?
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?