BI data on maps in SQL Server 2012 part 1
I often get questions around how to show business intelligence data on maps in the Microsoft BI stack. Some people thinks that we do not support this for the entire world but only for US. I think that this misunderstanding comes from the fact that the map gallery in Reporting Services only shows data from US.
However remember that the map gallery is only one out of three options to get map data. One is to use ESRI shape files, many companies do not have ESRI files in their data set and the link that Report Builder 3.0 links (http://social.technet.microsoft.com/wiki/contents/articles/767.find-esri-shapefiles-for-a-sql-server-2008-r2-reporting-services-map-ssrs.aspx) to is not that helpful if you would like to show maps from outside US.
Using ESRI shape files
So what if you would like to show data from other regions in your SSRS reports? Well there are several options available in the public domain; in this example I will use data from DIVA-GIS http://www.diva-gis.org/Data, DIVA-GIS provides both a program as well as free GIS data in the form of ESRI Shape files for all countries in the world (Ensure that the license is works for your implementation). After downloading the Shape file you can use them in your reports by selecting the second option in Reporting Services:
With this map data you can now build your report and combine your analytical data with the map data. The drawback with ESRI shapefiles is that you need to have one that shows the data at the level you would like to show it. As an example we can take the shapefile that I have used from DIVA-GIS it contains all the countries and their borders but what if I would like to show data on a continent level? If that is the case I need to find a shapefile that contains the continents instead. But there is an alternative. You can import the data from the shapefile into SQL Server and use the third alternative for map data; a SQL spatial query.
Importing ESRI shapefile data into SQL Server
To import data from a shapefile we need some sort of tool that can read shapefiles and convert them to SQL Server spatial. A good tool is the SQL Server Spatial Tools that can be download from http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx the tool that you need is Shape2Sql.exe. First you need to create a connection to your database and then you just need to point the tool to your shapefile and press Upload to database
There are some options on how to upload that I wont go into but basically they govern the type of map you want to use e.g. globe or a planar map; one thing to not is that if you would like to use a Bing Maps layer you would have to import the data as the geography data type.
After the upload you can do a simple select from your new countries table:
select * from countries
The results includes the spatial column
This column can be used in the report to show data by choosing the third option in the Map Wizard in Report Builder:
Now this shows you the same results as it did when I used the shapefile as the map source; but now when we have the data in SQL Server 2012 I have many more alternatives in creating my map.
Using Spatial Queries to format map data
SQL Server 2012 comes with a complete set of spatial functions (more can be found in the documentation http://msdn.microsoft.com/en-us/library/bb933790.aspx) that can be used in your queries to create a custom map. So lets say that I would like to create a map that only shows the continents; the data set contains information about what continent a certain country belongs to this was part of the shapefile. Now before we can use the data we need to ensure that each and every instance is a valid geometry instance; this is something that we can do with the MakeValid function. To use this function we issue the following query:
select *, geom.MakeValid() as validgeom into validcountries from countries
This query will create a new table with valid geometry data. This new table can be used to create different types of maps. The following query will for example create a map that just shows the continents:
select continent, geometry::UnionAggregate(validgeom) as validgeom
group by continent
The query uses the geometry::UnionAggregate function that will aggregate all geometries representing the countries to the continent level. This query can then be used in your report as the spatial query
As you can see you can now see the map without country borders and just the continents.
Now this was the end of part 1, in the next post I plan to show how to create drillable map report in Reporting Services.
The link to part 2 can be found here http://blogs.msdn.com/b/querysimon/archive/2012/06/18/bi-data-on-maps-in-sql-server-2012-part-2.aspx