Designing Data Access - Professional SQL Server 2005 Reporting Services - page 156

In the following example, I will step you through the process of defining a data source and data set for a
report based on the Analysis Services AdventureWorksDW database. This sample database is installed
with SQL Server 2005 Analysis Services. If you have installed the sample database with SQL Server to
the default installation path, you will find the Setup package at C:\Program Files\Microsoft SQL
Server\90\Tools\Samples . Once installed and connected, you will be able to use this database to
build sample reports following the steps in this section. I assume that Analysis Services is installed and
configured, and that you have sufficient permissions to browse cubes and retrieve data. If not, you may
need to have a chat with your resident database administrator. Start by bringing their favorite beverage.
I find this to be an effective means for getting things done.
In this example, I will give basic steps to design an entire MDX-based report, but I will not provide
detailed instructions for every single step. By now, you should have the necessary survival skills to work
with these tools and controls. I will offer the basic instructions to complete a simple matrix report so you
can see the results of the data source and query. You will learn more about the matrix and other report
items in subsequent chapters.
In order to complete the following exercise, you will need to have SQL Server
2005 Analysis Services installed and running. You will also need to have the
AdventureWorksDW Analysis Services database generated and loaded on your
server.
If you don't have the AdventureWorksDW database available, make sure that you have installed SQL
Server with all of the sample databases. This database is generated using Visual Studio. Find the
AdventureWorks.sln file in the AdventureWorks Analysis Services Project folder under your SQL
Server 2005 installation path. Open this solution in Visual Studio, and simply run the project. This con-
nects to the relational AdventureWorksDW database, generates the Analysis Services database, pro-
cesses and populates all of the cubes and associated objects, making this data available to Analysis
Services.
Going beyond the very basics with Analysis Services would take us pretty far off topic and beyond the
scope of this topic. For more information about SQL Server Analysis Services and MDX query program-
ming, please refer to the Wrox Press topic Professional SQL Server Analysis Services 2005 with MDX by
Sivakumar Harinath and Stephen R. Quinn.
Creating a Data Source
The first step, of course, is to create a data source to the SQL Server Analysis Server. I will be using my
local computer. I add a new shared data source to my report project and the Shared Data Source appears
as in Figure 5-36. By default, the connection type will be set to use the SQL Server relational database
engine. This will actually be changed in the next dialog. Give the data source a name, and then click the
Edit button. In case you have the connection string information, you can select the provider from the
Type list, type the Connection string, and then skip ahead to Figure 5-37.
This opens the Connection Properties dialog that you see in Figure 5-37. Use the Change button on this
dialog (shown in Figure 5-38) if it is not set to connect using the Analysis Services provider.