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 Aug 20, 2007

Introduction to Linked Objects in Analysis Services 2005 - Page 2

By William Pearson

Procedure: Add Linked Objects to a Basic Cube

Specific business requirements, often in conjunction with the general environment within which the enterprise operates, sometimes dictate the creation of multiple physical Analysis Services databases or cubes. Security and performance considerations are among the leading reasons for the establishment of separate data sources for analysis and reporting. Many times, however, these separate databases and cubes hold the need for certain structures, such as specific dimensions and measure groups, in common.

As is probably obvious, especially to those of us who have created virtual cubes in Analysis Services 2000, the capability to establish a dimensional or measure group structure in a given Analysis Services database, and to then reuse that structure across databases, between cubes in each database, offers many advantages. Moreover, because Analysis Services 2005 extends this support to allow object linking across Analysis Server instances, even more flexibility becomes available in “sharing” structures constructed in a single location. We can also link objects in both directions between two cubes, databases, or database instances. While we are limited to the types of links established within the Analysis Server properties (see details in the subsection above, entitled “Enable Creation of Linked Objects, If Necessary, on the Analysis Server”), it is easy to adjust the settings that support just the sort of links we plan to use within our local designs and implementations.

A few restrictions, which should, of course, be considered in determining their appropriateness for our local environments, come attached to our use of linked objects. For example, in selecting objects to link / import, our choice of a dimension in the target source which has an identical name to a dimension in the database / cube into which we are linking, Analysis Services will append an ordinal number (starting with '1' for the first duplicated name) to the selected name, as it adds the dimension to the Dimensions folder. Another restriction lies in the selection of a linked dimension in the remote database – we are not allowed to select dimensions that are, themselves, linked dimensions.

Other restrictions include the consideration that we cannot change the structure of a linked dimension from its new home, which means we cannot view it with the Dimension Structure tab of Dimension Designer. We can, however, view it from the Browser tab, once we have processed the linked dimension. Moreover, we can also change its name in the local database / cube, and, if we desire, create a translation for the name

Let’s set up a linked dimension / measure group pair between cubes that reside within the two sample Analysis Services 2005 databases we have created in our preparation section above. As a business scenario, we will assume that we are working within the DBJ_Basic AS DB database, a relatively simple environment whose sole cube, which we named Basic, has only a handful of dimensions and a couple of measure groups. Our intent is to “share” a dimension that exists in the more sophisticated ANSYS061_AWDW_DimSource database, a clone of the Adventure Works DW sample database (whose purpose is, after all, to provide examples of many of the capabilities of Analysis Services 2005, in general).

We will assume that we wish to link the dimension and measure group pair, named Sales Reason, from the larger database to the basic database, which contains neither the dimension or measure group.

Add Linked Objects to a Basic Cube

The Linked Object Wizard guides us easily through the process of creating a linked dimension and measure group within our cubes, as we shall see in the steps that follow. As we noted earlier, we will be linking a related dimension and measure group, which means that they must come from same source database - as they do in our practice session. It is important to realize that, once we link our dimension and measure group selection into our local cube, the relationships between them will need to be maintained in the source database from which we have selected them. (This is another reason I chose to use a copy of the Adventure Works DW sample database, so that subsequent changes to the original sample database would not impact the two data sources of the “working model” we construct in this session.)

Let’s enter the SQL Server Business Intelligence Development Studio (if it is not already open), from which we will perform the object linking procedure in our new Analysis Services database, DBJ_Basic AS DB.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

3.  Close the Start page, if desired.

4.  Select File -à Open from the main menu.

5.  Click Analysis Services Database ... from the cascading menu, as shown in Illustration 10.

Illustration 10: Opening the Analysis Services Database ...

The Connect to Database dialog appears.

6.  Ensuring that the Connect to existing database radio button is selected, type the Analysis Server name into the Server input box atop the dialog.

7.  Using the selector just beneath, labeled Database, select DBJ_Basic AS DB, as depicted in Illustration 11.

Illustration 11: Selecting the New Basic Analysis Services Database ...

8.  Leaving other settings on the dialog at default, click OK.

SQL Server Business Intelligence Development Studio briefly reads the database from the Analysis Server, and then we see the Solution Explorer populated with the database objects.

9.  Within the Solution Explorer, right-click the Basic cube (expand the Cubes folder as necessary).

10.  Click Open on the context menu that appears, as shown in Illustration 12.

Illustration 12: Opening the Cube Designer ...

The tabs of the Cube Designer open.

11.  On the Cube Structure tab, right-click somewhere in the white space within the Measures pane (in the upper left corner of the tab).

12.  Click New Linked Object on the context menu that appears, as depicted in Illustration 13.

Illustration 13: Launching the Linked Object Wizard ...

The Welcome ... page of the Linked Object Wizard appears next, as shown in Illustration 14.

Illustration 14: Welcome Page of the Linked Object Wizard

13.  Click Next.

The Select a Data Source page of the Linked Object Wizard appears.

14.  Click New Data Source.

The Welcome ... page of the Data Source Wizard appears next, as depicted in Illustration 15.

Illustration 15: Welcome Page of the Data Source Wizard

15.  Click Next.

The Select how to define the connection page of the Data Source Wizard appears.

16.  Click New.

The Connection Manager opens next.

17.  Type the name of the Analysis Server in the Server or file name input box.

18.  In the Initial Catalog dropdown selector, select our new target Analysis Services database, ANSYS061_AWDW_DimSource.

19.  Click Test Connection to ascertain connectivity to the specified Analysis Services database.

We should receive a confirmation that the “Test connection succeeded,” as shown in Illustration 16.

Illustration 16: Testing Positive for Connectivity ...

20.  Click OK to dismiss the message box.

The Connection Manager dialog appears, with our input, similar to that depicted in Illustration 17.

Illustration 17: Connection Manager with Our Input

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