accummulating snapshot aggregate design

View previous topic View next topic Go down

accummulating snapshot aggregate design

Post  mfinlay on Mon Mar 07, 2011 11:15 am

Hi all,

I have a design issue that I was hoping to get some comments on.

Assume we have a IT helpdesk calls system we want to model. helpdesk calls can be raised, and ultimately resolved, with a few stages in between. This fits an accumulating snapshot table nicely, having a grain of a single helpdesk call, and we have this fact table in use to report on the current status of a call. This is an easy/obvious design as it largely mimics the data in the source system.

There is also a requirement to report on the percentage of resolved calls on a given day. What appears to be a spanner in the works is that this calculation should be the number of calls resolved on a given date / the number of calls raised on that date. Eg given the following data in the accumulating snapshot:

Code:
Call Id  raised date  resolved date  call count  resolved count
-------- ------------ -------------- ----------- ---------------
1        15-FEB-2011  20-FEB-2011    1          1
2        15-FEB-2011  15-FEB-2011    1          1
3        20-FEB-2011                1          0
4        20-FEB-2011                1          0
5        20-FEB-2011  20-FEB-2011    1          1
6        21-FEB-2011  21-FEB-2011    1          1
The requirement is to see:

Code:
Date            raised count  closed count  percentage
------------  ------------- ------------- -----------
15-FEB-2011  2                1            50%
20-FEB-2011  3                2            66.6%
21-FEB-2011  1                1            100%

This crucially means there is no real relation between the raised calls and the closed calls for this calculation, and in theory the percentage could be over 100%.

At first I didn't think this was going to be much of an issue. I can't report this easily from the accumulating snapshot fact, so I thought we could just build a transaction fact, with a grain of helpdesk call status. This would have a new dimension of helpdesk status, containing Raised, Resolved etc. I have a few issues with this (eg the significant increase in volume of data) but the main one is that this is still not a straightforward calculation - I need to pivot the data, and limit the data to these two statuses. This is of course perfectly possible in reporting tools, but this percentage measure is a key metric and I really need to embed it into the reporting tool and not require the users to rebuild it on each report.

So that brings me round to some sort of modified aggregate table from the accumulating snapshot, where I can populate the raised/closed counts for each reporting date in separate columns during ETL. This is the solution I'm currently building, but this doesn't quite seem right as I lose the detailed information and can't drill easily to the accumulating snapshot fact to get the detail of either the calls or resolutions on a given date. My new reporting date has no relationship to the raised date or the resolved date as far as the reporting tool (Oracle Business Intelligence) is concerned, so it's not easy to pass this reporting date to another report to limit the data.

I can't seem to arrive at a neat solution, am I missing something? It appears to be such an innocuous requirement......

Thanks all
Matt

mfinlay

Posts : 1
Join date : 2010-01-04

View user profile

Back to top Go down

accummulating snapshot aggregate design

Post  Ashish Mishra on Mon Mar 07, 2011 11:50 am

Hi,

Incase if you are using OLAP based reporting than you can
create a custom measure using both raised date and resolved date as dimension
using MDX expression that should resolve the issue

Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: accummulating snapshot aggregate design

Post  ngalemmo on Mon Mar 07, 2011 1:27 pm

I can't report this easily from the accumulating snapshot fact, so I thought we could just build a transaction fact, with a grain of helpdesk call status.

What you go on to desribe as a transaction fact is, basically, an accumulating snapshot. An accumulating snapshot records progressive states with each row tagged with an effective/expiration date.

If you prefer to stick with your original design, the table should include date keys for each date a particular status occurs. The downside here is that, unless the number of statuses are fixed and relatively small, you have the problem of a wide fact table and potential future maintenance if the number of statuses change. In general, it is better to go tall rather than wide... a status dimension is not a bad idea.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: accummulating snapshot aggregate design

Post  meb97me on Tue Mar 08, 2011 11:56 am

What about athe Fact table looking something like this?

FactIDCallIdDateEvent/StatusEventCount
1Call115-FEB-2011Call Raised1
2Call115-FEB-2011Call Resolved1
3Call215-FEB-2011Call Raised1
4Call320-FEB-2011Call Raised1
5Call320-FEB-2011Call Resolved1
6Call420-FEB-2011Call Raised1

you could then quite easily create a calculation using MDX in the Cube far a "Resolved %"

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: accummulating snapshot aggregate design

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