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.

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