computers, information technology, data center, gray computer, gray technology, gray laptop, gray data, gray information, data center, data center, data center, data center, data center
Photo by tstokes on Pixabay

BI Architecture

BI system performance and cost control

Measure BI delays and recurring costs, locate the responsible workload, and check accuracy and freshness before accepting a change.

This is an editorial pen name used by the publication, not a claim of an independently verified personal identity. Original editorial credit: Desk Data Platform.

Manage BI performance and cost by measuring the reporting path, then changing the work behind avoidable delay or spend. A slow report may wait on a source query, model calculation, visual or shared capacity. Recurring queries, refreshes, deliveries and licences each need different cost checks, and the billing unit differs by service: Snowflake bills warehouse credits, while Microsoft Fabric capacity is sized in capacity units (CUs).

Establish a baseline

Choose maintained reports that support recurring decisions. Record the reader, required data cutoff, busiest use period and acceptable wait. For each measurement, keep the report and model versions, connection mode, filters, data volume, cache state and concurrent work. Repeat the same page load and common interaction under comparable conditions.

Measure scheduled work as well as what the reader sees. Keep usage and billing records for the same observation period, and express cost per unit of work, such as credits per scheduled refresh or licence cost per active reader. Elapsed query time alone is not a bill: the charge depends on the service and the organisation's account terms.

In Snowflake, credit use doubles with each step up in warehouse size for each full hour the warehouse runs. Billing is per second, with a 60-second minimum each time the warehouse starts, so the credits billed depend on how long it runs continuously.

Part of the pathUseful observationQuestion to ask
SourceQuery duration, work processed and concurrent jobsIs the report reading more than its decision needs?
ModelCalculation and refresh behaviourIs shared logic repeating expensive work?
ReportVisual and interaction timingWhich action delays the reader?
Scheduled workRuns, failures and overlapDoes each run serve a current output and required cutoff?
AccessAssigned licences and observed useDoes each paid entitlement still support a task?

Cost and Performance Metrics Across BI Platforms (Australia)

Platform
Microsoft Fabric
Billing Unit
Capacity Units (CUs)
Key Cost Driver
Compute capacity and concurrent user load
Platform
Snowflake
Billing Unit
Warehouse credits
Key Cost Driver
Warehouse size and continuous runtime (min 60 seconds per start)
Platform
Google BigQuery
Billing Unit
Bytes processed
Licence Model
Pay-per-query with reserved slots available
Key Cost Driver
Data scanned and job duration

Steps to Measure and Optimise BI System Performance and Cost in Australia

  1. Select key reports used for recurring decisionsEnsure they reflect real business use, including peak usage times and data cutoffs.
  2. Record baseline metrics under consistent conditionsInclude report version, connection mode, filters, cache state and concurrent activity.
  3. Track both user-facing and scheduled workMeasure cost per refresh, licence cost per active reader, and query execution time.
  4. Use diagnostic tools to identify bottlenecksPower BI Performance Analyzer, Snowflake Query History, BigQuery INFORMATION_SCHEMA.JOBS.
  5. Distinguish execution from waiting timeCheck queueing in Snowflake’s Warehouse Activity chart; assess idle time before scaling.
  6. Review recurring demand and ownershipConfirm frequency, overlap, and actual user activity; validate licence assignments.
  7. Apply cost controls and monitor thresholdsSet credit limits in Snowflake, pause unused Fabric capacities via Azure runbooks.
  8. Document the change and its impactRecord baseline, proposed change, owner, expected effect, and retained metrics.
  9. Assess reach of shared changesCheck all dependent reports and models; consider storage mode (Import, DirectQuery, Composite).

Manage the whole operating path

Manage the operating path across data sources, the data model, visualisations and the environment, including capacities, data gateways and the network. In Microsoft Fabric, the capacity's F SKU sets its compute: CUs measure the compute power for each SKU, so a larger SKU such as F64 buys more compute.

Place an observed constraint in its likely layer before choosing a response. This helps avoid changing a report when the limiting factor sits in shared capacity or connectivity, and gives the relevant owner a clearer decision to make.

A Power BI semantic model can support reports in Desktop and the browser, explorations, paginated reports, DAX queries, Excel reporting and exported visual data.

Licences and capacity interact. On F SKUs smaller than F64, each user viewing Power BI content must have a Pro, Premium Per User (PPU) or individual trial licence. On F64 or larger, users with only a Free licence and a viewer role can view Power BI content, so compare the capacity step with the per-user licences it would replace.

Choosing Between Power BI Fabric and Snowflake for Australian SMEs

  • Cons: Microsoft FabricSmaller SKUs require Pro or PPU licences for each user; cost scales with user count
  • Pros: SnowflakeHigh scalability, granular query profiling via Query History and Profile; auto-suspend reduces idle costs
  • Cons: SnowflakeCredit cost increases exponentially with warehouse size and continuous runtime; minimum 60-second billing window

