Hands-On Procedure
We will begin with a
scenario that illustrates a requirement for a schema simplification,
using a hypothetical business need to add practical value. Let's say that our
client, the FoodMart organization, has approached us with an enhancement
request it wishes to meet, from its Warehouse cube users. We are told
that the cube was developed by an energetic young professional who has since
moved on. The client is happy with the work in general, but in light of
several "discoveries" they have made since the developer left, they
suspect that this cube, as well as several others, might experience enhanced
performance if they subjected its design to a "second set of eyes."
When we hear that the
initial developer had no formal training in MSAS or even OLAP, except for
constantly referencing rudimentary text she had obtained from a popular online
auction site, we propose an examination of the overall setup, including the
schema design in Analysis Manager for the cube. To prepare, we will create a
copy of the original Warehouse cube, to preserve the original in its
current state, as well as to allow access to the original cube in the meantime
from enterprise users.
Preparation
Create a Copy Cube
Let's get started by
creating a copy of the Warehouse sample cube, which, along with the FoodMart
database that contains it, accompanies an MSAS installation. This will allow us
to keep the original sample cube intact for other uses.
1.
Open Analysis
Manager, beginning at the Start menu.
2.
Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
left.
Our
server(s) appear.
3.
Expand the
desired server.
Our
database(s) appear,
in much the same manner as shown in Illustration 1.
Illustration 1: Example
Databases Displayed within Analysis Manager
4.
Expand the FoodMart
2000 database.
5.
Expand the Cubes
folder.
The
sample cubes appear,
as shown in Illustration 2.
Illustration 2: The
Sample Cubes in the FoodMart 2000 Database
NOTE: Your databases / cube tree will differ, depending upon
the activities you have performed since the installation of MSAS (and the
simultaneous creation of the original set of sample cubes). Should you want or
need to restore the cubes to their original state, simply restore the database
under consideration. For instructions, see the MSSQL Server 2000 Books
Online.
6.
Right-click on
the Warehouse sample cube.
Again, we
are making a copy of the Warehouse cube, because our lesson will involve
making changes to the cube we use within the practice example. As we have
noted, working with the copy will allow us to maintain our existing sample cube
in its current condition, and available to other users.
7.
Select Copy
from the context menu that appears.
8.
Right-click on
the Cubes folder.
9.
Select Paste
from the context menu that appears.
The Duplicate
Name dialog appears.
As noted
in previous articles, we cannot have two cubes of the same name in a given MSAS
database.
10.
Type the
following into the Name box of the Duplicate Name dialog:
OPTIMAL SCHEMA
The Duplicate
Name dialog appears, with our modification, as depicted in Illustration
3.
Illustration 3: The
Duplicate Name Dialog, with New Name
TIP: As I have mentioned elsewhere in
this and other series, the foregoing is also an excellent way of renaming
a cube (a "rename" capability is not available here, as it is in
many Windows applications). Simply create a duplicate, give it the name to
which you wish to rename the old cube, and then delete the old cube, as
appropriate. This also works for MSAS databases, dimensions and other objects.
11.
Click OK
to apply the name change.
The new
cube, OPTIMAL SCHEMA, appears in the cube tree, among those already in
place. We now have a copy of the Warehouse cube, within which we can
perform the steps of our practice exercise. Let's process the new cube to "register"
it with Analysis Services, and to ensure that we are all in a "processed" state.
IE 7
Firefox 2.0