A while ago I had the opportunity to prepare a short training on the FreeForm SQL reports for some of my colleagues. I thought I’d better share this with you as well, so… here is a not-at-all-exhaustive list of things worth knowing about this type of report.
What is a FF SQL?
FreeForm SQL (aka FreeStyle SQL) is a type of report which allows the designer to define its own SQL code. Notice this is a rather different approach than what we are normally used to in MicroStrategy, where we would usually let the SQL Engine do the job.
When should it be used?
1. Whenever you tried everything (VLDB properties, Metric Levels, Attribute Relationships, Table Logical Size, Data Model changes…) and the SQL Engine still doesn’t perform that join the way you want it to.
2. Whenever you want to use a data source which does not exist in the Warehouse Catalog. That’s right, with FF SQL you can use almost any data source, including those tables you see in the left hand side of the Catalog.
How do I recognize it?
You don’t, at least not from the outside. You can either edit a report and see if you can spot the unnoticeable “Freeform SQL Definition” button, or you can right click on a report and make a component search. If it has no components, you found it.
So, unless you set up some naming convention for FF SQL reports you will very soon lose track of them.
How is it created?
Click on “New -> Report” and select “Freeform SQL” from the list (you need a special role to see this option).
Type your code in the upper right hand side frame.
Now you have to map the items from the “Select” section of your script to virtual Attributes and Metrics. To do this, right click on the lower frame and select either “Add new Attribute form” or “Add new Metric”.
For a script like “SELECT country_id, country_name, SUM(sales)…” the mapping should be defined as:
These are virtual objects. You may actually have a “Country” Attribute in your project, but please don’t make the mistake of thinking these two are the same thing.
Click OK and you are back on familiar grounds.
Prompts in FF SQL
You can either create or insert them into a WHERE clause, by using the two dedicated buttons. The Prompts will show in bold pink.
Now, here is the interesting part. Let’s say you want to make some changes to the script, and you copy it into an external editor. You will notice that the prompts were converted into object IDs. Now, paste the text back into the FF SQL editor. They are bold pink again. Too bad the report won’t work anymore…
The prompts will have to be inserted again, the “button” way, if you want the report to run properly.
Words of caution.
Being what I call a “manually defined object”, the FF SQL is not subjected to automatic Schema updates. That is to say, you won’t be warned if you delete a table which is being used by such a report. So please bear in mind that maintenance for FF SQL can be harsh.
Recent Comments