Introduction to Linked Objects in Analysis Services 2005

Monday Aug 20th 2007 by William Pearson
Share:

Business Intelligence Architect Bill Pearson overviews Linked Object options that debut with Analysis Services 2005.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

Introduction

Among many new capabilities that debut in Analysis Services 2005, our options for bringing together data from different data sources – and extending structures that we have already built elsewhere within our integrated business intelligence solution - is further enhanced with linked objects. We can link a cube to a measure group in another cube that we maintain in the same Analysis Services 2005 database, a cube on the same server, or a cube in any other Analysis Services instance. We can also add a link within a link local cube to a dimension that we have created within another database, in like manner. Once we establish the link, we enable information consumers to query data and metadata in the linked measure group or dimension, just as they can do in a respective object that is native to the cube to which they have access.

In this article we will gain some hands-on exposure to linked objects in Analysis Services 2005. Our examination of the linked objects will include:

  • A discussion surrounding the general concepts underpinning linked dimensions and measures, including how they can be useful within our support of analysis and reporting within the enterprise.
  • Addition of a related linked dimension and measure group pair within a sample cube to demonstrate the ease with which we can add these linked objects to the cubes in our individual business environments.
  • A discussion of other considerations that surround the selection, addition and use of linked objects.

Linked Objects in Analysis Services 2005

In Microsoft SQL Server 2005 Analysis Services (“Analysis Services 2005”), a linked object is based upon a dimension or measure group that is stored in another Analysis Services 2005 database. The source database for a linked object can be on the same server or on a different server. By using a linked object, we can create, store, and maintain a dimension or measure group on one Analysis Services 2005 database, and still make that dimension or measure group available to users of multiple databases. To users, a linked object appears like any other dimension or measure group (depending upon which said object it is based, of course).

Potential benefits of using identical dimension and measure group structures across databases can be substantial. The most obvious benefit is the extension of a single structure, built and maintained in one location, to multiple Analysis Services databases. This can mean enforced consistency and synchronicity in the application of the dimensional structures involved, assuming that use of a linked dimension or measure group is otherwise appropriate for the environments involved.

Limitations, such as non-support of dimension writeback, might be a consideration in some environments. Moreover, we must keep in mind that related measure groups and dimensions, which must come from the same source database, must have the relationships between them maintained in the respective source database, once we have added these linked dimensions and measure groups to our local cube. Finally, we must keep in mind the basics for defining and configuring dimension usage and dimension relationships with measure groups, for the most part, just as we would for the respective objects that are native to our cubes, in order to avoid unexpected results.

Adding Linked Objects within a Cube

Let’s consider an example of a use for linked objects, within the context of a pair of sample Analysis Services 2005 databases with cubes that are available to anyone who has installed Analysis Services 2005. We will work with a “stripped down” version of another sample database, which will serve to contain the “primary” cube, and to whose basic dimensions and measure groups we will add a linked dimension / measure group pair. We will target a clone of the more sophisticated Adventure Works DW database, which ships with Analysis Services 2005, as a source for a linked dimension and measure group that is absent in our more basic primary cube.

Analysis Services 2005 makes available the Linked Object Wizard, within the Business Intelligence Development Studio, to assist us in the addition of various objects from other Analysis Services 2005 data sources (some can be linked, others can be imported – the latter we discuss in other articles of this series). The “other database” can exist on the same server as the cube into which we are adding the linked objects, or it can exist on a remote server, once again. As we have also noted, once we add the links to the targeted dimensions and measure groups, those dimensions and measure groups are available to cube users, via the links, just as if they were non-linked objects.

Before we get started with the Linked Object Wizard, we will need to prepare our local environment for the practice session. We will take steps to accomplish this within the section that follows.

Preparation: Create and Modify Sample Databases and Enable Creation of Linked Objects

Create Sample Primary Database for the Practice Exercise

Before getting started with our practice session, we will need a basic sample Analysis Services database (with cube) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, located in the References section of my articles index:

Make a Copy of a Basic Analysis Services Sample Project within a New Solution

Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:

Ascertain Connectivity of the Relational Data Source

Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for your own when following the procedure):

