Register now or log in to join your professional community.
Freeform SQL Reports are tied with the query written, DB Instance used. Any change anywhere and you need to rework on Freeform Report to get the same aligned once again. This is because Freeform Reports are black boxes to the MicroStrategy Query Engine. But otherwise, MicroStrategy Query Engine is smart enough to adopt the changes.
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 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 for1-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)
