Simple SPF Query

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.

In this example, a company requirement is that each Document in SPF with a Microstation DGN file must also have an associated PDF file (for viewers who can't work with DGN's). End-users have reported that some DGN documents do not have the accompanying PDF file.

How do we find these documents, and determine how big the problem is?

Firstly we need to create a new Report for the SPF database.

When the Query window opens, we select the ISPFDocumentVersion table. 

This Virtual Table represents the ISPFDocumentVersion InterfaceDef in SPF. We can base our queries on either a ClassDef or an InterfaceDef, depending on our requirements. By using the InterfaceDef our results will include all Classes which use this Interface.



We now need to create two sub-queries. The first will count the number of PDF's associated with a Document Version, and the second will count the number of DGN's. To create a sub-query, we add a Derived Table to our query.

We should rename our sub-query to something meaningful, in this case I'm calling it PDFs.

This changes the name of the Derived Table on the main drawing, and also the name of the tab for the sub-query. We now switch to that tab to create our first sub-query.

On the sub-query tab, we add the SPFFileComposition and ISPFFile Virtual Tables. These represent the SPFFileComposition RelDef and the ISPFFile InterfaceDef respectively.

We join the two tables as shown, and select the fields we need. The Configuration, Attached to Domain UID, and Attached to UID fields are needed by the Main parent query for joining.

This sub-query needs to count the number of PDF's associated with each Document Version, so we Group by the appropriate fields, filter by the LocalFileName field (%.pdf) and then Count the results.

We build our second sub-query in exactly the same way, but this time we filter by %.dgn.

Back on the Main tab, we join the ISPFDocumentVersion table to the two sub-queries as shown, and select the fields we need in our results. In this case, I'm returning the Document Version DEFUID, Name, the PDF Count, and the DGN Count.

We also need our joins to be Outer Joins as we want all results returned.

Switching to our results grid, we can see our Document Versions, the DEFUID column (which shows us the Class of the Version), and the two count fields.

(Note that my sample data is virtual empty, so I don't see a lot of results with this database).


We're only interested in Document Versions which have DGN's, but no PDF's, so we should add appropriate filters.

Switching back to the results we can now see our problem documents.

We could also filter by selected ClassDefs, and should probably only included Versions that are not Superseded. Both are simple filters.

In fifteen minutes we've got a detailed list of problem documents. If there are only a few to be fixed, perhaps someone can do it manually, but if there are many hundreds, then that would indicate some type of systemic problem. 

APS Reporter/SP is a great tool for adhoc queries, and everything I've shown here can be done with the Free License, so it's simple to get started with it.

If you have any queries, post a comment below of contact me at one of the links to the right, where you will also find a download link for the free version.

Regards,

Brian Raynor.