Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Spatial and OLAP data in Reporting Services

Spatial and OLAP data in Reporting Services

  • Comments 2
  • Likes

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:

hastings day trip 048 

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.

Comments
  • Andrew,

    The postal code data, while designed for spatial purposes (routing of mail), it is 'just' a classification system that has *some* mapping to spatial data - but at the detailed level it becomes arbitrary.

    You may want to look at overlaying your data on top of a more spatially correct heirarchy.  You could, since the RSPB is UK based, use the ordnance survey grid references for your heirarchy. Becuase it is a flat and square grid, you could classify according to 100km, 10km and 5km sqaures to get your three levels - or even further, such as 1km and 100m (which may be more useful for nature conservation where rural postal codes are not granular enough. So your MTC postal address has a six digit OS Grid reference of SU 747 741 (the 100m sqaure), which puts it in a 100km sqaure of 'SU', 10km of 'SU77', 5km of 'SU77SW', 1km of 'SU7474'. The added advantage is that there are mathematical ways of determining nearby squares (TQ is to the east of SU and SU77SE is to the east of SU77SW). Conversion from postal codes to OSGrid is public data and from GPS (WGS84) co-ordinates (which more sightings will be made with), although not straightforward is well documented and generally understood.

    Apologies for the detail (possibly too much), but this is something that is top-of-mind for me at the moment.

  • Note Robert Edgson is also giving a talk on the detail of this at SQL Bits 7 , and there are other session on spatial on reporting  as well(http://www.sqlbits.com).

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment