In the Global Explorer panel, expand the Security node. Right-click the Users node and select New.

Note: You must save or revert your changes before creating a new user. To save previous changes, select Save All from the Design menu or click on the toolbar.

On the Create User dialog, you can select from the available list of database users or create a new one.

Click on Create DB User to create a new target user in the database.

To be able to create a new target user, you need to have DBA privileges. In the Create Database User dialog box, enter system as username and your system account password as the password.

Enter EXPENSE_WH/EXPENSE_WH as the username/password for the new DB user. Accept the defaults for the tablespace properties. Click OK.

You are returned to the Create User dialog. Observe that EXPENSE_WH has been added to the Selected Users list. Click OK.

When the progress window closes, examine the Users node. You find the EXPENSE_WH user added to the list of existing target users.

You now create the target location for the EXPENSE_WH user.

Note: Locations define information about the database schema or target tool where you will be deploying objects. Locations are specific to a type of module, such as Oracle Database, SAP, or flat file.

Expand Locations > Databases. Right-click Oracle and select New.

In the Create Oracle Database Location window, specify the following information:

	In the Name field, change ORACLE_LOCATION1 to EXPENSE_WH_LOCATION
	User Name: EXPENSE_WH
	Password: EXPENSE_WH
	Host: localhost or machine name
	Port: 1521
	Service Name: orcl
	Schema: EXPENSE_WH
	Version: 10.2 or 11.1, depending on database version you are using

Click Test Connection. A success message should appear.

Click OK. Examine the Locations > Database > Oracle node. You find EXPENSE_WH_LOCATION added to the list of existing locations.

In the OWB_DEMO project, expand the Databases node, right-click the Oracle node, and select New.

The Create Module Wizard is launched. Click Next on the Welcome page.

On the Name and description page, enter EXPENSE_WH as the name of the module. Accept Development as the module status and ensure that Warehouse Target is selected as the module type. Click Next.

On the Connection information page, from the Location drop-down list, select EXPENSE_WH_LOCATION as the location of the module. Observe that all the relevant connection details are already specified. This is because Warehouse Builder derives the location information directly from the user you created. Click Next.

Note: Before you selected EXPENSE_WH_LOCATION from the Location drop-down list, EXPENSE_WH_LOCATION1 was selected by default. This is because whenever you try to create a module, Warehouse Builder automatically assigns a location with _LOCATION<no> suffixed to the module name.

You have selected the location, EXPENSE_WH_LOCATION, which was created when you created the EXPENSE_WH target user.

On the Summary page, examine the details and click Finish.

Observe that the EXPENSE_WH target module has been added under the Oracle node of the OWB_DEMO project. Expand EXPENSE_WH and examine its contents.

SQL Support and Heterogeneous Joins: When you use external tables, you can query and transform data contained in the flat files directly; SQL code is generated by Warehouse Builder. In addition, heterogeneous joins between flat files and relational tables are enabled.

Enabling Parallel SELECT Statements: You can get parallel access to the file through the table. For example, SELECT statements can be run in parallel thereby optimizing performance.

Eliminating Flat-File Staging Tables: When you use a flat file operator, Warehouse Builder generates SQL*Loader code and you need a relational staging table to load the data. External tables eliminate the need for a staging table thus reducing the processing time to load flat-file data and the need for extra storage space.

In the Project Explorer panel, expand OWB_DEMO. Expand Databases > Oracle > EXPENSE_WH. Right-click External Tables and select New.

The Create External Table Wizard is launched. Click Next on the welcome page. The wizard guides you through the creation of an external table.

On the Name and Description page, enter EXPENSE_CATEGORIES as the name of the external table. Click Next.

On the File Selection page, select EXPENSE_CATEGORIES_CSV and click Next. This file, which you sampled in the previous lesson, contains the metadata for the external table.

On the Locations page, you select the default location that is used by the external table. Select SOURCE_LOCATION from the location drop-down box. Click Next.

On the Summary page, review the details and click Finish. The wizard creates the external table. In Project Explorer, you see EXPENSE_CATEGORIES added to the External Tables node.

Now, you repeat the same steps to create an external table for the export_csv file.

Right-click External Tables and click New. Click Next to skip the welcome page of the Create External Table Wizard. On the Name and Description page, enter EXPENSE_DATA as the name of the external table. Click Next.

On the File Selection page, select EXPORT_CSV and click Next.

