IMD SolutionComputing LibraryShop main store

Explainer

OLAP versus plain historian storage in SCADA databases

Edge computing and IIoT Published 6 min read

Short answer

A SCADA historian that stores timestamped values and serves trend queries is not doing OLAP, however many rows it holds. Only a deployment where a separate engine or scheduled job builds analytical structures, such as cubes or data marts, carries the OLAP licensing question. Classify by processing path, then decide whether the review is needed.

On this page
  1. What OLAP actually is
  2. Plain historian storage, defined by its processing
  3. The preparation path and the query path
  4. Worked example: five billion rows against seven hundred cells
  5. The technical record to bring to a licensing review
  6. The decision for each deployment
  7. What engineering cannot settle
  8. Questions asked

If your SCADA database stores timestamped process values and serves trend queries, it is not doing OLAP, no matter how many rows it holds. The two are different kinds of processing: plain storage inserts values and reads them back, while online analytical processing builds separate analytical structures — cubes and data marts — that group measures by dimensions before anyone queries them. That difference also decides the licensing question that periodically surfaces around SCADA historians, because disputes over analytical database features concern the structures being built, not the size of the database. This article gives you the processing-path test, a worked example with the arithmetic shown, the technical record to bring to a supplier, and the decision for each deployment pattern.

What OLAP actually is #

Online analytical processing organizes analysis around measures and dimensions. A measure is the quantity being analyzed: cases produced, downtime seconds, kilowatt-hours. A dimension is the category you slice it by: line, shift, product, day. An OLAP cube is a multidimensional structure that an analysis engine builds ahead of time from facts held in a data mart, so a report reads combinations of measures and dimensions instead of recomputing them from raw records; SQL Server Analysis Services is the analysis engine Windows plants encounter most often.

Behind the cube sit the warehouse terms. A data warehouse consolidates several operational sources into one database organized for analysis, and a data mart is the smaller, single-subject version of the same idea, fed by fewer sources. Both terms describe what gets built, never how big the database is.

Plain historian storage, defined by its processing #

A historian does two kinds of work. It inserts time-ordered rows — tag, timestamp, value, quality — as data arrives, and it reads ranges back for trends and reports. Alarm and event records follow the same pattern. A panel PC writing production rows into ordinary SQL tables, the setup in logging production data from a panel PC to SQL Server, performs exactly this processing, and a historian on relational time-series storage such as the Ignition historian with TimescaleDB evaluation stays in the same class.

Aggregation does not move a database out of the class. A report that groups operational rows by shift and sums them computes on the query path, against the same tables the historian already writes. That is capable, ordinary database work, and no separate analytical structure is involved.

The preparation path and the query path #

Classify a deployment by tracing two paths. The preparation path is anything that reads operational data and writes structures beyond the operational tables: a scheduled load into a star schema, a package that rebuilds a mart, an analysis engine processing a cube. The query path is what a trend client or report executes when someone opens a screen. OLAP exists where the preparation path builds or processes an analytical structure; a dashboard over a cube consumes work that path already did.

A totals report lives entirely in the query path, so output by shift proves neither the presence nor the absence of OLAP — the same shaped result comes from either path. The screen is therefore the wrong place to look: review the builder, not the report.

Worked example: five billion rows against seven hundred cells #

Two deployments of the kind this question arrives with, with the arithmetic shown.

Deployment A: a line historian that only stores #

A SQL historian records 2,000 tags at 1-second resolution:

  • 2,000 rows/s × 3,600 s/h = 7,200,000 rows/h
  • 7,200,000 rows/h × 24 h/day = 172,800,000 rows/day
  • 172,800,000 rows/day × 30-day retention = 5,184,000,000 rows, about 5.18 billion

At an illustrative 50 bytes per stored point — row formats differ by engine, so treat this as an order of magnitude — the archive houses 5,184,000,000 × 50 B = 259.2 GB. The processing is inserts and range reads. Classification: plain storage. Decision: size the disk and retention, and no analytical-structure review applies.

Deployment B: a nightly cube on the same plant #

A scheduled 02:00 job loads three measures — good cases, downtime seconds, kilowatt-hours — across four lines, three shifts and twenty products:

  • 3 measures × 4 lines × 3 shifts × 20 products = 720 cells each night
  • 720 cells/night × 365 nights = 262,800 cells over a full year of runs

That is a rounding error beside Deployment A's 5.18 billion rows, and Deployment B is the OLAP deployment: its preparation path builds a multidimensional structure separate from the operational tables. Decision: this deployment carries the supplier licensing question, so document the job, the engine and the supplier.

The technical record to bring to a licensing review #

