Getting Historian Data Into Power BI
The Request That Sounds Simple
A plant manager asks for a Power BI dashboard showing OEE, downtime by reason code and energy per tonne, refreshed every morning. The data all exists. It has been sitting in the process historian for years, every tag 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 refresh times out overnight. Someone decides to copy the historian data into a SQL database so Power BI can query it faster, and within a quarter that copy has grown into a second historian: a large, expensive, poorly governed duplicate of the system you already own, kept in sync by a fragile nightly job that nobody wants to touch. This is the single most common failure pattern when operational technology meets business intelligence, and it is entirely avoidable.
This article is for engineering managers, plant IT teams and reporting leads who own the join between a process historian and the business reporting stack. Having spent 18 years working in OSIsoft PI, AVEVA Ampla, AVEVA System Platform and Citect on Australian industrial sites, the mistake is nearly always the same. The historian and Power BI think about data in fundamentally different ways, and skipping the layer that translates between them is what produces the slow dashboard and the accidental second historian.
Why the Naive Connection Is Slow
To fix the problem you have to see why it happens, and it comes down to a mismatch in what each system is good at.
A process historian such as OSIsoft PI (now AVEVA PI System), AVEVA Historian or Citect Historian is optimised to write millions of timestamped values per second and to answer time-series questions: give me this tag between these two times, interpolated to this interval. It stores data in a compressed, time-ordered structure that is superb at that job and deliberately unlike a relational table.
Power BI wants the opposite. Its engine is built to slice and aggregate rows of already-shaped, modest-cardinality data. Ask it to pull two years of a one-second tag, which is more than 60 million rows for a single sensor, and it will try, slowly, and then fall over when you add the next tag. The historian can hand you that data. Power BI cannot digest it in that shape. The naive connection fails because it asks each system to do the thing it is worst at.
Historian and Power BI Think Differently
| Metric | Process historian | Power BI model | Improvement |
|---|---|---|---|
| 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 | 1000x |
| Fails when | Asked to behave like a SQL table | Asked to swallow raw sub-second data | Both |
The answer is not to make Power BI faster or to copy everything into SQL. It is to put a thin layer between them that asks the historian for exactly the aggregates the report needs, at the resolution the report needs, and nothing more.
The Layer That Fixes It: Aggregate at the Source
The historian already knows how to summarise. 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. The whole trick is to ask it for the hourly or shift-level summary you actually want to chart, and to let Power BI store only that.
A single machine reporting one hourly average per tag produces 24 rows a day, not 86,400. Two years of that is roughly 17,500 rows, which Power BI handles without noticing. You have thrown away nothing that a management dashboard needs, because no plant manager reads a monthly OEE trend at one-second resolution. The raw data stays in the historian, which is exactly where it should live and where it is already governed and backed up.
Historian to Power BI, Done Right
The context step is what separates a useful dashboard from a wall of tag names. Raw historian tags are named for instruments, not for the business. A tag like PL2_MTR07_kW means nothing to a manager. This is where an asset framework earns its place. If you run the PI System, PI Asset Framework lets you build a model of your plant, equipment, lines and areas, and attach the raw tags to it, so that the report speaks in terms of Mill 2 energy rather than 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, the same units and the same equipment hierarchy, which is worth far more than any single dashboard.
What Not to Build
The temptation to copy everything into a warehouse is strong, and sometimes a data platform genuinely is the right answer. The distinction is whether you are aggregating with purpose or duplicating out of habit.
Aggregation Layer Versus a Second Historian
| Metric | Thin aggregation layer | Accidental second historian | Improvement |
|---|---|---|---|
| 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, nothing to keep in sync | Fragile nightly ETL nobody owns | Removed |
| Source of truth | The historian stays authoritative | Two systems disagree, neither trusted | Single |
The second-historian anti-pattern is seductive because the first version works. You copy a few tags, 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. Now you have the reconciliation headache described in our companion piece on why SCADA, the historian and the ERP disagree about production, except you created it yourself by duplicating the source.
Which Pattern Fits Your Reporting?
The right architecture depends on what the report has to do, and getting this decision right at the start saves the rebuild later.
Choose Your Historian Reporting Pattern
Most requests that land on a reporting team belong in the top row, and they are the ones that go wrong most often, because a daily KPI dashboard looks trivial until the raw-data mistake makes it crawl. The operator dashboard in the second row is a different job entirely, and PI Vision or the historian's native trending tools do it better than Power BI ever will. Knowing which pattern you are building is half the work.
What This Returns
The payoff of getting the pattern right is not just a faster dashboard. It is a reporting layer that does not rot.
Aggregate-at-Source Versus Copy-Everything
Downtime and Reason Codes Break the Simple Model
The aggregate-at-source approach works cleanly for continuous signals like flow, temperature and power. Downtime reporting is where teams get caught, because downtime is not a signal, it is a set of events, and events do not aggregate the way a time-weighted average does.
A downtime Pareto answers questions the historian was not designed to answer directly: how many stoppages over four minutes on Line 2 last month, grouped by reason code, ranked by total lost hours. That is event logic, not signal logic. Trying to reconstruct it by thresholding a raw running-status tag in Power BI produces a slow, brittle report that disagrees with what the operators logged, because the raw tag does not know why the line stopped or whether a short stop should count.
The fix is to derive the events once, close to the source, and store them as records rather than signals. A stoppage becomes a row with a start time, an end time, a duration, an equipment reference and a reason code, and Power BI slices those rows the way it was built to. Where the historian or the platform already captures reason codes, through AVEVA Production Management, a downtime module or an operator log, use that as the authoritative event source rather than re-deriving it. Where it does not, deriving events belongs in the aggregation layer, not in the report.
Continuous Signals Versus Downtime Events
| Metric | Continuous signal | Downtime event | Improvement |
|---|---|---|---|
| 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 that ties both cases together is the same: do the hard computation where the data and the domain logic live, and hand Power BI a result it can simply display. A continuous KPI wants a source-side summary. A downtime report wants source-side events. Neither wants raw tags dragged into the model and processed there.
Sequencing the Work
Like most operational technology projects, this succeeds when it is done incrementally and fails when it is attempted as one large build. The order below front-loads the decisions that are expensive to change later.
A Historian Reporting Build
The scoping week decides everything downstream. The most common reason these dashboards balloon is that nobody asked what resolution the KPI needs, so the team defaults to raw and pays for it forever. A downtime Pareto needs event records, not one-second signals. An energy trend needs hourly totals. Pin the resolution to the decision the report supports and the architecture almost designs itself.
Why It Pays to Get an Outside View
Plant teams that live inside their historian every day are often the worst placed to see these decisions, because the naive connection is the obvious one and the raw-data instinct is hard to argue with until it fails. An engineer who has seen the second-historian pattern collapse on three other sites will steer you around it in an afternoon.
This is operational technology consulting at its most practical: not a strategy deck, but a specific decision about where to aggregate, what to model and what to leave in the historian. For Australian manufacturers and Brisbane resources operators sitting on years of untapped historian data, the reporting layer is usually the fastest, lowest-risk place to start, because the data is already there and the win is visible in weeks.
Two adjacent considerations are worth flagging. First, this reporting layer feeds naturally into asset health and predictive maintenance work, which draws on the same historian tags for a different purpose. Second, because plant data is sensitive and, for critical infrastructure operators, subject to regulatory obligations, where this layer runs matters: our guide to keeping regulated data onshore and the option of private AI infrastructure both apply when the historian sits inside an OT network you cannot expose.
Where to Start
If you have a slow historian dashboard, or a growing SQL copy that is turning into a second historian, the first step is to look at one report and ask what resolution it genuinely needs. Nine times out of ten the answer is hourly or coarser, and the fix is to aggregate at the source rather than copy at the destination. That single change usually turns an overnight refresh into a five-second one.
Solve8 helps Australian industrial operators get real reporting out of PI, AVEVA and SCADA historians without the second-historian tax, drawing on 18 years of hands-on OT and historian work. If your plant data is trapped in a system nobody can report on, start a scoping conversation and we will look at your historian and your reporting goals before proposing anything.