Expanding the Basic Cube with The Cube Editor
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
Click the Schema tab to
return to the table view.
the sales_fact_1997 table appears, at this stage of our development
effort, as shown below:
Illustration 29: Schema Pane, Initial Cube Design (Compressed)
Click Insert from the
Click Tables from the
menu that appears, as shown.
Illustration 30: Insert -> Tables from the Main Menu
Table dialog appears.
Select the time_by_day
table by clicking and highlighting it.
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
Illustration 31: The Select Table Dialog, time_by_day Table Selected
Click the Add button.
table appears on the schema tab, while the Select Table dialog remains
Add the following additional
tables after the manner of the time_by_day table insertion above:
Click Close on the Select
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.
Click the upper portion (where
the name appears) of the time_by_day table to select the table.
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
Data Viewer appears, as shown in Illustration 34 below.
Illustration 34: Partial View of the Browse Data Viewer
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.
Select the Calendar Time
dimension level member (the single level under the Calendar.Time dimension), as
Illustration 35: Calendar.Time Dimension as Currently Constructed
Right-click the Calendar
Time dimension level member.
Select Delete from the
Click Yes at the Confirm
Level Delete dialog to delete the member.
Perform steps 15 through 18 for
the Customer and Product dimension level members (the only level
members within each), respectively.
Page 9: The Cube Editor (Continued)
See All Articles by Columnist William E. Pearson, III