Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification - Page 3
March 14, 2005
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.
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.
4. Expand the FoodMart 2000 database.
5. Expand the Cubes folder.
The sample cubes appear, as shown in Illustration 2.
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:
The Duplicate Name dialog appears, with our modification, as depicted in Illustration 3.
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.