C H A P T E R 12
Delivering Business Intelligence
with SSRS
Most companies accumulate business data that, if analyzed correctly, can provide insights into what
direction the company should take to achieve ultimate success. The main intention of Business
Intelligence (BI) is to provide data in such a way that it can be immediately utilized to make important
decisions. Microsoft’s BI platform comprises many services and applications that work together to facili-
tate the analysis and delivery of critical business data. SQL Server is at the heart of the BI model,
providing data storage, data transformation, notification, scheduling, analysis, and reporting services.
Having the right data available is just the first part of the challenge in building an effective BI
system. The second part is to ensure that this data is delivered in an effective and accessible way to all
the people who need to see it in order to make the right decisions. This is where SSRS comes into play. In
our organizations, we found that by integrating SSRS with many of the other components of the BI
platform, we were able to dramatically improve our overall business strategies by making necessary
information available to our employees wherever they were and whenever they needed it.
In this chapter, you will examine the following applications that we have extended to include the
SSRS reports for our software development company:
Analysis Services: Having the ability to “slice” through dimensions of data often renders unexpected
and meaningful results. When OLAP Services was introduced with SQL Server 7.0, we were asked to build
a data warehouse, transforming our OLTP data into an OLAP cube. We maintained this project using the
version of Analysis Services that shipped with SQL Server 2000 and had some new functionality, such as
data mining models. Microsoft has invested heavily in OLAP technologies ever since. One such
enhancement is the VertiPaq engine that is used with SharePoint 2010, PowerPivot, and Excel Services.
Using PowerPivot models and deploying them to a configured SharePoint 2010 server, an OLAP cube is
spun up in memory to allow rapid slicing and dicing of hundreds of millions of records.
SharePoint Services: Part of Microsoft Office, SharePoint Services provides our company with an
intranet portal that we have departmentalized. Any information relevant to individual departments or to
the company as a whole is indexed and searchable. Integrating SSRS reports with SharePoint lets our
employees easily find the data they require to do their jobs. I will show you how to add SSRS reports to
SharePoint. Since SSRS builds on Windows SharePoint Services, the work you will do in this chapter will
also be applicable to the Windows SharePoint Services included with Windows Server 2003. For a
SharePoint 2010 and Reporting Services 2012 installation, you will need a 64-bit version of Windows
Server 2008 SP2.
Most companies have similar applications to the ones we are describing. We are providing these
examples to give you some ideas of how, with a modicum of effort, SSRS can easily enhance these types
of business applications. Our purpose is not necessarily to provide a step-by-step guide, but to show
how your company might use SSRS. If you use any of the technologies mentioned in this chapter, you
can easily integrate this chapter’s ideas into your own environment.