Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor - Page 8
November 11, 2002
We will now expand our
cube to include data from the respective dimension tables, among other
enhancements. First, we will add the dimension tables we require to the
existing core cube, and derive more useful dimension data thereby, using the
following steps. 1.
Click the Schema tab to
return to the table view. Only
the sales_fact_1997 table appears, at this stage of our development
effort, as shown below:
We will now expand our cube to include data from the respective dimension tables, among other enhancements. First, we will add the dimension tables we require to the existing core cube, and derive more useful dimension data thereby, using the following steps.
1. Click the Schema tab to return to the table view.
Only the sales_fact_1997 table appears, at this stage of our development effort, as shown below:
Illustration 29: Schema Pane, Initial Cube Design (Compressed)
2. Click Insert from the Main Menu.
3. Click Tables from the menu that appears, as shown.
Illustration 30: Insert -> Tables from the Main Menu
The Select Table dialog appears.
4. Select the time_by_day table by clicking and highlighting it.
The Detail section of the dialog becomes populated with the columns of the time_by_day table. The Select Table dialog now appears as shown in Illustration 31.
Illustration 31: The Select Table Dialog, time_by_day Table Selected
5. Click the Add button.
The time_by_day table appears on the schema tab, while the Select Table dialog remains open.
9. Add the following additional tables after the manner of the time_by_day table insertion above:
10. Click Close on the Select Table dialog.
The dialog disappears, leaving the schema view as depicted below (after arrangement):
Illustration 32: The Arranged Schema View with Added Dimension Tables
Note that joins between the fact table and the dimension tables are already in place, using the id keys in each. This will likely not be the case in a real world design effort, and the appropriate joins (perhaps not as straightforward as those found in our model) would need to be created.
As part of making our cube more user friendly for information consumers, we wish to substitute the dimension ID keys that it currently presents with more intuitive dimension fields. Let's begin with the time_by_day table.
In a case where we may not be familiar with the nature of the data or the characteristics of the fields in a given table, such as our time table, we can browse the data to get a look at its makeup.
11. Click the upper portion (where the name appears) of the time_by_day table to select the table.
12. Right-click and select Browse Data from the context menu, as shown in Illustration 33.
Illustration 33: Select Browse Data to View a Sample of a Table's Data
The Browse Data Viewer appears, as shown in Illustration 34 below.
Illustration 34: Partial View of the Browse Data Viewer
13. Close the viewer after reviewing the data columns and the formats of their contents.
14. In the Cube Tree, expand the existing Calendar.Time dimension by clicking the "+" sign to its immediate left.
Beginning with the Calendar.Time dimension and proceeding to the Customer and Product dimensions, we will eliminate the original dimension key as the member and substitute a more user friendly field from the related dimension table.
15. Select the Calendar Time dimension level member (the single level under the Calendar.Time dimension), as shown below:
Illustration 35: Calendar.Time Dimension as Currently Constructed
16. Right-click the Calendar Time dimension level member.
17. Select Delete from the context menu.
18. Click Yes at the Confirm Level Delete dialog to delete the member.
19. Perform steps 15 through 18 for the Customer and Product dimension level members (the only level members within each), respectively.