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.
