Virtualisation for SPI & SPF

In the previous post I discussed the effect that database structure has on reporting, explaining how normalisation and optimisation often results in a more complex database, making it more difficult to extract the required data. Today I'm going to look more deeply at the SPI and SPF databases and how we can simplify reporting using the Schema Virtualisation technology in APS Reporter/SP.

Warning! SQL Statements Ahead....Proceed at your own risk!

Most applications recognise that users will want to produce reports in a wide variety of formats, with different combinations of data, and they will usually provide a rudimentary reporting system. In the good ol' days, databases were generally simple and if the application didn't provide the reports you needed it was easy enough to grab your copy of MS-Access or Crystal Reports and produce your own reports, perhaps after an hour or two to get your mind around the database.

The ubiquitous Query Builder window, found in almost all database management applications (except perhaps those favoured by certain Oracle command-line die-hards), was the standard tool for extracting data. The Query Builder, and the SQL statements produced, were effectively standardised and once you'd learnt how to produce a Query for one database, moving to another database was straight-forward and even switching applications and database platforms (eg Oracle to MS-SQL) was not usually a problem.

Progressively, as databases evolved and become more complex, options for extracting data and producing reports became more limited. External tools that had been useful became more difficult to use, users required a deeper understanding of the applications databases, and had to rely entirely on the tools provided by the application. Specialists with expert-level skills and knowledge of the arcane secrets of each database were required.

Ironically, as more and more data is stored in databases, the data became effectively hidden behind behind a wall of it's own complexity.

With APS Reporter/SP we wanted to open up these databases again. SQL, and the Query Builder, are still the best way to manipulate large data sets, whether you're extracting data for reports or exporting. The challenge was to hide the complexity of each database from the user, while still allowing them to work with the traditional Query Builder window and SQL.

Schema Virtualisation technology presents the user with a view of the database that appears to be a standard relational database "de-normalised". Rather than a design optimised for performance, it is optimised for reporting. Fields appear in tables where the user would intuitively expect to find them, or where they would be most convenient, and tables are arranged to represent items the user is familiar with, rather than the software developer.

SPI Instrument Index Query simplified with Virtualisation

For example, for an Instrument Index in SPI, users would require:

Instrument Tag, Service Description, Instrument Type, Status, I/O Type, Manufacturer, Model, Location, Certification, Loop Name, P&ID

These are all logically related to the Instrument, but are actually stored in 10 separate tables in SPI (for excellent integrity & performance reasons). To produce a simple report requires a Query as shown on the left above, or in the SQL statement below:

Select
  COMPONENT.CMPNT_NAME,
  COMPONENT.CMPNT_SERV,
  COMPONENT_FUNCTION_TYPE.CMPNT_FUNC_TYPE_NAME,
  COMPONENT_FUNCTION_TYPE.CMPNT_FUNC_TYPE_DESC,
  COMPONENT_HANDLE.CMPNT_HANDLE_NAME,
  COMPONENT_SYS_IO_TYPE.CMPNT_SYS_IO_TYPE_NAME,
  COMPONENT_MFR.CMPNT_MFR_NAME,
  COMPONENT_MOD.CMPNT_MOD_NAME,
  COMPONENT_LOCATION.CMPNT_LOC_NAME,
  COMPONENT_CERTIFICATION.CMPNT_CERTIF_NAME,
  LOOP.LOOP_NAME,
  DRAWING.DWG_NAME
From
  COMPONENT
Inner Join COMPONENT_FUNCTION_TYPE
  On COMPONENT.CMPNT_FUNC_TYPE_ID = COMPONENT_FUNCTION_TYPE.CMPNT_FUNC_TYPE_ID
Inner Join COMPONENT_MFR
  On COMPONENT.CMPNT_MFR_ID = COMPONENT_MFR.CMPNT_MFR_ID
Inner Join COMPONENT_MOD
  On COMPONENT.CMPNT_MOD_ID = COMPONENT_MOD.CMPNT_MOD_ID
Inner Join COMPONENT_SYS_IO_TYPE
  On COMPONENT.CMPNT_SYS_IO_TYPE_ID = COMPONENT_SYS_IO_TYPE.CMPNT_SYS_IO_TYPE_ID
Inner Join COMPONENT_LOCATION
  On COMPONENT.CMPNT_LOC_ID = COMPONENT_LOCATION.CMPNT_LOC_ID
Inner Join COMPONENT_HANDLE
  On COMPONENT.CMPNT_HANDLE_ID = COMPONENT_HANDLE.CMPNT_HANDLE_ID
Inner Join COMPONENT_CERTIFICATION
  On COMPONENT.CMPNT_CERTIF_ID = COMPONENT_CERTIFICATION.CMPNT_CERTIF_ID
Inner Join LOOP
  On COMPONENT.LOOP_ID = LOOP.LOOP_ID
Inner Join DRAWING
  On COMPONENT.DWG_ID = DRAWING.DWG_ID

