Building a Simple Map in SSRS 2008 R2

In a previous post we discussed mapping the SQL Server geography data type in Mathematica. Now we’re going to build a simple map in a SSRS report using the same query as before. To start, we’ll open Visual StudioBusiness Intelligence Development Studio (BIDS) and create a simple data source connection. Right click on Shared Data Sources and click on, “Add New Data Source”.

In the next window that pops up, name the connection (here we just called it Localhost) and then click on the Edit button.

In the window that pops up, enter the server and the database that you would like to connect to.

You can click the Test Connection button if you wish but really, if you can select the database, you can connect to the database. Click OK twice to create the shared data source.

Once that is done, we will want to create a report. As usual, you can just right-click on Reports to get the menu. If you click on, “Add New Report”, it’ll take you to the Add New Report Wizard. I prefer to just create a blank report and create from there. To do that, click on Add -> New Item…

Select the Report option and give it a name. I named mine, Top 500 Sales Around the World.

Next, click on your Toolbox pane, select the Map object, and drag it over to your report.

When you drag it over, a new window will pop up with options for adding a new map layer. Select the option for a SQL Server spatial query and hit, “Next”.

The next window asks if you wish to choose an existing dataset or create a new one. We will want to create a new one (it’s at the bottom). So select that option and then hit the Next button.

Click the “New…” button on the next window that pops up to create a new data source.

On the Data Source Properties window, name the connection something sane and then select the, “Use shared data source refence” option and in the dropdown list, select the shared data source we created before. Click OK.

Then click Next.

The next window that pops up will want you to create a query. Select, “Edit as Text” and enter the following query:

SELECT TOP (500) Address.SpatialLocation

FROM Person.Address AS Address INNER JOIN

Sales.SalesOrderHeader ON Address.AddressID = Sales.SalesOrderHeader.ShipToAddressID

Click Next. SSRS will detect that the values we are using are points. With the points themselves just being points in space, you’ll want some form of underlying map to plot them on to. At the bottom, select the option to add a Bing maps layer and click Next.

Select the type of marker map you want to use and click Next.

Select your final options such as theme, marker types, and color options and then click Finish.

Once that is done, you can click on the Preview pane and see your shiny new SSRS map!

Please note that while my map is called Top 500 sales locations, it isn’t a top 500 in the usual sense (500 highest grossing sales). It’s merely the first 500 rows in the Person.Address table that have matching values in the Sales.SalesOrderHeader table. Building a map in SSRS is impressively easy.

Installing SQL Server 2008

To get things started off, I’m going to cover some of the super basics. I remember a time when I was an early mathematics student. I had never encountered a database before in my life. In my mind, my idea of a database was something along the lines of lots of Excel spreadsheets. This first, non-salutory post is going to address that student or person like him. There’s an ocean of knowledge awaiting beneath our feet and this install guide is the start to getting at it!

The first thing in getting started with SQL Server 2008 is to get the software. I assume that as an individual, you’re likely not interested in purchasing a full version of SQL Server 2008 for personal use. That means either using SQL Server 2008 Express or SQL Server 2008 Enterprise Trial. You may find the Express version here and the trial version of Enterprise here. I’m going to assume during the set up process that you’re installing this on your workstation or in a development environment and if you’re not, then you know what you’re doing.

The install process is fairly painless. After downloading the file, choose the path you wish to save your extracted files to.  Make sure that you’re placing the files in a subfolder otherwise the install files will be placed at the same level as your saved documents and et cetera. I appended SQL2008 to the end of the path defaulted to by the extractor and the folder was created when I hit, “Ok”.

For personal purposes, I usually just go straight to the Installation section and click on, “New SQL Server stand-alone installation or add features to an existing installation”. You may wish, since this might be your first time, to poke around and look at some of the documentation linked from the Installation Center window.  Usually, I just proceed through the install wizard selecting the default options and selecting every feature. If this was a production environment, I would highly recommend not installing any unnecessary features. Normally, unless there’s a good case for it, I would recommend using Windows mode for security; however, Mathematica likes to use SQL Server authentication so you’ll want to select Mixed Mode. I am working on getting Windows authentication and Mathematica to play nice but so far, I haven’t found the silver bullet. If security and best practice is something you’d like to explore more, Paul S. Randal’sCommon SQL Server Security Issues and Solutions” is a great starting place. There’s also an MSDN article linked right from the SQL Server Installation Center window. If you’re so inclined, attempting to set up your database server like a production environment with service accounts and minimal permissions is a great way to play around and learn. Allow me to also recommend Pinal Dave’s Journey to SQL Authority. There’s a ton of great SQL Server community resources out there. and SQL Authority are great places to start. They both have a lot of links. Happy surfing!

