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.

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.

Instruments & Junction Box Connections

One of the really cool features of APS Reporter/SP is the Query Builder.

If you've worked with almost any database tools, you've seen graphical query builders. They help build SQL statements by dragging & dropping tables, creating joining, selecting fields, etc. Mostly they work much the same, with just some minor graphical changes, and usually they are great for simple queries, but have difficulty with complex queries. When it comes to writing complex queries, with sub-queries, nested unions, etc, it's back to directly coding SQL statements.

Novice users also generally have problems with Query Builders. Yes, they're graphical, and they are supposed to make it easy to create SQL statements, but they still use arcane Table & Field names, and the user still needs to understand how the database fits together. Despite being a "graphical" tool, they're still a barrier to new users getting up to speed.

For APSR, we have implemented a Query Builder that can handle almost any SQL statement, no matter how complex, all graphically. The weird field names now have clear descriptions of each field, and Virtual Tables & Fields simplify the database structure. Suddenly complex queries aren't so complex any more!

Here's a typical example. We wanted to create a simple Instrument Index Report for SPI, with the usual fields (Tag, Service, Manufacturer, Model, I/O Type, etc) plus we also wanted to include the Junction Box that the Instrument is connected to (ie where the field device is wired to). Simple enough requirement, but the Query is non-trivial.

The first step was to select all of the simple fields from the Component table in SPI:

Fortunately, the Virtual Fields in APSR make this really easy. Almost all of the fields we want are included as Virtual Fields in the Component table, so we just need to pick them. There's no need to join to the various supporting tables (like Function Types, I/O Types, Equipment, Manufacturer, Model, etc).

The only complicated field in this Report will be the Junction Box that the field devices connect to. To identify the correct Junction Box, the Query uses a sub-query with Wire Group (signal) information to find, for each signal, the lowest signal sequence number in a Junction Box. Then a second sub-query is used to find the particular Junction Box that contains that signal sequence number.

In APSR, sub-queries are defined as "Derived Tables". Creating a Derived Table produces a new work-space for us to build our sub-query.

The first sub-query actually finds the lowest wire GROUP_SEQ number for each WIRE_GROUP_ID, for wires that are connected to Junction Boxes (CATEGORY_SEQ = 6).

Then, we use the another Derived Table to use the minimum GROUP_SEQ from above to identify the correct Panel (Junction Box).

Our first Derived Table appears in the middle, with only the two selected fields showing. This second Derived Table has two outputs, the CMPNT_ID to identify the field device, and the Panel Name (which conveniently uses another Virtual Field to simplify our query). This Derived Table appears in our Main query, where we join to the Component table, and select the Panel name (using an Outer Join, as not all Instruments are connected to Junction Boxes).

Switching to the Query Results, we get:

The final SQL Query Statement is shown below, although fortunately we don't ever need to actually work with this. You could copy-and-paste it into your own APSR system, although the full report is included as a Sample Report in the installation package. (Note that this Query will not work in other tools as it uses APSR Virtual Fields that are not supported by other applications)

Select
                    COMPONENT.CMPNT_NAME,
                    COMPONENT.CMPNT_SERV,
                    COMPONENT.CMPNT_FUNC_TYPE_DESC,
                    COMPONENT.CMPNT_FUNC_TYPE_NAME,
                    COMPONENT.PLANT_NAME,
                    COMPONENT.AREA_NAME,
                    COMPONENT.UNIT_NAME,
                    COMPONENT.LOOP_NAME,
                    COMPONENT.CMPNT_MOD_NAME,
                    COMPONENT.CMPNT_MFR_NAME,
                    JUNCTION_BOX.PANEL_NAME As FIRST_JB,
                    COMPONENT.LOOP_DWG_NAME,
                    COMPONENT.PID_DWG_NAME
From
                    COMPONENT
                    Left Join
                    (Select WIRE_GROUP.CMPNT_ID,
                      WIRE_TERMINAL.PANEL_NAME
                    From WIRE_GROUP
                      Inner Join WIRE_TERMINAL On WIRE_GROUP.WIRE_GROUP_ID = WIRE_TERMINAL.WIRE_GROUP_ID
                      Inner Join (Select Min(WIRE_TERMINAL.GROUP_SEQ) As MIN_GROUP_SEQ,
                        WIRE_TERMINAL.WIRE_GROUP_ID
                      From WIRE_TERMINAL
                        Inner Join PANEL On WIRE_TERMINAL.PANEL_ID = PANEL.PANEL_ID
                      Group By WIRE_TERMINAL.WIRE_GROUP_ID,
                        PANEL.PANEL_CATEGORY_SEQ
                      Having PANEL.PANEL_CATEGORY_SEQ = 6) FIRST_JB_CONNECT 
                            On WIRE_GROUP.WIRE_GROUP_ID = FIRST_JB_CONNECT.WIRE_GROUP_ID 
                            And WIRE_TERMINAL.GROUP_SEQ = FIRST_JB_CONNECT.MIN_GROUP_SEQ
                    Where WIRE_TERMINAL.WIRE_GRP_LEVEL = 1) JUNCTION_BOX
                            On COMPONENT.CMPNT_ID = JUNCTION_BOX.CMPNT_ID
