Historical Data from Indicator Source Best Practice

[Mike Rae]

Initially my Indicator Source only pull records created ON TODAY.   The problem I had was when I ran a historical job it only pulled records for the current day and 0 records or all historic days. As a solution I followed an OOB example I found here on the community to use ON TODAY and BEFORE TODAY shown below.

The new indicator source works great but every day it collects a lot of extra data. What I'm confused about is Isn't this going to collect all records everytime the job runs going forward?   So when my daily job runs overnight, will it pull all records from the source table?  

Is this the best way to do this, or am I missing something?

Thanks,

Mike

[Tara Romero]

Hi Mike,

Using on or before today means that you are pulling the same records everyday, instead of the net new records daily.   You really should be using created on today.   Can you tell me, does that table get purged?   I don't understand why you would get zero records for your historical data.   Can you send me a quick pic of what your job collection looks like?

Thanks

Tara

[Mike Rae]

The table does get purged, but only every 5 days or so.   When I run the job with only ON TODAY I see the following logs:

SELECT sysevent0.`sys_id` FROM sysevent sysevent0 WHERE (sysevent0.`name` = 'login' OR sysevent0.`name` = 'impersonation.start') AND (sysevent0.`sys_created_on` >= '2017-08-29 07:00:00' AND sysevent0.`sys_created_on` <= '2017-08-30 06:59:59')

This returns the expected records for the first day. Then the query for the second day:

SELECT sysevent0.`sys_id` FROM sysevent sysevent0 WHERE (sysevent0.`name` = 'login' OR sysevent0.`name` = 'impersonation.start') AND (sysevent0.`sys_created_on` >= '2017-08-28 07:00:00' AND sysevent0.`sys_created_on` <= '2017-08-29 06:59:59')

I know there is data there but it returns 0 records

[Josh Cooper]

Sure - the best practice is, you run the historical job once whenever you create the new Indicator to backfill the scores as much as you can, then set up a daily version of the same job to run each night, for the previous day.

Your criteria when setting up the indicator source should be based on Today, (like closed on today (Closed),   or created on today (New), or opened on today or before today and closed after today or closed it blank (Open).

When you do the historical job processing, PA interprets "Today" as "This Day" so even with "Today" selected, it sets Today to be the day it's currently pulling the records for, so if it starts 10 days ago, then in its context, Today is that day ten days ago.

A gotcha about historic jobs though, is that we don't have a way to see what values of fields were on a particular day, so for example, on a historic pull from 30 days ago, you can see what records were Open because the Opened and Closed are dates.   But when you add the "State" breakdown, the state for almost all of them will be "Closed" because the record that was open THEN is state = closed NOW.   That's why you want your historical runs to be one-offs... because any data is better than no data in most cases, but data collected daily is going to be the most accurate.   The other reason that's important is that whenever it collects scores, the first thing it does is delete the score for that day/indicator/breakdown.   So even if you have accurate data, then your historical data pull will delete it and replace it with less accurate data based on that current context.

So just to clarify - indicator sources should always be based on a today (wherever possible, there are FEW places it's not possible, like if you want running totals of ALL records - but depending on how much data that is you should avoid that situation.   Imagine 15 years from now running a nightly report to breakdown states on all the incidents between now and then!   Also, you'd use "this month" or "this week" or "this quarter" if you're wanting those kind of indicators).   But generally speaking, indicator sources should always be based on today, for daily - historical jobs should always be set to execute only onDemand, and then the Daily job will populate the most accurate scores going forward.

Hopefully that helps!

[pieter_goris]

Hi Mike,

you can also check in you indicator source the number of records it will select. So if you go to the indicator source and

-change the condition to 'created on yesterday' (instead of Today),

-delete the before Today condition

(-and not save the source)

-click the double sided yellow arrows:

the blue link should show how many records match your conditions. Clicking the blue link opens a new tab with a list of those records.

If these is more than zero, than check if there any additional conditions on your indicator (not indicator source!) that possible limit the number of records retrieved during scores collection. As Josh mentioned in his reply, when you want to do a historic score collection, do not use fields in your conditions for which the values changes during the lifecycle of the records (i.e. in this case 'events'). You will not retrieve an accurate historic number.

You can also try to build a report for a specific date for which you expect records. The condition builder in reporting is the same as the one in performance analytics.

Hope this helps in the troubleshooting.

Cheers, Pieter