When you view the new DSV in the DSV designer, you'll note several missing
primary key and foreign key relationships. Although adding these relationships
isn't required, doing so can help SSAS form better queries against the data
source when refreshing dimensions or cubes. Also, if you don't add the appropriate
relationships in the DSV and you use the Auto Build option to invoke Intellicube
to build the cube, Intellicube won't be able to differentiate fact tables from
dimension tables. In the case of FoodMart 2000, you'll notice that the five
tables (account, category, department, product_class, and product) show up as
fact tables if you attempt to auto-build the cube without creating these relationships.
They show up this way because they have no defined relationships to other tables
in the DSV. If you uncheck the box for the table in the Fact column, you'll
see the warning message that Figure
2 shows. So, it's important to create relationships between the fact and
dimension tables.
Another good practice is to add logical primary keys to all dimension tables.
You can create relationships based on your knowledge of the underlying data
source. If the data source isn't available, you can easily view it by bringing
up the cube editors in the SSAS 2000 databases and examining the underlying
relationships. You can modify relationships in the DSV designer. Note that these
added relationships are in the DSV only. The DSV is an abstraction of the source
and doesn't result in changes to the source.
After creating the appropriate relationships, you can create a new cube in
SSAS 2005 by right-clicking Cubes in the Solution Explorer in BIDS, then selecting
New Cube. Make sure Auto Build is selected to invoke Intellicube. The wizard
now lists only one dimension table incorrectly as a fact table, presumably because
of its relationship to other tables (i.e., itself ). You can simply uncheck
the box in the Fact column next to the Employee table to remove this designation.
The Cube Wizard produces 10 dimensions, versus the 19 dimensions that the Migration
Wizard created. These 10 dimensions, which Figure
3 shows, are more "pure"—all related attributes, hierarchies, and
properties are contained within a single dimension. And the Cube Wizard has
created just one cube. (We could have eliminated the HR information by removing
it in the DSV or not selecting it during the cube creation).
As you can see in Figure 3, the
resulting dimensions and cubes reflect the desired architecture in SSAS 2005.
The biggest downside to starting from scratch is that the "extras" that existed
in the SSAS 2000 cube aren't reflected in our brand-new cube. Namely, we have
to manually set up calculated members, parent/ child dimensions, actions, custom
member rollups, and unary operators just as they were initially set up in SSAS
2000. If we intend to use cube partitioning, we'll have to re-implement this
functionality in the new database.
We also need to determine aggregation design before processing the cubes.
Before designing aggregations, be sure to evaluate the dimension attributes
to be used and set AttributeHierarchyEnabled to False on those attributes that
won't be used for analysis.
So, What's the Best Plan?
In upgrading SSAS cubes, both migrating and rebuilding cubes have pros and cons.
You might want to initially perform a straight migration just to take advantage
of new engine features such as more efficient memory management and multi-instance
support in SSAS 2005. Even when rearchitecting the cubes, you might find it
beneficial to ultimately leverage both methods. Table
1 gives a high-level overview of the benefits of both methods.
As you can see, the benefits of the two methods have very little overlap, which
makes combining the two methods even more appealing. You can migrate the database
by using the Migration Wizard, create a new database and build the cube from
scratch, script objects in the migrated database, and run the scripts in the
new database as a means of copying the desired objects from the migrated cubes.
The sidebar "Scripting a Cube in XMLA," page 34, describes how you can create
a script to run in SSMS to create calculated members from the source Sales cube
in the newly generated database.
Although I've tried to address the most common considerations for migrating
cubes from SSAS 2000 to SSAS 2005, I don't have enough space here to include
every possibility. For example, neither of the methods I discussed optimizes
aggregation usage and attribute relationships. But you can use the ideas in
this article to help you search BOL for specific migration issues that
you might encounter.