Deploy the Analysis Services Project

Conduct Further Sample Database Creation within Microsoft SQL Server Management Studio

We are now ready to ascertain that the new Analysis Services database is in place, as well as to move into further preparation. To do this, we will work from within the Microsoft SQL Server Management Studio.

1.  Start Microsoft SQL Server Management Studio.

2.  Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.

3.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

4.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears similar to that depicted in Illustration 1.


Illustration 1: Connecting to the Server ...

5.  Click the Connect button to connect with the specified Analysis Server.

The Microsoft SQL Server Management Studio opens.

6.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the “+” sign to its immediate left), appearing underneath the Analysis Server within which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.

7.  Right-click the Adventure Works DW database, and select Backup ... from the context menu that appears next, as shown in Illustration 2.


Illustration 2: Back Up the Sample Database

NOTE: The sample databases do not come preinstalled. If the Adventure Works DW database does not appear within the Object Explorer pane, you will need to install the constituent samples to continue with the steps that follow. Please see the documentation surrounding installation of MSSQL Server 2005 samples, which can be found on the installation CDs, to some extent within the Books Online, on www.microsoft .com, and elsewhere for installation instructions.

The Backup Database – Adventure Works DW dialog appears.

8.  Using the Browse ... button to the right of the input box labeled Backup file, navigate to a convenient place to place database backups.

9.  Name the backup as follows:

ANSYS061_AWDW_DimSource

10.  Ensure that, within the Options section of the dialog, all three checkboxes are cleared.

The Backup Database – Adventure Works DW dialog appears, with our input, as depicted in Illustration 3.


Illustration 3: Backup Database – Adventure Works DW Dialog

11.  Click OK to begin the backup process.

Processing begins, and, once it completes, the Backup Database – Adventure Works DW dialog disappears. We will next restore the backup, to provide a clone Analysis Services database to use as our target source for a linked dimension and measure group combination.

12.  Right-click the Databases folder for your Analysis Server, and select Restore ... from the context menu that appears next, as shown in Illustration 4.


Illustration 4: Restoring the Sample Database Backup

The Restore Database dialog appears.

13.  Type the following into the Restore Database selector box:

ANSYS061_AWDW_DimSource

14.  Using the Browse ... button to the right of the input box labeled From backup file, navigate to the location of the backup we created above.

15.  Select backup ANSYS061_AWDW_DimSource.abf from the tree displayed within the Locate Database Files dialog, as depicted in Illustration 5.


Illustration 5: Selecting the Backup to Restore ...

16.  Click OK to accept the selection, and to dismiss the Locate Database Files dialog.

The Restore Database dialog appears, with our input (and all other settings at default), similar to that shown in Illustration 6.


Illustration 6: Restore Database Dialog

17.  Click OK to begin the restoration process.

18.  Once restoration completes, right-click the Databases folder for your Analysis Server, as before.

19.  Select Refresh from the context menu that appears next, as depicted in Illustration 7.


Illustration 7: Refreshing the Analysis Services Databases ...

Our two new Analysis Services databases, DBJ_Basic AS DB and ANSYS061_AWDW_DimSource, appear in the tree of the Object Explorer. We now have two Analysis Services databases (one containing more dimensions and measure groups than the other), with which we can complete our practice session.

Enable Creation of Linked Objects, If Necessary, on the Analysis Server

By default, Analysis Services 2005 disallows the creation of linked dimensions and linked measure groups. In order to enable the linking of these objects both instances (if linking is occurring between two Analysis Services instances) need to enable this feature. We will ascertain if linked objects are already enabled within our own environment in the steps that follow. We can both observe and modify the values of the Feature\ LinkFromOtherInstanceEnabled, Feature\ LinkInsideInstanceEnabled, and the Feature\ LinkToOtherInstanceEnabled server configuration properties, if necessary, within SQL Server Management Studio.

1.  Within the Object Explorer pane, right-click the Analysis Server, and select Properties from the context menu that appears next, as shown in Illustration 8.


Illustration 8: Select Properties from the Context Menu

