Process mining dashboard not showing latest data after ETL job

BPM OutSystems
, , , , , , , ,
1

Our process mining dashboard is showing stale data even after the nightly ETL job completes successfully. The ETL job runs at 2 AM and finishes around 3:30 AM according to the logs, but when users access the dashboard at 8 AM, they’re still seeing yesterday’s data.

We’ve verified that the ETL job is actually loading new records into the database:


SELECT COUNT(*), MAX(load_timestamp)
FROM process_events
WHERE load_date = CURRENT_DATE;
-- Returns: 45,231 records, timestamp: 03:28:15

The dashboard data source mapping appears correct, and we can see the new data when we query the underlying tables directly. It’s like the dashboard widgets are cached and not refreshing automatically. Has anyone experienced similar issues with dashboard data not updating after ETL jobs complete?

2

I’ve implemented this exact solution before. The key is understanding the relationship between ETL job scheduling, dashboard data source mapping, and widget cache refresh. You need all three components working together. First, ensure your ETL job updates a metadata table with completion status. Then configure your dashboard data sources to check this metadata before serving cached results. Finally, implement a scheduled task that runs at 4 AM (after ETL) to explicitly refresh all dashboard widgets. This three-layer approach has worked perfectly for our compliance reporting dashboards.

3

This is almost certainly a widget cache issue. OutSystems process mining dashboards cache query results by default to improve performance. Check your dashboard settings - there should be a cache refresh policy. If it’s set to manual or has a long TTL, that would explain why you’re seeing stale data even though the ETL completed successfully.

4

Another thing to check: does your ETL job commit the transaction properly? If the ETL process doesn’t commit before the dashboard tries to read, you might be hitting transaction isolation issues where the dashboard can’t see uncommitted data even though the ETL thinks it’s done.

5

I checked the cache settings and found that the default TTL is set to 24 hours, which would definitely cause this problem. However, I’m not sure about the best approach to fix it. Should I reduce the TTL to something like 1 hour, or is there a way to trigger a cache refresh automatically when the ETL job completes? We need the dashboard to show fresh data by the time users start working at 8 AM.

6

Here’s the comprehensive solution addressing all three focus areas:

ETL Job Scheduling Enhancement: Modify your ETL job to include a completion notification step. At the end of your existing ETL process, add:


// Pseudocode - ETL completion handler:
1. Commit all data load transactions
2. Update ETL_JOB_STATUS table with completion timestamp
3. Call InvalidateDashboardCache() API method
4. Log cache refresh request to ETL_AUDIT_LOG
5. Send completion notification to monitoring system

Schedule this as the final step in your ETL workflow. The key is ensuring the cache invalidation happens immediately after data commit, not relying on scheduled refreshes.

Dashboard Data Source Mapping Configuration: Update your dashboard data sources to be cache-aware. In Service Studio:

  1. Open your Process Mining dashboard
  2. For each widget, edit the Data Source properties
  3. Set Cache Policy to “Smart Refresh” mode
  4. Configure Cache Invalidation Trigger: “On ETL Completion”
  5. Add a data freshness check query:

SELECT CASE
  WHEN MAX(load_timestamp) >= DATEADD(hour, -6, GETDATE())
  THEN 1 ELSE 0 END as is_fresh
FROM process_events

This ensures widgets check data freshness before serving cached results.

Widget Cache Refresh Implementation: Create a Timer in OutSystems that runs at 4:00 AM (30 minutes after your ETL typically completes):


// Pseudocode - Dashboard refresh timer:
1. Query ETL_JOB_STATUS for jobs completed after 2:00 AM
2. For each completed ETL job:
   a. Get list of affected dashboard widgets
   b. Call RefreshWidget() for each widget
   c. Verify refresh completion via API response
3. Update DASHBOARD_REFRESH_LOG with results
4. If any refresh fails, trigger alert

In addition, implement an on-demand refresh mechanism. Add a “Refresh Data” button to your dashboard that users can click if they need immediate updates:


// Button action logic
InvalidateAllWidgetCache();
ReloadDashboardData();
ShowSuccessMessage("Dashboard refreshed with latest data");

Critical Configuration: In Integration Studio, configure the dashboard service connection:

  • Enable “Post-ETL Auto Refresh”
  • Set refresh timeout to 300 seconds (some large dashboards take time)
  • Configure retry logic: 3 attempts with 30-second intervals

Verification Checklist:

  1. Confirm ETL job logs show cache invalidation call
  2. Check dashboard audit logs for refresh events at 4 AM
  3. Verify widget query timestamps match ETL completion time
  4. Test manual refresh button functionality
  5. Monitor dashboard load times (cache rebuilding may slow first access)

This three-pronged approach ensures your ETL job scheduling triggers cache refresh, dashboard data source mapping validates data freshness, and widget cache refresh happens automatically. The combination eliminates stale data issues while maintaining good dashboard performance.