Free MicroStrategy training in Seville (students and unemployed only)

curso_microstrategy_nov_2016

Espacio RES (@espacio_RES) is organizing in Seville a 2 week Business Intelligence course with MicroStrategy and SQL Server.

SELECT CONVERT(DATE,'20161114',112) AS [Start Date],
CONVERT(DATE,'20161127',112) AS [End Date]

The course is free for students and unemployed professionals. The classes will be held in English from Mon to Fri (9am – 2pm).

I will be the main teacher, with some little help from colleagues and friends in the BI sector.

Please see this document:

introduction-to-business-intelligence-with-microstrategy

for more information, and contact hola@espacio-res.org to apply

A map, a map! My kingdom for a Choropleth map! (Power BI)

One of the most fascinating aspect of Business Intelligence is the power to picture data in an easy to understand way. I personally like maps a lot, not the google or bing type with lots of circular points which give very low added value to the information, but the Choropleth maps. Those shaped areas with shaded colors intended to convey an idea at the first sight.

Choropleth maps are mostly used with regional data but they can picture any kind of information on any kind of shape, the idea behind is very simple: you have a SVG image (a vectorial one), with one id for each closed area. Remember those days when we were kids and used the coloring book to paint on pre-designed images?
330fdcec81c9109b4ee013c58e188774

That’s it. Then you load your data and assign to each id a numeric value to get a colored area with a increasing intensity of color.

This is exactly what the Synoptic panel does.

It’s a custom visual that you can use with Power BI, more on this here. You can download the current version from gitHub, and import it into your Power BI report (yes, it’s free).

The Synoptic panel itself doesn’t paint the picture, you need to have a coded SVG to use with it. And creating a coded SVG is not difficult, you may use any SVG editor, or just go to http://synoptic.design which is an extremely useful web build by the same people who did the custom visual.

I must thank @DanielePerilli for his very good job and his continuous support. He was very responsive and helped me through building my first map.

spanish elections 2011

So in about an hour and a half here we have two maps with polls data from the Spanish elections in 2011. I downloaded the CSV from http://www.electionresources.org/es/data/index_es.html#PROVINCIAS and used the Synoptic panel to create two maps where I filtered respectively the votes obtained by PP and PSOE.

No rocket science, anybody can do that. Just a little caveat, it’s not very intuitive: if you want to have different shades of color, you should drag a field to the “Saturation Values” box in the visualization settings. And if you want to display names on the map, you need to enable “Category Labels” in the format tab of visualization settings. That took me more than 5 minutes to figure out.

Anyway, go download it, create your own map or use those available in the gallery and happy coloring!

How to revoke access permission from Google account

If you granted access to some of your data in Google (analytics, calendar, contacts, etc.) to some external application (like Xobni for example); you may want -at some point- to revoke them as you no longer use that application.

To do that click on the gear icon and select “Account settings” go to your account settings once you’re logged into Google:

then click on “Authorizing application & sites” link:

 

 

 

 

 

 

In the following page click “Revoke Access” to any application you no longer use. That ensures that none will be able to see your private data with that particular application.

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…>

3. Change the default catalog SQL statement (SELECT DISTINCT OWNER NAME_SPACE, TABLE_NAME TAB_NAME FROM ALL_TABLES WHERE OWNER = ‘#LOGIN_NAME#’ AND DROPPED = ‘NO’ UNION SELECT DISTINCT OWNER NAME_SPACE, VIEW_NAME TAB_NAME FROM ALL_VIEWS WHERE OWNER = ‘#LOGIN_NAME#’) and add this lines:

UNION SELECT ‘SYS’ NAME_SPACE, ‘DUAL’ TAB_NAME FROM DUAL

So that the whole resulting statement is:

SELECT DISTINCT OWNER NAME_SPACE, TABLE_NAME TAB_NAME FROM ALL_TABLES WHERE OWNER = ‘CDM’ AND DROPPED = ‘NO’ UNION SELECT DISTINCT OWNER NAME_SPACE, VIEW_NAME TAB_NAME FROM ALL_VIEWS WHERE OWNER = ‘CDM’ UNION SELECT ‘SYS’ NAME_SPACE, ‘DUAL’ TAB_NAME FROM DUAL

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 LinkedIn

With custom SSO it is possible to allow users log into MicroStrategy using their LinkedIn account. By leveraging the OAuth API offered by the social network we can authorize and create new users without previously knowing their names. Once validated and connected it’s simple to use they user ID to retrieve profile, groups or even post to the network update stream.

See an example at: http://moraschi.eu/MicroStrategy

You will be asked to authorize the application to access your LinkedIn account,

and you’re in.

To revoke authorization, from the Home page in LinkedIn, go to the menu with your name in the upper right corner and click <Settings>; in the settings page click on <Groups, Companies & Applications> link, then on <View your Applications>. From this page you can remove Euro-Strategy from the list.

please post feedbacks here or mail them to euostat.microstrategy@gmail.com

regards,

Davide

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:

http://techdows.com/2011/09/enable-net-framework-3-5-1-on-windows-8-developer-preview-to-run-applications.html

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,

Davide