Locate the delay

Power BI's Performance Analyzer, available when editing a report, shows how long each visual takes to load and breaks load time down into categories, including the time taken by the DAX query. Use these timings as clues, then inspect the source, gateway or capacity where relevant.

BigQuery's INFORMATION_SCHEMA.JOBS view provides metadata about jobs in the current project. Snowflake Query History and Query Profile can help locate long-running work, subject to the user's access and the available history. Neither diagnostic view establishes a saving until the changed workload is measured.

Try a change aimed at the identified cause: a narrower query, simpler calculation, necessary visuals only, a different refresh time or a job with a confirmed lack of consumers. Check the figure, access and freshness after the change. A faster report with a changed population has missed its purpose.

Separate work from waiting

Distinguish time spent executing work from time spent waiting for shared resources. In Snowflake, Snowsight's Warehouse Activity chart shows warehouse load, including whether queries were queued; use queueing as a shared-workload signal when deciding whether the response belongs at report or environment level.

Historical task execution times can also help show whether scheduled transformations contribute to a busy period. Consider that evidence alongside reader-facing response and the agreed cutoff, so a change to recurring work does not improve one measure by undermining another.

Before moving to a larger warehouse, check the idle time. Auto-suspend stops a warehouse running, and consuming credits, when no queries arrive, while auto-resume starts it again when needed.

Review recurring demand

Register interactive queries, refreshes, exports and scheduled deliveries with their owners and consumers. Frequency matters: modest work repeated many times can use substantial resources. Overlapping jobs may also cause a delay absent from a quiet test.

Compare each run with the agreed cutoff and delivery need. For licences, compare assignments with activity, then confirm unusual cases with the owner. An annual user or someone receiving a scheduled output may look inactive in a short usage window. Confirm purchase terms before reporting a monetary saving from an entitlement change.

Put limits on spend as well as reviewing it. A Snowflake resource monitor sets a credit limit for an interval or date range and, at a threshold, can send notifications or suspend the assigned warehouses; only the ACCOUNTADMIN role can create one.

A Fabric capacity that is not operational can be paused to save cost, and pause and resume can be scheduled with an Azure runbook. Resuming it, for example in the Azure portal, also resumes billing, and content assigned to the capacity becomes available again only once it is resumed. Move scheduled refreshes and deliveries out of the paused window first.

Key BI Cost and Performance Indicators for Australian Organisations

Average Credit Usage (Snowflake)
Doubles per warehouse size step; billed per second (min 60 sec)
BigQuery Cost Factor
Based on bytes processed per job; optimise queries to reduce scan volume
Scheduled Refresh Overlap Risk
Can cause delays even if individual jobs are fast
Auto-Suspend Benefit (Snowflake)
Reduces idle credit spend by stopping warehouses when inactive

Record the decision

Keep the baseline, proposed change, owner, expected effect and figures that must remain correct. Compare before and after using the same reporting population and representative workload where possible, for example credits per day for the warehouse or the capacity's use in the Microsoft Fabric Capacity Metrics app. State when a changed source, workload or contract limits the comparison.

Consider migration separately if the current arrangement cannot meet a defined requirement at an acceptable operating cost. Preserve metric definitions and reference results so a proposed replacement can be assessed for accuracy, access, freshness, performance and cost.

Pre-Change Validation Checklist for BI Performance and Cost Optimisation

  • Baseline metrics recorded?Yes – include report version, filters, cache state, and concurrent load
  • Change target identified (e.g., source, model, visual)?Yes – based on Performance Analyzer or query logs
  • Impact on other reports confirmed?Yes – check dependencies in semantic model and shared datasets
  • Freshness and accuracy preserved?Yes – ensure data cutoff and population remain valid post-change
  • Cost controls in place?Yes – e.g., Snowflake resource monitors, Fabric capacity pauses

Assess the reach of a change

A change to a shared model can affect more than the maintained report used for the baseline. Check which other reporting and analysis uses depend on it, and include their required figures and freshness in the change decision.

Power BI semantic models can use Import, DirectQuery or Composite table storage modes. Record the relevant mode when describing the current arrangement, and treat choosing a different mode as a design decision to assess against the solution's requirements, not as an automatic performance or cost saving.

In this guide

  1. Diagnosing slow dashboards before replacing the toolReproduce a slow dashboard action, locate the delay, and test whether a targeted fix meets the reader’s need before considering replacement.
  2. Reducing expensive queries in recurring reportsFind repeated report queries, reduce unnecessary reads and runs, and verify the result under the relevant billing model.
  3. Monitoring unused BI licences and scheduled jobsCompare BI entitlements and scheduled jobs with actual tasks, telemetry coverage and dependencies before changing access or schedules.
  4. Planning a BI platform migration with reproducible metricsPlan a BI migration using versioned metric definitions, fixed reference data and explicit checks for accuracy, access, freshness and performance.

More from BI Architecture