Build the record before asking the supplier anything: a defined architecture gets a written answer, and 'is our SCADA affected?' gets a shrug. Record:

  • the database engine and version, read from installation and purchase records rather than inferred from the connection string;
  • whether an analytical engine is installed, and which application consumes it;
  • every scheduled job that writes — warehouse loads almost always run as scheduled jobs, they rarely stay on one server, and the page on tracking automated cron jobs and scheduled tasks across multiple servers is the practical way to enumerate them;
  • the reporting application, and the supplier responsible for each layer;
  • a drawn data flow: acquisition -> operational tables -> any transformation -> reporting structures -> reports.

Work read-only against the running system, leave the historian's insert path untouched, and make any configuration change in a maintenance window with the trend archive backed up first.

The decision for each deployment #

Deployment pattern Classification Decision
Timestamped inserts, alarm records, trend range reads Plain storage No analytical review; plan disk and retention
Shift or product totals from GROUP BY queries on operational tables Relational aggregation on the query path No review; keep the queries documented with the reports
Scheduled job loads a star-schema mart or processes a cube OLAP structures present Written supplier licensing review; record engine, version and job
Dashboard reads a cube another system built OLAP consumer Identify the building system and engine; include it in the review
Analytical engine installed but nothing consumes it Not an operational function Remove it or record why it stays

One rule carries all five rows: the review follows the preparation path. If nothing writes beyond the operational tables, close the question and spend the effort on sizing — plant historian selection by scale and support needs is the decision that matters there. If any job builds a mart or processes a cube, assemble the technical record, ask the supplier in writing what its licensing covers for your use, and keep the answer with the purchase records.

What engineering cannot settle #

The classification above is a technical fact, and it is all this article decides. Whether a supplier's patent license extends to your deployment is contractual, settled by the license terms and written confirmation; how far a patent claim reaches is a question for counsel, not for plant-floor testing. Verbal assurances settle neither question. Disputes over analytical-database patents have reached major database vendors before — that is why plants ask — and their outcomes belong to the parties and the courts. Keep the architecture record and the licensing record as two separate files, so anyone reviewing the deployment later can act on both without re-deriving either.

Questions asked

Does the classification change once the historian database grows large?

No. Row count and retention change capacity planning, not classification. Classification follows what builds the data: inserts and range reads stay plain storage at any size, and a prebuilt cube stays an analytical structure even when it is small. Size the disk for retention and sample rate, and raise the licensing question only when a scheduled job builds structures beyond the operational tables.

How do I tell a data mart from a backup or replica of the operational tables?

Look for the load job and the shape. A data mart serves one analytical subject and is rebuilt or updated on a schedule from operational sources; a backup or replica mirrors the operational schema for recovery or read scaling. If rows arrive in tables grouped by line, shift and product through a scheduled load, you are looking at a mart.

What should I ask the SCADA supplier, and in what form?

Ask which of their components create analytical structures, which database engine executes those builds, whether any analytical feature ships disabled by default, and whether their licensing covers your use of the deployed system. Request the answers in writing, keep them with the purchase records, and have counsel read the scope where the deployment is substantial.

Can the analysis engine run on the same PC as the SCADA host?

Technically yes, but keep the plant-floor host dedicated to collection. Cube builds and warehouse loads compete with live data collection for disk and processor, and an analysis engine widens the host's attack surface. Run preparation and analysis on a separate machine on the plant network and keep change windows aligned between the two.

Keep reading

  • Explainer

    Historian data retrieval speed: what actually decides it

    An indexed historian returns a year-old sample about as fast as yesterday's, so data age is the wrong variable to optimize. Retrieval speed is set by the requested window, returned point count, retrieval mode, archive storage tier and client rendering. Benchmark candidates with identical query semantics before choosing one.

    Edge computing and IIoT

  • Troubleshooting

    Historian trend empty: PLC tag collection troubleshooting

    A live PLC value proves nothing about what the historian stored. Work one known, changing tag through four boundaries — controller to data server, server to collector, collector to point, point to chart — and with a 2 s update rate, a 1% deadband on a 200 in span and a 24 in/h fill, a 20 min window must show about 4 stored events. Zero events means the collector; four events with a flat chart means the display.

    Edge computing and IIoT

  • Comparison

    Operator access to historian trends on night shift

    Put routine historian trend access in front of operators: prebuilt HMI or SCADA trend screens for the recurring questions, point selection over already-collected points for everything else, and a read-only web portal as the alternate route. Keep collection configuration with engineering. No permission setting retrieves samples for a point the historian never collected, so that gap is closed by adding collection, not by access rights.

    Edge computing and IIoT