Make a Copy of the Warehouse Cube and Prepare it for Usage-Based Optimization
1. Open Analysis Manager, once again.
2. Expand the Analysis Servers folder by clicking the “+” sign to its immediate left.
Our server(s) again appear.
3. Expand the desired server.
Our database(s) appear, in much the same manner as shown in Figure 5.
Figure 5: A Sample Set of Databases Displayed within Analysis Manager
4. Expand the FoodMart2000 database.
5. Expand the Cubes folder.
The sample cubes appear, as shown in Figure 6.
Figure 6: The Sample Cubes in the FoodMart2000 Database
NOTE: Your databases / cube tree may 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.
Because our lesson will involve making changes to the Warehouse cube, let’s make a copy of the cube. Working with the copy will allow us to maintain our existing sample cubes in their original condition.
7. Select Copy from the context menu that appears, as shown in Figure 7.
Figure 7: Select Copy from the Context Menu
8. Right-click on the Cubes folder.
9. Select Paste from the context menu that appears, as shown in Figure 8.
Figure 8: Select Paste from the Context Menu
The Duplicate Name dialog appears.
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:
SSPerf_Warehouse
The Duplicate Name dialog appears, with our modification, as depicted in Figure 9.
Figure 9: The Duplicate Name Dialog, with New Name
TIP: As I have mentioned elsewhere in the series, this 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. (This also works for MSAS databases.)
11. Click OK to save the name change.
The new cube appears in the cube tree, among those already in place. We now have a copy of the Warehouse cube, SSPerf_Warehouse, upon which we can work with the Usage-Based Optimization Wizard. There is, however, another series of steps we need to perform to prepare the new cube for usage-based optimization.
When we made a copy of the Warehouse cube, we copied the structure exactly as it existed for the original sample. One component critical to the Usage-Based Optimization Wizard, however, could not be a part of our cloning process – the Query log entries that relate to our use of the Warehouse cube cannot accompany our new cube, because they are identified in the Query log database (see Optimization Tools: Basic Usage Analysis for details regarding the log) as belonging to the cube that generated them.