accummulating snapshot aggregate design
4 posters
Page 1 of 1
accummulating snapshot aggregate design
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:
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
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
- 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
accummulating snapshot aggregate design
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
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
Re: accummulating snapshot aggregate design
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.
Re: accummulating snapshot aggregate design
What about athe Fact table looking something like this?
you could then quite easily create a calculation using MDX in the Cube far a "Resolved %"
FactID | CallId | Date | Event/Status | EventCount |
1 | Call1 | 15-FEB-2011 | Call Raised | 1 |
2 | Call1 | 15-FEB-2011 | Call Resolved | 1 |
3 | Call2 | 15-FEB-2011 | Call Raised | 1 |
4 | Call3 | 20-FEB-2011 | Call Raised | 1 |
5 | Call3 | 20-FEB-2011 | Call Resolved | 1 |
6 | Call4 | 20-FEB-2011 | Call Raised | 1 |
you could then quite easily create a calculation using MDX in the Cube far a "Resolved %"
meb97me- Posts : 34
Join date : 2010-07-28
Similar topics
» Accumulating Snapshot and Transaction Fact tables : question to design and use them together
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Accumulating Snapshot and Transaction Snapshot
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Accumulating Snapshot and Transaction Snapshot
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum