How to model with a requirement for multiple grains?

While trying to model our Call Management process, I found this solution. I'm wondering however if this is the correct way of modelling this.

The Model:


Because there are measures on both Snapshot as Transaction grain, I created two fact tables. Next I created a Transaction Dimension DimCall which holds one record for every call (just like FactCall).

Need to model the Change Management and Problem Management process next, and there will be a relation to Call Management because of questions like:
- What Calls were caused by Change x?
- What Changes solved Call y?
- Which Calls are linked to Problem z?

Because of the design now, I'm required to also model Changes and Problems as 1 dimension, 2 fact tables (transaction, snapshot). However there are multiple ways of creating the relations. Example:
What Calls were caused by Change x?
DimChange(ChangeKey) -> FactCall(CausedByChangeKey)
FactChange(ChangeKey) -> DimCall(CausedByChangeKey)

This scenario makes me wonder if I chose the correct design in the first place. Could the Dimension be removed by using Degenerates in both Fact tables? But how to relate to other Facts then?

The book offers many solutions which I think might apply to my design issue. However, due to my own inexperience, I'm unable to select the correct one.

Could someone guide me in the right direction? Many thanks.

Why do you have a snapshot fact? This data seems very transactional to me.

BoxesAndLines wrote:Why do you have a snapshot fact?  This data seems very transactional to me.

In order to answer questions like:
- What Calls/Changes/Problems were open on Date X
- Average age of backlog on Date Y
- Backlog per department growth since period Z
Etc

I would put a 'Closed Date' (or something similar) on your transaction fact table. I don't think snapshots are the solution as they will tie the queries you can answer very closely to how you implement the snapshots and you will have a very inflexible model e.g.
You create a daily snapshot at 6pm. A call is closed at 6:05pm. That call would presumably appear as closed in tomorrow's snapshot i.e. your snapshot is defining your business day. If this definition every change then you can't apply the new business day logic to historic records.
Queries that span multiple snapshot records are not that easy to run - at least not compared to a pure transactional fact table.

nick_white wrote:I would put a 'Closed Date' (or something similar) on your transaction fact table. I don't think snapshots are the solution as they will tie the queries you can answer very closely to how you implement the snapshots and you will have a very inflexible model e.g.
You create a daily snapshot at 6pm. A call is closed at 6:05pm. That call would presumably appear as closed in tomorrow's snapshot i.e. your snapshot is defining your business day. If this definition every change then you can't apply the new business day logic to historic records.
Queries that span multiple snapshot records are not that easy to run - at least not compared to a pure transactional fact table.

Thanks for your reply. I'm not very experienced at dimensional modelling so my apologies if the answers to my questions seem obvious.

I will elaborate a bit on the requirements.

I think snapshots (being periodic or accumulating) are required because of future requirements like:
- What was the backlog per employee during time, and how did he perform (booked hours per call/calls closed/customer satisfaction)
- What was the status per call at the end of the day, and how long has it been at that state?
- If the status is on-hold, subtract the on-hold duration from the total time open
- How many calls does a customer log per user (using FactEnduser with a monthy snapshot)
- Does the backlog grow or shrink? And why?
- How does a contract perform (Calls + Booking time vs Customer -> Contract (monthly snapshot of worth over contract period))

The snapshot as it is, is always made at the real end of the day (not business day). By leveraging DimCall (no measures, but dimension keys) and FactCall (snapshots with FK's CallKey, DateKey, PhaseKey, EmployeeKey and Measures Logged, Open, Closed, CurrentDaysOpen, TotalDaysOpen(logged at closed event), etc).

Having to report on calls open per day, status and employee-backlog pushed me towards this design, as I did not see another solution on how to report this using a transaction fact table. Please tell me, how would you model this?