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
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
Lets 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
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.)
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.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
-à Open from the main menu.
Services Database ... from the cascading menu, as shown in Illustration 10.
10: Opening the Analysis Services Database ...
to Database dialog appears.
that the Connect to existing database radio button is selected, type the
Analysis Server name into the Server input box atop the dialog.
Using the selector just
beneath, labeled Database, select DBJ_Basic AS DB, as depicted in Illustration 11.
11: Selecting the New Basic Analysis Services Database ...
other settings on the dialog at default, click OK.
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 Solution Explorer, right-click the Basic cube (expand the Cubes
folder as necessary).
on the context menu that appears, as shown in Illustration 12.
12: Opening the Cube Designer ...
of the Cube Designer open.
the Cube Structure tab, right-click somewhere in the white space within
the Measures pane (in the upper left corner of the tab).
Click New Linked Object
on the context menu that appears, as depicted in Illustration 13.
13: Launching the Linked Object Wizard ...
... page of the Linked Object Wizard appears next, as shown in Illustration
14: Welcome Page of the Linked Object Wizard
a Data Source page of the Linked Object Wizard appears.
... page of the Data Source Wizard appears next, as depicted in Illustration
15: Welcome Page of the Data Source Wizard
The Select how to define the connection page of the Data
Source Wizard appears.
The Connection Manager opens next.
Type the name of the Analysis
Server in the Server or file name input box.
In the Initial Catalog
dropdown selector, select our new target Analysis Services database, ANSYS061_AWDW_DimSource.
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
Illustration 16: Testing
Positive for Connectivity ...
Click OK to dismiss the
Manager dialog appears, with our input, similar to that depicted in Illustration
Illustration 17: Connection
Manager with Our Input