Where
                    COMPONENT.CMPNT_ID <> 0 And
                    COMPONENT.PLANT_ID <> 0
Order By
                    COMPONENT.LOOP_NAME,
                    COMPONENT.CMPNT_NAME

Sub-queries are an incredibly powerful tool for expanding your Reports, and with the APSR Query Builder creating Sub-queries (or Derived Tables) is as simple as almost any other query.

If you'd like to see some other sample queries and reports for SPI, check the relevant Wiki page. More information is also available from the various links to the right.

If you have any queries (pun?) or comments, feel free to add them below.

Regards,

Brian Raynor.

Graphs & Stats from the SPF Database

In this final post of three covering Virtualisation in SPF (the others are here and here) I'm going to look at producing simple statistical reports from SPF databases using straight-forward SQL Statements.

As discussed in the previous posts, using a Virtual Model of the SPF database we're able to produce reports directly from the source database without complex coding. The model appears as a straight-forward relational database, with Virtual Tables representing the various Classes, Interfaces and Relationships of SPF. The SQL language is a powerful tool for manipulating relational databases and modelling complex systems as simple relational systems allows us to leverage the power of SQL, while ignoring much of the underlying complexity of the source database.

By way of example, I'm going to produce some reports on Transmittals in SPF. The Queries are deliberately simple, but they could easily be extended to cover a range of requirements. If you would like full step-by-step guidelines to build these reports, refer to the Wiki.

Starting with a simply Query, just the ISPFTransmittal Table. This is a Virtual Table for the corresponding InterfaceDef, and provides a list of all Transmittals in the system.

ISPFTransmittal Table

The Query is simply generating a list of Transmittal Issue Dates (Years & Months), and then counting the Transmittals in each month. This gives us a simple list:

Chart Wizard (click to enlarge)

A simple list of results is fine, but a graph will make a much better report. Switching to the Report Designer, we can add a simple a simple Chart to our report.

This gives us a nice visual indication of the spike in Transmittals issued in November & December. 

But how many documents were actually issued? In November, were there 120 documents, or 1200 documents?

By adding the relationship between Transmittals and Document Versions to the original query it will now count Documents issued rather than Transmittals. (Wiki)

This gives us:

An extreme peak in November still, almost 1000 documents issued via Transmittals. Does that mean that there were a lot of Transmittals with around 10 documents each, or perhaps there were one or two that accounted for the bulk of the documents? (Wiki)

Going back to the original Query, we can wrap the SQL statement in another clause, and this time count the number of Transmittals based on the size of each (number of Documents), using the CASE statement:

This will assign each Transmittal into one of 5 Categories. 

Just a few simple Queries lets us extract useful data, without custom programming or specialised skills. This is the power of the Schema Virtualisation in APS Reporter/SP, it lets us work with our data without regard to the complex structures of the native databases.

There is a complete walk-through of the Queries and Charts above on the Wiki site.

If you have any questions or comments, please feel free to leave them below, or contact me at one of the links to the right.

Regards,

Brian.

SPF as a Source of Data

I'll apologise in advance, this is going to turn into a long post with lots of text and some obscure concepts, but I promise it will be worthwhile. I'm already planning for this to be the first in a trilogy covering APSR and the SPF database (sort of an APSR: A New Hope), with this one setting the stage and the next couple diving deeper into the technical stuff. If after reading this you have any comments or would like any further information, please feel free to use the comment box below, or contact me through one of links to the right.

---

When we're designing reports we work with a variety of databases and mostly it's simple enough to connect to the database and identify the major tables, find the fields we need for a report, and build a query to extract the data. Of course, some databases are more complex (eg SPEL, SPPID) and even relatively simple reports require a good understanding of the underlying database, but it can be done without too much trouble.

Most databases are highly normalised and are optimised for performance. This usually means they have many tables, in a highly structured form, where each table has a unique key (id) field. This allows the database engine (Oracle or MS-SQL Server) to find the most efficient way to retrieve data when requested by the application.

