Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 11, 2006

Mastering Enterprise BI: Introduction to Translations - Page 3

By William Pearson

Define a Data Source

Defining a Data Source, as we have discovered in other articles of our series, 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 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 AdventureWorksDW database.

Let's create a Data Source within our new ANSYS051_Translations 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 7.

Illustration 7: 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 8.

Illustration 8: Welcome to the Data Source Wizard ...

3. Click Next.

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.)

4. Click New on the Select how to define the connection page.

The Connection Manager dialog appears.

5. Ensure that .Net Providers\SqlClient Data Provider is selected in the Provider selection box atop the Connection Manager dialog.

6. Type / select 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. (My server name with instance is MOTHER1\MSSQL2K5, as may be seen in several of the illustrations in this and other articles.)

7. Select / complete the appropriate authentication choice for the local 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. 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 9.

Illustration 9: Connection Manager Dialog in My Lab Environment

9. 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 10.

Illustration 10: 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 11.

Illustration 11: "Select How to Define the Connection" Page, with New Data Connection

12.  Ensuring that the new Data Connection is selected, click Next.

We arrive at the Impersonation Information page, where we define connection credentials for the data source.

13.  Click the radio button to the immediate left of Use the service account (or supply other appropriate local credentials), as shown in Illustration 12.

Illustration 12: Select the Use the Service Account Option

14.  Click Next.

The Completing the Wizard page appears, as depicted in Illustration 13.

Illustration 13: Completing the Wizard ...

We will leave the assigned Data Source name in place for purposes of our practice session.

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 14.

Illustration 14: 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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM