SQL Server 2005 Analysis Services (SSAS) provides many compelling reasons for
upgrading from SSAS 2000. Among the many enhancements, SSAS 2005 provides more
flexible analysis than SSAS 2000 by exposing most dimension attributes as analytical
objects. But executing on the decision to upgrade comes with some challenges.
Should you use SQL Server 2005's built-in tools to migrate your SSAS 2000 cubes?
Or, with all the architectural changes in SSAS 2005, should you rebuild the
cubes from scratch? Both methods have pros and cons, and your decision will
likely depend on your environment. Let's take a look at some key decision points
that will help you make your choice.
Note that this article assumes familiarity with basic concepts in SSAS 2005.
SQL Server 2005 Books Online (BOL) provides excellent information and
tutorials that cover these concepts. The sidebar "Upgrade Your Analysis Services
Terminology" also gives a high-level guide to the SSAS 2000 terms that have
changed in SSAS 2005. Also, to explore the pros and cons of the migration-versus-rebuild
decision, we'll refer to the FoodMart 2000 sample database. Although FoodMart
presents a simplistic cube implementation, many of the fundamental features
of SSAS 2000 are represented in one or more of the cubes in this database. FoodMart
also provides a universally understood data model, so the examples we use will
be easy to follow.
Migration
You can migrate an SSAS 2000 database to SSAS 2005 in a few different ways,
but all options use the Analysis Services Migration Wizard. For information
about the various migration options, check out Darshan Singh's November 2005
article "Step Up to SQL Server 2005" (InstantDoc ID 47749). Darshan notes that
before you start your upgrade, it's a best practice to run the Upgrade Advisor
to determine possible migration problems specific to your database. Also, you
can read about overall migration considerations in BOL (http://msdn2.microsoft.com/en-us/library/ms143235.aspx).
Known Analysis Services upgrade issues include the fact that drillthrough options,
linked objects, and remote partitions won't be migrated.
Note that the Migration Wizard will
make no effort to redesign your cubes to
leverage the features of SSAS 2005. The
wizard's mission is to simply recreate the
cube in SSAS 2005 as it was in SSAS 2000.
This simplistic approach is by design; Microsoft intended to minimize potential problems with front-end applications. Knowing
about this design will help you more easily
understand the choices that the wizard
makes.
You can migrate the FoodMart 2000 sample cube by opening an SSAS 2005 instance
in SQL Server Management Studio (SSMS) and walking through the Migration Wizard
(right-click the instance name and select Migrate Database). The wizard will
ask for predictable information regarding the source SSAS 2000 server and the
databases you want to migrate. After running the Migration Wizard, you can open
Business Intelligence Development Studio (BIDS) and view the resulting SSAS
cubes and shared dimensions, which Figure 1
shows.
There's some room for interpretation about how FoodMart should be implemented
on SSAS 2005, but most people agree that the migrated database in Figure
1 isn't the best design. The following documents some of the high-level
observations:
- Because of SSAS 2005's ability to create more comprehensive cubes than SSAS
2000, the four regular cubes should be collapsed into one. It can be argued
that the HR cube could stand on its own.
- Virtual cubes in SSAS 2000 were migrated as cubes because no equivalent
for a virtual cube exists in SSAS 2005. We shouldn't need virtual cubes when
the SSAS 2000 cubes are consolidated into a single SSAS 2005 cube.
- Several of the dimensions we got are variations on what should be a single
dimension in SSAS 2005. For example, Gender, Education Level, Marital Status,
and Yearly Income should become attribute hierarchies in a single Customer
dimension. Note that these attributes actually appear in the Customer dimension
because they were defined as member properties in SSAS 2000.
Another issue is the ability to easily add new attribute dimensions (which
are a cornerstone of SSAS 2005) to any of the core dimensions. The data source
view (DSV) that the Migration Wizard creates includes only columns that support
the migrated objects. The Customer table in the source FoodMart 2000 relational
database contains several columns that might make for rich analysis options,
such as city, state, customer region, total children, houseowner, number of
cars owned, and more. You can update the DSV to include all columns from the
source tables by right-clicking the background in the DSV designer and selecting
Refresh. But do this at your own risk; some properties of pre-existing attributes
might change. You might find it easier to simply replace specific dimension
tables in the DSV (right-click the table, select Replace Table, With Other Table,
then select the same table). Once you've updated the DSV with all available
columns, you'll need to edit each dimension to add desired attribute hierarchies
from the newly available columns.
Despite the negatives, a lot of good stuff happened during this migration.
All of the MDX calculations that we defined in calculated members migrated successfully
(look under the Calculations tab for the Sales cube in BIDS). Our parent/child
dimensions (Account and Employees) migrated successfully. And the partitions
and aggregation designs, actions, unary operators, and member properties were
retained and are operational. Finally, note that the Migration Wizard will migrate
only metadata. Migrated cubes must be fully processed to populate the cubes
with data.
Starting from Scratch
Building a cube from scratch isn't as extreme as it first sounds. The main thing
you need is a well-designed star or snowflake schema. You start in BIDS by adding
a new data source, then a new DSV. Create the new DSV in the BIDS Solution Explorer
by right-clicking Data Source Views and selecting New Data Source View. The
wizard will guide you through selecting the data source and adding the tables
required for all cubes (which will be measure groups in SSAS 2005).
Prev. page
[1]
2
next page 
