There has been lot of threads on community about the possibility of sending PI data updates to Oracle database. I am opening this thread to discuss on possible options and their Pros and Cons and derive on the best possible solution based on expert advise .
The equipment status(OPEN/CLOSE) is updating in PI system in real time.Requirement is to get the equipment status update from PI to Oracle database. The Equipment ID in PI doesn't match with Equipment Mask in Oracle table. Equipment mask in Oracle consist of different aliases which are configured as static attribute in PI AF Data Model. Also customer doesn't want to push the status update directly into Oracle. There has to be some kind of validation (e.g. Avoiding Momentary interruption, avoiding status update older than 2 hours etc.) before pushing data to Oracle table.
Here are 4 options which we have thought of to send the data over to Oracle from PI -
- Use PI Event Frames to Create an Event frame for Open and Close Status. Use PI Notification to call a webservice based on eventframe generation and write the status update to a Staging SQL Server. The Staging SQL Server will have a mapping table between PI Tags based on PI attributes and Oracle Equipment Mask. Run Stored Procedure or a SSIS package on staging SQL Server to do the data validation and push the updates to Oracle Table.
- Use PI Eventframes and PI OLEDB Enterprise Connector and Pull all the static attribute data and tag data into Staging SQL server via SSIS package. do the data validation and push the updates to Oracle Table.
- Create a mapping of PI tag with Oracle Equipment Mask and assign Equipment Mask to Extended Descriptor of PI Tag and then use PI-RDBMS interface to push data either to staging SQL or Oracle table. In this scenario, the data validation can be done in staging database or in the Oracle database itself.
- If using PI-RDBMS interface consume tag count, create a custom windows service to subscribe to status changes for the target tags and push the data to Staging SQL table. and then Push data to Oracle Table after doing data validation.
I would really appreciate if anyone can provide some feedback on the best possible option to go with or else you think there could be another better option, please suggest as well.