THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

OLAP and GIS - Analysis Services and MapPoint

Ever wanted to get the data from your Analysis Services cube into the geographic map. It is actually easier then you think. There is a little publicized free OLAP AddIn for Microsoft MapPoint which allows you to do exactly that. The way this AddIn works is it contains a wizard which builds MDX query behind the scenes, send it to the cube, and then uses standard MapPoint mechanism to map tabular resultset into the map. There are few interesting things on the way though. First wizard screen lets you to choose a measure, and the second screen asks for the geographic dimension which will map into the map.

    

In this scenario we connect to the Foodmart 2000 database, Sales cube. Note that only hierarchy levels which have geographical information are shown - i.e. Country, State, City etc, but not other dimensions. This is done through the magic of the "Show only Geographic dimensions" checkbox. How does it do it ? I actually don't know the answer, but I like to believe that the Wizard is looking into LEVEL_TYPE column inside MDSCHEMA_LEVELS rowset. There are many different level types that can be set, and some of them are important to MDX operations (for example all Time related types are used in YTD, QTD etc; Account types are important for Account Intelligence, Currency types are important for Currentcy Conversion Wizard etc) - but there are some types which Analysis Services itself doesn't take advantage of - for example all the Geographical types. We have always said, that it is up to application to make itself more intelligent by using this kind of metadata hint, and MapPoint is great example of application which should take advantage of it.

Below is the example of how the results look like after running through wizard. I have chosen to show Store Sales by Customer City comparing sales in 1997 and 1998 in the form of sized pie charts. I.e. bigger the pie - bigger store sales were in that city. Two colors depict two different years. Anybody familiar with Foodmart's data, will notice how sales in Canada have single color - this is, of course, because Foodmart models scenario where in 1997 Foodmart was selling only in USA, but in 1998 expanded into Canada and Mexico :)

The free OLAP AddIn for MapInfo can be downloaded from here: http://www.microsoft.com/downloads/details.aspx?familyid=e22fe181-1d5f-4bbd-9e39-825a74fb1788&displaylang=en

Published Friday, February 04, 2005 8:57 AM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement