Introduction to Linked Objects in Analysis Services 2005 - Page 2

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.

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.

The Connect to Database dialog appears.

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.

The tabs of the Cube Designer open.

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

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

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

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

The Connection Manager opens next.

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

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