Microstrategy Blog

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.

Reports, Things I Learned FreeForm SQL, Microstrategy

Did you know you can actually create on the fly reports in MicroStrategy Office? Of course you did…I mean, even I saw the video lauding how it can be done.

I just never took the time to figure out how. On the video it say’s “here’s the wonderfulness of creating reports”, however, it never says the settings that need to be changed. When I click on “File”, all I see is the image below. There’s now “New”.

So, there’s a couple of key settings you have to change. In your MicroStrategy Office application, click on “Tools -> Options“, and then click on the first option under General, which is Server. If you did an upgrade like we did, you’ll see that your Web URL is blank. Here’s where you enter your actual MicroStrategy web URL, usually: http://localhost/microstrategy/asp/main.aspx as seen below.

Hit save, and voila:

Now you get the pleasure of this when you create a new Blank Report:

Web is now integrated with Office, making you Excel snobs who refuse to Migrate to a strictly web environment content…at least for the time being.

Basic Series, MicroStrategy Office, Reports, Tips and Tricks

Okay, so this is an old one, but I still find it useful.

If you can’t run a report because of a spool space error, and you can’t convince the DBA to give you a couple of gigabytes more, then you might want to go to the Report Editor, Data ->VLDB Properties -> Tables -> Intermediate Table Type and select True temporary table.

What this does is modifying the SQL so that instead of having a huge chunk of code (that would use up a lot of memory) it breaks the code into CREATE – INSERT bits that are far easier to handle. It also makes the SQL more readable by humans (this includes programmers).

By the way, this setting also comes to rescue when you get the “Database is terrified by the script and it can’t even begin to analyze it” error. I don’t have the specific error message at hand, but I know for sure that Teradata does this when the SQL code is all in one pass and is so large that it can be seen from the Moon.

Reports, Theory, Things I Learned, Tips and Tricks

It’s the little things in life that make me smile. A brand new video game…a good sci-fi book…cheese…and now, notes.

I’ve been playing around with my 9.0 Development installation, and I really like what see. Integrated Narrowcast functions, a smoother ride in the “new” environment, and gee golly, “Notes”. One of the reasons that we rely so heavily on MicroStrategy Office in our organization, is because the 8.X version of MicroStrategy does not give users the ability (short of SDK or creative RSS) to communicate with each other. So we pull our reports out of MicroStrategy, into PowerPoint, where notes are added, and then printed into a PDF where they’re distributed. It’s a complicated process that usually involved as much time being spent formatting the visuals as is spent actually understanding the data. With 9.0, that can come to an end. Now, Administrator and report users can communicate back and forth…complete with time stamp (editable of course), without the complexities and time drain of exporting.

While not exactly as “stylized” as some might like (I prefer my style in the report itself, not the explanation), this “Notes” feature can be seen in the Object Manager, in a collapsible menu underneath your Dataset Objects. “Notes” can be added to Reports, Dashboards and Documents, adding the benefit of not only dynamic formatted data, but the explanations necessary to give the report viewer the context in which those numbers matter. Once the explanation is added…MicroStrategy 9.0 then allows you to send that report to whomever you please, whenever you please.

Dashboards, MicroStrategy 9.0, Reports, Tips and Tricks Dashboards, Documents, MicroStrategy 9.0, Notes

Here’s some weekend reading suggestions for any of you data heads out there. Everything from the Data Visualization essentials from Tufte and Few, to a few books about Data Warehousing. Great Stuff:

There’s really is a lot that can be learned about Dashboarding through the reading of these books, and check the sidebar for the link to Perceptual Edge. I have to give credit to one of the better presentations I attended that at MicroStrategy World 2009 by Michael Sandberg, who at the time was with Disney, and turned me on to some of these publications.

Reports, Theory