Freeform SQL Reports

Freeform SQL Reports can be a handy tool in your MicroStrategy bag of tricks.  We’re all tempted to turn to them from time to time, but when is the right time and what kinds of tricks can we manage by leveraging them?  As always, the answer to the first question is going to be a personal preference and depend greatly on your project scenarios.  Today I’ll offer my opinions, as well as highlight some of the tricks you can do with Freeform SQL Reports.

What are Freeform SQL Reports?
MicroStrategy’s core purpose is a SQL engine.  You define the schema and it will dynamically write the SQL by reacting to the objects you drag on and off of the report.  It’s incredibly powerful and flexible and empower users to do their own data mining and analysis.  A Freeform SQL report skips all of that.

Basically, you bypass the MicroStrategy SQL Engine and instead provide the SQL directly for the report.  You still have to use MicroStrategy objects and map it to the report so that it has something to anchor on to, but you have a little more flexibility in doing this.  You don’t get advantages like true drilling (you can still use links) but it can really help out in a pinch by letting you provide some complex queries directly instead of breaking your back trying to manipulate the schema to handle it.

When to use it
Personally, I love using Freeform SQL Reports for Exception Reporting.  Specifically, when I’m going to be using tables that would otherwise violate my normal data model.  For example, if I’m trying to expose an exception table that contains attributes and facts my normal reporting using, exposing it to the schema could create join paths and aggregate table options I wouldn’t otherwise want a normal report to access.  While there are plenty of ways to avoid those tables, it’s not necessarily a complication I want to deal with.  Freeform SQL Reports are best used for 1-off reports that either access tables not otherwise used in normal operations.

When not to use it
Since you’re basically skipping the core function of MicroStrategy, they really should be used as a last resort.  MicroStrategy is a tool, and sometimes it can’t do everything you want immediately (specifically, things like theta joins and difficult relationships) but these should usually be solved with more thought out ETL and Data Model design than taking the FFSQL shortcut.  If you lean too heavily on these reports, you can really put your project at a disadvantage down the road in terms of flexibility (drilling), portability (changing database platforms) and scalability (introducing aggregate tables).

Mapping Objects
When you paste your SQL into the FFSQL Editor, you must map it to objects.  You can either create new objects specifically for Freeform use (referred to as Managed Objects) or map to existing objects.  It’s important to note that mapping to existing objects will require the mapping of the ID form, and then optionally any other forms you want to use in your query (most commonly the DESC form).  The advantage of linking to existing objects is that you have a slightly cleaner environment and better support for the links feature, but the disadvantage is it gets a little more complicated to unlink from the Managed objects which can cause some migration issues down the line.  Personally, I only link to the normal schema objects if I intend for there to be some interaction between normal reports and FFSQL reports, and usually stick to Managed Objects for 1-off reports.


Tricks & Tips

• One of the best uses for a Freeform SQL Report is to point it to a Stored Procedure or use MultiPass SQL.  There are VLDB settings to ignore passes with no data results, but for some reason you can only accept data from the 1st or 2nd pass.  Anything after that gets ignored for results, but still gets executed.  
• Another thing I like to use FFSQL reports for is to place some custom processing SQL that a user needs to run.  This is a pretty rare task for a Data Warehouse, but sometimes there’s a case where a user needs to kick off a process.  This is an easy way to give a user access to database Processes.
• You can use normal Element Prompts in your Freeform SQL Reports so the user won’t notice anything is different.  You can also use the User Prompt to pass the User Login.  The editor also comes with the ability to mark section of SQL optional so that if the user doesn’t answer the prompt, incorrect SQL isn’t generated.
• You can point a Freeform SQL Report to any DSN, not just the primary.  Without a Multisource license, this is an easy way to expose some quick bits of data from another system (like a Transactional system) into your project without additional costs or complicating modeling.
• If you closed out the option to create a Freeform SQL report long ago, you can get it back by going to My Preferences in Desktop and under the Object Templates section, checking the box for Report.   Now when you click New Report, you’ll have the option to create it from a DSN (Freeform).