Selection guide
Choosing a PI System to Oracle integration route with validation
Short answer
With read-only PI access and cleansing owned by the receiving side, pull AF assets and status with the PI OLEDB Enterprise Provider through SSIS into a staging SQL Server, qualify transitions there — reject momentary interruptions and anything older than 2 hours — then write Oracle. Event Frames and the RDBMS interface need PI-side configuration or tag-based mapping that loses AF context.
On this page
- What actually decides the choice
- The four routes compared
- The recommended route: PI OLEDB Enterprise with SSIS
- The decision for each ownership case
- Keeping the AF asset context in the mapping
- The two validation rules and where they run
- Working a 10:02 CLOSE transition through the 2-hour rule
- When Oracle lands the update on the wrong row
- Questions asked
Use the PI OLEDB Enterprise Provider, read by an SSIS package into a staging SQL Server, and write Oracle only from validated staging records. Two constraints decide this: Engineering exposes the PI System read-only, so analyses, Event Frames and notification logic cannot be added on the PI side, and the Oracle Equipment Mask is built from aliases held as static attributes in AF, so acquisition must see the asset model, not only tags. In staging, reject momentary interruptions and any update older than 2 hours, then write what survives. Every alternative fits a different ownership arrangement, and Oracle pulling PI through PI SQL Client ODBC and the DG4ODBC gateway is a genuine option once the Oracle team owns the whole pipeline.
What actually decides the choice #
Ownership decides this, not product capability. Every route on the shortlist can move an OPEN/CLOSE status; the question is who is allowed to configure what. Under read-only PI access, any route that needs new analyses, tags, event frame templates or notification rules on the PI server is out until Engineering's position changes, and cleansing belongs to the receiving side by decree.
A staging SQL Server costs a network hop and one more system to manage, and that cost is real. It also buys the only place you control where candidates can wait, be qualified against rules, and be replayed after a failed Oracle write. With PI-side validation ruled out and unvalidated writes to Oracle ruled out by the customer, staging is not overhead; it is where the requirement lives.
Keep one distinction clear while comparing: a staging database is not an acquisition method. It is independent of the pull-versus-push choice, so judge each route on identity, PI-side work and validation ownership, and treat staging as the shared back half of every option.
The four routes compared #
| Route | Where the Oracle mask is resolved | Needed on the PI side | Who validates | Fit under read-only access |
|---|---|---|---|---|
| Event Frames with Notifications calling a web service, then staging | AF context travels in the notification payload | Analyses, event frames, notification rules — Engineering work | Receiving side | Not deployable |
| PI OLEDB Enterprise Provider with SSIS into staging SQL Server | AF static attributes, read in the same query as the status | Read access and provider configuration | Your team, in staging | Recommended |
| PI SQL Client ODBC through Oracle's DG4ODBC gateway | In the query text or in Oracle-side tables | Read access plus gateway configuration | Oracle team | Fits when Oracle owns the pipeline |
| PI Interface for Relational Database (RDBMS via ODBC) output points | Duplicated into each tag's ExDesc | Interface and output-point configuration | Staging or Oracle | Weak fit: tag-based, consumes tag count |
| Custom Windows service subscribing to status changes | Maintained alongside the subscription | Supported client access plus service development | Your team, in staging | Only for a written latency requirement |
Read the table as an ownership matrix. The recommended row is the only one that resolves identity from AF, needs no PI-side configuration, and keeps validation on your side of the fence at the same time.
The recommended route: PI OLEDB Enterprise with SSIS #
The product is a provider, not a connector: PI OLEDB Enterprise is a read-only OLE DB data-access component that exposes AF data — elements, attributes and event data — as SQL-queryable tables, with element attributes and their values available through tables such as Asset.ElementAttribute. One query can therefore return the status history and the alias attributes that build the Oracle Equipment Mask together, so identity and measurement arrive in the same row set and the mapping can be rebuilt from AF instead of maintained by hand.
SSIS owns the schedule and the transform. Nothing in this route asks Engineering for analyses, tags or notification rules; access stays exactly as restricted as it is today.
The provider never faces Oracle directly. Oracle has dropped support for the OLE DB standard, so the package lands data in SQL Server and the Oracle write uses the Oracle-side connectivity you already operate. Any package that still attempts an OLE DB call into Oracle fails at the connection layer first — the layer an Oracle OLE DB connection fix for plant-floor software works through.
Event Frames are a bonus here, not a prerequisite. The same provider exposes event data read-only, so if Engineering already generates suitable frames you can pull them without waiting for a notification path; if they do not, qualify transitions yourself from the status history.
The decision for each ownership case #
- Read-only PI, your team owns cleansing — today's case: build the provider-and-SSIS route with staging validation.
- Engineering later accepts PI-side processing: Event Frames with Notifications calling a web service becomes the leanest design, because the event logic runs where the data lives. Keep the same staging schema and rules so only the acquisition leg changes.
- The Oracle team wants extraction and validation on their side: PI SQL Client ODBC reaching a PI System from Oracle 18c through DG4ODBC on Windows is the documented combination. Run pass-through queries as the configuration guide describes, treat a linked table as advanced Oracle work, and move both validation rules into Oracle along with the pipeline.
- A written latency requirement that scheduled extraction cannot meet: add the custom Windows service subscribing to the target tags and writing the same staging tables. Same rules, but now you own recovery logic as code.
- The RDBMS interface with ExDesc mapping: choose it only when the tag-based limitation and the tag-count consumption are accepted in writing. It is the one route that actively discards AF context.
Keeping the AF asset context in the mapping #
A correct status value and a correct database binding are separate requirements. The PI Equipment ID does not match the Oracle Equipment Mask, the mask's aliases live in AF static attributes, and a join on tag name alone drops that context — which is how a good value lands on the wrong equipment row.
Build an explicit mapping record for every candidate: status point, AF element, alias values, resolved mask, and the mapping revision used. Reject unmapped and ambiguous candidates outright rather than taking the first row a query returns.
Static AF attributes change through configuration maintenance. Refresh the mapping deliberately, and decide once whether queued data uses the mapping valid at event time or the current approved mapping — otherwise a retry after a rename targets different equipment than its first attempt did.
The two validation rules and where they run #
Persistence first: qualify a transition only when the new status is still the live value at validation time. Interruptions shorter than the extraction cycle are then rejected by construction, and if the business wants a longer hold it becomes a configuration value in staging rather than logic buried in the package.
Age second: reject any candidate more than 2 hours — 120 minutes — old at validation time. The same rule bounds replay: after an outage the queue drains, and anything too old discards itself. Accept the consequence openly: after a long outage the current state may never be written, and the next qualified transition restores it. That is the deal this rule makes.
The staging write itself is ordinary plant-data logging — the same shape as logging production data from a panel PC to SQL Server — a mapped destination, an explicit key, a retryable insert. When the host loses the link to staging mid-cycle, handle it as you would when reconnecting an edge PC to SQL Server after a connection loss: retry the package and let the age rule bound what survives. Keep a disposition record per candidate — accepted, rejected and why, written when — so a status that staged but never reached Oracle is always answerable.
Working a 10:02 CLOSE transition through the 2-hour rule #
Inputs for this case: a valve's PI point that went OPEN at 09:58 and CLOSE at 10:02 (an illustrative case for this requirement); a validation run at 10:09 taken from the SSIS schedule; a live snapshot of CLOSE at 10:09 from the same extraction; and the age limit of 120 minutes, which is the requirement's "no updates older than 2 hours".
The rule in words: write a candidate only if its age at validation is at most 120 minutes and its status is still the live value; log every rejection with a reason.
The arithmetic:
- Age of the CLOSE candidate: 10:09 − 10:02 = 7 min. Test: 7 min ≤ 120 min, so it passes.
- Persistence: the live value at 10:09 is CLOSE, matching the candidate, so it passes.
- A replayed candidate from a backfill, timestamped 07:55, reaches the same run: 10:09 − 07:55 = 2 h 14 min = 134 min. Test: 134 min > 120 min, so it fails.
Result: the 10:02 CLOSE is written to Oracle under its resolved mask; the 07:55 event is rejected as stale and logged. The threshold this leaves you with: anything over 120 minutes old never reaches Oracle, so set the extract-validate-write cycle to finish well inside 120 minutes — a slower cycle rejects its own fresh data.
When Oracle lands the update on the wrong row #
| What you see | Where the fault sits | First check |
|---|---|---|
| PI shows the right state, Oracle updates a different equipment row | Identity mapping or destination predicate | Trace the point, AF element, aliases and resolved mask for that one candidate |
| Status reaches staging but never Oracle | Validation rejection or delivery failure | Read the candidate's disposition and the write result separately |
| A brief interruption becomes a lasting Oracle state | Persistence rule | Compare the status history against the rule as configured |
| An old state replaces a newer one | Age or ordering | Compare source timestamp, accepted timestamp and replay order |
| AF reads succeed, the Oracle write fails | Connectivity on the write leg | Test acquisition and the Oracle write independently |
Under today's access model the decision stands: build the provider-and-SSIS route with staging validation, and revisit only when ownership of PI-side processing or of the pipeline itself changes hands.
Questions asked
Does the staging SQL Server make the integration slower?
It adds a network hop and one more system to manage, so the cost is real. Measure the added latency as one extraction interval plus the Oracle write; if that fits the business window, keep staging, because it is also the only place you are allowed to enforce rules and the only queue that replays a failed write. What it cannot do is make data fresher than its extraction interval.
Can Oracle read PI directly, with no staging server in the middle?
Yes. PI SQL Client ODBC reaching a PI System from Oracle 18c through DG4ODBC on Windows is a documented combination, with pass-through operation recommended and linked tables left to Oracle specialists. It removes the staging SQL Server from your scope entirely, which is its real appeal, and it hands extraction plus both validation rules to the Oracle team, so agree that ownership in writing first.
Why not carry the Oracle Equipment Mask in each tag's ExDesc?
That duplicates identity data that already lives as aliases in AF static attributes, and the copy drifts the first time AF is maintained. The interface is tag-based rather than asset-aware, and its output points consume tag count, which you should confirm with the supplier. If you do use it, keep validation in the staging database anyway; the interface feeling direct is not a reason to skip it.
What counts as a momentary interruption?
The requirement gives a number only for age: 2 hours. For interruption, qualify a transition only when the new status is still the live value at validation time; chatter shorter than the extraction cycle is then rejected automatically. Log the rejections with timestamps, because a recurring chatter pattern is diagnostic information about the valve, not just noise to filter.
What changes if Engineering later allows processing on the PI side?
The switch touches only the acquisition leg, so keep the same staging schema and rules and swap in the PI-side route described in the decision list. Move the persistence check into AF only if Engineering agrees to own it, and keep the age check on your side either way.