The latest version of Reporting Services, due out any day now, has built in support for mapping. This is a very welcome addition and adds significantly to the “cool” factor. I recently completed a quick demo project using public data available from Elections Canada, and I will be sharing some observations over the next few days. I’m a newbie to mapping, so I’m sure that that the GIS folks out there will think this is quaint, but this really does take a technology that was previously available to a select few, and make it available to a much broader audience, something that Microsoft is particularly good at.
Firstly, I have to mention that this was built using the November CTP of SQL Server, and was implemented on SharePoint 2010 beta. Technology Preview on Beta would typically be a recipe for frustration, and while there were bugs, it went together surprisingly well.
There are essentially 2 major data components to a map report,the spatial data and the analytic data. The spatial data contains the map elements themselves,and data that will be used to relate to the analytic data, where the analytic data contains the data that is to be mapped. In my first example, I have data that describes all of the riding boundaries in Canada, along with their metadata (riding number, province, etc). I also have election result data that can be grouped by riding number, so a relationship is created between them. I’ll demonstrate below.
You can create a map in any Reporting Service report, either using the Report Designer in Business Intelligence Design Studio (BIDS), or by using the new Report Builder end user application. Using BIDS, you need to drop a map control onto the design surface to start the map wizard, and Report Builder gives you the option at create time.
The wizard then prompts you for your spatial data. You have 3 choices as shown above. The map gallery lets you choose from the full list of pre-packaged maps available in the product consisting of a grand total of 1 country, the USA. To be fair, this is by design. There are so many border disputes in the world that the US is the only safe bet. In fact, as a Canadian, we share a border with 4 different countries, and unless I’m mistaken, we have border disputes with three of them. This leads us to the next choice, the ESRI Shapefile. These files are the de facto standard for spatial data, and are readily and freely available from the public domain. The Shape files used in this example came originally from Elections Canada. Finally, SQL Server, starting with 2008 supports a spatial data type. This is essentially a series of polygons. It’s a little known feature, and spatial data is supported directly within Management Studio, as can be seen below:
Whenever a query result contains spatial data, a little “Spatial results” tab appears which allows you to visualize the results.
Which one to choose? Well importing the shapefile directly is fast, but using SQL data gives you much more flexibility from a querying standpoint. Also, if the gallery or shapefile are chosen, the data is embedded directly in the report, which is not the case with SQL.
Luckily there’s an excellent little utility available from SharpGIS that makes it very easy to just move your shape files up to SQL.
The next screen you see will depend on your choice, but in the case of SQL you can either choose a shared dataset that has the spatial data, or you can create an embedded dataset for this purpose. Once the data has been defined, you can configure the map’s viewport.
Here you set the zoom level, and can set the panning parameters. You can also choose the type of data that the map is to use – in this case polygon, but other options are point and line (for single point and route type data). Also of note are the two options at the bottom. You can choose to embed map data in the report (removing reliance on the back end data for the map data, effectively creating a snapshot). The second option is to add a Bing Maps layer – allowing you to add the richness of Bing Maps to your visualizations. However, this can only be done if your map data is geographic. Geographic data maps directly to real geographic positions (latitude and longitude) whereas planar data is simply a set of shapes that fit together.
The next window allows you to choose your visualization.
The basic map will only show the data in the map itself, and required no further data. the color analytical map essentially fills in the regions according to analytical data, and the bubble map positions analytical data on the map. Additional layers can be added to any map after the fact. In our example, we choose the Color Analytical Map.
The next screen prompts us to either choose a shared dataset that contains our analytical data, or to create a new dataset. Once we have selected the data to use, the next dialog box allows us to establish the relationship between the map data and the analytical data. In our case, the field FED_NUM in the map data corresponds to the EDANum field in our election results data.
Finally, we choose a theme (which defines the “chrome” of the report), the field to use to determine our our colouring, the patterns to use to visualize the data, and what to display as a label, if anything.
And that’s all there is to creating a basic map! at this point, you can click run in Report Designer, or preview in BIDS, and you should see the results of your analysis as below, which in our case are the results of the Canadian 2008 General Election on a riding by riding basis.
Many things need to be adjusted further (zoom, which color to use for which party, and a number of other things), and I’ll cover some of these in a future blog post, but as you can see, the map wizard is quite capable of delivering immediate results.
Bring on the RTM of SQL Server 2008 R2!
I’m pretty disappointed with Windows Virtual PC (the latest version). It’s April 2010, and it still doesn’t support 64 bit guest operating systems, which I believe is a pretty glaring omission. I’m a big fan of Microsoft’s server virtualization (Hyper-V) but the client side is just so lacking, I’ve been forced to find another solution. For a number of reason (chief among them video performance) I do not want to put Server 2008 on my laptop.
Microsoft actually forced the issue themselves. The latest waves of server releases (SharePoint, SQL Server) are 64 bit only. As a developer/architect I need to run these servers from my laptop, which makes it impossible to use Windows VPC. Originally Windows VPC also required hardware virtualization, which made it unusable on some of our systems, but they have recently removed that restriction. Good.
I was originally a VMWare user, and I only switched to VPC after I noticed that it performed considerably better on Vista 64 bit systems. VMWare had become a dog. I was also hoping that using Microsoft virtualization would make it easy to move VMs between client systems and Hyper-V. Alas, that was not to be the case.
Given all that, I recently installed VMWare 7, and I’ve been quite impressed with its capabilities and its performance. Don’t get me wrong,it would be much simpler,and cheaper for me as a Microsoft Partner to use MS technology across the board, but until such a time as they support 64 bit guest operating systems (which is something they have been very quiet about), it’s simply not an option for me.
Don’t. Really, don’t.
The Title field is the only exposed site column in the Item content type. Pretty much every single list item across the board inherits from this content type. Therefore, when you change the name of the title field, you’re changing it for all lists in the site collection.
The problem is, that there’s no warning that that’s about to happen, and if you start from a list, you may be under the impression that you’re only changing it in that list.
Pretty much not cool.
So let’s say that you do change it. You can just change it back, right? Wrong. The Word “Title” is a reserved name in SharePoint and you can’t use it in a field name. There’s no way in the UI to change it back.
However, you can do it through the object model (API) by writing code, or using a Powershell script (get used to Power Shell everybody…). Thanks to the From the Field Blog for this highly useful Powershell script:
$fld = $web.Fields.getFieldByInternalName(“Title”)?
$fld.Title = “Title”?
$fld.PushChangesToLists = $true?
And yes, I’ve had to use it!
I’m quite liking the the Virtual PC and XP Mode on Win 7. One thing that I just sorted out and thought I should share is how the file sharing works. If you’re used to Virtual PC, you’re likely familiar with shared folders. You would normally use either that or mapped drives to share files between the VM and the host operating system. However, XP Mode doesn’t use Shared folders as such, it doesn’t quite work that way.
Any application running in XP Mode that opens up a file dialog will open to the “Documents” folder (what used to be “My Documents”). However, it’s not the same Documents folder as the one for the user that is currently logged in. Where is it then?
What XP Mode considers to be its Documents folder is actually the Public Documents folder on the host. By default, it’s available under Favorites in Windows explorer.
Pretty simple really. Anything put there by the host is available to the XP Mode applications, and vice versa.