Featured Database Articles

Introduction to Linked Objects in Analysis Services 2005 - Page 2

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.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.