Useful Tool for Developers and Administrators

In this post I want to briefly discuss how Developers and Administrators can use the new Free License for APSR to get easier access to SmartPlant databases.

When you're working with COTS (commercial-off-the-shelf) software, sometimes you want to go beyond what the app will let you do. Sometimes you want to look under hood and work out what it's doing, or maybe pull some data out to do something clever with it. If you're a Developer or a Power-Admin, sometimes you need more than the tools the vendor gave you.

For example, the SmartPlant apps are all complex, powerful tools. They provide a wealth of functions, and an experienced user can do amazing things very quickly, but occasionally you want something that the developer just hasn't provided. Perhaps you want to use it in a way that wasn't intended, maybe you want to integrate with some other 3rd party application, or you just want to see how it works under the hood.

Unfortunately, delving into the databases behind powerful apps such as the SmartPlant suite can be daunting. Databases for modern apps are generally very complex. They're highly optimised and designed for flexibility and performance, but not necessarily ease of use or access by the end-user.  The databases are complex and cryptic, a lot of experience is needed to work with these.

For example, the SmartPlant Instrumentation (SPI) database has hundreds (300+?) of tables and extracting meaningful information usually means identifying a dozen or so tables that you need to work with, and then constructing complex queries to pull out just the info you need.

On the other hand, SmartPlant Foundation (SPF) goes the other way. It only has a handful of tables (15 or so), but all of the data for hundreds of Classes and thousands of Properties is contained in just those tables. Finding what you need is incredibly complex, and pulling out a few properties almost impossible. If you want to create anything like a complex query relating multiple classes, you're out of luck.

Then there's SmartPlant P&ID (SPPID) and SmartPlant Electrical (SPEL) both of which use a type of inheritance model that fits somewhere between SPI & SPF in complexity, but doesn't really function like either of them.

Simple database query tools are available, such as those in the database management apps, or 3rd party tools like Toad, or even reporting apps like MS-Access or Infomaker. With these you can access your data, but they do nothing to simplify the very complex database structures, or help the user navigate the database.

APSR is designed to solve this problem. It provides the raw access to the database needed by Developers and Power-Admins, but also uses Virtualisation to simplify the data model.

Suddenly, in SPI you don't need to find those dozen tables anymore, maybe just two or three, and the field have meaningful descriptions. The Component table shown below includes many Virtual fields, and the descriptions in green text help to find the right fields.

In SPF, Classes are available as Tables! And Properties are Fields!

And in SPEL & SPPID, Items appear as Virtual Tables with all Properties resolved (no need to link tables up the inheritance tree).

No other database tool provides this SQL Virtualisation support for the SmartPlant apps, or makes it so easy to access the database and produce you're own queries and reports.

The next few blog posts will explore some examples of using APS Reporter/SP with each of the SmartPlant apps, and how useful it can be to the Developer or Administrator. I'll provide some real-world examples of what you can do for each system, and things that just aren't possible without a tool like APSR.

For now, if you'd like to try the latest version of APS Reporter/SP, a Totally Free License is included in the download package. Use this for free, indefinitely, it will not expire or require any purchase. The installation package can be downloaded from the links to the right.

Regards,

Brian Raynor.