On the Locations page, you select the same default location that you selected for the previous external table. Select SOURCE_LOCATION from the location drop-down box. Click Next. On the Summary page, review the details and click Finish. The wizard creates the external table.

Observe, that both external tables are added to the target module.

You need to configure the physical file system details for the two external tables. Right-click EXPENSE_CATEGORIES and select Configure.

In the Configuration Properties dialog box, right-click Data Files and select Create. Accept the default name, NEW_DATAFILE_1.

On the right side in the Configuration Properties dialog box, select SOURCE_LOCATION for the Data File Location property. For the Data File Name property, enter expense_categories.csv. Click OK.

Repeat the same steps to configure the EXPENSE_DATA external table. The configuration should look as shown in the screenshot.

Click OK.

In the Project Explorer panel, expand OWB_DEMO > Databases > Oracle. Expand SALES_WH, and then expand Dimensions. Right-click PRODUCTS and select Open Editor.

Note: Alternatively, you can also double-click PRODUCTS.

The Data Object Editor is launched. The Data Object Editor is the single interface where you can easily design, create, and manage a variety of database or dimensional objects. The Data Object Editor is a central place where you can build or modify various warehouse schema designs through the use of diagrams and property sheets. This is how the interface looks:

As you observe there are numerous panels, but you focus on the Dimension Details panel to understand the details of a dimension.

To view the dimension's attributes, click the Attributes tab in the Dimension Details panel.

Observe that the dimension is assigned a sequence, PROD_DIM_SEQ, to populate a dimension key when loading data.

Also, note that one of the attribute IDs has been identified as a Surrogate identifier and SOURCE_ID attribute is assigned as the Business identifier.

Note: The dimension key column is the primary key for the dimension and implements the surrogate identifier of each level. Using a sequence, you can populate the dimension key with unique values.

A surrogate identifier uniquely identifies each level record across all the levels of the dimension and is loaded from a sequence, as specified in the dimension key field.

A business identifier is an attribute that is always derived from the natural key of the data source.

A parent identifier is also available when you want to use value-based hierarchies.

In the Descriptor column, the dimension attributes marked as Long Description and Short Description can be used to display the dimension members in business intelligence tools.

Click in the Name column and add two DATE attributes, VALID_FROM_DATE and VALID_TO_DATE, with the following details, as shown in the screenshot.

To view the dimension levels and level attributes, click the Levels tab in the Dimension Details panel. Observe that there are four levels and each level has an applicable set of attributes, for example PRODUCT level implements all the dimension attributes as level attributes, except the two date attributes that you added in the previous step.

To include the two date attributes as applicable attributes for the PRODUCT level, select the Applicable check boxes for VALID_FROM_DATE and VALID_TO_DATE attributes, as shown in the screenshot.

Note: A level is not required to implement all dimension attributes. Implementing a dimension attribute on a level enables you to store this information on a level.

In a relational implementation, a level attribute becomes a column in a table. Level attributes do not have to have the same name as the dimension attribute, but Warehouse Builder defaults it as such in the panel.

When defining the levels you need not worry about the order in which you enter the levels. The levels are only organized in the hierarchies.

To view the dimension hierarchy, click the Hierarchies tab in the Dimension Details panel. Observe that there is only one hierarchy, PROD_STD, with levels arranged in a particular order (top down in the panel list).

The Default check box allows you to select a hierarchy that will display when a user queries. So it is important to pick the most commonly used hierarchy for this.

When you define a dimension, you can also specify the slowly changing dimension strategy.

Slowly Changing Dimensions determine how you store historical changes to your dimension values. You can choose between the three strategies, as shown in the screenshot.

Click the SCD tab to view the slowly changing dimension strategy for the PRODUCTS dimension.

Note: SCD Type 2 and 3 are available with the Enterprise ETL option of OWB 11g. With Core ETL features, SCD Type 1, that is, Do not keep history option, is only available. If you are using the Core ETL features package and you are not able to perform this step, you can still continue with the next steps in this lesson and later lessons. This step is only for your examination and does not impact the hands-on steps going forward.

If you want to store the complete change history, you would use a type 2 implementation. Select Type2: Store the complete change history. Notice that the Settings button is enabled. Click Settings.

