UpdateDashboardTables

Overview

UpdateDashboardTablesTask regenerates pre-aggregated tables for all dashboard elements defined in the system. It iterates through every dashboard report, creates optimized aggregate tables for each element (charts and measures), and updates the dashboard definitions with the new table references.

Use this task when: You need to refresh all dashboard data after source data changes, on a scheduled basis (nightly/hourly), or after adding/modifying dashboard configurations.

Don't use this task when:

• Refreshing a single specific dashboard (no single-dashboard task exists; this refreshes all dashboards)
• Source data hasn't changed since last refresh (unnecessary resource usage)
• During peak business hours (can be resource-intensive depending on dashboard complexity)

Parameters

Execution Flow

Processing Algorithm

The task creates pre-aggregated tables to optimize dashboard performance. For each dashboard element, it:

Table Generation Process

1. Generate Table Name: Creates a unique table name using the pattern:

   Dashboard_{ReportKey}_{ElementName}_{Timestamp}
   

   All special characters are converted to safe column names.

2. Build Aggregation SQL: Constructs a SELECT ... INTO query that:

   • Selects all global filters (from DashboardReport.Filters)
   • Selects the time dimension (if specified)
   • Selects element-specific filters/dimensions (from DashboardChart.Dimensions or DashboardMeasure.Filters)
   • Aggregates measures using expressions (e.g., count(*), sum(Amount))
   • Groups by all dimension columns
   • Sources data from the element's DataQuery

3. Execute and Update: Runs the SQL, creates the table, and updates the element's GeneratedTable property

Example Execution

For a dashboard element "Visit Count by Provider" with:

• Global filter: Facility
• Time dimension: ScheduledDate
• Chart dimension: Provider
• Measure: count(*) as VisitCount
• DataQuery: SELECT * FROM Visit

The generated SQL would be:

SELECT * INTO Dashboard_ProviderReport_VisitCount_2025_01_15T14_30_00
FROM (
    SELECT [Facility], [ScheduledDate], [Provider], count(*) as [VisitCount]
    FROM (SELECT * FROM Visit) as dQ
    GROUP BY [Facility], [ScheduledDate], [Provider]
) as preAgg

This pre-aggregated table is then used for all dashboard queries, dramatically improving performance.

Usage Examples

Example 1: Basic scheduled refresh

// Run nightly to refresh all dashboard data
var task = new UpdateDashboardTablesTask();
// No parameters needed - processes all dashboards automatically

Example 2: Part of a data loading pipeline

// After loading new visit data, refresh dashboards
var loadTask = new LoadDataFileTask
{
    DataFileSpecKey = "DailyVisits",
    FilePath = @"C:\imports\visits.csv",
    ImportMode = ImportMode.Append
};

// Then update all dashboards to reflect new data
var dashboardTask = new UpdateDashboardTablesTask();

Example 3: Manual refresh after configuration changes

// After adding new dashboard elements or modifying existing ones,
// regenerate all pre-aggregated tables
var task = new UpdateDashboardTablesTask();
// This will create tables for new elements and recreate existing ones

Related Tasks Comparison

Key Characteristics:

• UpdateDashboardTables is an all-or-nothing task
• No granular control over which dashboards or elements to refresh
• Creates new tables with timestamps; old tables remain until manually cleaned up
• ProcessLog entries track each element's creation status

Performance Considerations

Database Impact

• Tables Created: One new table per dashboard element (chart or measure group)
• Old Tables: Previous GeneratedTable tables are dropped before creating new ones
• Query Complexity: Depends on dashboard element DataQuery complexity
  • Simple queries: SELECT * FROM Visit → Fast aggregation
  • Complex queries: Multi-table joins, subqueries → Slower aggregation
• Aggregation Operations: Uses SELECT INTO for table creation (fast bulk operation)
• Locking: Minimal - each table creation is independent

Optimization Tips