How to avoid refreshing full data everytime on refreshing in on premises PowerBI Dashboards

Question

How to avoid refreshing full data everytime on refreshing in on premises PowerBI Dashboards

Syed Uzair 0

I work in a finance corporate where there is a lot of data in our on premises PowerBI dashboards from either sql or excel, so on regular basis we face challenges on refreshing all the dashboards as every time it refreshes from the beginning, so I want a solution that would refresh all the tables in the dashboards not from the beginning but from after it has been refreshed, please guide a solution

0 comments

1 answer

Your answer

Answer 1

Power BI Report Server cannot incrementally refresh an embedded Import-mode PBIX model; to avoid reloading its complete model, move the reusable model to an on-premises SSAS Tabular instance, partition its large tables, process only changed partitions, and connect the PBIRS report to SSAS using a live connection.

PBIRS scheduled refresh loads new data into its internal Analysis Services model, but it doesn't expose partition management for the embedded model. Incremental refresh is a Power BI service capability rather than a listed PBIRS feature; PBIRS supports imported data with scheduled refresh, DirectQuery, or live connections to SSAS ^1^ ^2^.

Recommended fully on-premises design: SSAS Tabular partitions

This is an architecture change, not a PBIRS report setting. It requires:

An appropriately licensed and supported SSAS Tabular instance.
Visual Studio/SSDT for model development and deployment.
SSAS deployment and processing permissions.
SQL Server Agent, PowerShell, or another scheduler.
Coordination with the database, security, and BI-platform administrators.

There is no built-in “move embedded PBIX model to SSAS” command. Recreate or migrate the model metadata in an SSAS Tabular project, validate DAX compatibility, and expect to rebind or rebuild the report visuals against the SSAS live connection.

Stage Excel data in SQL Server.
Load recurring Excel files into durable SQL staging/fact tables first. Files don't provide a reliable server-side partition-processing architecture.
Build the SSAS Tabular model.
Recreate the PBIX tables, relationships, measures, calculated columns, and security roles in the Tabular project.
Partition large fact tables by a stable date key.
For example:
- Closed historical years: one partition per year, refreshed rarely.
- Current year or month: daily/monthly partitions, refreshed frequently.
- Recent adjustment window: reprocess enough recent partitions to capture late postings, reversals, and corrections.
Every partition query must use a unique, non-overlapping range. SSAS partitions can be processed independently, avoiding unnecessary processing of unchanged history ^3^.

-- Example current-month partition
SELECT TransactionId, PostingDate, AccountId, Amount
FROM dbo.FactFinance
WHERE PostingDate >= '2026-10-01'
  AND PostingDate <  '2026-11-01';

Deploy and initially process all partitions once.
Process only the current and adjustment-window partitions afterward.
Processing a partition reloads that entire partition—it does not append only rows added since the last run. Choose daily or monthly granularity according to volume and how far back finance adjustments can occur.

Example TMSL for one partition:

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "FinanceModel",
        "table": "FactFinance",
        "partition": "FactFinance_2026_10"
      }
    ]
  }
}

Save this as C:\SSAS\RefreshFinancePartition.json, then schedule a SQL Server Agent PowerShell job step:

$ErrorActionPreference = "Stop"
$server = "SSAS01"
$file = "C:\SSAS\RefreshFinancePartition.json"

Invoke-ASCmd -Server $server -InputFile $file
if ($LASTEXITCODE -ne 0) {
    throw "SSAS partition processing failed."
}

TMSL can be executed through SSMS, Invoke-ASCmd, or SSIS ^3^.

Create the PBIRS report using a live SSAS connection.
Use the Power BI Desktop version optimized for your PBIRS release. A live-connected report doesn't import another copy of the model; SSAS is refreshed independently. PBIRS supports live connections to on-premises SSAS ^4^.
Validate before production.
- Compare source and model row counts by partition.
- Verify date boundaries have no gaps or overlaps.
- Test late-arriving and back-dated adjustments.
- Confirm measures, relationships, and RLS results.
- Test SSAS service-account source permissions.
- Configure Kerberos only if delegated Windows identity or source-level RLS requires it; PBIRS notes this requirement for relevant DirectQuery/live-connection scenarios ^4^.

Alternative for SQL tables: DirectQuery

If source performance can support interactive queries, rebuild SQL-backed tables in DirectQuery mode. PBIRS supports DirectQuery for SQL Server, but Excel doesn't support DirectQuery, so Excel data must first be staged in SQL Server ^4^.

PBIRS documents that DirectQuery connections must either include Encrypt=True;TrustServerCertificate=True or use the PBI_SQL_TRUSTED_SERVERS environment variable. Prefer a properly issued and trusted SQL Server certificate with certificate validation enabled where your tested PBIRS configuration permits it. TrustServerCertificate=True skips certificate-chain validation and should only be used after a security assessment.

DirectQuery eliminates Import refresh but transfers workload to SQL Server and can make visuals slower. PBIRS also doesn't support automatic page refresh for DirectQuery models ^2^.

If the architecture cannot change

Keep Import mode and reduce full-refresh cost:

Remove unused rows, tables, and columns.
Push filters and transformations into SQL.
Replace repeated Excel ingestion with curated SQL tables.
Stagger schedules into low-usage periods.
Optimize expensive Power Query and DAX operations.

PBIRS embedded models have a nonconfigurable 2-GB scheduled-refresh limit; Microsoft recommends external Analysis Services with a live connection when the model exceeds it ^1^.

References