In the Type 2 slowly changing dimension dialog box, in the Record History drop-down list, you need to specify the attributes as:

	Trigger History: Select this option for an attribute if a change in the attribute's data element requires the record to create a new version of itself.
	Effective Date: Set this option for the single attribute that stores the value of the date/time the record becomes active.
	Expiration Date: Set this option for the single attribute that stores the date/time the record is no longer valid or effective.

For example, to record the change history whenever the LIST_PRICE, NAME and PACK SIZE of a product changes, set them as Trigger History attributes, as shown in the screenshot. Also, VALID_FROM_DATE is set as Effective Date and VALID_TO_DATE is set as Expiration Date attributes.

Click Close in the Type 2 slowly changing policy dialog box.

You design dimensional objects using Warehouse Builder, and then deploy them either in a relational form or in a multidimensional form to the database.

On the Storage tabbed page, you decide whether the designed dimension is implemented as relational or multidimensional and based on the type, Warehouse Builder generates appropriate storage type code.

Click the Storage tab to view the selected implementation for the PRODUCTS dimension. For a relational implementation, you can choose between Star, Snowflake, or Manual.

The Data Object Editor also provides a Data Viewer that enables you to view and drill down into a dimension's data.

From the Diagram menu, select Close Window to exit the Data Object Editor.

You were previously examining the SALES_WH module. Now, expand EXPENSE_WH, right-click Dimensions and select New > Using Time Wizard.

The Create Time Dimension Wizard is launched. Click Next on the Welcome Page.

On the Name and Description page, enter REL_TIME as the name of the dimension. Click Next.

On the Storage page, select ROLAP: Relational storage to specify relational implementation of the dimension. Click Next.

On the Data Generation page, you specify the range of time data that is required for your warehouse. This information is used to generate a mapping that populates the time dimension. Within this mapping, the dates you enter are added as parameters, allowing you to rerun this mapping with dates at a later stage.

For Start Year, enter 2003 and for Number of Years enter 3. Ensure that Calendar is selected, as shown in the screenshot.

Note: A hierarchy defines hierarchical relationships between adjacent levels in a time dimension. A time dimension can have one or more hierarchies. Each hierarchy must be either a fiscal hierarchy or a calendar hierarchy.

If your time dimension contains fiscal data, you must specify the start month and day for the fiscal year and start
day for the fiscal week. You also need to specify month and quarter boundary convention that can be 445 or 544.

Click Next.

On the Levels page, select Normal Hierarchy and select all the levels: Calendar Year, Calendar Quarter, Calendar Month and Day. Click Next.

On the Pre Create settings page, examine the details and click Next.

A progress bar shows the progress as the wizard creates the necessary objects.

On successful completion, click Next. On the Summary page, examine the details again and click Finish.

Observe that the wizard has generated four objects necessary for a fully functional TIME dimension: REL_TIME dimension object, REL_TIME_SEQ sequence that populates the surrogate ID of the time dimension levels, REL_TIME table to support the relational implementation of the time dimension that physically stores the time data, and most importantly the REL_TIME_MAP mapping to populate the time dimension.

If you are curious to know how REL_TIME_MAP looks like, double-click REL_TIME_MAP under the Mappings node to launch the Mapping Editor.

From the View menu, select Auto Layout. You can see the expanded mapping. You can scroll or select the zoom options from the View menu to view the mapping appropriately. Warehouse Builder creates this complex mapping automatically for you, there by enhancing the productivity and saving your time.

From the Mapping menu, select Close to exit the Mapping Editor.

In the EXPENSE_WH module, right-click Dimensions and select New > Using Wizard.

The Create Dimension Wizard is launched. Click Next on the Welcome Page.

On the Name and Description page, enter REL_CATEGORY as the name of the dimension. Click Next.

On the Storage Type page, select ROLAP: Relational Storage to specify the relational implementation of the dimension. Click Next.

On the Dimension Attributes page, you find three predefined columns, ID, NAME, and DESCRIPTION. Note that for the ID attribute, the identifier is set as Surrogate and for the Name attribute the identifier is set as Business.

You can change these attributes to your liking, but you must have a surrogate and a business identifier in the list. The surrogate identifier must have the NUMBER data type.

For the NAME attribute, change Length to 30. Click Next.

On the Levels page, specify the following levels for the default hierarchy in the dimension:

	CATEGORY (Description: Accounting Category)
	TYPE (Description: As entered on Expense Report)

Click Next.

On the Level Attributes page, for each level that you have defined, you select the dimension attributes that apply to that level. Accept the default selection and click Next.

On the Slowly Changing Dimension page, leave the default slowly changing dimension option selected. Click Next.

Note: SCD Type 2 and 3 are available with the Enterprise ETL option of OWB 11g. With Core ETL features, SCD Type 1, that is, Do not keep history option, is only available.

If you select any other policy, Warehouse Builder defaults the implementation to the lowest level of the hierarchy and creates the date attributes for you.

Click Next on the Pre Create Settings Page. The Dimension Creation Progress page appears. On successful completion, click Next. Click Finish on the Summary page.

Observe that REL_CATEGORY dimension has been added to the EXPENSE_WH target module. With the dimension, Warehouse Builder also creates a table that implements the dimension in a star schema and also creates a sequence to load the ID column of the dimension.

In the Project Explorer panel, expand OWB_DEMO > Databases > Oracle. Expand SALES_WH, and then expand Cubes. Right-click SALES and select Open Editor.

Note: Alternatively, you can also double-click SALES.

The Data Object Editor launches.

In the Dimensional panel in the middle, scroll up or down to understand the design diagram. You will observe that the SALES cube is linked to five dimensions: PRODUCTS, CHANNELS, CUSTOMERS, PROMOTIONS, and TIMES.

To examine the dimensionality of the SALES cube, click the Dimensions tab in the Cube Details panel of the Data Object Editor. Observe that the SALES cube is linked to levels within the five dimensions.

For example, SALES cube refers to PRODUCT level of the PRODUCTS dimension. The Role column displays in a drop-down list, the dimension roles (if defined previously) that the selected dimension contains.

To examine the measures of the SALES cube, click the Measures tab in the Cube Details panel. Note that there are three measures and all of them are numeric. These measures can be aggregated to determine the total sales cost, amount, or quantity across a period of time or for a particular customer or for a particular product.

Click the Aggregation tab to define the aggregations that must be performed for each dimension that the cube references.

You select the aggregate function that is used to aggregate data. You can also precompute measures along each dimension that the cube references. The default aggregate function is SUM.

You specify the following:

	Cube Aggregation Method: Select the aggregate function used to aggregate the cube data. The default selection is SUM.
	Summary Refresh Method: Select the data refresh method. The options you can select are On Demand and On Commit.
	Summary Strategy for Cube: Define the levels along which data should be precomputed for each dimension.

You can implement a cube in a relational form or a multidimensional form in the database. In relational implementation, a relational table stores the cube data. When a cube is implemented in a multidimensional environment, the cube data is stored in an analytic workspace.

If you want to modify the physical implementation of the cube, click the Storage tab in the Cube Details panel. You can select between relational or multidimensional implementation.

For relational implementation of the cube, you can opt to create bitmap indexes. Having these indexes on the cube improves your performance at query time.

As for dimensions, you can view the cube data in the Data Viewer. From the Diagram menu, select Close Window to exit the Data Object Editor.

You were previously examining the SALES_WH module. Now, switch to the EXPENSE_WH module, right-click Cubes, and select New > Using Wizard.

The Create Cube Wizard is launched. Click Next on the Welcome Page.

On the Name and Description page, enter REL_EXPENSE as the name of the cube. Click Next.

On the Storage Type page, select ROLAP: Relational Storage to specify relational implementation of the cube. Click Next.

On the Dimensions page, you select the dimensions that the cube will refer to. In the Available Dimensions list, expand Dimensions, and then expand EXPENSE_WH. Select EXPENSE_WH, and then click > to move the two dimensions REL_CATEGORY and REL_EXPENSE to Selected Dimensions list. Click Next.

On the Measures page, enter EXPENSE in the Name field. Accept the default data type. Click Next.

On the Summary page, review the cube details and click Finish.

Observe that REL_EXPENSE cube is added under the Cubes node of the EXPENSE_WH module. To view the cube in the Data Object editor, right-click REL_EXPENSE and select Open Editor or double-click REL_EXPENSE. Examine the cube, and then close the Data Object Editor.

From the Design menu, select Save All to commit your work.

In this lesson, you created two external tables mapped to the two previously sampled source flat files, two dimensions, REL_TIME and REL_CATEGORY, and a cube REL_EXPENSE linked to the two dimensions.

Create a target user schema and module

Create external tables pointing to sampled flat files

Create dimensions including TIME dimension using the wizard