Here’s the process step by step (each bullet represents a discrete step, usually punctuated by hitting, “Next” in the wizard):

  • Navigate to the folder you extracted the files to and double click on Setup.exe
  • Click on the installation category in the left hand pane and when the main window changes, click on “New SQL Server stand-alone installation or add features to an existing installation”.
  • SQL Server 2008 will run an environment checker to ensure that your system is ready for SQL Server 2008. Once the operation completes, you should have passed each of the prerequisites. If you’re curious, click on the, “Show Details” button. Otherwise just hit, “Ok”.
  • SQL Server 2008 Setup will then ask you for permission to install setup support files. Click, “Ok” and let it do its work.
  • The set up process will ask you what sort of installation type you want. For our purposes, select “Perform a new installation of SQL Server 2008”.
  • You’re going to be asked what edition of SQL Server 2008 you want to install. I’m installing the 180 day Enterprise Evaluation version. I’ll probably also perform an install of SQL Server Express with Advanced Services as well. I’m not very familiar with SQL Server Express on a day to day basis since I work with Standard and Enterprise at my work. I’m going to fix my lack of familiarity but for now, if you’re following along and you haven’t done this before, just select the evaluation version.
  • Accept the license terms.
  • I’ve selected the following options for my installation:

The essential features you want installed is Database Engine Services, Full Text Search, and Client Tools. It’s also my intent to work with Analysis Services and Reporting. That means I’ll want Business Intelligence Development Studio. I’m also going to want Management Tools installed as well (mine are greyed out because I’ve already installed them in another instance on this computer). Clicking on each feature will update the Description portion of the window with a summary of the feature you’ve selected.

  • You may select this installation to be the default instance or you may make it a named instance and give it a special name. The most obvious difference between the two is that for the default instance when you connect, it’ll be to <SERVERNAME> whereas with a named instance you’ll be connecting to <SERVERNAME>\<HOSTNAME>. Make sure to select the path to where you wish the root directory for your instance to live if you’re unhappy with the default path. After stepping through a summary of space usage, you’re going to find yourself on a screen asking for service account names. I love this feature of the installer. If you’re comfortable with creating custom accounts (or have some set up in your environment already), just click in and type the account name and password. Make your choices about how the services start up. On my computer, I have the service start up type set to manual because it takes long enough to get to a workable desktop in the first place. When I want to play with SQL Server, I can just go to Services in the Administrative Tools and start it manually. Pick whatever you’re most comfortable with. If you’re working at home and don’t wish to use custom accounts you may pick either NT AUTHORITY\NETWORK SERVICE or NT AUTHORITY\SYSTEM from the drop down. Picking these accounts is really against best practice and should be done only in an environment where it’s appropriate. You may read more about NT AUTHORITY\NETWORK SERVICE  here and about NT AUTHORITY\SYSTEM here. Choose wisely.
  • In the Account Provisioning tab, you may select the means by which you wish users to authenticate with SQL Server. If you select Windows authentication mode, then Windows credentials will be used. In mixed mode, you may set up SQL Server specific users that exist outside of the Windows domain. If you do this, you will need set the password for the, “sa” account. Make this a strong password with the recognition that this account is truly top dog in the environment. Since this is my home computer and I have a single sign on, I just usually keep Windows authentication mode and then I hit, “Add Current User” at the bottom of the window to add my Windows user as an administrator. For the Data Directory tab, you may select the path for each of your database and log files. Many production servers will place these files on separate drives for performance considerations; however, if you’re just play working like I am, the default paths work fine here.
  • For Analysis Services Configuration, you may wish to add your current user as an administrator here as well on the Account Provisioning tab.  You may also select the default data directories for your OLAP files.
  • For Reporting Services configuration, I select the native mode installation.
  • For Error and Usage Reporting, select the options that make the most amount of sense to you. Personally, I send this kind of data to Microsoft.
  • SQL Server 2008 Setup will next perform some last sanity checking to make sure that what you’ve selected to install will make work during installation.
  • Once you’re ready, hit “Install” and it’s off to the races!

Congratulations, you’ve installed SQL Server 2008. Once you’ve completed the installation process, I suggest running Windows Update (or Microsoft Update) and downloading any pertinent updates to SQL Server. If you think you’re done, you’d be almost right. There’s one more task we must complete before we can think about Mathematica and SQL Server putting together like peanut butter and chocolate. We need data.