The red text indicates the links between the primary COMPONENT table of SPI (containing the master list of Instruments) and the various related tables. Schema Virtualisation removes this complexity, users can treat the various related fields like there are actually part of the primary table and leave APS Reporter/SP to work out how to relate the additional tables. The result is the Query shown on the right above, and in the image below:

Virtualised Component Table

APS Reporter/SP also provides meaningful names for the fields, rather than the cryptic database field names selected by the developer. The resulting SQL Statement is also much simpler:

Select
  COMPONENT.CMPNT_NAME,
  COMPONENT.CMPNT_SERV,
  COMPONENT.CMPNT_FUNC_TYPE_NAME,
  COMPONENT.CMPNT_SYS_IO_TYPE_NAME,
  COMPONENT.CMPNT_LOC_NAME,
  COMPONENT.CMPNT_MFR_NAME,
  COMPONENT.CMPNT_MOD_NAME,
  COMPONENT.LOOP_NAME,
  COMPONENT.LOOP_DWG_NAME,
  COMPONENT.CMPNT_FUNC_TYPE_DESC,
  COMPONENT.PID_DWG_NAME
From
  COMPONENT

(Note of course that this SQL Statement could not be used in any other application, it would not know how to resolve the various field names into their respective tables)

For SPI, the improvement is dramatic, but for SPF it is simply amazing.

The SPF database does not contain tables that most users could recognise. For example, if you're looking for a list of documents, there is no Document table. In fact, there is no table representing any particular object type, and writing a query to extract even a simple list is incredibly complex (so much so, I don't even have an example to show you...perhaps someone could volunteer one?).

On the other hand, the Schema Virtualisation in APS Reporter/SP lets us work with the SPF database just like it was a conventional relational database! We can ignore the underlying complexity, and just select what we need:

SPFDesignDocMaster Vitualised Table

In this case we're working the the SPFDesignDocMaster Class in SPF, Virtualised as a Table. The SQL Statement is as follows:

Select
  SPFDesignDocMaster.SPFDesignDocMaster_Name,
  SPFDesignDocMaster.Description,
  SPFDesignDocMaster.SPFDocCategory_Desc,
  SPFDesignDocMaster.SPFDocState,
  SPFDesignDocMaster.SPFTitle,
  SPFDesignDocMaster.SPFDocType
From
  SPFDesignDocMaster

Again, the Query Builder provides us with clear descriptions for each field, and handles the translation to SQL.

With the Virtualised Schema available, the full power of SQL can be used. Sophisticated Queries can manipulate the data in creative ways to extract just the information needed. For example, in SPI, producing an Instrument Index which includes the I/O data for each tag, or the corresponding Junction Box & Marshalling terminals, becomes simple.

Another example, an SPF client had a problem. Their old Document Control system had a huge number of scanned hard-copy documents in TIF format, and when they were migrated to SPF a copy of the scanned document in PDF format was to be added to each document. After a short time they discovered that a few of the documents were missing the PDF file, but they had no idea just how many. Somehow they had to identify just how many documents were affected (how big was the problem), and get a list of the documents they had to fix. Extracting such a report from SPF would typically require some specialist skills and possibly some custom software, but a straight-forward SQL Statement answered the question in a few minutes (a few hundred documents).

This is the key to a good reporting tool, and it should be expected from any database tool. Providing quick and easy access to the data for the routine reports required regularly, plus also the one-off adhoc queries that come up from time to time.

You're not getting full value from your data while it is hidden inside a complex database that prevents access. Having the full power of SQL available, while avoiding the complexity of the underlying database, allowing you to access your data anyway you wish, is the key.

In this post I've had a deeper look at some specific examples of working with the SPI & SPF databases, and highlighted the advantages of working with Schema Virtualisation. Next time I'm going to have a look at some of the clever things that can be done with a good reporting engine, including exception reporting to identify errors in data, and some statistical reporting for analysis and KPI's.

Regards,

Brian.

Cable Schedules

This is a brief post covering a feature of APSR that was introduced in Version 3.0 but is not often used, despite it being a really powerful tool for controlling page layouts in the Report Designer.

Sub-reports are one of those features that will either make your eyes light up, or will draw a blank "Huh?". They're great when you need to design a complex report, and there are some designs that just can't be done without sub-reports, but if you've never needed them, then you've probably never even tried them.

For the experienced Report Designer, Sub-reports provide the ultimate in fine-grained control over your report layout, and in APSR we have perhaps the easiest sub-reports of any reporting application. When the report designer is open, all report sections and sub-reports are shown as tabs across the top of the window, and switching between the Parent report and the Child (sub) report is as easy as switching tabs.

The new report "Cable Schedule with Routing Sub-report" is a great example.

Cable Schedule with Routing Sub-report

Each Report Section can be either a full-page Section that prints on separate pages in the final report, or a Sub-Report Section that printed embedded in another page. This report contains six separate sections, the first three of which are separate printable sections which appear full-page, and the last three are sub-reports which appear embedded on the Cable Schedule page.

