Detecting Freeform SQL Reports

I’ve written about the merits of Freeform SQL reports before, and how they can become very useful in a pinch but with a few consequences.  For example, one of the great advantages of MicroStrategy is that you can switch the underlying database platform without having to change anything except to tweak a couple of settings for performance.  Of course, that won’t apply to Freeform SQL reports since you’re providing your own code.  When facing such an upgrade, it may be necessary to round up all of your rogue Freeform SQL reports so that you can address them.  Today I’ll talk about two different methods for finding and keeping an eye on them.

Method 1 – Search for Dependents
The first thing to note is that Freeform related objects are slightly different than normal Attributes and Metrics.  They’re referred to as Managed Objects which you can’t easily browse.  The best way to reach them is to perform a Search in Desktop.  Under the Tools -> Options dialog, there are two options to Display Managed Objects and those only:

Perform the search at the Project level, and you’ll find all of the Attributes from your Freeform SQL reports.  It won’t find the reports themselves, but you can subsequently highlight the attributes and do a Search for Dependents to find the attached reports.

This is the fastest and easiest method, but if you have a lot of Freeform SQL reports, it could be cumbersome to work with the Search dialog, not to mention that you can’t export this into a list.

Method 2 – Query the Metadata
Fortunately, there’s a simple query to extract this list directly from the metaata using a little trick.  The DSSMDOBJDEPN table contains all of the relationships for direct dependencies.  These are what’s visualized when you choose Search for Dependents from an object in Desktop.  Interestingly, Freeform SQL reports create a Logical Table that is not visible anywhere, but represents the query that you’ve provided.  This hidden Logical Table is a direct dependent of a Freeform SQL report itself, which is something unique to these types of reports.  Since the relationship between Reports and Logical Tables in standard reports has several layers between it (Attributes, Metrics, Facts, etc), we can use this fact to easily find them:

Type 15 is a Logical Table and Type 3 is a Report, so as mentioned above, the only Reports that are going to have direct Logical Table dependencies are Freeform SQL reports.  Voilà!


Notes

• After 9.2, the metadata stored Object IDs (including the Project ID in the query above) changed format and no longer match the IDs that you find via Desktop’s UI.  Consult this post for how to convert them.
• If you want to add folder paths to your query so that you know where the reports are located, check out the first two blocks of SQL in the Site Map post that shows how to do this.  I won’t reproduce it here.