Instrument Data from SPI and SPPID

Various techniques are available to connect the SPI & SPPID databases (for comparison or reporting), each with it's own advantages and disadvantages.

Of course, a fully integrated SPE environment is the Rolls-Royce option. It permits intelligent, automated exchange of data in a structured environment with fine control over each step. Of course it also imposes a significant overhead, requires careful implementation, and requires an experienced developer to make any customisations.

Various other techniques are available to connect the two systems, such as using the direct SPI to SPPID tools provided in the apps, exporting reports from each system and linking them, or importing SPPID data directly into SPI.

The latest release of APS Reporter/SP (Version 4.0) introduced a new option for linking databases. Not just SPI and SPPID, but any combination of MS-SQL and Oracle databases can be linked for comparison, data extraction or reports. Potentially, data could be combined from SPI, SPEL, SPPID, SPF plus other engineering or corporate databases into a single report.


APSR allows each Report to connect to multiple databases, run multiple Queries for each database, and then combine the results into a single Dataset for reporting or analysis. This could be a simple two-database report (eg SPI & SPPID) where Instruments are extracted from each and then combined for reporting, or a much more complicated report. Discrepancy reports can also compare the data from each database and highlight differences.

In the example below, Connections are configured for SPI & SPPID, with one Query for each Connection, to return a list of Instruments.

Report Properties, with Multiple Database Connections

1. Each Report has a list of Connections
2. Each Report Connection refers to a specific application database.
3. Queries are then created for each Connection

The SPI Query uses only the COMPONENT table from the SPI database. Everything else is handled by Virtual Fields, so there's no need for a complex Query.

Query for SPI Database

1. The COMPONENT Table is selected
2. The required data fields are selected. Virtual Fields eliminate the need for related Tables
3. The Result Table is named "Instruments". This is important for the two result sets to be combined.
4. The main Instrument Tag field name (CMPNT_NAME) is aliased as "Instrument"

Similarly, the SPPID Query uses only the INSTRUMENT Virtual Table. Again, Virtual Fields handle all of the related tables.

Query for SPPID Database

1. The INSTRUMENT Table is selected
2. The required data fields are selected. Virtual Fields eliminate the need for related Tables
3. The Result Table is named "Instruments". This must be the same name used in the SPI Query for the two result sets to be combined.
4. The main Instrument Tag field name (ITEM_TAG) is aliased as "Instrument"

When the Report is executed, APSR extracts the data from the two source databases (SPI & SPPID) and merges the results. Both Queries created a Result Table named "Instruments", with "Instrument" as the Primary Key, so the final merged result is a single table with the combined data, correlated on the Instrument Field.

Grid with combined SPI and SPPID Data

The Instrument field (and any other common fields) appears only once in the results. If a Tag only appears in the SPI database, and has no corresponding entry in the SPPID database, then the results will show only the SPI fields (the SPPID fields appear blank). Similarly for Tags that only appear in the SPPID database.

For Tags that appear in both databases, all fields will be populated.

The results can be further processed in various ways, including:

• Compare data from the two systems, highlighting discrepancies
• Format for exporting to Excel
• Use the Report Designer for fully-formatted Deliverable Document production
• Export to various formats

Note that this process does not require the SPI & SPPID databases to be integrated in any way. Potentially, they may not even have the same Plant Breakdown Structure or Tag Convention. For example, if the two databases used different Tagging Conventions, the Queries could include a Tag format conversion or mapping function to format the Tags into a common structure.

This is a very simple example of a very powerful function. I'll also be posting another example shortly, demonstrating how to produce a consolidated Cable Schedule from SPI & SPEL. If you would like any further information, please add a comment below or contact me using the links to the right.

Regards,

Brian Raynor.