To get PI System or SCADA historian data into Power BI without slow dashboards, ask the historian for hourly or shift summaries computed inside the historian, attach asset context from PI Asset Framework or your platform model, and load only those summaries into a small Power BI model. Raw tags stay in the historian. Copying them into SQL builds a second historian.
This guide is for engineering managers, plant IT teams and reporting leads at Australian and New Zealand manufacturers, mining operations and utilities who own the join between a process historian and the business reporting stack.
Key takeaways
- Power BI and a process historian are good at opposite things. Pointing one straight at the other produces slow refreshes and timeouts.
- Aggregate at the source: use the historian's own time-weighted averages, totals and interpolation, and store only the result in Power BI.
- Add asset context once (PI Asset Framework or equivalent) so every report speaks in equipment names, not tag names.
- Downtime is a set of events. Derive stoppage records close to the source instead of thresholding raw tags in Power BI.
- Copying every tag into a warehouse "for speed" is how sites end up with an expensive, poorly governed second historian.
Why does connecting Power BI to a historian go wrong?
A plant manager asks for a Power BI dashboard showing OEE, downtime by reason code and energy per tonne, refreshed every morning. The data exists. It has sat in the process historian for years, logged at sub-second resolution. So the reporting team points Power BI at the historian, drags in a few tags, and the trouble starts.
The dashboard takes minutes to load. The overnight refresh times out. Someone copies the historian data into a SQL database so Power BI can query it faster, and within a quarter that copy has become a second historian: a large, poorly governed duplicate kept in sync by a fragile nightly job nobody wants to touch.
Across our consultants' careers on Australian industrial sites working with OSIsoft PI, AVEVA Ampla, AVEVA System Platform and Citect, this is the failure pattern we see most often when operational technology meets business intelligence. It is avoidable.
Why is the direct historian connection so slow?
A process historian such as AVEVA PI System (formerly OSIsoft PI), AVEVA Historian or Citect Historian is built to write large volumes of timestamped values and answer time-series questions: this tag, between these times, interpolated to this interval. It stores data in a compressed, time-ordered structure that is deliberately unlike a relational table.
Power BI wants the opposite. Its engine slices and aggregates rows of already-shaped, modest-cardinality data. Two years of a one-second tag is more than 60 million rows for a single sensor (2 x 365 x 86,400). Power BI will try to load that, slowly, and then fall over when you add the next tag. The direct connection asks each system to do the thing it is worst at.
| Process historian | Power BI model | Impact | |
|---|---|---|---|
| Best at | High-frequency time-series writes and reads | Aggregating shaped, low-cardinality rows | Mismatch |
| Natural query | This tag, this interval, interpolated | This measure, sliced by these dimensions | Different |
| Data volume | Millions of raw values per tag | Thousands of pre-aggregated rows | Far smaller |
| Fails when | Asked to behave like a SQL table | Asked to swallow raw sub-second data | Both |
The fix is a thin layer between them that asks the historian for exactly the aggregates a report needs, at the resolution it needs, and nothing more.
What does aggregating at the source mean?
Every serious historian exposes time-weighted averages, totals, minimums, maximums and interpolated values over an interval, computed inside the historian engine where the raw data lives. Ask it for the hourly or shift-level summary you want to chart, and let Power BI store only that.
One hourly average per tag is 24 rows a day instead of 86,400. Two years of it is roughly 17,500 rows, which Power BI handles easily. Nothing a management dashboard needs is lost, because no plant manager reads a monthly OEE trend at one-second resolution. The raw data stays in the historian, where it is already governed and backed up.
- Raw data stays putSub-second tags remain in the historian, untouched
- Aggregate at sourceAsk the historian for hourly or shift summaries
- Add asset contextAttach equipment, line and reason-code metadata
- Shape a modelLand a small star schema Power BI can slice fast
- ReportDashboards refresh quickly and agree with the control room
How does PI Asset Framework help Power BI reporting?
Raw historian tags are named for instruments. A tag like PL2_MTR07_kW means nothing to a manager. An asset framework fixes that. In the PI System, PI Asset Framework models your plant, areas, lines and equipment and attaches the raw tags to them, so a report shows "Mill 2 energy" instead of a cryptic point name. AVEVA System Platform and other platforms offer the same idea.
Build that model once and every downstream report inherits the same names, units and equipment hierarchy. That consistency is worth more than any single dashboard. Our guide to PI Asset Framework templates covers how to structure them.
Should you copy historian data into a data warehouse?
Usually not. Sometimes a data platform is the right answer. The test is whether you are aggregating with purpose or duplicating out of habit.
| Thin aggregation layer | Accidental second historian | Impact | |
|---|---|---|---|
| What it stores | Hourly and shift summaries the report needs | A full raw copy of every tag | Governed |
| Size | Thousands of rows | Billions, growing daily | Bounded |
| Sync risk | Query on demand, little to keep in sync | Fragile nightly ETL nobody owns | Lower |
| Source of truth | The historian stays authoritative | Two systems disagree, neither trusted | Single |
The second-historian pattern is tempting because the first version works. A few tags are copied, the dashboard is fast, everyone is happy. Then the tag list grows, the nightly job slows, a sync fails silently, and one morning the dashboard and the control room disagree about last night's tonnes. That is the reconciliation problem described in our piece on why SCADA, the historian and the ERP disagree about production, created by duplicating the source.
Which historian reporting pattern fits your report?
The right architecture depends on what the report has to do. Deciding this at the start saves a rebuild later.
What does the report actually need?
- Management KPIs and trends, refreshed dailyAggregate at source, thin Power BI model
- Live shift dashboards for operatorsUse the historian's own visualisation tools, not Power BI
- Blending plant data with ERP and financeAggregation layer into a small, governed data model
- Ad-hoc engineering analysis of raw signalsQuery the historian directly, keep it out of BI
Most requests that reach a reporting team belong in the top row, and they go wrong most often, because a daily KPI dashboard looks trivial until raw data makes it crawl. The operator dashboard is a different job: PI Vision or the historian's native trending does it better. Our comparison of Power BI and PI Vision sets out where each fits.
What does getting the pattern right return?
You get a reporting layer that stays maintainable, as well as a faster dashboard.
- Dashboard refreshSummaries, not raw tags
- Storage duplicated from the historianMinimal
- Nightly sync jobs to maintainFew or none
- Historian stays the single source of truthPreserved
Why does downtime reporting break the simple model?
Aggregating at the source works cleanly for continuous signals such as flow, temperature and power. Downtime is different. It is a set of events, and events do not aggregate the way a time-weighted average does.
A downtime Pareto asks how many stoppages over four minutes occurred on Line 2 last month, grouped by reason code and ranked by lost hours. That is event logic. Rebuilding it by thresholding a raw running-status tag in Power BI produces a slow, brittle report that disagrees with what operators logged, because the tag does not know why the line stopped or whether a short stop should count.
Derive the events once, close to the source, and store them as records: start time, end time, duration, equipment reference and reason code. Power BI slices those rows easily. Where AVEVA Production Management, a downtime module or an operator log already captures reason codes, use that as the authoritative event source. Where nothing does, event derivation belongs in the aggregation layer, never in the report.
| Continuous signal | Downtime event | Impact | |
|---|---|---|---|
| Example | Energy per tonne, temperature trend | Stoppages by reason code, lost hours | Different |
| Natural shape | Time-weighted average over an interval | A record with start, end and reason | Event |
| Where to compute it | Historian summary function | Event logic in the aggregation layer | Source-side |
| Gets wrong when | Resolution too fine for the KPI | Rebuilt by thresholding a raw tag in BI | Avoid |
The rule for both cases is the same: do the hard computation where the data and the domain logic live, and hand Power BI a result it can display.
How should a historian reporting build be sequenced?
This work succeeds when done in steps and struggles when attempted as one large build. The order below front-loads the decisions that are expensive to change.
- Week 1Report scopingDefine the KPIs, the resolution and the refresh cadence they truly need
- Weeks 2-3Asset modelBuild or extend the PI AF or platform model that names the plant
- Weeks 4-5Aggregation layerConfigure source-side summaries and event records at the chosen resolution
- Weeks 6-7Model and publishShape a star schema, build the dashboards, hand over the pattern
The scoping week decides everything after it. Dashboards balloon when nobody asks what resolution a KPI needs, so the team defaults to raw. A downtime Pareto needs event records. An energy trend needs hourly totals. Pin the resolution to the decision the report supports and the architecture follows.
Where should the reporting layer run?
Plant data is sensitive, and critical infrastructure operators carry obligations under the Security of Critical Infrastructure Act 2018. Where the historian sits inside an OT network you cannot expose, the aggregation layer should run on your infrastructure in Australia (your cloud or your own servers), with read-only access to the historian. Our guide to keeping regulated data onshore and our private AI infrastructure service cover this.
The same historian tags also feed asset health and predictive maintenance work, so a clean asset model and aggregation layer pays off twice.
Where should you start?
If you have a slow historian dashboard, or a growing SQL copy turning into a second historian, take one report and ask what resolution it needs. Usually the answer is hourly or coarser, and the fix is to aggregate at the source instead of copying at the destination.
Teams that live inside their historian every day can find this hard to see, because the direct connection is the obvious one. An outside engineer who has watched the second-historian pattern fail elsewhere can usually point out where to aggregate, what to model and what to leave alone within a short review. For manufacturers and resources operators with years of historian data, the reporting layer is often the lowest-risk place to start, because the data already exists.
How Solve8 can help
Our consultants and engineers build historian reporting layers for PI, AVEVA and SCADA environments: asset models, source-side aggregation and Power BI models your team can maintain. This sits within our data, BI and analytics and system integration services, delivered as fixed-fee phases. Talk to us about one report that is not working.
