Averaging periodic counts?

[kranberry]

We have an application that will periodically report information, especially counts of how many times an event occurred. It will also report how long it has been since the last report. For example, I may get a record say event E occurred 20 times since the last report, which was 40 days ago.

My question is how do I handle this in a fact table? Should I divide the count by how many days since the last report and create a record for each day in that period with the average count? So in the example above, would I create a 40 records with a count of 0.50? Or is there a better way to represent this information?

Thanks.

 

[RiverGuy]

Ideally of course, you would want this at a very granular level--one record with a Date/Time that each occurrence happened.

But since you don't have that, I think you are onto the right track. Your 40 day-span example doesn't fit neatly into a Day/Month/Quarter/Semester/Year scenario, otherwise you could look at one record per month and aggregate up from there. So I think your average for the time-span is a good option. One thing to look at is that since it is an average, and not a true count per day, it will be hard to produce any meaningful analysis on attributes such as day of week.

 

[kranberry]

Thanks for the insight, RiverGuy.

At this point, most of the reports will be on a monthly basis so I think the analysis will be more meaningful.

 

[Hans63]

Actually you are talking about 2 different facts. The number of times an event E has occurred; the number of days since the last report.
I would only suggest to everage out if your reporting is to be done on a daily or so base.
I wonder where this questions originates from. It can be that the information question simply cannot be answered by the data you get. So you may have to change the frequency of the data you get, or the business must decide that averaging is a valid solution.

Let the business decide what information it wants, you can say what is and what is not possible.