SPPID PipeRuns and Recursion....what fun!

One of the things I really enjoy doing with APSR is pulling apart databases to uncover things hidden deep in the data. Most application databases provide simple reports, but when you want to do something complex is when APSR really shines.

(Warning: This post contains some advanced Queries and Scripting, sorry :-)

Recently a client found some issues with the tagging of PipeRuns in their SPPID system, and it raised questions about how many other problems were lurking in there, and how could we find them. This is a great example where APSR should be useful.

Actually, the initial problem related to duplicated Line Numbers in unrelated parts of the plant, how could it happen and how could we find other instances (if there were any)? As the Line Numbers were just Labels associated with PipeRuns, the question really is "How do we check PipeRuns Item Tags?"

Duplicate PipeRun Item Tags are everywhere, adjacent PipeRuns usually have duplicate Item Tags, and a whole series of interconnected PipeRuns will often carry the same Item Tags, or sometimes differing only in their nominal size. The task then was to identify incorrectly duplicated PipeRun Item Tags!

The details of the solution are specific to the client, but the general approach could be applied to any SPPID where an analysis of interconnected PipeRuns is required. Below is a brief outline of the Query & Script we developed.

Report Design

We started by identifying sets of interconnected PipeRuns and related items. Of course PipeRuns don't always directly connect, but rather they connect through other components (Instruments, Valves, etc), so we needed to include these interconnected devices. In theory, all PipeRuns related to a Line should be interconnected, so if we could identify a "Set" of interconnected PipeRuns and components that relate to the same PipeRun ItemTag, and then identify another "Set" elsewhere in the Plant with the same PipeRun Item Tag, then that could indicate a problem (of course, with the way Process Lines are defined, there are always exceptions to the rule).

To identify a "Set" we could start with any PipeRun and check the items it's connected to. If those items have the same PipeRun Tag, or we decide they're part of the same Set, then we add them to the Set, and repeat the process (check the items connected to those items, etc). If the connected items are not part of the same set, then we stop checking in that direction. If we continue this process until we've found all interconnected items, then we have fully identified a complete "Set".

This would give us a list of PipeRun Sets, and if any Line Number had more than one PipeRun Set, it could indicate a potential problem, each of which would then need to be checked (interestingly, the results actually revealed a wide range of "odd" situations, many unrelated to the problem we were actually looking for).

To implement this, we need to:

• Get a list of all Line numbers in the database (not shown here)
• Get a list of all PipeRun Connections in the database (ie connection points between PipeRuns and other items).
• Using each Line Number, recursively work through the PipeRun Connections list, tracing interconnected Sets.

PipeRun Connections

First we needed to identify everything connected to PipeRuns. This means extracting a complete list of all connections from the database, and then iterating through the list tracing connections. For each PipeRun we identify connected items, then find other PipeRuns connected to that item and check if they're still part of the same "Line". If they're related to the original PipeRun, we repeat the process until we either run out of connections or related PipeRuns.

1. Start with the PipeRun table
2. Join to the Connector table, which identifies the Connectors on drawings that relate to each PipeRun. Connectors also indicate the symbols that each end of the Connector is connected to.
3. Join one end of Connector (SP Connect Item1 ID) to the Symbol table. This will give us the Symbol representing the Object at the first end of each Connector.

PipeRun Connections

4. From the Symbol Table, we need to join to the ModelItem table, 
5. The ModelItem table allows us to work out what type of Item the Symbol represents. Usually it'll be a PlantItem of some type, but it can also be an OPC.
6. We also need the PlantItem table (so we can get the ItemTag field), but this needs to be an Outer Join
7. Similarly we need an Outer Join to the OPC table. In the case of OPC's, we will want to find the paired OPC, so we can find connected PipeRun's on other drawings.

8. Finally for our Query, we need to use a Union and repeat the whole thing
9. In the second part of the Union, the Query is identical except we're looking for Connections on the other end of the Connector table (SP Connect Item2 ID)

Other side of the Union

The Query now gives us a complete list of all Connections to PipeRuns.

PipeRun Connections List

Tracing Related Items

To identify items connected to each PipeRun, and in turn the connected PipeRuns, we need some scripting.

The Script starts with a list of Unique Line Numbers (generated by another Query, not shown), and for each Line in the list, uses the LinkPipeRuns function show below to identify related items.

1. If the related Item is another PipeRun, then it recursively calls itself to check the next PipeRun,
2. Otherwise, it calls it's partner function LinkModelItem.

Scripting with Recursion

Results

The results of our Report identify the number of "PipeRun Sets" related to each Line. Ideally each Line should just have a single PipeRun Set, but if there are more than, it may represent a duplicated Line. By filtering to hide the Lines with just a single related set, we see those that need to be checked.

Line Numbers with more than 1 PipeRun Set

The report can also show us the connections related to each "Set", which helps to isolate the problem.

Expanded set of PipeRun Connections related to a Line Number

In the screenshot above, we can see that a Line Number has been expanded to show the two sets of Connections. These two sets may be related, or one may be mis-tagged.

Reviewing the results of the report we found various issues in the database, such as isolated PipeRuns with ItemTags that were unrelated to PipeRuns either side of them (when the PipeRuns should have been contiguous), PipeRuns that were disconnected from anything else (old PipeRuns that should have been deleted from the drawings), and of course some actual duplications where the same Line Number had been used more than once.

It's an interesting report that demonstrates some of the complex processing available in APSR.

As usual, contact me using the links on the right if you'd like more information.

Regards,

Brian Raynor.

Note: My apologies for not including some example P&ID's, but I can't post any client P&ID's. Also, the data samples have been randomised and blurred to disguise the source Plant.