I am working with service/maintenance notifications and I need a report the see how many notifications were open at a specific time.
So for example:
So this reports says that on 31.01.2013 I had 5 notifications in the state "open" - no matter when the notification was created.
There is a DataSource "0PM_OBJ_STATUS" which gives me every status-change for a single notification.
Notification 1234:Date Status User inactive 01.01.2013 open XXX
so I get the info when the status starts and when it ends.
So my problem now is, that in this example I have an entry in my cube for 01.2013, 03.2013, 05.2013 but no info what was the state in 02.2013 and 04.2013.
Is there a best practice how to handle such requirements?
I have some ideas like:
1. Create a "snapshot" on each last day of a month. (But in this case I loose everything if I need a complete reload of the data)
2. expert routine to create a entry for each month (but in this scenario i always have to make a full load which is not good cause there are a lot of notifications and even more status changes for the notifications)
every help is welcome!
Thanks in advance