Define a Data Source
a Data Source, as we have discovered in past articles, is typically the
first step we take with most BI Projects be they Analysis Services,
Report Model, or other varieties. A project can have multiple Data
Sources, within which we are essentially defining the connection string
used to connect to the actual source of our data. While we can define
connections to source databases on remote computers in the business
environment, we will, in this article, be working with one of the sample
databases that are available to us when we install MSSQL Server 2005,
the AdventureWorks database.
create a Data Source within our new RS028 Intro Report Model Project by taking the following steps:
Sources folder within the Solution Explorer.
Select Add New
Data Source from the context menu that appears, as depicted in Illustration
Illustration 9: Initial
Step in Defining a New Data Source
The Data Source Wizard
appears, opening with the Welcome to the Data Source Wizard page, as
shown in Illustration 10.
The Select how to
define the connection page appears. This page allows us to define a Data
Source based upon a new or existing connection. (It also allows us to base
a Data Source upon a connection that has been previously created, as
long as the existing Data Source definition exists within the same project,
within another project housed in the same solution, or from
within another Report Model Project.)
The Select how to
define the connection
the radio button to the left of Create a data source based on an existing or
new connection is selected, click New.
The Connection Manager
Ensure that .Net
Providers\SqlClient Data Provider is selected in the Provider
selection box atop the Connection Manager dialog.
Type \ select
the appropriate server name / server name with instance into the Server name
We can substitute "localhost"
or "localhost\<instance name>" here, if it is
appropriate to our environment.
complete the appropriate authentication choice for your environment in the
section labeled Log on to the server (I selected the radio button to the
left of Use Windows Authentication in the lab environment in which I
prepared this article).
AdventureWorks in the Select or enter a database name selector box,
within the Connect to a database section of the dialog.
Connection Manager dialog appears similar to that depicted in
Connection Manager Dialog in My Lab Environment
Click the Test
Connection button (in the bottom left corner of the Connection Manager
dialog) to ascertain that we have a valid connection.
We receive a positive
confirmation message, Test connection succeeded, assuming settings in
the dialog are in accordance with the above steps, as appropriate for our own
environments. The confirmation message box appears as shown in Illustration
Confirmation Message Indicating Successful Connection Test
10. Click OK to dismiss the
message box and return to the Connection Manager dialog.
11. Click OK on the Connection
Manager dialog to accept our settings and create the data connection.
We return to the Select
how to define the connection page of the Data Source Wizard, which
appears similar to that depicted in Illustration 13.
Illustration 13: Select
How to Define the Connection Page with New Data Connection
The Completing the
Wizard page appears.
13. Replace the name that appears in
the Data source name box with the following:
The Completing the
Wizard page appears, as shown in Illustration 14.
Completing the Wizard ...
14. Click Finish to create the
new Data Source, and to dismiss the Data Source Wizard.
The Wizard closes,
and the new Data Source appears in the Data Sources folder within
the Solution Explorer as depicted in Illustration 15.
Illustration 15: The New
Data Source Appears in the Solution Explorer
Having created a Report
Model Project, and defined a Data Source, we are ready, to define a Data