It makes the visual browsing of adhoc data quick and efficient. You connect to the database table or view, and just drag and drop what you want. Where it bests MicroStrategy (as I’ve used it) is the numerous amounts of dynamic charts that can be created at a whim without lots of planning. So, view the site, play around with it, and let me know if this is something that MicroStrategy can do, especially in regards to the idea of 50 or more charts on one page, quick, and responsive.

Here’s a simple one to customize you’re look and feel of MicroStrategy web. After logging into the web site, at the top of your quick toolbar, you’ll see an option for preferences. Under general, you should see the menu below ->

Here you can actually change the look or feel. For those CSS inclined, you can make your own, or add your corporate branding. The style sheets are located in

C:\Program Files (x86)\MicroStrategy\Web ASPx\style\mstr

And you can keep drilling in for the images, and their specs.

Any people out there know the steps to add their own?

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.

Give me time.

You probably found out by now that the Type and SubType properties of an IDSSObjectInfo (or similar class) return a numeric value. Call me picky, but if I were to build an application I would rather use layman’s terms such as Attribute or Drill Map instead of SubType 3072 or SubType 14336.

This third part of our tutorial offers a great way to convert these not-so-intuitive numerical values into their English meaning, by using a custom made function called DSS2Name.

You can download the VB6 files here:
SDK Kit part 3.zip

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.

There was a conference call last Thursday (10-08-09) led my COO Sanju Bansal to address the Free MicroStrategy Reporting Suite. I’ll keep it concise. It’s awesome, I’ve always thought it’s awesome. This call was meant to assist in the roll-out (getting bloggers to chatter about it, I’m assuming, is part of their Marketing strategy). So here’s my chatter. If you don’t take advantage of this, you’re retarded (take that PC police). I consult by the way…send me an email.

The conference starts with a background about the company. Largest Independent Public BI program….great fiscal health…etc. If you’ve attended MicroStrategy World, this is the first 15 minutes of any meeting you’ve attended. They present MicroStrategy as “Big Picture” platform software. Why “Free”? They want to get on the “right side of history earlier than everyone else.” The product is truly free, and there aren’t any “catches”. In order to attach to a piece of the BI software pie, then your free product needs to be fully capable, otherwise, what’s the point?

In other news…Subway just bought MicroStrategy?

There is a Gartner report they wanted to push. Check out the web page www.microstrategy.com/GartnerVol3

I only had one question (the least interesting since I’m not a industry analyst, but a video game geek), and so I’m not articulate or capable of putting together a coherent thought. I asked Sanju Bansal how customers that bought licenses before the free package would be able to match some of the free offerings. In my case…the free version has 2 “full” desktop licenses, my company has one. His answer was to download and utilize the entire free package…well, not exactly what we can do. We have well over a hundred licenses. What I want is that one extra licenses to I’ve at least met that minimum threshold of free licenses. I just want a free license! I’ll have to follow up with my sales person on that.

Anywho, here’s the link to the free reporting suite. Use it. Love it. I’ll leave the rest of the coverage to the pros.

The Rank function takes care of all of this. The power isn’t that you can rank everything necessarily, but that you can decide at what point the rankings are broken up. Who was your best customer in 2008? How is your worst customer now?

The Rank function is the easiest way to tackle this. When creating a metrics, simply use Rank([Fact]) or Rank([Metric]). That’s the easy part, the next part is what requires a little bit of critical thinking. Rank is only valuable when you’ve broken down some sort of parameters. To set the parameters, click on the word “Rank” with your mouse and select Rank Parameters from the Tools option in your metrics editor, or right click on the word “Rank” and select parameters. What you will see is this:

You set the specific attributes you want to break by in the parameter screen. In this case, I’m interested in seeing the top customers by Year and Business Unit. You can add as many metrics as you want in this screen, keeping in mind that you have to build the reports with those specifications in mind…rank metrics can act funny if you’re not very aware of how you’re aggregating them and you just start throwing them into any old report.

Remember when I did this post about how awesome derived elements are? Well, they’re still awesome, but sometimes I forget that I have awesome all powerful access equal to that of the Gods…sometimes it comes back to bite me.

I rolled out the Derived Elements function to our users. They were as thrilled as I was. Since we’re a project based organization (I work in Video Games) projects come and go, and our Data Warehouse has a constantly expanding list of projects. So, our user base gets to dynamically group like projects based on their predesignated lists (it varies per Business Unit); one list might be “fighting games” and the other “downloadable games”, but next month, there might be more.

Things fell apart when the Web Professional users had new projects that appeared, and they were unable to edit their Derived Elements…once they created them and saved them out to their “My Objects” folder…that was it…it became static. Great functionality…but only so much. As a desktop user, I can get into them and edit them, but our users don’t want to call me to say, “Hey, add project A to group B.” The tutorials like to use “Seasons” to group months, but I think the assumption here was that the lookup lists were static, which isn’t the case with an organization such as ours. So case logged. P4.

I’m thinking Hot Fix 3 has my name written all over it…right?

Some quick lessons…

After you created a linked data source using an ODBC connection for the Pervasive SQL database (seemed the easiest way) I found that querying it was a pain…

Sometimes I’d get:

The OLE DB provider "MSDASQL" for linked server "BUSINESSWORKS" returned an invalid column definition for table.

That’s because half the time "SELECT * FROM BUSINESSWORKS.DATABASE_NAME..GLAccount " just doesn’t work.

The solution?

SELECT * FROM OPENQUERY(BUSINESSWORKS, 'SELECT * FROM DATABASE_NAME.GLAccount')

This seems to be the fix for any Pervasive SQL linked database in SQL Express. After you get the connectivity stable, now you have to deal with the fact that this is one of those databases that has columns named “History01, History02, etc”.

Good times.