Average of Averages… no, seriuosly.

Tom, Bill and Sandra have one euro each; they go and buy some fruits.

Tom  buys 3 apples for 1€. Bill buys 2 oranges for 1€ and Sandra buys a coconut for 1€.
Tom says “apples cost 0,33 euro cents on average”.
Bill “oranges are 0,50 euro cents on average”
Sandra “coconuts are 1 euro on average”

What is the overall average price of fruits ?

I have seen more than once calculating this as:

0.33 + 0.50 + 1 = 1.83
1.83 / 3 = 0.61

Now, you are a Business Intelligence professional: imagine your customer asking you to calculate an average, such as the price per product grouped by product type and the total overall price per product at company level.
Next he dig into his laptop and proudly shows off his “treasure” Excel sheet that does it all, and by magic copy and paste computes the AVG() of the AVG()…

Unfortunately for your client, acrobatic math is not yet an Olympic discipline.

Tom, Bill and Sandra soon realize that they bought 3 + 2 + 1 = 6 pieces of fruit and they spent 3€, so a single piece is 0,50 euro cents.

Similarly your customer (after an intense discussion) will realize that for the last 15 years he used to calculate a price per product average “from a different point of view”. You may want to ease the pain by saying that it is a quite common error and he’s in good company: see Simpson’s paradox and the Berkeley gender bias case

In MicroStrategy terms this is what is called a “Smart Metric”.

When in the Metric Editor, switch to the Subtotals / Aggregation tab, down to the left there’s a check box that you can enable to surprise your customer… cool.

Reminder: MicroStrategy Express is Retiring

MicroStrategy Express will be retired at the end of June 2015. We hope you have found it a valuable tool in exploring and visualizing your data.

Now join us in welcoming the era of MicroStrategy Desktop – the latest in data discovery and visualization for business users, available to install on Mac and PC.

We are pleased to announce that as an Express user, you qualify to receive complimentary access to the new MicroStrategy Desktop 10. Valued at $600, this will be available to MicroStrategy Express users for free. In order to get access to the new MicroStrategy Desktop, please fill out this request form.

We are committed to helping you make the transition from Express as smooth as possible. In order to preserve any MicroStrategy Express dashboards or information, we encourage you to back up files to a local machine by June 30th.

For guidance on how to back up your MicroStrategy Express dashboards, please refer to the FAQ below.

MicroStrategy Express is Retiring: What You Need to Know

When will MicroStrategy Express service be retired?

End of June 2015, at which time all accounts will be terminated and you will no longer have access to your teams and dashboards on that account. All information on the MicroStrategy Express service, including user accounts, dashboards, and data will be deleted.

Can I back up all my dashboards on my MicroStrategy Express account?

Yes. Over the next few weeks while the accounts are still active, you may back up your dashboards to avoid losing them. However, if you don’t back them up, they will be deleted when Express is retired.

How do I back up dashboards within my Express teams?

By clicking on the “Share” menu option for each dashboard, you can select the “Export to MicroStrategy File” option to download your dashboard with data and save the file (.mstr) on your local computer.

Is there another option to back up my dashboards if I don’t do it myself?

Unfortunately there isn’t. Account holders will have to back up their own dashboards as no one else will have access to these accounts.

How can I continue to use my dashboards and create new dashboards with MicroStrategy after June 2015?

As an Express user, MicroStrategy will be giving you complimentary access to the new MicroStrategy Desktop client. This is valued at $600 for each Desktop user, but will be available to MicroStrategy Express users for free. The MicroStrategy Desktop client is the crown jewel in the new MicroStrategy 10 release, and is available as a client install for both Mac OS and MS Windows. All dashboards created using templates in Express can be used with the new MicroStrategy Desktop. Advanced Template dashboards may only be imported using MicroStrategy Web and will need the Server license.

What new capabilities exist in MicroStrategy 10 that were not available in MicroStrategy Express?

MicroStrategy 10 offers a completely new analytics experience, with a revamped HTML5 interface, native connectivity to more data sources, data wrangling, new types of visualizations, and faster performance. Visit http://www.microstrategy.com/us/analytics/10 for more information about all the exciting new features.

Can I back up dashboards built with Out-of-the-box Templates (Visual Insight)?

Yes. Dashboards built with existing templates can be backed up while the account is still active over the next few weeks. Dashboards built with live connections to databases (Express Delivery dashboards) may not be backed up.

Can I back up dashboards built with Advanced Templates?

Yes. Dashboards built with Advanced Templates can be backed up. In order to use them, you will need MicroStrategy Server and Web license as Desktop does not support advanced templates.

Can I back up my Mobile and Team Launchpads?

No. The Team Launchpad only applies to MicroStrategy Express, and you cannot back it up.

Can I back up the list of user accounts and profiles within my team?

Yes. On the administrator tab for team owners, you may click on the “Export Users” option to retrieve the list of users and profiles in Excel format.

If you have other questions about transitioning off of MicroStrategy Express, please don’t hesitate to contact us.

SELECT 'MicroStrategy' FROM DUAL;

I often use the DUAL pseudo table in Oracle to generate constants or execute functions, like in SELECT SYSDATE FROM DUAL;

Appearently there is no easy way to convince MicroStrategy to use DUAL in FROM clause because it doesn’t appear in the list of available tables.

Until now I used to create a Logical view with the syntax “SELECT * FROM DUAL”, but I found a more elegant shortcut.

1. Open the Warehouse Schema windows:

2 Click on <Options…>, select <Read Setting> category, and click on <Settings…>



So that the whole resulting statement is:


4. Click Ok two times to get back to the Warehouse schema window and click on the lightning icon to refresh the table list. Select the DUAL table and move it to the right

5. Click on <Save and Close>. Now you can use it in FROM clauses.

To test how it works you may create a new “Today” attribute with the function CurrentDate(), like this

be sure to set it to Manual and select Date as datatype. Now drop the “Today” attribute onto a new report, click on SQL view and look at the SELECT.

MicroStrategy and Windows 8 (2)

If you try to install MSTR Intelligence Server onto Windows 8 Preview, the setup will run smoothly with no error, but the service won’t start: the reported error  is Failed to start service. Unexpected error occurred. The dependency service or group failed to start

The Intelligence Server depends on several Windows services (more details in technical Note: TN31892); one of which is not present in the Developer Preview of Windows 8.

According to this MSDN article http://msdn.microsoft.com/en-us/library/bb432405(v=vs.85).aspx the Protected Storage service was to be deprecated.

In Vista and Windows 7 is still present but it will apparently removed from Win8.

See this post for details: http://social.msdn.microsoft.com/Forums/en-US/windowsdeveloperpreviewgeneral/thread/9e37eebe-b20a-46a1-b0bc-d8586a9dbea9

UPDATE Sept. 21 2011

The solution is to run the Intelligence Server as an application and not as a service in Windows 8. Open a command prompt, go to:

C:Program FilesMicroStrategyIntelligence Server and start it with: MSTRSvr.exe. Now you can use 3 tier connections and web interface. There are some limitation running it as an application, see manuals for details.

MicroStrategy and Windows 8 (1)

Trying to install MicroStrategy Desktop on Windows 8 Developer Preview you may get this error:

MSTR setup tries to install .NET framework. Windows 8 come with .NET 4 and you also have the option to enable 3.5.1 in the operating system.

For further details see the article:


After enabling .NET framework you should be able to install the software.

Once installed the icons are created in the start page of Metro UI.

hope this helps,