Define a Data Source
Defining
a Data Source is typically the first step we take with an Analysis
Services Project. A project can have multiple Data Sources, within
which we are essentially defining the connection string used to connect
to the actual sources 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 AdventureWorksDW database.
Let's create
a Data Source within our new ANSYS040 Data Source View project by taking the following steps:
1.
Right-click Data
Sources folder within the Solution Explorer.
2.
Select New
Data Source from the context menu that appears, as depicted in Illustration
9.
Illustration 9: Initial
Step in Creating a New Data Source
The
Data Source Wizard appears, opening with the Welcome to the Data
Source Wizard page, as shown in Illustration 10.
Illustration 10: Welcome
to the Data Source Wizard ...
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, or within another project housed
in the same solution within which we are currently working.
4.
Click New on
the Select how to define the connection page.
The Connection Manager
dialog appears.
5.
Ensure that Native
OLE DB\SQL Native Client is selected in the Provider selection box
atop the Connection Manager dialog.
6.
Type the
appropriate server name / server name with instance into the Server name
box.
We can substitute "localhost"
or "localhost\<instance name>" here, if it is
appropriate to our environment.
7.
Select / 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).
8.
Type the
appropriate server name / server name with instance name into the Server
name box.
9.
Select
AdventureWorksDW in the Select or enter a database name selector
box, within the Connect to a database section of the dialog.
The
Connection Manager dialog appears similar to that depicted in
Illustration 11.
Illustration 11: Connection
Manager Dialog in My Lab Environment
10. 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
12.
Illustration 12: Confirmation
Message Indicating Successful Connection Test
11. Click OK to dismiss the
message box and return to the Connection Manager dialog.
12. 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
We arrive at the Impersonation
Information page, where we define connection credentials for the data
source.
14. Click the radio button to the
immediate left of Use the service account, as shown in Illustration
14.
Illustration 14: Select
the Use the Service Account Option
The Completing the
Wizard page appears, as depicted in Illustration 15.
Illustration 15: Completing
the Wizard ...
We will
leave the assigned Data Source name in place for purposes of our
practice session.
16. 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 shown in Illustration 16.
Illustration 16: The New
Data Source Appears in the Solution Explorer
Having established the
basic prerequisites, we are ready, at this stage, to create a Data Source
View.