Introduction to Linked Objects in 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:

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:

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):

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles