Oracle Warehouse Builder 11g Release 2 enables the entire spectrum of operations on dimensional objects, from defining them in a visual environment and then deploying them to either a relational schema or an analytical workspace in an Oracle database, to populating them with data, based on the logic you’ve defined in a mapping. Even a complex object, such as a cube, can be built within a few minutes with the help of a wizard, mostly with a few clicks of the mouse. Later, you can always use a respective object editor to edit that wizard-built object according to your requirements.

Although the wizards available in Oracle Warehouse Builder can do a lot of tedious work for you, the needs of your particular project may require you to roll up your sleeves and do some manual work, including coding. For example, you may need to specify the PL/SQL code that is part of a table function performing transformations or producing a set of source rows on the fly.

This article presents a few common tasks in Oracle Warehouse Builder that go beyond what you can accomplish with the wizards. In particular, it describes how you might add time series analysis at hour, day, month, and year levels to a data warehouse for analyzing Web traffic data. The article extends the example originally presented in "Cube Development for Beginners", which describes how to build a data warehouse designed to analyze the outgoing traffic of a Web site. To work through the examples here, you will need to complete the steps from that article as a starting point.

Organizing & Reorganizing Data with Dimensions
If you recall, a Time dimension that is created using the Warehouse Builder's Time Dimension wizard allows you to perform time series analysis at the year, quarter, month, and day levels. However, you may need to perform time analysis not only at the year-to-day levels but also down to the hour, minute and even second. For example, adding the hour level may sound reasonable if your data warehouse concentrates on traffic statistics, just as in the example in "Cube Development for Beginners". To achieve this, you might try to modify a wizard-built time dimension. That would not be easy, though. Instead, we will create another time dimension containing only the hour level data. The following steps describe how to do that:

1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL node and right-click the Dimensions node. In the popup menu select New Dimension.
2. On the Name and Description screen of the Create Dimension master, specify the name for the dimension: CLOCK_DM.
3. On the Storage Type screen, select ROLAP: Relational storage.
4. On the Dimension Attributes screen, modify the Length property for the NAME attribute to 2.
5. On the Levels screen, specify the only level: HOUR.
6. After the Create Dimension wizard is complete, the CLOCK_DM object should appear under the MY_PROJECT->Databases->Oracle->TARGET_MDL->Dimensions node in the Projects Navigator. Now double-click this object to open it in the Dimension Editor.
7. In the Dimension Editor, move on to the Hierarchies tab and delete the STANDARD hierarchy.
8. Finally, deploy the CLOCK_DM dimension as well as the objects created along with it by Warehouse Builder implicitly. Thus, you have to deploy the CLOCK_DM_TAB table, the CLOCK_DM_SEQ sequence, and then the CLOCK_DM dimension.

The next step is to load the newly created dimension with data. As you might guess, the CLOCK_DM dimension discussed here is supposed to contain only 24 members (one for each hour in the day).

As usual, loading a dimension with data can be done with a mapping in which you define the data flow and transformations from the source to the target. So, the first thing you need to do is define a source. In this case, unlike the mappings in the previous article, we do not need an external data source to know the 24 hours of the day. We will instead generate the dimension members for the CLOCK_DM dimension on the fly: 24 two-char values, each of which represents an hour of the 24-hour clock.

When we think of some nonpersistent data to be generated on the fly by the database, selecting from the DUAL table usually comes to mind. If you recall, however, the DUAL table contains only one row. So, to generate 24 values, you might want to put it in a loop. This might be accomplished with a table function that will generate and return those 24 values as a collection of rows; actually, as a varray. The following steps describe how you might define that varray and then the table function:

1. In the Projects Navigator, expand node MY_PROJECT->Databases->Oracle->TARGET_MDL->User Defined Types and then right-click the Varrays node. In the popup menu, select New Varray.
2. In the Create Varray dialog specify the name, say, HOURS_VAR, and then click OK. As a result, the Varray editor should appear, in which move on to the Details tab and change the settings as follows:
Data Type: VARCHAR2
Length: 2
Size: 24

3. Now deploy the newly created Varray. For that, in the Projects Navigator, right-click node MY_PROJECT->Databases->Oracle->TARGET_MDL->User Defined Types->HOURS_VAR, and select Deploy… in the popup menu.
4. You are ready now to create the table function. In the Projects Navigator, expand node MY_PROJECT->Databases->Oracle->TARGET_MDL->Transformations and right-click Table Functions. In the popup menu, select New Table Function. As a result, the first screen of the Create Table Function wizard should appear.
5. On the Name and Description screen, provide a name for the table function being created; for example, HOURS_GEN.
6. On the Return Type screen, select the HOURS_VAR varray created and deployed earlier. This should be found under the Collections->Private->TARGET_MDL->Varrays node.
7. On the Implementation screen, click the Code Editor button, and modify the table function implementation as follows:
--initialize variables here
s VARCHAR2(2);
-- main body
BEGIN
NULL;
FOR i IN 0..23 LOOP
--Fetch rows from the input cursor.
--Process the rows.
--Assign the row to the output_buffer record
-- Return rows with piperow statement.
SELECT TRIM(LEADING ' ' FROM TO_CHAR('00' + i, '09')) INTO s FROM DUAL;
PIPE ROW (s);
END LOOP
RETURN;
EXCEPTION
WHEN OTHERS THEN
NULL; -- enter any exception code here
RETURN;
END;


8. After the Create Table Function wizard has been completed, in the Projects Navigator, right-click the newly created table function, which should appear under the node MY_PROJECT->Databases->Oracle->TARGET_MDL->Transformations->Table Functions, and select Deploy… in the popup menu.

Now that you have the table function HOURS_GEN created and deployed, you can move on and design the mapping that will load the CLOCK_DM dimension. Here are the steps to follow:
1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL node and right-click Mappings. In the popup menu, select New Mapping to launch the Create Mapping dialog. In this dialog, specify the mapping name; for example, CLOCK_MAP, and click OK. As a result, the Mapping Editor canvas should appear.
2. From the Projects Navigator, select the HOURS_GEN table function object and drag it onto the Mapping canvas. Do the same operation to the CLOCK_DM dimension object.
3. In the mapping canvas, connect the VALUE attribute of the HOURS_GEN operator to the NAME attribute of the CLOCK_DM operator. Then, connect the VALUE attribute of the HOURS_GEN operator to the DESCRIPTION attribute of the CLOCK_DM operator.
4. Now you can deploy the mapping. In the Project Navigator, right-click the CLOCK_MAP object under the MY_PROJECT->Databases->Oracle->TARGET_MDL->Mapping node and select Deploy… from the popup menu.
5. After a successful deployment, you can execute the mapping, which should populate the CLOCK_DM dimension with data. To do this, right-click the CLOCK_MAP object and select Start… from the popup menu.

Full article...