Accumulating Snapshot that acts like a SCD

View previous topic View next topic Go down

Accumulating Snapshot that acts like a SCD

Post  CJenner on Tue Nov 02, 2010 7:32 pm

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.

CJenner

Posts : 4
Join date : 2010-03-18

View user profile

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  BoxesAndLines on Wed Nov 03, 2010 9:17 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  CJenner on Wed Nov 03, 2010 10:23 am

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

CJenner

Posts : 4
Join date : 2010-03-18

View user profile

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  BoxesAndLines on Wed Nov 03, 2010 12:06 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  CJenner on Wed Nov 03, 2010 12:56 pm

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.

CJenner

Posts : 4
Join date : 2010-03-18

View user profile

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  ngalemmo on Wed Nov 03, 2010 7:20 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  CJenner on Wed Nov 03, 2010 7:50 pm

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!

CJenner

Posts : 4
Join date : 2010-03-18

View user profile

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  hang on Wed Nov 03, 2010 10:18 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Accumulating Snapshot that acts like a SCD

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum