In the OWB10g_DEMO project, expand the Databases node, expand the Oracle node, expand the WH target module and expand the Dimensions node.

Select the T_TIME dimension definition. Right click on T_TIME dimension and select Editor.

Oracle Warehouse Builder ships a pre-defined time dimension that is fully compatible with a time dimension that an OLAP system requires.

The Dimension Editor pops up with the time dimension. Click on the L_YEAR level to see the level attributes (use the little downward shape arrow on the right of the level L_YEAR).

When the L_YEAR level is expanded you see the arrow is now changed to an upward shaped arrow. Click on the arrow to collapse the level.

In the Dimension Editor, from the menu select Edit > Object Properties. The Dimension Properties window pops up.

Examine the different tabs, Name showing the name and description, Levels shows all the levels created for the dimension, Level attributes shows the attributes or level columns, similar to columns in a table, Hierarchies and Level Relationships. Close the property sheet by clicking Cancel.

Warehouse Builder automatically implements a star schema. In the Dimension Editor, select Edit menu, select Table Properties and see how the dimension will be created in a relational environment.

Click on the various tabs, Name showing the name and description for the table, Column, contains the names of the columns in the table, Constraints and Attribute Sets. Click Cancel to close the Table properties window.

Close the Dimension Editor.

Expand the OWB10g_DEMO project, expand Databases, and expand the Oracle node. Expand WH target module and select the Dimensions node.

Right click the Dimensions node and select Create Dimension.

Click Next on the New Dimension Wizard: Welcome page.

The New Dimension Wizard: Name page appears. Enter WAREHOUSE as name for the dimension, and WRE as the prefix. The prefix is used to identify the dimension and used to name unique keys and level attributes.

Click Next to move to the next step of defining levels for the WAREHOUSE dimension.

The New Dimension Wizard: Levels page appears.

To define the first level COUNTRY: Type COUNTRY as the level name, CTY as the prefix. Click Add.

On clicking Add the level COUNTRY appears on the right-hand side.

To define the second level LOCATIONS. In the New Dimensions Wizard: Levels page, type LOCATIONS as the level name, LCN as the prefix. Click Add.

On clicking Add the level LOCATIONS appears on the right-hand side below COUNTRY.

To define the third level WAREHOUSE. In the New Dimensions Wizard: Levels page, type WAREHOUSE as the level name, WRE as the prefix. Click Add.

All the three levels COUNTRY, LOCATIONS and WAREHOUSE appears on the right-hand side.

Click Next to move to the next step of defining level attributes for the levels created.

You can use this chart as a lookup for understanding the level attributes for each level.

In the New Dimension Wizard: Level Attributes page, from the level drop-down box select WAREHOUSE. You see the pre-defined attribute ID. Make no change to it.

The WAREHOUSE level has two level attributes ID and NAME. To create a new attribute NAME: Type NAME as the name for the level attribute, select VARCHAR2 from the data type drop down list box and type 35 for length. Click Add.

In the New Dimension Wizard: Level Attributes page, select LOCATION from the level drop-down.

You see the pre-defined attribute ID. Make no change to it.

The LOCATION level has three level attributes, ID, CITY, STATE_PROVINCE.

To create a new attribute CITY: Type CITY as the name for the level attribute, select VARCHAR2 from the data type drop down list box and type 30 for length. Click Add.

To create a new attribute STATE_PROVINCE: Type STATE_PROVINCE as the name for the level attribute, select VARCHAR2 from the data type drop down list box and type 25 for length. Click Add.

in the New Dimension Wizard: Level Attributes page, select COUNTRY from the level drop-down.

You see the pre-defined attribute ID.

You have to update the ID attribute from number datatype to char datatype. To update the ID attribute. Select ID from the list box on the right hand-side. Change the datataype to CHAR and type 2 for length. Click Update.

Add a new attribute NAME. Type NAME as the name for the level attribute, select VARCHAR2 from the data type drop down list box and type 40 for length. Click Add.

Click Next to move to the next step of creating a hierarchy of levels using the New Dimension Wizard: Hierarchy page.

In the New Dimension Wizard: Hierarchy page type WAREHOUSES as the hierarchy name, WRE as the prefix. Click Add.

Click Next.

The New Dimension Wizard: Level Relationships page appears. In the hierarchy drop-down list box select WAREHOUSES.

Select and move all the levels (COUNTRY, LOCATION, WAREHOUSE) to the Selected Levels list using the >> (double arrow). In the Selected Levels list box the levels should appear in the order as COUNTRY should appear above LOCATION. WAREHOUSE should appear below LOCATION.

On this page you define how the levels in each hierarchy relate to each other. You can use the up and down arrows to arrange the selected levels into the appropriate hierarchical level relationship, making sure to place the lowest level of the hierarchy at the bottom of the list.

Click Next. The New Dimension Wizard: Finish page appears. Click Finish to create the WAREHOUSE dimension.

To see the code Warehouse Builder generates, In the OWB10g_DEMO project, expand the Databases node, expand the Oracle node, and expand the WH target module. Expand the DIMENSIONS node and right click the WAREHOUSE dimension. Select Generate.

In the Generation Results window, select the WAREHOUSE dimensions. Select the Script tab in the bottom half of the screen, and select the WAREHOUSE_DIM.ddl DIMENSION script. Click View Code at the bottom.

You view the create dimension definition in the script window. Close the script window and click OK to close the Generation Results window.

In the OWB10g_DEMO project, expand Databases, expand the Oracle node and expand the WH target module. Expand the Cubes node. Right click the SALES cube and select Editor.

The SALES cube has one measure REVENUE and six dimensions CHANNEL, PRODUCT, SALES_REP, CUSTOMER, PROMOTION, T_TIME. Close the cube editor. In the next topic you will learn how to create a cube.

To create a cube, right click the Cubes node, select Create Cube, and click Next in the New Cube Wizard: Welcome page

In the New Cube Wizard: Name page, type INVENTORIES as the name of the cube. Click Next.

The New Cube Wizard: Define Foreign Keys page appears. The cube will have three dimensions: PRODUCT, T_TIME and WAREHOUSE. For each of these dimensions, you have to select the lowest level in the dimension, the corresponding unique or primary key, and add the foreign key.

Select dimension PRODUCT from the dimension drop-down box.

Select level PRODUCT from the level drop-down box.

Click Add.

Clicking Add moves the choices you have made to the List of Foreign Keys field.

In the New Cube Wizard: Define Foreign Keys page,

Select dimension T_TIME from the dimension drop-down box.

Select level L_MONTH from the level drop-down box.

Click Add.

Clicking Add moves the choices you have made to the List of Foreign Keys field.

In the New Cube Wizard: Define Foreign Keys page,

Select dimension WAREHOUSE, level WAREHOUSE and click Add.

In the New Cube Wizard: Define Foreign Keys page, select the checkbox Create a segmented unique key from foreign keys.Click Next.

Create a segmented unique key from foreign keys will ensure the combination of product, month, and warehouse in the INVENTORIES cube be unique. Warehouse Builder automatically creates the unique key definition for you.

The New Cube Wizard: Define Measures page appears. Click Add, add a measure, AMOUNT, data type NUMBER. Click Next.

On this page you define the numeric or quantitative columns for the cube.

The New Cube Wizard: Finish page appears. Click Finish to complete the object definition creation.

The INVENTORIES cube as it appears in the cube editor.