BI on SharePoint

Getting reports onto SharePoint is an important step in centralizing knowledge and increasing awareness of a company's available Business Intelligence. Unfortunately, people shy away from taking this step because it seems like something that would require a lot of time, effort and expense. This two part series will illustrate the process of putting BI on SharePoint, remove some of the mystery around it and hopefully encourage further investigation. Part one will cover the setup and part two will illustrate working with SSRS reports within SharePoint.

Where to Begin?

As with most endeavors, getting started can easily be the hardest part. However, once everything is ready and key players are on board, the initial step will be to ensure that the SQL Server instance being used for Reporting Services is compatible with the SharePoint server. Below is a compatibility matrix I grabbed from MSDN. Note that SQL Server 2008 is compatible with SharePoint 2010, but it needs to be updated at least to SP1 with cumulative update eight applied. If the SQL Server being used has SP2 or greater, it will be fine.

Compatibility From: http://msdn.microsoft.com/en-us/library/bb326358.aspx
Reporting Architecture

Now, with initial setup taken care of, the first step is to decide on a reporting architecture. What it boils down to is that there are two basic topology choices for SharePoint integration. You can either install Reporting Services on the SharePoint server (option 1) or you can put SharePoint on the Reporting Services server (option 2). I prefer and recommend Option 1 which is illustrated below in Figure A. This method is simpler and only requires that you to install a new instance of SSRS. Also, it will not interfere with current state reporting. With option 2, you will need to reconfigure SSRS, minimally install SharePoint on the SSRS server, and then join the new SharePoint instance to the farm. This is a bit more involved and requires a bit of SharePoint expertise. Caveat: I am not going to delve into architectures that involve scale out deployments of SSRS. But, if that is the scenario, each report server needs to be configured for SharePoint Integrated mode, so I believe option 2 becomes the only real choice. (http://msdn.microsoft.com/en-us/library/bb326356.aspx)

Figure A. High-Level Architecture Option 1
Figure B. High Level Architecture Option 2

Regardless of the option that is chosen from above, the Reporting Services instance being used will need to be configured to run in SharePoint Integrated mode. This is very well documented. However, what isn't totally obvious or very widely known is that if an existing SSRS instance is modified to be in SharePoint Integrated mode, it will lose its current report manager site. I found that after going down this path, it is pretty easy to revert to native mode and it will not cause you to lose any site content, folder structure, security, etc... However, this just reiterates my earlier point about option 1 allowing for the continuation of current state reporting. Choosing option 1 allows the current report manager site to stay live during and after development on SharePoint. Now... it is possible to keep current reporting live with option 2 by standing up a new SSRS server or a new SQL Server instance, but that is definitely overkill when the alternative is so simple.

Components

The next step after deciding on an architecture is to install the necessary components. For option 1, SSRS needs to be installed on the SharePoint Server. If you already have SQL Server on the server, great! Just install SSRS and configure it to run in SharePoint Integrated mode. If you do not have SQL Server, I would recommend installing just the SSRS components on the SharePoint Server and placing the report server databases on a remote SQL Server instance. To do this, choose "Install, but do not configure the report server" when running the install. Otherwise, you will have to create another database server that will have to be managed by somebody.

For option 2, first install SharePoint Web Front End (WFE) on the database server and join it to the SharePoint site or farm. Next, reconfigure the SSRS instance for SharePoint integrated mode (steps illustrated below).

Configuring SharePoint Integrated Mode

Here are the steps for setting up SSRS in SharePoint integrated mode...

1) If installing SSRS only. From the install wizard choose "Install but do not configure the report server"

Report Server Installation

2) After install is complete or with an existing SSRS instance, open Reporting Services Configuration Manager. (Start -> Programs -> SQL Server 2008 -> Configuration Tools -> Reporting Services Configuration Manager ) Connect to the instance of SSRS. Hint: You may need to start the report server service if it is not running.

3) Go to the "Database" tab and select "Change Database"

Reporting Services Configuration

4) Choose "create a new report server database" click next. On the database server tab, enter the server that will host the report server databases and enter credentials that have sufficient access to create a database. Click next. On the database tab select the radio button for "SharePoint Integrated Mode". Fill in something descriptive for the database name especially if this will be on a remote server.

Reporting Services Configuration Database

5) Click Next through the remaining tabs and finish setup.

Now everything is almost ready to start working in SharePoint.