In the EASYDW schema, we have a geography hierarchy within the CUSTOMER table, where postal codes roll up into cities, which roll up into states, which, in turn, roll up into countries. One technique that makes it easy to construct the CREATE DIMENSION statement is to draw a bubble diagram, such as the one in Figure 8.2, showing these rollup relationships within a hierarchy. The direction of the arrows indicates how rollup can be done.

Figure 8.2. Bubble Diagram for a Geography Dimension

To convert the diagram in Figure 8.2 into a SQL dimension definition, each bubble in the diagram becomes a LEVEL in the dimension, as shown in the following example:

CREATE DIMENSION geography_dim
LEVEL postal_code IS customer.postal_code
LEVEL city        IS customer.city
LEVEL state       IS customer.state
LEVEL country     IS customer.country
HIERARCHY loc_rollup (
          postal_code      CHILD OF
          city             CHILD OF
          state            CHILD OF
          country
         );

Each level is specified using a name and the underlying column in the dimension table. For example, the level named CITY corresponds to the column CITY in the CUSTOMER table. The relationships between the levels are described with the HIERARCHY clause. The hierarchy is also given a name, LOC_ROLLUP in our example. The bubbles with arrows coming out of them in Figure 8.2 are described with the CHILD OF clause. In our example, postal code rolls up into city; therefore, postal_code is a CHILD OF city. City rolls up into state; therefore, city is a CHILD OF state and so on.

Note that the definition of a hierarchy signifies that for any value of a child column in a hierarchy, there must be one and only one value of its parent column. In our customer table, a postal code is unique to any given city; therefore, it satisfies this rule. For example, postal code 02134 is in the city of Boston. The code 02134 refers to addresses in Boston only; it cannot also be used to refer to addresses in San Francisco.

Sometimes it may be possible to logically roll up the same data in different ways. In our sample schema, EASYDW, as in many businesses, we use both a regular calendar and a fiscal calendar. In the regular calendar, days roll up into months, which roll up into years. In the fiscal calendar, days roll up into weeks, which roll up into fiscal quarters. This type of a logical model can also be represented in the dimension object by defining multiple hierarchies.

Figure 8.3 shows a diagram for this TIME dimension. It contains two hierarchies, describing the two ways by which the data can be rolled up from the TIME_KEY (i.e., daily) level, using the regular and the fiscal calendars.

Figure 8.3. Bubble Diagram for the Time Dimension

In the CREATE DIMENSION statement, each way of rolling up the data will be described using a HIERARCHY clause, as shown in the following example. The two hierarchies are called CALENDAR_ROLLUP and FISCAL_ROLLUP. Each arrow coming out of a bubble in Figure 8.3 is described in a CHILD OF clause. There are two arrows coming out of the TIME_KEY bubble, and hence there are two CHILD OF statements, one in each hierarchy. From a given date (TIME_KEY), the FISCAL_ROLLUP hierarchy tells us the week and fiscal quarter this date is in and the other, CALENDAR_ROLLUP, tells us the month and year.

CREATE DIMENSION time_dim
 LEVEL time_key    IS time.time_key
 LEVEL month       IS time.month
 LEVEL quarter     IS time.quarter
 LEVEL year        IS time.year
 LEVEL week_number IS time.week_number
 HIERARCHY calendar_rollup (
                time_key  CHILD OF
                month     CHILD OF
                year
           )
 HIERARCHY fiscal_rollup (
                time_key    CHILD OF
                week_number CHILD OF
                quarter
           );

At the top of every bubble diagram is the special level “ALL,” representing the “grand total” level for that dimension. All levels of a hierarchy can be rolled up to ALL. We don’t always define the ALL level in the CREATE DIMENSION statement explicitly.

Again, for each value of a child column in a hierarchy, there is only one parent value. Any given date—for example, 21-Mar-2003—falls into one and only one fiscal week and in a specific quarter.

In a dimension definition, the ATTRIBUTE clause is used to define any functional dependencies between columns within the same table that are not hierarchical in nature.

In the EASYDW schema, in the PRODUCT table we have two columns, PRODUCT_ID and PRODUCT_NAME, such that given a PRODUCT_ID, there is only one PRODUCT_NAME. The following example shows the definition of a dimension with the attribute clause, representing this relationship. Note, however, that this relationship is true only in one direction (i.e., it does not mean that given the PRODUCT_NAME we can determine the PRODUCT_ID).

CREATE DIMENSION product_dim
 LEVEL product_id   IS product.product_id
 LEVEL category     IS product.category
 HIERARCHY merchandise_rollup (
                   product_id    CHILD OF
                   category
                  )
 ATTRIBUTE product_id DETERMINES (product_name)
 ATTRIBUTE prod_manufacturer
           LEVEL product_id DETERMINES (manufacturer);

In the ATTRIBUTE clause, the name on the left side of the DETERMINES keyword should be a level name—for example, PRODUCT_ID. To the right of the DETERMINES keyword are the dependent columns—for example, PRODUCT_NAME. Note that you can either specify multiple dependent columns within the same attribute clause or specify different attribute clauses for each one—both ways convey equivalent semantics.

