Simple SPI Query

APS Reporter makes it very easy to extract data from an SPI database, whether you want to dump data to an Excel file for import to another system, or produce a fully formatted, pixel-perfect document. In this post I wanted to show just how quick and easy it is to create a Query and get the data out, with only a minimal knowledge of the SPI database.

For demonstration purposes, lets assume we want to get a list of Instruments with some basic data from the Index (I/O Type, Location, Instrument Type), plus details of the I/O Channel for the Instrument (Panel, I/O Card, Channel). Perhaps we need to provide this data to our DCS Supplier, for them to import into their own applications.

Also, lets for argument sake, assume we also want to include the Specification Sheet number for each Instrument, just for demonstration purposes.

This means we're looking for data from the Index, Wiring and Specification modules of SPI and typically you wouldn't be able to produce a report like this in the SPI Browser module. You could perhaps use Infomaker, MS-Access, or a similar query tool, but these would require a deeper understanding of the SPI database and the resulting queries would be quite complex.

APS Reporter/SP makes this process a lot easier.


Firstly, we start with a New Report for our SPI Connection

Once the Query Builder window opens, drill down the table tree to find the Component table, this is the main Instrument table in SPI.

Double-click the Component table to add it to the Query Drawing pane, and select the fields we'd like to include in our query. I've selected the Instrument, Service, Function Type, Location, I/O Type, Loop Number and Loop Drawing fields.

Notice that in APSR we can select all of these fields directly from the Component table. In reality, in the database they are actually stored as part of five other tables, but APSR understands the database structure and resolves these fields for us.

Next, we need to add the Control System Tag table. Double-click this to add it to the drawing pane.

We need to join the Control System Tag table to the Component table, which we do by dragging the Instrument ID (CMPNT_ID) field from one table and dropping it on the corresponding field in the other table.

We then can select the fields we need from the Control System Tag table. I've selected the Control System Tag Name, Panel, Strip and Channel names.

To get the Specification Sheet Number, we also need to add the Spec Sheet Data table. Again, double-click this to add it to the query drawing pane.

Again, we need to join the new table by dragging the Instrument ID (CMPNT_ID) field from the Component table to the Spec Sheet Data table. We can also select the Specification Sheet Number field at this time.

We need to make one more change to our query. By default, our results will ONLY include tags that have BOTH an I/O Channel assigned PLUS a Spec Sheet Number.

In this case, we actually want to include ALL Instruments in our results, so we need to change the Joins between the tables as shown in the image to the right (right-click on the Join line to see the menu).

That's It!

Switch to the Results Grid to see the data.
In addition to the data, the results grid also has correct title for all the columns.

If this query were written in some other tool (Infomaker, MS-Access, etc), it would have required a minimum of twelve tables, and upwards of 15 joins. Also, the fields would be labelled with the standard database names and would need to be modified for our end-users.

Our query required a total of three tables and two simple joins. APSR was able to resolve the other tables and fields as required. Of course, if you're an SQL Guru, don't worry, the APSR Query Builder can be used like any other SQL Query tool, and the Virtual Tables & Fields can be mixed and matched with standard objects. In short, SQL Virtualisation adds a whole lot of power, without taking anything away.

If you'd like to try APSR for yourself, download the Free Version from the link to the right (the Free Version is not a time-limited trial, it will not expire, and can be used in production systems if required).


Regards,

Brian Raynor.

Simple Instrument Index Query

When we sat down to build our own reporting tool for engineering databases, we really wanted the best of all possible worlds (doesn't everyone!). We wanted something that was easy to use, that novices could learn without trouble, but something that experienced users wouldn't feel constrained by. The ease of use should work with the user, making complex tasks simple, not dumb it down to the point where it couldn't do anything but the most basic task.

The Query Builder was perhaps the most challenging part of the application. Typically, Query Builders are confusing for new users, they can be daunting and often new users will be discouraged as soon as the Query Builder window opens. They have lots of tables floating around the window, with connections between them, strange symbols, and cryptic field names. For Expert users though, Query Builders are great, they open up so many options that would otherwise be unavailable, and we had to maintain that.

When it came to the Query Builder for APSR, should we go with the traditional approach, or should we try something different. Almost everything "different" we've ever seen has meant "basic", where the user has a limited range of options, sometimes in some overly graphical window with lots of pretty pictures. Graphics are great, but they shouldn't actually confuse the user, and they shouldn't come at the expense of power!

We elected to stick with a fairly traditional Query Builder window, but we introduced a powerful new system of Virtualisation that simplifies the structure of Queries. This means that many queries can now be built with just a single table, or perhaps two, while the system automatically filled-in the missing information. We also added meaningful descriptions to field names, so rather than just seeing CMPNT_SYS_IO_TYPE_NAME, the user also sees "Instrument I/O Type".

The Result?

If you're an SPI user, you'll find building a Query is not very different to defining a Browser View. Simply select the fields you want, and the system does the rest. This one table is all that's needed for a simple Instrument Index report.

Of course, if you'd like to add some more tables, that's fine too, it's still a fully functional Query Builder. For example, if we wanted to include some fields from the Component Function Type table, then we just add it to the Query and join in the traditional way.

Here we've added the second table, and then used Virtual Fields to provide the default Cable Name, Panel Name and Specification Sheet Number for each Instrument Type. (Click the image to enlarge).

For the SQL-geeks, this produces the following (for the non-geeks, you can ignore this bit, it doesn't really matter!):

Select
                    COMPONENT.CMPNT_NAME,
                    COMPONENT.CMPNT_FUNC_TYPE_NAME,
                    COMPONENT.CMPNT_SYS_IO_TYPE_NAME,
                    COMPONENT.CMPNT_LOC_NAME,
                    COMPONENT.CMPNT_MFR_NAME,
                    COMPONENT.CMPNT_MOD_NAME,
                    COMPONENT.CMPNT_SERV,
                    COMPONENT_FUNCTION_TYPE.CABLE_NUM,
                    COMPONENT_FUNCTION_TYPE.PANEL_NAME,
                    COMPONENT_FUNCTION_TYPE.SPEC_FORM_CNUM
From
                    COMPONENT
                    Inner Join
                    COMPONENT_FUNCTION_TYPE
                    On COMPONENT.CMPNT_FUNC_TYPE_ID = COMPONENT_FUNCTION_TYPE.CMPNT_FUNC_TYPE_ID

A simple SQL statement, which you could hand-edit if you chose to. Of course, this SQL statement wouldn't work in any other application (the Virtual Fields would cause errors), but APSR correctly interprets it and translates it into the SQL statement required by the database server. There are actually nine (9) different tables in the query above, seven (7) of which are managed automatically by APSR.

I believe we've found the right balance between simplicity and power. Virtual Fields, and Field Descriptions, help both the novice user by making queries incredibly easy to build, and the expert user by allowing them to create much more complex queries while still automatically handling many of the trivial relationships.

If you'd like to try APS Reporter/SP, and the Query Builder, use the link on the right to request a free trial license, or if you have any queries(!) or comments just post them below.

Regards,

Brian