Unfortunately, the trade-off for us is that a highly normalised and optimised database is usually more complex to produce reports. The required data fields are spread across a lot of tables, and the designer needs to understand how they're all related.

SPF Is Different

Then there are databases like SPF. If you've ever looked at the SPF database, at first glance it doesn't seem too complicated as there are just a handful of tables, however the complexity of the SPF database lies not in the number or structure of the tables but in how the data is stored in the tables. A single application Object is not stored as a row in a table, but is spread across many rows and often many tables. This design is not optimised for performance, instead it is designed for flexibility, allowing the SPF Developer to add new Classes, Interfaces and Relations without modifying the underlying database structure. This allows SPF to support a catalog of 2000+ Classes, with 70,000+ Properties, with less than 20 database tables.

For the Report Designer, however, this structure is a major problem. Extracting meaningful reports directly from the SPF Database is incredibly difficult, and those who have tried do not always succeed. Just working out how the data is stored is a challenge, but then creating SQL Queries to piece together all of the required data requires specialist skills. It is possible to manually write queries that provide simple lists of data, such as a document list, or a tag list, but creating queries that join the data together in meaningful ways is almost impossible.

For example, if you were working with a conventional relational database, and you wanted to get a list of documents that were associated with transmittals that are overdue, it typically wouldn't be very complex (probably two or three major tables, plus some supporting tables). A similar Query from the SPF database would be enormously complex, and even if it worked, just validating the query would be difficult (how many people could even understand it!).

This is one of the trade-off's made by the Intergraph developers in implementing SPF, it would be flexible, but working with the database would be complex. SPF users cannot use conventional database tools, like Toad or Crystal Reports, to extract data or create reports, they are almost cut-off from their data and must use the SPF software itself for all queries and reports. Standard SQL queries became impractical.

Simplifying Databases

APS Reporter/SP was designed to simplify the creating of Reports, by making it easier to extract the data (query), easy to produce either simple tabular reports, or complexly formatted reports, and easier to keep revisions, add title blocks, etc.

The Queries are simplified by using a technique known as Schema Virtualisation, which uses a Model of the database that is optimised for Reports. The user doesn't work directly with the underlying database, they work with a Virtual Model that appears to be a standard relational database with Tables and Fields that are easy to understand (but not necessarily optimised for the database server).

The first Virtualiser we developed was for SmartPlant Instrumentation. SPI has a database that is relatively large (300+ tables), but is not overly complex. It's not a very difficult database to use for reports, but often even a simple report (such as an Instrument Index) can require 10 or more tables, and complex queries could include many more. Using the SPI Virtual Model the same query can include just 1 table, and complex queries often just 3 or 4.

SPI Queries using the native database, and the Virtual Model

The SPI Virtualiser was a great success. It creates a model of the SPI database that is very similar to the original, so users who have mastered the SPI database structure can use that knowledge, but it simplifies all of the common relationships. Novice users can easily generate simple queries , and just as importantly, expert users can leverage the Virtual Model to create complex queries much more easily. The SPPID & SPEL Virtualisers were similarly successful.

But what about SPF?

SPF was challenging. Could we produce a Virtual Model that made it easy to create queries, with no knowledge of the SPF database structure?

Initially, we tackled SPF 3.8 (Business Objects), and it worked beautifully. Users could build SQL Queries, working with the various Business Objects, while completely ignoring the cryptic table names and field names of the native database.

The real challenge was SPF 4.x. The Virtual Model had to fully support the ClassDef's, InterfaceDef's and RelDef's of the Schema model, allowing Report Designers to write Queries using the Objects they were familiar with. It also had to adapt to each client's database, including all of the custom schema objects defined in each system, and for the custom objects to work seamlessly with the out-of-the-box objects.

SPF Query with DocMaster & DocRevision

The SPF 4.x Virtual Model demonstrates the power of Schema Virtualisation. The native SPF database is completely hidden from the user, while Virtual Tables representing of Classes, Interfaces and Relations are available to build SQL Queries. For example, the query above joins the SPFDesignDocMaster and SPFDesignDocRevision Virtual tables, using the RelDef SPFDocumentRevisions.

It's now possible to write quick and easy adhoc SQL queries for the SPF database using standard SQL syntax. Complex queries can be written without any specialist knowledge of the SPF database structure, and Reports can be produced in a fraction of the time usually required.

In the next post I'll look at the process of writing queries for SPF with some tips and tricks to make life even easier.

If you have any comments, please feel free to use the comment box below. If you would like any further information, a free trial license, or a demonstration of APS Reporter/SP, please feel free to use the links to the right.