Spatial and OLAP data in Reporting Services
I spent a very interesting afternoon last week with the IT team at the RSPB to explore what they could do to combine their mapping data with the olap cubes they have in analysis services. The only out of the box solution in SQL Server for spatial data is the map control in reporting Services (in SQL Server 2008 R2). This map control allows you to create the appearance of drilling down to more details as you can pass details of where on the map is being clicked to another report, much like passing parameters in previous versions of reporting services. The other interesting thing about the map control is that it does allow you to combine sets of data in the one control. This would allow the RSPB to combine a relational query with the spatial data with an MDX query and then join them on a common field.
The spatial hierarchy would use the standard UK postcode:
[changed 20/09/10 as I got the labellign of the postcodoes mixed up - thanks to Robert Edgson for picking this up]
Postal Town/ London area( (1st 1or 2 characters) –> outbound code(the group before the space) –> sector( 1st number after the space) –> full postcode
for example for Microsoft UK:
RG –>RG6 –> RG 6 1-> RG6 1WG which equates to
This would then translate into three linked reports:
- A static UK wide report with all the postal town boundaries on e.g. RG – Reading.
- A postal town report which takes a parameter of postal town (e.g. RG). and shows the inbound codes in that postal town/London area
- An inbound report which takes a parameter of an inbound code shows the postcodes and or sectors in the area specified by that inbound code.
I would then publish the map report parts for these maps, another new feature in SQL Server 2008 R2 Reporting Services.
The olap data in the cube has a location dimension with the parts of the postcode in a postcode hierarchy; postal town –> Inbound code –>sector –>postcode. The parameter passed into the postal town report would then filter the map to the town and because the olap data is inner joined to that town you’ll only get the olap data for that town.
It is then up to the users as to which visualisation they wish to use to express high and low values e.g. heat colour the polygons or add symbols with different colours or sizes to show the measures in the cube.
What you loose in surfacing BI in this way is the ad hoc interaction you have with analysis services proper, however what you gain is a quick method of seeing data on a map from a cube, that can be created by an end user.
In the meantime here’s a gratuitous painting I did of a merlin to show I have more than a casual interest in the success of the RSPB:
I’ll keep you posted on how the RSPB goes (subject to their permission of course) and in my next couple of posts I’ll show you how to do some of this using freely available (and unfortunately American based) data.