Since we intend to add linked objects between Analysis Services databases within the same Analysis Server instance, for purposes of our immediate needs, we will enable only linking inside that instance. (We can always return and enable other options as our needs change.)

2.  Locate the Feature\ LinkInsideInstanceEnabled property, and change the Value setting to “true” (if not already indicating “true”), with the dropdown selector, as depicted in Illustration 9.


Illustration 9: Enabling Linked Objects within the Same Analysis Server Instance

Our setting allows us to add linked objects within our basic cube (housed within the basic Analysis Services database we have created in earlier steps as a part of preparation, objects that will be targeted for linking from within the more sophisticated cube that belongs to the Adventure Works DW clone database, which we have also created as a part of preparation. The two Analysis Services databases lie within the same Analysis Server instance, which makes this single property setting ideal for our current needs.

Once we have made the setting change (assuming it was not already enabled), it appears among the other basic Analysis Server Properties.

3.  Click OK to accept modifications, and to dismiss the Analysis Server Properties settings page.

Having ensured that linked objects are enabled within the Analysis Server instance within which we wish to establish them, we are ready to continue to the procedural part of our practice session.

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

21.  Click OK to accept settings and to dismiss the Connection Manager.

We are returned to the Select how to define the connection page of the Data Source Wizard, which appears, with our definition pre-selected, as shown in Illustration 18.


Illustration 18: Select How to Define Connection Page, with Our Definition Pre-selected

22.  Click Next.

23.  Click the Service account radio button, to select it, on the Impersonation Information page that appears next, as depicted in Illustration 19.


Illustration 19: Select Service Account Credentials ...

24.  Click Next.

The Completing the Wizard page of the Data Source Wizard appears, as shown in Illustration 20.


Illustration 20: Completing the Wizard Page of the Data Source Wizard

We will leave the Data Source Name at default, and complete the Data Source Wizard at this point.

25.  Click Finish.

We are returned to the Select a Data Source page of the Linked Object Wizard, which appears, with our new data source definition pre-selected, as depicted in Illustration 21.


Illustration 21: Select a Data Source Page of the Linked Object Wizard

26.  Click Next.

We move to the Select Objects page of the Linked Object Wizard. It is here that we make our choice among the options available for linked objects. Scrolling down as necessary, click-select the checkboxes for the Sales Reason dimension, and then the Sales Reason measure group, containing the Sales Reason Count measure, as shown in Illustration 22.


Illustration 22: Select Objects Page of the Linked Objects Wizard, with Our Input

27.  Click Next.

We arrive at the Completing the Wizard page, where we see our selection of linked objects, as depicted in Illustration 23.


Illustration 23: Completing the Wizard Page of the Linked Object Wizard

28.  Click Finish.

The Linked Object Wizard is dismissed, and we are returned to the Cube Designer, where we see the newly linked Sales Reasons measure group, as shown in Illustration 24.


Illustration 24: The Cube Designer with Newly Linked Measure Group and Dimension

29.  Click the Dimension Usage tab.

Here we can see the new dimension and measure group currently displayed, as depicted in Illustration 25.


Illustration 25: The Dimension Usage Tab Displays the Newly Linked Dimension and Measure Group

And so we see that linking dimension and measure groups is a snap with the Linked Object Wizard. All that remains at this point is to reprocess the cube and to proceed with adding our new dimension and measure group into our analysis and reporting efforts.

We can leverage linked objects under many flexible scenarios, and enjoy the multiple benefits we have enumerated, within our own business environments, as easily as we have done in our practice session. Linked objects rank highly among a host of design enhancements in Analysis Services 2005.

Conclusion

In this article, we examined linked objects in Analysis Services 2005. We reviewed the general concepts that underlie linked dimensions and measure groups, including how they can be useful within our support of analysis and reporting within the enterprise. After several preparatory steps, where we created an environment with which to perform the steps of our practice session, we next got some exposure to the hands-on addition of a related linked dimension and measure group pair within a sample, basic cube.

Throughout our procedure, we demonstrated the ease with which we can add linked objects to the cubes in our individual business environments. Moreover, we discussed other considerations that surround the decision to use linked objects, as well as their selection, addition and use in general.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved