TotalAgility Reporting Service Processing Rate

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 @HoursTotal INT ; SET @HoursTotal=24
DECLARE @MinutesPerSegment INT ; SET @MinutesPerSegment=60
DECLARE @SegmentStart DATETIME ; DECLARE @SegmentEnd DATETIME;
DECLARE @LastETL DATETIME

--Last ETL job rounded to next hour
SELECT @LastETL = MAX(etl_end_time) from etl_job
SELECT @LastETL=dateadd(HOUR, datediff(HOUR, 0, @LastETL)+1, 0)
 
--Start X hours ago
SELECT @SegmentStart = DATEADD(HOUR,-@HoursTotal,@LastETL) from etl_job
        
CREATE TABLE #results (SampleStart DATETIME, SampleEnd DATETIME, seconds_of_data_processed int,
EarliestBatchSessionEndTimeProcessed datetime, LatestBatchSessionEndTimeProcessed datetime, BatchSessionCount BIGINT);
        
WHILE (@SegmentStart < @LastETL)
BEGIN
SELECT @SegmentStart = DATEADD(MINUTE, @MinutesPerSegment, @SegmentStart)
SELECT @SegmentEnd=DATEADD(MINUTE, @MinutesPerSegment, @SegmentStart)
INSERT into #results select @SegmentStart, @SegmentEnd, DATEDIFF(SECOND, MIN(bssf.dt_end_datetime), MAX(bssf.dt_end_datetime)),
    MIN(bssf.dt_end_datetime), MAX(bssf.dt_end_datetime), COUNT(*)
FROM batch_sess_snapshot_fact bssf
JOIN etl_job ej on (bssf.etl_job_key = ej.etl_job_key)
WHERE ej.etl_end_time BETWEEN @SegmentStart AND @SegmentEnd
END
  
SELECT * FROM #results
WHERE 1=1
    --AND seconds_of_data_processed IS NOT NULL --ignore sections of time with no processing
ORDER BY SampleStart DESC
  
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.