Data refresh architecture is the design of the complete system for keeping BI datasets current — covering the schedule or trigger for each dataset refresh, the refresh method (full reload versus incremental), the dependency sequence between datasets (dimension refreshes must precede fact refreshes), the source system impact of refresh queries, and the monitoring and failure handling that ensures the finance team knows immediately when a refresh fails or completes late. It is the operational backbone that determines whether the CFO’s morning dashboard reflects last night’s close data or data from three days ago. For finance BI, data freshness is not a technical nicety — stale data in a management dashboard used for operational decisions has direct business consequences.
How It Works
A finance BI refresh architecture typically involves multiple layers, each refreshed on different schedules with different methods:
| Layer | Typical Refresh Method | Frequency |
|---|---|---|
| Raw data landing (lake/warehouse) | Incremental extract from ERP | Nightly or intraday |
| Dimension tables (conformed) | Full reload or SCD-aware merge | Nightly, after raw load completes |
| Fact tables | Incremental refresh by period key | Nightly, after dimensions complete |
| Aggregate fact tables | Full or partitioned reload | Nightly, after fact tables complete |
| Power BI datasets | Incremental or full, triggered by pipeline completion | Nightly post-pipeline completion |
| Real-time KPI tiles | DirectQuery or push dataset | Live (no scheduled refresh) |
The dependency chain must be respected: if a Power BI dataset refresh begins before the warehouse fact table load is complete, the dataset will capture a partial load. In Azure Data Factory, Synapse Pipelines, or Airflow, this dependency is enforced through pipeline orchestration — the dataset refresh is triggered only upon successful completion of the upstream warehouse load, not on an independent schedule.
Design Decisions and Trade-offs
The source system load during refresh queries is a critical but frequently overlooked design consideration for ERP-connected finance BI. An overnight ERP extract that runs during the finance team’s off-hours but during business hours for a subsidiary in a different time zone can degrade ERP performance for that subsidiary’s users. Oracle EBS and SAP extract queries against large GL tables are resource-intensive; they should be scheduled against production replicas or read-only standby databases where possible, not against the live transactional ERP.
Common Implementation Errors
The implementation error that produces the most consistently late finance BI refreshes is scheduling the Power BI dataset refresh for a fixed time — say 06:00 — rather than triggering it upon completion of the upstream warehouse pipeline. When the warehouse pipeline runs long on month-end (because it processes more data than typical weeknights), the 06:00 Power BI refresh begins while the warehouse load is still in progress, capturing partial data. The finance team sees a dashboard showing incomplete actuals for the close period, without any error indication — the refresh succeeded against a partially loaded warehouse. Event-triggered refresh — the Power BI refresh starts when the warehouse pipeline sends a completion notification — eliminates this class of partial-load error.
How Loop Wise Solutions Designs for This
We design data refresh architectures with event-driven triggers throughout — each layer is triggered by the successful completion of the layer it depends on, not by an independent schedule. We also implement a freshness indicator in every finance dashboard: a visible “data as of” timestamp derived from the latest record in the fact table, so that finance users can immediately see whether the dashboard reflects current data without investigating the refresh history.
