Accumulating Snapshot that acts like a SCD

I'm creating a model of our help desk ticket tracking system, and I'm not sure how to model this. We need to do a snapshot of the data because the transactional system will make overwriting updates. It's accumulating because the ticket has a lifecycle, it opens, it gets assigned and it closes, but it could be reopened.

My reporting requirements are such that I need to be able to report how many tickets are opened on a particular day, closed and how long it's open and closed. But if a ticket is reopened it gets counted as an open item. But I need to show that it was closed as some point.

For example, Monday a ticket was opened, assigned. Tuesday it was closed. Wednesday the ticket was reopened.

What advice does anyone have for modeling the reopened tickets, all the examples I've seen the accumulating snapshot goes through beginning middle and end. Keep in mind the ticket can be opened and closed many times.

Thank you.

Add ticket open date to the grain of your fact table. This assumes that the same ticket can only be opened once per day.

From your reply, then the ticket could only open once when the reality is the ticket can open and close multiple times, in the same day. I do have a time dimension in my schema for getting that metric.

Unless I'm miss understanding your logic, if so please elaborate how it solves my issue with multiple open and closes.

Thank you

Adding the date or datetime or even a sequence number will make the ticket PK (i.e. grain) unique. Per your statement, even though a ticket has the same number, it's logically treated as a different ticket from a metrics perspective. Is that not the case?

BoxesAndLines wrote:Adding the date or datetime or even a sequence number will make the ticket PK (i.e. grain) unique. Per your statement, even though a ticket has the same number, it's logically treated as a different ticket from a metrics perspective. Is that not the case?

I see it differently that it's the same ticket in a different state in a time period, but I think you're correct in that it's really a different ticket. Talking with someone else on the team, his suggestion is what you're suggesting, that each change is really a new ticket and we have a DimTicket that would also capture the changes as well and using the natural key of the ticket we should be able to get accurate reporting for metrics but also be able to view the ticket in it's lifecycle.

Record the activity of the ticket as a transactional fact. The ticket itself should be a degenerate dimension. Things about the ticket should be dimensions off the fact (requestor, who worked on it, etc...). Have a row for each action on the ticket (open, close, re-open, worked on, transferred, etc).

It would be misleading from a metrics standpoint to treat each re-opening of a ticket as a new ticket as you would not be able to gather any real quality statistics (how long did it really take to resolve it) and would have no idea how much re-work is going on.

After some discussion that's what we ended up doing! Each Transaction is a record in our Fact table and using some mock data we were able to get the metrics to report correctly.

Thanks!

When there is time of the day involved in the transaction grain fact, Kimball suggested, in his later book, to have a degenerate datatime field alongside with a date key, instead of a single FK pointing to a million record date-time dimension. Kimball has also given up the idea of having separate date and time dimensions as he realised that the query became too complex when trying to compute time spans that cross daily boundaries.

I also noticed that most of the posts are talking about the transaction fact which is important as one part of the solution to the titled task. However you may still need to have an accompanying accumulating snapshot fact based on Kimball’s idea to let ETL work out the measures at each stage of the life cycle.