APS Reporter/SP is a great tool for SPF Developers and Administrators who need to run adhoc queries on the SPF database. It makes it very easy to review data and investigate problems, and also to extract data when required.
In this post I'm going to look at a simple example of querying the SPF database to check a problem reported by an end-user. Of course, each SPF installation is different and the specific queries shown here are probably not applicable to your site, but APSR automatically adapts to the local SPF Schema and will generate queries based on your site's configuration.
Tuesday, April 22, 2014
Wednesday, April 16, 2014
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.
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,
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.
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! 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.
Saturday, April 5, 2014
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.
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.
Friday, April 4, 2014
Free Query & Reporting Tool for SmartPlant Databases
APS Reporter/SP is the best tool for extracting data or producing complex reports from SmartPlant databases, or other Oracle or MS-SQL databases. It has a powerful Query tool with features not found in other applications, a unique Virtualisation process to simplify the source databases, a flexible Grid tool for formatting reports, and a powerful Banded Report engine for pixel-perfect Reporting.
At APS, our developers love Reporting (a bit geeky, I know). Some of us have been doing it for our whole professional career, and we know a thing or two about what makes a good reporting system. We've distilled that knowledge into an application that produces the Reports we need, that works the way we know it should.
It's only problem? Not enough users.
Solution? Give it away for Free!
Solution? Give it away for Free!
Wednesday, September 4, 2013
APS Reporter/SP & SPF Integration
Deploying reports to a large number of end-users can be a logistical headache, you need to either deploy the software to a large number of PC's in your organisation, or perhaps provide access through a Citrix system, and then you need to train end-users in the software, and manage software upgrades, etc. Fortunately, if you're working with SPF, there is a simpler way.
Friday, July 26, 2013
Loading Data into SPF
SPF Projects often require large volumes of legacy data to be imported, and this initial loading can account for a significant portion of the time and expense of establishing new SPF systems. With staff involved in a variety of SPF projects, we believed that the data migration process could be significantly improved using our reporting application, APS Reporter/SP. In this blog post I discuss some of the new features of APSR for generating SPF Load Files directly from source data.
Friday, March 22, 2013
SPPID PipeRuns and Recursion....what fun!
One of the things I really enjoy doing with APSR is pulling apart databases to uncover things hidden deep in the data. Most application databases provide simple reports, but when you want to do something complex is when APSR really shines.
(Warning: This post contains some advanced Queries and Scripting, sorry :-)
Recently a client found some issues with the tagging of PipeRuns in their SPPID system, and it raised questions about how many other problems were lurking in there, and how could we find them. This is a great example where APSR should be useful.
(Warning: This post contains some advanced Queries and Scripting, sorry :-)
Recently a client found some issues with the tagging of PipeRuns in their SPPID system, and it raised questions about how many other problems were lurking in there, and how could we find them. This is a great example where APSR should be useful.
Subscribe to:
Posts (Atom)