Procedure: Ascertain Connectivity of the Analysis Services Data Source

Objective:

Use
this procedure to ensure connectivity of a project’s Shared Data Source with
an Analysis Services database.

Background:

Many of us will be running “side-by-side
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced, within a
data source context, as a server / instance combination, versus a server
name
alone (the default for the AdventureWorks Sample Reports project
sample’s connection is localhost).

From
within the open Adventure Works Reports Sample project, in the SQL Server Business Intelligence
Development Studio
,
take the following steps:

  1. Double-click
    AdventureWorksAS.rds, within the Shared Data Sources folder
    seen in Solution Explorer.

The Shared
Data Source
dialog opens, and appears with default settings as
depicted in Illustration 1.



Illustration 1: The Shared
Data Source Dialog with Default Settings …

  1. Click the Edit button on the Shared Data Source dialog.

The Connection
Properties
dialog opens, and appears with default settings shown in Illustration
2
.



Illustration 2: The Connection
Properties Dialog with Default Settings …

We note that the default Server name is “localhost.”
While this might prove an adequate setting for a PC with only MSSQL Server
2005
installed (default instance), in many of our environments, the
requirement is for the server / instance combination that correctly
identifies the correct MSSQL Server 2005 instance. (Clicking the Test
Connection
button at this point will provide confirmation as to whether we
need to make this change).

  1. If appropriate, type the correct server / instance
    name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\MSSQL2K5,
    as depicted in Illustration 3.)



Illustration 3: The Connection
Properties Dialog with Corrected Settings …

  1. Ensure that authentication settings are correct for
    the local environment.
  2. Click the Test Connection button.

A
message box appears, indicating that the Test connection succeeded,
assuming that our changes (or lack of same, as appropriate) are appropriate.
The message box appears as shown in Illustration 4.



Illustration 4: Testing
Positive for Connectivity …

  1. Click OK to dismiss the message box.
  2. Click OK to accept changes, as appropriate,
    and to dismiss the Connection Properties dialog.

The Shared
Data Source
dialog appears, with our modified settings, similar to
that depicted in Illustration 5.



Illustration 5: The Shared
Data Source Dialog with Modified Settings …

  1. Click OK to close the Shared Data Source dialog, and to return to the
    development environment.
William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles