CHAPTER 12 DELIVERING BUSINESS INTELLIGENCE WITH SSRS
Building SSRS Reports for SQL Analysis Services
When we began the journey of building an OLAP solution for our health-care application with SQL
Server 2000 Analysis Services, we were eager to jump right in and start analyzing data the first morning.
We quickly became the resident experts at developing SQL queries to interrogate our OLTP database.
Since this was the source database from which we were going to build the warehouse, our team thought
it would be a simple case of adding a few queries and processing the cube. It did not turn out to be quite
that easy. In retrospect, however, the process of creating an OLAP cube from a known source of data was
worthwhile, because we were able to apply the skills we learned to many other projects. With each new
version of Analysis Services, more and more features were delivered. At first, this can seem
overwhelming, simply because of the volume of enhancements and the time required to not only
become familiar with the technology, but also master it. However, while adding new features, Microsoft
also adds many new tools that simplify time-consuming tasks. In the case of Analysis Services 2005,
these new tools included a graphical MDX query builder and a cube wizard that automates many of the
steps that create the intricate parts of an Analysis Services solution. SQL Server Analysis Services 2008
and 2008 R2 went even further to add time-saving functionality, enhanced data analysis, and the
capability to integrate with SharePoint. SQL Server 2012 Analysis Services brought on new additions like
the VertiPaq Engine mentioned earlier and Tabular Models, which are based off of the new Business
Intelligence Semantic Model (BISM).
In this section, we will show how to use a simple Analysis Services cube as the data source to build
and deploy SSRS BI reports. The cube is based on a SQL Server database that serves as a data warehouse
for the health-care application you have been using throughout the book. The cube is populated with
data relevant to patient admissions for a health-care agency. Though we designed the report to analyze
many aspects of patient admission history, such as patients with multiple recurrent admissions,
changing diagnosis, and patient referral sources, we will show how to create a report that specifically
delivers analytical information about the length of time between when a patient is referred to the agency
and when he or she is actually admitted. Over time, the data that is collected can help assist decision
makers isolate problem areas and improve the processes that may be causing inefficient patient referral
times. First, let’s look at the database and cubes on which the report you create will be based.
The data warehouse database you will use as a source for the Analysis Services project is called
HW_Analysis. It is a simple database containing only eight tables, and is populated with data using SQL
Server Integration Services (SSIS). The typical process for preparing a data warehouse database with SSIS
is to export data from the source OLTP database, transform the data to make it more conducive to
analysis by SSAS, and finally load this transformed data into the data warehouse or data mart.
We have already built both the HW_Analysis database and the SSAS cube called Patient Referral,
and we have included the required files for deploying these two key components and detailed
installation instructions in the ReadMe.txt file included with code download for this book. The source
code and related files are available in the Source Code/Download area for the book on the Apress Web
site (http://www.apress.com). Once you have restored the HW_Analysis data warehouse database per the
instructions in the ReadMe.txt file, you can open the Pro_SSRS project, which contains the Patient
Referral cube. Figure 12-1 shows the simple Patient Referral cube structure in BIDS. The cube has six
dimensions and two measures.