Question / Problem:
Is it possible to determine how much data has been processed by the reporting service in the past?
Answer / Solution:
The ETL process processes the raw reporting data in the reporting staging database into the data in the reporting database. The following diagnostic query can be run against the TotalAgility_Reporting database when troubleshooting reporting issues to determine the rate of processing over time. This can help determine if a problem started or stopped on a particular date.
DECLARE @days INT ; SET @days=7 DECLARE @dt DATETIME DECLARE @dtlast DATETIME SELECT @dtlast = MAX(etl_end_time) from etl_job --look at records for all time (slow) or last x number of days --SELECT @dt = MIN(etl_end_time) from etl_job --all time SELECT @dt = DATEADD(DAY,-@days,@dtlast) from etl_job --last x days CREATE TABLE #results (dt_execution_plus_minus_2hrs datetime, seconds_of_data_processed int, dt_start_time datetime, dt_end_time datetime); WHILE (@dt < @dtlast) BEGIN SELECT @dt = DATEADD(HOUR, 4, @dt) INSERT into #results select @dt, DATEDIFF(SECOND, MIN(bssf.dt_end_datetime), MAX(bssf.dt_end_datetime)), MIN(bssf.dt_end_datetime), MAX(bssf.dt_end_datetime) FROM batch_sess_snapshot_fact bssf JOIN etl_job ej on (bssf.etl_job_key = ej.etl_job_key) WHERE ABS(DATEDIFF(HOUR, ej.etl_end_time, @dt)) <= 2 END SELECT * FROM #results --WHERE seconds_of_data_processed IS NOT NULL --ignore sections of time with no processing DROP TABLE #results;
To use the query in a tenant database of an OPMT system, add either “live.” or “dev.” (depending on environment) before each occurrence of the table names. Tables names used here are etl_job and batch_sess_snapshot_fact.