I am looking for an example of a data model to create a reporting dashboard that shows compliance with time-based targets. The first type of question I need to answer is:
We have a standard that some action must be taken every day for a person in the system. What percent of the time do we meet that standard?
One approach I started was to create a reference date and then calculate the denominator as all individuals who were in the system on that date, then count the associated data in each interval. Example:
Person added 1/1. Person left on 1/7.
create reference date rows for 1/1-1/7
load associated data for the actions
create a dimension for the account number of the person based on the action being true
As a daily census, this works. We had 1000 people here today. We had 995 unique people with the desired action taken today. so: 99.5%.
This approach does not scale down to finer granularity as we now need to consider 12-hour or 8-hour intervals. For example if the date bins are 8 hours, and the measured actions occur at 7am, 9am, and 11pm, this would result in an action being counted for each of the 8 hour bins, when, in fact, the 9am-11pm gap is a failure. So what I really need is to consider, for each person, is whether a required action is taken every 8 hours or less? This might be better described in the negative: for each person, on any given day, are there any gaps in coverage greater than 8 hours?
To make the dashboard actionable, the next, immediate, obvious need is to be able to generate a list of all the people who are not meeting the goals so we can drill down and understand why. The dashboard that scores compliance is typically good at filtering out; but how to then select the inverse set of individual users.
Does anyone have a simple version of such a model? Some examples could be:
Once a customer has opened a support ticket, do we communicate with them every 8 hours?
Once a patient is admitted to the hospital, do we take vital signs at least every 8 hours?
