Freeform SQL Reports

Author: Bit Software
Date: 09- 09- 2011

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.


by Bryan Brandow

(This article was first published on Bryan`s MicroStrategy Blog)