A worked example of using spatial and olap data in Reporting Services

In my last post I went through a possible approach for showing spatial and olap data that the RSPB are planning to evaluate, now I want to show you how this might look in an example.

First of all an apology, I am not going to pay the £1500 to license UK postcodes as this is just a demo, rather I am going to show how it might work with example data from the US Census boundaries. Not only is this free but the data I am using is available on Codeplex (here.) so you can recreate what I am doing to get the idea.

These files are pretty big sql  scripts, so the best way to load them in is to create a database (mines called USCensus) and then use SQLCMD to run the script for each SQL file:

SQLCMD –S “MIAMI\PowerPivot” –E -d”USCensus” –i “d:\samples\US Census\postcode.table.zip”

where:

-S is the server instance

-E signifies windows authentication

-d is the database

-i is the sql file you want to run (change and rerun this for each of the four files in the census)

The State, City and County tables have the relevant boundary in as a geography column, and county and city both show which state they’re in.

I can then combine this withe the adventure works sample (the version for SQL Server 2008 R2 )cube which I can also get from CodePlex here, as this has a geography dimension.

Now I have the data I need I can start to use Reporting Services, specifically by opening Report Builder 3 and using the map wizard.  Unlike all the other controls in Reporting Builder the map control allows you to combine different sets of data in the one control.  In my case I need the state polygons from the US Census database I have created and analytical data form the cube.  The wizard guides you through this process including identifying how the data sets are to be joined. In this case the join is between the  state name in the state table to the State Province attribute in the Adventure Works cube.

Rather than grab every screen I have made a short video which you can pause and replay to see what I have done..

There’s also a bunch of sessions on Report Builder and spatial data at the next SQLBits in York  1 –2 October 2010.