Define a Data Source
a Data Source, as we have discovered in past articles, is typically the
first step we take with a new 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.
create a Data Source within our new ANSYS048 Measure Groups project by taking the following
Sources folder within the Solution Explorer.
Data Source ... from the context menu that appears, as depicted in Illustration
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.)
Click New on
the Select how to define the connection page.
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. (My server name with instance is MOTHER1\MSSQL2K5,
as may be seen in several of the illustrations in this and other articles.)
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.)
AdventureWorksDW 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
We arrive at the Impersonation
Information page, where we define connection credentials for the data
13. Click the radio button to the
immediate left of Use the service account (or supply other appropriate
local credentials), 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.
Completing the Wizard ...
leave the assigned Data Source name in place for purposes of our
15. 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