Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube - Page 3
August 8, 2005
Clone the FoodMart 2000 Sample Database
We will create a copy of the FoodMart 2000 sample database, which, along with the several cubes and other objects it contains, accompanies an Analysis Services installation. This will allow us to keep the original sample database 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 depicted 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.
4. Right-click the FoodMart 2000 database.
Again, we are making a copy of the database, because our lesson will involve making changes to the data connections, as well as the Sales cube, shared dimensions, and other objects, 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 possible users and applications.
5. Select Copy from the context menu that appears.
6. Right-click on the Analysis Server.
7. Select Paste from the context menu that appears, as shown in Illustration 3.
The Duplicate Name dialog appears.
As noted in previous articles, we cannot have two databases of the same name in a given Analysis Server.
8. Type the following into the Name box of the Duplicate Name dialog:
The Duplicate Name dialog appears, with our modification, as depicted in Illustration 4.
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 Analysis Services databases, dimensions and other objects.
9. Click OK to apply the name change.
The new database, AgingDim, appears in the tree beneath the Analysis Server, among the databases already in place, as shown in Illustration 5.
We now have a copy of the FoodMart 2000 database, within which we can perform the steps of our practice exercise. We will be working with the Sales cube within this database. We will next need to apply filters to contain only the purchase dates that occur within the last six months of year 1997, in accordance with the specifications that the information consumers have communicated.
Apply a Filter to the Time Dimension
We will next enact a source filter upon the Time dimension to limit the transactions in our cube to those that have occurred in the last half of 1997. This is one part of preparing the data for our practice session, and constitutes a step that would probably not occur in many real world scenarios. Again, we are simply making it possible to practice the creation of an aging capability using a sample data environment that is available to virtually anyone with an Analysis Services installation.
1. Expand the new AgingDim Analysis Services database by clicking the "+" sign to its immediate left.
2. In like manner, expand the Shared Dimensions folder within the AgingDim database.
3. Right-click the Time dimension.
4. Select Edit from the context menu that appears, as depicted in Illustration 6.
The Dimension Editor opens.
5. With the Time dimension (top of the tree) selected, click the Advanced tab in the Properties pane (underneath the tree pane)
6. Type (or cut and paste) the following into the Source Table Filter box within the Advanced Properties tab:
"time_by_day"."the_date" >#05/31/1997# AND "time_by_day"."the_date" <#01/01/1998#
7. In the Allow Duplicate Names box (on the Advanced tab, immediately above the Source Table Filter box), select True.
The Advanced Tab Properties pane appears, with our modifications, as shown in Illustration 7.
The filter we have put in place ensures that the new shared dimension will entrain only transactions that fit within the time band specified by the information consumers (the last half of year 1997). Next, we will process the dimension to prepare it for use within the Sales cube in our practice session.
8. Right-click the Time dimension, atop the tree, as before.
9. Select Process Dimension ... from the context menu that appears, as depicted in Illustration 8.
10. Click Yes on the dialog that next asks if we wish to save the dimension, as shown in Illustration 9, if necessary.
The Process a Dimension dialog appears next, affording us an opportunity to select a processing method.
11. Ensure that the radio button to the immediate left of Rebuild the dimension structure is selected, as depicted in Illustration 10.
12. Click OK.
Processing commences and completes rapidly. Successful processing is indicated by the green message at the bottom of the Process viewer, which appears as shown in Illustration 11.
13. Click Close to dismiss the Process viewer.
We are returned to the Dimension Editor. While we would typically reprocess a cube containing a dimension that we have modified, we will defer processing the Sales cube until we have completed most of the steps of the procedure that follows, in the interest of saving time.
14. Select File --> Exit from the main menu to return to Analysis Manager.
We are now prepared to add an aging dimension to the Sales cube, pursuant to the expressed business requirements of the information consumers.