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

PipeRuns, Lines, and Regular Expressions

Many of the custom reports we develop perform some type of data validation, whether we're checking data delivered to clients, looking for data-entry errors, or preparing data for publishing. Validation usually involves multiple processes, but often the initial issue is to determine whether the data even conforms to the required format.

For this, Regular Expressions can be a really useful tool, but they're also a little cryptic and confusing. Regular Expressions have a variety of uses, but for data validation they let us define a format for data (text strings) and then to check our data for matches or errors.

Recently we were designing a report to validate the Item Tags of PipeRuns and of course the initial issue was determining whether the format of the Item Tag conformed to the project's standard. The correct format is as follows:

              LX-100-789-GN12-BL1XY1-ISN

It's possible to write some complicated SQL using SubStrings to pull apart the text, but Regular Expressions are more concise, easier to modify, and easier to test.

Using APSR, we use a short Script to initialise a Regular Expression, and then test each data item against it. For example, to test the Line Number format above, we'd use the following Regular Expression:

              ^[A-Z]{2}-\d{2,3}-\d{3}-[A-Z]{2}\d{2}-BL\d{1}[A-Z ]{2}\d{1}(-[A-Z]{1,2})?$

That looks confusing, but breaking it down, it all makes sense:

              ^ 
                            Start of the String
              [A-Z]{2} 
                            Characters must be between A and Z, exactly 2 of them
              \d{2,3} 
                            Between 2 or 3 digits
              \d{3} 
                            Exactly 3 digits
              [A-Z]{2} 
                            Characters must be between A and Z, exactly 2 of them
              \d{2} 
                            Exactly 2 digits
              BL 
                            Literal text (the characters B & L)
              \d{1} 
                            Exactly 1 digit
              [A-Z ]{2} 
                            Characters must be between A and Z, exactly 2 of them
              \d{1} 
                            Exactly 1 digit
              (-[A-Z]{1,3})? 
                            Between 1 and 3 Characters from A-Z, but they're optional.
              $ 
                            End of the String

In the first Validation phase we check the PipeRun ItemTag against the Regular Expression. For ItemTag's that fail this first test, there's not much more we can do, but if we get a match, then usually we'd move on to checking the individual components of the ItemTag (Nominal Diameter, Fluid Code, etc).

The Script below takes the "Lines" table created by the Query, adds a new Boolean column to indicate the result of the Regular Expression match, and then iterates through all of the entries in the Lines table (unique PipeRun ItemTags in this case).

This Script actually checks two different Regular Expressions, checking each ItemTag against first one, and if it fails then checks the other. If either Regular Expression is a Match, then "FormatMatch" is set to True (otherwise False).

The remaining Validation checks are then implemented in the Grid window (the Regular Expression can also be implemented in the Grid window, but we chose to use a Script for performance reasons).

In the sample report, we construct a "Derived ItemTag" which is a Calculated Grid Field composed of the various data fields that make up the Item Tag, in the correct format. Ideally, the Derived Item Tag, and the actual Item Tag field will match, but if they don't then someone needs to check why.

The Grid is filtered to only show PipeRuns with errors, and to highlight PipeRuns with invalid Item Tags in yellow, and mismatched Derived Item Tags in pink. Note that we could have configured the Grid to also compare the constituent parts of the Item Tag and highlight the specific sections in error. (apologies for the blurred text, but we can't show our Client's data).

If you'd like to use this report on your own SPPID system, or to modify it for your requirements, a copy of the final APSR report is available (note that you will need APS Reporter/SP installed).

By the way, if you're creating your own Regular Expressions, I'd recommend a copy of Expresso. It's a great tool for building regular expressions, and you can also provide test data to confirm you're finding the right errors.

Regards,

Brian

First Post

Welcome to "Engineering Intelligence" where I'll probably talk about Engineering (occasionally), Data Management (mostly), Software Development (sometimes), Business (rarely), and some of it may even be Intelligent!

At APS we spend most of our time working with various Engineering Database systems such as Intergraph's SmartPlant Suite, providing consulting services for SPI, SPEL, SPPID, SP3D & SPF, and for the last few years we've been putting an unreasonable amount of time into our pet project, APS Reporter/SP (APSR).

This has been an interesting journey into .NET (and Software Development in general) that has taken far longer than anyone expected, but we now have undoubtedly the best database reporting tool for engineering requirements. APSR lets us do things that just can't be done with other data management & reporting tools, we get to delve deep inside our databases mining for the information we know is in there, but is often hidden behind application front-ends and layers of database normalisation & optimisation.

In this Blog I'll be discussing some of the cool things we can do with APSR, some development related stuff about features for upcoming releases, info about the various databases we support, and some general Engineering Data Management issues. We're really enthusiastic about APSR and the software development team are proud of what we've achieved, we now want to help as many people as possible get the most from their databases.

Our initial plans were to develop a package that could handle complex reporting requirements,  to automatically manage title blocks, to separate report design from document definitions, to support revisioning of documents, to enable revision comparisons, and to seamlessly support multiple project databases from the same system. These were all functions that we need in almost every report we issue, but no existing tool could handle them out of the box. Version 1 implemented most of the planned features, with support for MS-SQL and Oracle databases.

In Version 2.0 we added a new function that could revolutionise reporting for complex relational databases (and not just engineering databases). Virtualisation of the database schema allows us to hide the complexity of large  database systems behind a simplified Virtual Model of the database, making reporting (and data extraction) incredibly easy. Currently supporting SPI, SPEL, SPPID & SPF, we plan to extend the Virtualisation to additional systems very soon. Words can't do justice to the power of Schema Virtualisation, you really need to see this in action to fully appreciate just how powerful it is. (Of course, we still support any other MS-SQL or Oracle database in a non-virtualised mode).

Version 3.0 added Sub-Reports to the Banded Reporting System in APSR, and begun laying the ground work for major new features to be added later. With Sub-Reports we can get much more creative with the page layout and solve problems that would be incredibly difficult otherwise (for example SPI Termination Reports with Left & Right hand connections are virtually impossible without Sub-Reports).

Now, in Version 4.0 we've added the most requested feature from clients, reporting from multiple databases. Reports can now combine data from more than one database, such as creating Cable Schedules by combining Cables from SPI & SPEL, or producing an Instrument Index from SPI with additional data from SPPID, or linking SPF data with other corporate databases for consolidated reports. We also threw in multi-query support, and a complete Scripting system (VB & C#).

This new release is the complete app we've been working toward for almost four years and we can finally do all those things we really want to, without hitting brick walls when the tool came up short. With the Virtualisation system we can generate powerful queries to extract data, Scripts can slice & dice the data any which way we need to, the Grid tool provides a simple tabular (think Excel) tool for formatting data, and the powerful Banded Report Designer allows us to produce pixel-perfect deliverable Reports.

Of course, it's not just Reports. We can extract data for users to run adhoc analysis, or export data to other systems, and even schedule reports with automatic email dispatch (great for regular daily or weekly reports).

If you'd like to learn more about APS Reporter/SP, you can check out the web site at APS Reporter/SP, or watch our demo videos at YouTube.

Regards,

Brian