Each Report Section has a Sequence Number that determines the order in which they appear in the final Report (a value of zero (0) indicates it's a sub-report only). For example, the Summary Page is a simple list of the Cable Types included in the main Cable Schedule, with the total length of each Type shown. This is the second section to print, as set in the Properties box.

Section Display Name and Sequence Number (print order)

The main section of the report (Cable Schedule) includes two sub-reports, one for the Cable Route information, and another as a Title Block. Cable Routes show the various Cable Ladder sections or Nodes that the Cable passes through, and there can be anywhere from zero (0) to dozens of Cable Route items for each Cable. We want them to appear in a compact area of the detail section of each Cable, in the Routing column.

Routing Sub-report Section embedded in the Parent Report Section

Finally, the Filter String is used to link the Parent and Child Reports. In this case, it filters the sub-report (with the route data) to only show route information for the Cable currently appearing in the Parent report.

Filter String linking the Parent & Child Report Sections

The Title Block sub-report appears in the Page Footer of the main Cable Schedule, and in-turn contains another sub-report for Revisions (ie nested sub-reports!) 

You can check-out the full report by downloading a trial package. We have also included an SPI Wiring Report "Terminal Strip with Wiring Connections" which shows the wire connections on each side of a Terminal Strip. For each terminal, a sub-report is used on the Left and Right hand sides which nicely handles situations were 2 or more wires are connected to the same terminals.

Alarm & Trip Schedules

This week I was working on an Alarm & Trip Schedule for a client (hi Terri) and it occurred to me that this is a report that is required by almost every project, but is always a little complicated to produce.

Projects often require an Alarm & Trip Schedule to be produced from SPI, and it's often a frustrating process to extract the required data. The required fields appear in the Instrument Index module, the Process Data module, and the Wiring module, and no Browser conveniently combines everything into a single View. In fact, as a general rule, SPI doesn't provide many Browsers that mix data between modules.

Fortunately, it's actually quite simple to create an Alarm & Trip Schedule with APSR. The Query is relatively easy, making use of Virtual Fields and a nested Union, and of course the Grid or Designer make it simple to produce a deliverable report.

Typically an Alarm & Trip Schedule is a list of Instruments (usually Analog Inputs) with some basic tag data, the range of the device and/or DCS/PLC input, the Alarm & Trip Set-points (as defined by the Process Engineers usually) and the Set-points configured in the DCS/PLC. There may be other information, such as the I/O Card details, the Control System Tag & Description, etc.

The primary fields are found in the COMPONENT table, from which we select the core Instrument fields (Tag, Service, I/O Type) plus some range fields if required (Instrument Range, Calibrated Range and/or DCS Range).

Unfortunately, the Alarm & Trip Set-points are not in the Instrument Index, they're in the SPI Process Data module (a design decision that has caused a lot of frustration for many many projects). This creates two problems, one that the Alarm & Trip Set-points cannot be included in any Browsers in the Instrument Index module, and that it is necessary to create a Process Data entry for an Instrument before the Set-points can be entered. 

Often, to avoid these problems, projects will choose to use UDF's for alarms. This is a very practical solution, although it can cause confusion on projects where both the UDF's and the Process Data fields are used. 

The Process Data module has another complication, the Alarms & Trip Set-points are not stored in a single table, they're in the FLOW, LEVEL_INSTRUMENT , PRESSURE, and TEMPERATURE tables (depending on the Process Function Type of the Instrument). We need to use a UNION to combine the results from each of these tables in APSR, first by creating a Derived Table (sub-query) to hold the Union

In the Derived Table tab, we start with the FLOW table, and select the various Alarm & Trip Set-points and related fields:

Then create the Union by clicking the + next to the Q in the upper right corner of the window, this will open a new blank query pane for the next part of the Union. Add the LEVEL_INSTRUMENT table and select the corresponding fields here:

Repeat the process by adding new Union sections, adding the PRESSURE and TEMPERATURE tables, and selecting the corresponding fields. When we return to the Main tab, we now have a sub-query providing the combined Alarm & Trip Set-points from all of the Process Data tables:

(Note that the field descriptions are based on the first table in the Union)

The other place Alarm data is entered in SPI is as part of the Control System Tags associated with Instruments. By adding the CONTROL_SYSTEM_TAG table to our query we can extract the Control System Tag Name, Description, Calibration Range, Alarm Settings, plus other DCS and Wiring data as required.

In the end it's a relatively simple query. 

The results are shown below (click to enlarge).

I've used the APSR Grid to display the results. This makes it very easy to layout a simple report of rows & columns, and also is ideal if you need to export the final report to Excel. You could also use the Report Designer if you need a pixel-perfect fully formatted report.

You can download

• Alarm & Trip Schedule Report for APSR (you will need APSR installed), 
• Exported Excel report.

That's the basic report, but of course it can be modified to the requirements of any particular project. Also, the Grid Layout I've developed is designed for exporting to Excel and issuing as a document, but if a data extract is required for export to the DCS programmers (for example), that is just as easy.

Let me know your thoughts. Do you have any specific requirements for an Alarm & Trip Schedule? 

Regards,

Brian.