In the past, SQL Server 2005 shipped with the Adventure Works database. With 2008, we will need to download and install the database ourselves. The Adventure Works database is an example database full of example data. It’s a great way to practice and learn database technology without using a production database. To get the Adventure Works 2008 databases, point your browser toward its Codeplex page, select the database examples that match your database version (likely 2008 R2) and download away. Once the executable extracts, you’ll be presented with a screen that looks like the following. Read and accept the terms and hit, “Next”.

On the next screen, you’re going to find a window that appears like so:

If you do not, then you will see buttons to the right of the databases that cannot currently be installed labeled, “More information”. If you follow them, they will take you to this page where they have short guides and links to getting you up and running. I found that once I’ve fixed the issues keeping me from installing the databases I wanted (in particular, AdventureWOrks OLTP 2008), I had to close out of the installer and restart it. I found this to be the case in particular for the FILESTREAM settings change. Once you’re happy, fire off the installer and watch it go. It’ll take a bit so if you’ve been jamming on the keyboard for a bit, now’s a good time to attend to some business.

Once the installer is done, we need to install the Analysis Services databases. Follow the appropriate, “More information” link to this page and get ready for Business Intelligence Development Studio (known from here on out as BIDS). I’ve copied and pasted its contents here for ease of use:

  • Ensure that the SQL Server Analysis Services service account for the instance you will be deploying to has access to the instance where the AdventureWorksDW2008R2 sample database is stored, and also has access to that databases, and the account is at least a member of the db_datareader role for the AdventureWorksDW2008R2 sample database.
  • Start Business Intelligence Development Studio by clicking Start / All Programs / Microsoft SQL Server 2008/ SQL Server Business Intelligence Development Studio.
  • Click File / Open / Project/Solution
  • Navigate to the appropriate folder containing the Analysis Services database solution. If the sample databases were installed to the default location:
    • For SQL Server 2008:
      • C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project\standard\Adventure Works.sln will contain the solution for SQL Server Standard.
      • C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project\enterprise\Adventure Works.sln will contain the solution for SQL Server Enterprise and SQL Server Developer.
    • For SQL Server 2008R2:
      • C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\standard\Adventure Works.sln will contain the solution for SQL Server Standard.
      • C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\enterprise\Adventure Works.sln will contain the solution for SQL Server Enterprise and SQL Server Developer.
    • Analysis Services databases are not supported on other editions of SQL Server.
  • After opening the solution, in the Solution Explorer double click the “Adventure Works.ds” data source. Click the Edit… button just below the Connection String box. Supply your SQL Server (database engine) server and instance name (if its a named instance). Click the Test Connection Button. If the test succeeds, click OK and OK to save the changes.
  • Right click on the solution in Solution Explorer, choose Properties. On the Adventure Works DW 2008 SE Property Pages dialogue, choose the tree item for Deployment. Change the Target – Server property to your AS server name and instance name (if it is not a default instance).
  • In the solution explorer click right on the solution (Solution ‘Adventure Works’) and click Deploy Solution

This deployment will take a bit so now’s another good chance to get up and attend to other matters or do some reading about SQL Server. Once the deployment is finished, we’re done right? Almost.  Let’s just check and make sure everything’s alright. To do that, let’s open SQL Server Management Studio (hereon known as SSMS) by going to Start/All Programs/Microsoft SQL Server 2008. You should get a window that looks like the following:

We’re going to just check and make sure that our transactional databases are set up. So make sure the server type is set to, “Database Engine”, the server name is set to your computer name, and the authentication is set to what you set it up as during the install (preferably Windows Authentication). Click, “Connect” and let’s see if we’ve done it right. Click the plus sign next to, “Databases” and you should see something like the following:

If I have more databases than you do, don’t worry. I’ve installed the Adventure Works databases for both 2008 and 2008R2. What’s important is that you have AdventureWorks2008R2 and AdventureWorksDW2008R2. Next up, we need to make sure our Analysis Services database installed too. Above your database server name, click on “Connect” and select Analysis Services. You’ll get a dialog box akin to that of when you first pulled up SSMS. Note this time that, “Server type” reads, “Analysis Services”. Click on, “Connect” and go. Expand the databases section and you should see Adventure Works DW 2008R2.

That’s it. We’re done. Next up, getting Mathematica to connect to SQL Server 2008. Not to worry, the process should not nearly be as arduous as this. If you have any particular issues that you’re unable to resolve or if I’ve left something out, please leave a comment and I’ll try to help you.