Enhancing the Basic Cube with The Cube Editor
We will now expand our
cube to include data from the individual tables associated with the dimensions
we have already selected for our basic Revenue cube, as well as making
other enhancements consistent with those we made to the Expense cube.
These modifications will help to flesh out our cube model for its intended use
for financial reporting.
1.
From the Analysis Manager
console, expand the Analysis Servers folder (as appropriate), and then expand
the Analysis Server name (shown as MOTHER), to see the underlying
databases, as illustrated below.
Illustration 29: Expand the Analysis Server
NOTE: Factors specific to our individual environments, such as the
presence of additional Analysis Servers, as well as other databases, will mean
the appearance of varying objects here.
- Expand the FinRep_DB database to see the
underlying folders.
- Expand the Cubes folder, as displayed in Illustration
30 below.
Illustration 30: Expand the FinRep_DB Cubes Folder
We will now extend our
new Revenue cube to include data from the related dimension tables,
among other enhancements. First, we will add the dimension tables we require
to the existing core cube, from which we will be able to derive more useful
dimension data, using the following steps.
3.
From the cube tree, click the
new Fin_Rptg_Rev cube to select it.
4.
Right click Fin_Rptg_Rev
cube, and select Edit from the context menu that appears.
The Cube Editor
presents itself once again; we see Revenue_fact_Query in the data tab, just as we left it above.
5.
Click Insert from the
Main Menu.
6.
Click Tables from the
menu that appears, as shown.
Illustration 31:
Insert -> Tables from the Main Menu
The Select
Table dialog appears.
7.
Select the store table
by clicking / highlighting it.
The Detail
section of the dialog becomes populated with the columns of the store
table. The Select Table dialog now appears as shown in Illustration
32.
Illustration 32:
The Select Table Dialog, Store Table Selected
8.
Click the Add button (once
...).
The store table
appears on the schema tab, while the Select Table dialog remains open.
9.
Add the following additional tables, after the manner of the store
table insertion above:
10. Click Close on the Select Table
dialog.
We note that joins
between the fact table and the dimension tables are already in place, using the
id keys in each. As many of us have come to realize, this will often not be
the case in a real world design effort, where the appropriate joins (perhaps
not as straightforward as those found in our model) would need to be created.
As a part of making our
cube user-friendlier for information consumers, we wish to substitute the
dimension names that it currently presents with more intuitive dimension
fields. Having browsed the data in our time_by_day table in the first
half of the lesson, we know that it contains fields whose date related
information would serve our users well. (To browse the data to get a look at
its makeup, review the relevant steps in Part 1). We
will use these fields in our model for a more user-friendly appearance in the
cube we ultimately produce.
11. 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 remaining dimensions, we will substitute a
user-friendlier field than the original key number, from the related dimension
table.
12.
Select the Calendar Time
dimension level member (the single level under the Calendar.Time
dimension), as shown below:
Illustration 33: Calendar.Time Dimension as Currently Constructed
13.
Right-click the Calendar
Time dimension level member.
14.
Select Delete from the
context menu.
15.
Click Yes at the Confirm
Level Delete dialog to delete the member.
16.
Perform steps 13 through 15 for
the Store and Product dimension level members, respectively.
We should now have each
dimension in place, with no levels under each of the Calendar.Time, Store
and Product dimensions. We will add dimension levels from the dimension
tables in the next steps.
17.
Drag the following fields from
the time_by_day table over to drop onto the Calendar.Time
dimension in the cube tree, in the order shown:
- the_year
- quarter
- the_month
- the_date
18.
Right-click on each of the
above newly added dimension levels, and rename each, respectively, to the
following:
- Calendar Year
- Calendar Quarter
- Calendar Month
- Date
Dragging the fields
onto the folder in the order above, then changing their names, will result in
their alignment as shown below:
Illustration 34:
Calendar.Time Dimension with New Member Additions / Modifications
» 
Comment and Contribute