Note that you can also specify a name for the attribute relationship; however, this is optional. To do this, you need to use the extended clause with the LEVEL keyword. For example, in the preceding example, the relationship between PRODUCT_ID and MANUFACTURER is given a name, PROD_MANUFACTURER.

If you have a snowflake or other normalized schema, then your hierarchy may refer to columns in multiple dimension tables. The CREATE DIMENSION statement allows you to declare such relationships as well. Figure 8.4 shows a bubble diagram for a normalized TIME dimension. The bubbles and bold arrows define the rollup relationships as before. The dotted rectangles represent the tables where the levels come from. The dotted arrows show how the tables join to each other. In this example, there is a separate table for time, week, month, quarter, and year. The TIME table joins to the WEEK table using the join condition time.week_number = week.week_number and to the MONTH table using the condition time.month = month.month.

Figure 8.4. A Normalized Dimension

To convert this diagram to a CREATE DIMENSION statement, you would first define a level for each of the bubbles, specifying both the table name and column name for that level. For example, the level WEEK corresponds to the column WEEK.WEEK_NUMBER, which is the WEEK_NUMBER column from the WEEK table. Next, you would define your hierarchy using the level names as before. Finally, you would specify any joins that must be performed when traversing up the hierarchy using the JOIN KEY clause. Thus, you would get the following CREATE DIMENSION statement:

CREATE DIMENSION time_dim
 LEVEL time_key IS time.time_key
 LEVEL month    IS month.month
 LEVEL quarter  IS quarter.quarter
 LEVEL year     IS year.year
 LEVEL week     IS week.week_number
 HIERARCHY calendar_rollup (
                    time_key CHILD OF
                    month     CHILD OF
                    year
                    JOIN KEY time.month REFERENCES month
                    JOIN KEY month.year REFERENCES year
           )
 HIERARCHY fiscal_rollup  (
                    time_key CHILD OF
                    week     CHILD OF
                    quarter
                    JOIN KEY time.week_number  REFERENCES week
                    JOIN KEY week.quarter REFERENCES quarter
           )
 ATTRIBUTE time_key DETERMINES time.day_of_the_week
 ATTRIBUTE time_key DETERMINES month.month_name;

In the fiscal_rollup hierarchy in this example, the join key, TIME.WEEK_NUMBER column, is used to join the table TIME to the WEEK level in the WEEK table.

You can also specify attribute clauses in a normalized dimension. For example, the level TIME_KEY determines the column DAY_OF_THE_WEEK from the TIME table and the column MONTH_NAME from the MONTH table.

As discussed in Chapter 2, normalized dimension tables incur the overhead of extra joins during query processing and hence must be used with care.

You can use Oracle Enterprise Manager as an alternative to SQL to manage dimensions. To create a new dimension, from the Administration page, select the Dimensions link and click the Create button. You will see a screen such as the one shown in Figure 8.5. You need to name the dimension and identify which schema it will reside in. In our example, we are creating a dimension, EASYDW.TIME_DIM. We recommend that you use an appropriate naming convention to easily identify dimensions in the database and to differentiate them from dimension tables.

Figure 8.5. Creating a Dimension

The screen in Figure 8.5 has several tabs for creating levels, hierarchies, and attributes for the dimension. Each tab will have an Add button, which you can use to create the requisite levels, hierarchies, and attributes. For example, Figure 8.6 shows the levels tab, where you can see several levels we have created.

Figure 8.6. Creating a Dimension—the Levels Tab

At any stage, you can see the current SQL statement for the dimension by pressing the Show SQL button. Figure 8.7 shows the screen for adding a new level.

Figure 8.7. Creating a Dimension—Adding a Level

You must name the level and choose a table, which must be in the form of schema.tablename—for example, EASYDW.TIME. If you are creating a normalized dimension, then different levels could come from different tables. Once you pick a table, press the Populate Columns button to see the table columns in the Available Columns list. Click on the Move arrow keys to choose the columns for the level. In Figure 8.7, we are creating a level named TIME_KEY, based on the TIME_KEY column from the EASYDW.TIME table. Once you are done filling in all the information and press the OK button, the newly created level will now show up.

Once you have added all the levels, you can move on to creating hierarchies. Figure 8.8 shows the screen to create a new hierarchy, where we are adding a hierarchy named CALENDAR_ROLLUP.

Figure 8.8. Creating a dimension—Adding a Hierarchy

To build the hierarchy, use the Move keys to move levels from the Available Levels list to the Selected Levels list. One important point to note is that you must correctly order the levels in the Selected Levels list to reflect the hierarchy. The columns in the Selected Levels list are ordered from the highest to the lowest level in the hierarchy.

Finally, if you would like to create any attributes, click on the Attributes tab in Figure 8.5 and you will get a screen such as that in Figure 8.9. In this example, we are creating an attribute named MONTH_NAME. You must choose the level for which the attribute is being defined (MONTH in our example) and choose, from the drop-down box, the column the attribute determines (MONTH_NAME in our example).

Figure 8.9. Creating a Dimension—Adding an Attribute

Note that when you first go to the Attributes tab, you will find two predefined attributes, called long-description and short-description, which are used by the OLAP Option. You can edit these attributes from the OLAP Options tab, which we will discuss in Chapter 15.

Once you have created all the levels, hierarchies, and attributes, press the OK button in Figure 8.5 to create the dimension.