Graphs & Stats from the SPF Database

In this final post of three covering Virtualisation in SPF (the others are here and here) I'm going to look at producing simple statistical reports from SPF databases using straight-forward SQL Statements.

As discussed in the previous posts, using a Virtual Model of the SPF database we're able to produce reports directly from the source database without complex coding. The model appears as a straight-forward relational database, with Virtual Tables representing the various Classes, Interfaces and Relationships of SPF. The SQL language is a powerful tool for manipulating relational databases and modelling complex systems as simple relational systems allows us to leverage the power of SQL, while ignoring much of the underlying complexity of the source database.

By way of example, I'm going to produce some reports on Transmittals in SPF. The Queries are deliberately simple, but they could easily be extended to cover a range of requirements. If you would like full step-by-step guidelines to build these reports, refer to the Wiki.

Starting with a simply Query, just the ISPFTransmittal Table. This is a Virtual Table for the corresponding InterfaceDef, and provides a list of all Transmittals in the system.

ISPFTransmittal Table

The Query is simply generating a list of Transmittal Issue Dates (Years & Months), and then counting the Transmittals in each month. This gives us a simple list:

Chart Wizard (click to enlarge)

A simple list of results is fine, but a graph will make a much better report. Switching to the Report Designer, we can add a simple a simple Chart to our report.

This gives us a nice visual indication of the spike in Transmittals issued in November & December. 

But how many documents were actually issued? In November, were there 120 documents, or 1200 documents?

By adding the relationship between Transmittals and Document Versions to the original query it will now count Documents issued rather than Transmittals. (Wiki)

This gives us:

An extreme peak in November still, almost 1000 documents issued via Transmittals. Does that mean that there were a lot of Transmittals with around 10 documents each, or perhaps there were one or two that accounted for the bulk of the documents? (Wiki)

Going back to the original Query, we can wrap the SQL statement in another clause, and this time count the number of Transmittals based on the size of each (number of Documents), using the CASE statement:

This will assign each Transmittal into one of 5 Categories. 

Just a few simple Queries lets us extract useful data, without custom programming or specialised skills. This is the power of the Schema Virtualisation in APS Reporter/SP, it lets us work with our data without regard to the complex structures of the native databases.

There is a complete walk-through of the Queries and Charts above on the Wiki site.

If you have any questions or comments, please feel free to leave them below, or contact me at one of the links to the right.

Regards,

Brian.