9 Importing Oracle Warehouse Builder Data into Business Intelligence Applications

Naming the Oracle Discoverer Module

In the Name and Description page, enter a name and an optional description for the Oracle Discoverer module. Also, indicate the type of module you are creating.

For more information about naming conventions, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Setting the Connection Information

On the Connection Information page, define the location to deploy the Oracle Discoverer module. For example, this may be the system where you are currently running Oracle BI Discoverer.

To use a deployment location you previously created, you can select it from the Location list. The connection information for this location displays on the wizard page.

You can also choose to create this location later and skip to the next page. You cannot deploy the Oracle Discoverer module unless you provide the connection information for the target location.

The wizard initially creates a default target location for the module you are creating. For example, if your module is named DISCOVERER_OBJECTS, then the location is called DISCOVERER_OBJECTS_LOCATION1. You can choose to provide the connection information for this location by clicking Edit. The Edit Oracle Discoverer Location dialog box is displayed. Provide the required information to connect with your target system and click OK. For more information about the Edit Oracle Discoverer Location dialog box, see "Defining Discoverer Locations".

Reviewing the Summary Information

In the Summary page, review the name and location information for the Oracle Discoverer module. Click Back to make any changes or click Finish to finish creating the Oracle Discoverer module.

After the Oracle Discoverer module is created, you can locate it under the Oracle Discoverer node on the Projects Navigator. Expand the module to see that Oracle Warehouse Builder provides a representation for the different objects types that comprise the Discoverer EUL. You can define the following types of Discoverer EUL objects:

• Item Folders

• Business Areas

• Drill Paths

• Lists of Values

• Alternative Sort Orders

• Drills to Detail

• Registered Functions

Name Editor

The Name editor enables you to edit the name and description for the Item Folder.

Source Items Editor

The Source Items editor displays the available source items for your Item Folder.

When you are editing an existing item folder, the Selected column displays the source items that were selected at the time of creating the Item Folder. To select different items as the source, use the left arrow to return the items from the Selected column to the Available column. Then use the right arrow to move the new source item from the Available column to the Selected column.

Your selected objects can contain related items from multiple Item Folders.

To change the selected items, then use the left arrow to return the previously selected items. Now select an initial folder item from any of the available Item Folders within the same Oracle Discoverer module. You can then select additional folder items that have a relationship with the previously selected item. You cannot select items from unrelated Item Folders. The relationship between Item Folders are defined by the joins between them. To create a join between Item Folders, use the Joins editor to specify the relationships between the two Items Folders.

Items Editor

The Items editor displays the details and properties of all items in an Item Folder. You can view, create, and edit the following for an item:

Joins Editor

Joins enable you to associate data between two Item Folders. During data analysis, you may require information that resides in multiple folders. Joins enable users to perform business analysis and run reports across multiple Item Folders. After you create joins between Item Folders and deploy them to your Discoverer EUL, they are available for analysis in Discoverer Plus and Discoverer Viewer.

The Joins editor displays the relationships or joins between two Item Folders. You can define new joins by clicking on a new row and providing the required information. You can delete a join by right-clicking the box at the left of each join row that specifies the join's number and selecting Delete.

Figure 9-1 shows the contents of the Joins editor.

Figure 9-1 Creating and Editing Joins

Description of "Figure 9-1 Creating and Editing Joins"

On the Joins page, click a row in the Join Name field. Provide the following information:

• Join Name: Enter a name for the join you are creating.

• Master Item Folder: Select the Item Folder that is the Master. In the above example, DEPARTMENTS is the local item folder and COUNTRIES is the master item folder. A join is created between these two item folders.

• Detail always has Master: Select this to indicate if your detail Item Folder always have this master.

• One to one: Select this to indicate a one-to-one relationship between the two Item Folders.

• Outer join: Indicate from the list if there is an outer join in this relationship and its type.

• Description: Optionally describe the join.

For each join, you can specify the Join Components by clicking in the field below and providing the following information:

• Local Item: This list is populated with the items contained in the current Item Folder. Select an item from this list.

• Operator: Select the relationship between the Local Item you selected and the Remote Item you select from the Master Item Folder.

• Remote Item: Select an Item from your Master Item folder to join with the Local Item from your current Item Folder.

If you select a Join name, the property inspector displays the following values for the join:

• Business Name: Business name of the Join.

• Description: Description of the Join.

• External Foreign Key: Specifies the external foreign key for the join.

• Physical Name: Provide a physical name that is different from the default.

Also see "Adding Joins Using the Graphical Navigator" for an alternate way to add joins.

Conditions Editor

The Conditions editor enables you to define or edit a condition that restricts selection on the chosen Item Folder. Use this editor to provide or edit a condition. This editor contains the following:

• Condition Name: The name of the condition.

• Condition: Click the Ellipsis button in this field to display the Expression Builder. Use this to create or edit a condition. For more information about the Expression Builder, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

• Description: Optionally describe the condition.

• Mandatory: Select this to specify if the condition is mandatory. A mandatory condition is always applied to filter data that is being retrieved for this item folder. Non-mandatory conditions can be switched on and off by the user.

• Case Sensitive: Specifies whether the case should match for character data types.

Composition Editor

The Composition editor enables you to view the components of an Item Folder. It displays the objects that contribute to the composition of this item folder. These are database objects for Simple Item Folders and contributing Item Folders for Complex Item Folders.

General Properties of an Item Folder

Whenever an Item Folder is selected, the property inspector displays a list of properties for that Item Folder. These properties include Business Name, Description, External Object (Read Only), Folder Type (Read Only), and Visible.

Using the Create Item Folder Wizard

To create an Item Folder using the Create Item Folder Wizard:

1. Expand the Oracle Discoverer module in which you want to create an Item Folder.

2. Right-click Item Folders and select New Item Folder.

   Oracle Warehouse Builder opens the Create Item Folder Wizard.

3. Follow the wizard steps by clicking Next.

Naming and Describing the Item Folder

In the Name and Description page, enter a name and optional description for the Item Folder.

Oracle Warehouse Builder distinguishes Simple Item Folders from Complex Item Folders in the same way as Discoverer. A Simple Item Folder is directly based on columns from a single table in an Oracle module and calculated items based on constants or items from that Item Folder. A Complex Item Folder can contain items from multiple Item Folders within the same Oracle Discoverer module, and calculated items.

Selecting Source Items

Select items for your Item Folder.

For a Simple Item Folder, you can select exactly one table, view, or external table from any module or user folder in the Oracle module, to be referenced by the Item Folder. Expand the selected object and proceed to select columns within the selected object, to your selected items. You can multi-select these referenced items by pressing the Ctrl key and using the right arrow to move them to the list of selected Items.

A Complex Item Folder can contain items from multiple Item Folders within the same Oracle Discoverer module. You can select the initial folder items from Item Folder A within an Oracle Discoverer module. You can then select additional folder items from another Item Folder B within the same module. However, the two Item Folders A and B must be related. You cannot select items from unrelated Item Folders. Thus, complex Item Folders combine multiple Item Folders that must be joined. You can define the joins using the Graphical Editor for an Item Folder. For more information about creating joins, see "Joins Editor".

Selecting the Join

For a Complex Item Folder, if there are multiple join paths between the item folders selected as the item sources, then the Join Selection page is displayed. The list on this page displays all the joins between the item folders. Select the join to be used for the Complex Item Folder being created.

Reviewing the Summary

In the Summary page, review the name and type of your Item Folder and items to be included in your Item Folder. Click Back to make any changes or click Finish to create the Item Folder.

You can locate the Item Folder on the Projects Navigator under the Item Folders node in the Oracle Discoverer module. This Item Folder contains all the selected items. You can edit the Item Folder properties, create joins and conditions, and edit item properties using the Graphical Editor, as described in "Editing an Item Folder".

After creating the Item Folder, you can use the following editors from the graphical editor:

• "Name Editor"

• "Source Items Editor"

• "Items Editor"

• "Joins Editor"

• "Conditions Editor"

• "Composition Editor"

For more information about how you specify the details on each editor, refer to the description of these editors in the "Editing an Item Folder" section.

Using the Graphical Navigator to Create Item Folders

An alternate way of creating item folders is to use the Graphical Navigator. You can use the editor menu or the editor canvas of the Graphical Navigator to create an item folder.

Adding Joins Using the Graphical Navigator

An alternate way to create a Join is to use the Graphical Navigator.

To add a Join:

1. Ensure that the item folder is available on the Graphical Navigator.

2. Right-click Joins, and select Add a Join.

   The Add Join dialog box is displayed.

3. Specify a name for the join, and click OK.

4. The Joins editor completes the definition of the Join or use the graphical navigator for this.

Similarly, you can also drag an Item from the Items node to the Joins node to create a local item. You can also drag an Item to the Joins node of another Item folder to create a remote item.

Synchronizing Item Folders

Simple Item Folders are defined based on existing tables, views, or external tables. When the definition of the underlying object changes, you can update the Item Folder definition by synchronizing it with the object on which it is based.

To synchronize an Item Folder:

1. Expand the Item Folders node in the Projects Navigator.

2. Right-click the Item Folder and select Open.

   The editors for the Item Folder are displayed.

3. Click the Composition editor to view the Item Folder.

4. On the canvas, right-click the Item Folder and select Synchronize.

   The Synchronize Item Folder dialog box is displayed.

5. Review the details displayed on this dialog box and click OK.

   Oracle Warehouse Builder synchronizes the item folder with the data object on which the item is based.

Synchronize Item Folder Dialog Box

The Synchronize Item Folder dialog box enables you to update the Item Folder with any changes made to the data types used in the database object on which the Item Folder is based. This dialog box displays the details of the changes to be made to the Item Folder.

The Synchronize Item Folder dialog box contains three columns: Object, Reason, and Action. The Object column lists the component in the underlying database object that has changed. The Reason column displays a brief description of the reason for the synchronization. The Action column displays the action that is taken to synchronize the Item Folder. The available actions are Update and None. If you select None for a component, no synchronization is performed for that object. Only definitions that have an Action set to Update are synchronized.

For example, the Item Folder DEPT_ITMF is derived from the DEPT table. After the Item Folder is created, you modify the DEPT table and change the data type of the column LOCATION from VARCHAR2 to NUMBER. When you synchronize the Item Folder DEPT_ITMF, the Synchronize Item Folder dialog box displays LOCATION in the Object column. The Reason column displays "Datatype mismatch". The Action column displays Update.

Click OK to perform the actions listed on the Synchronize Item Folder dialog box and update the Item Folder definition. If you do not want to perform the actions listed on this dialog box, click Cancel.

Using the Create Business Area Wizard

To create a Business Area using the Create Business Area Wizard:

1. Expand an Oracle Discoverer module.

2. Right-click Business Areas and select New Business Area.

   Oracle Warehouse Builder opens the Create Business Area Wizard.

3. Follow the wizard steps by clicking Next.

Using the Graphical Navigator

Alternatively, from the Graphical Navigator you can use the main menu or the canvas to create a business area.

Name Editor

The Name editor enables you to edit the name and description of a Business Area.

Source Editor

The source editor displays the source of those Item Folders that have been included in the Business Area. You can add new item folders to the Business Area or remove existing ones using this editor.

Contents Editor

The contents editor enables you to add item folders to a Business Area. Right-click anywhere on the editor and select Add, Item Folder. The Add a New or Existing Item Folder dialog box is displayed. Follow the steps listed in "Steps to Create an Item Folder". You can also add an item folder by dragging and dropping the item folder from the Projects Navigator into the Contents editor. This creates a shortcut to the item folder. You can double-click the item folder to access it directly from the Contents editor.

Naming the Drill Path

In the Name and Description page, enter a name and optional description for the Drill Path.

Specifying Drill Levels

Use the Drill Levels page to define a drill level and specify the Item Folder it references. Optionally, you can provide a description for the Drill Levels. To define drill levels, click a row and provide the following information:

• Drill Level: Enter a name for the drill level.

• Item Folder: From the field, select the Item Folder it references.

• Description: Provide an optional description for the drill level.

When you select a referencing Item Folder for the Drill Level, the wizard lists the available Items within that Item Folder under the Drill Level Items field at the bottom.

In this field, you can specify one or more items to act as drill items. Select the Use as Drill Item option for each Item you want to include as a drill item in the level.

Figure 9-2 displays the Drill Levels page of the Create Drill Path Wizard.

Figure 9-2 Creating Drill Levels

Description of "Figure 9-2 Creating Drill Levels"

Specifying the Join

If there are multiple join paths between the Item Folders referenced by the drill levels, then the Join Selection page is displayed. The list displays the existing joins between the selected Item Folder. Select the join to use for the drill path.

Reviewing the Summary

In the summary page, review the drill levels you are creating. Click Back to make any changes or click Finish to create the drill path.

You can locate the drill path on the Projects Navigator under your Oracle Discoverer module. Oracle Warehouse Builder enables you to edit a drill path using the Edit Drill Path dialog box.

Editing the Drill Path Name

The Name editor enables you to edit the name and the description of the drill path.

Reviewing the Drill Levels in the Drill Path

Use the Levels editor to edit the drill levels that you defined. The Drill Levels section lists the drill levels along with the item folders that they reference. The Item Folder column displays the item folder that a drill path references. You can modify this by selecting the new item folder from the list.

The Drill Level Items section displays the items that act as drill items. You can modify this list by selecting more items that act as drill items.

You use the Structure panel to manipulate hierarchies. For example drill levels can be moved up in the same subtree, moved out of the current tree, or moved to the root level. However, multiple roots for Discoverer modules are not enabled and subsequently fail validation. You can also remove a level. In this case, sub levels of deleted levels move up to the root level.

Naming the List of Values

In the Name and Description page, enter a name and optional description for this list of values. Select the Set as Drill to Detail box if you also want to set this as a Drill to Detail. When you deploy these definitions to Discoverer, an Item Class that you can use both as a List of Values and as a Drill to Detail is created.

Defining Items in a List of Values

The Defining Items page enables you to select the item that generates your LOV in Discoverer. This page displays all the Items available in your Discoverer module. Expand the nodes to select an item and click Next.

Referencing Items in a List of Values

The Referencing Item page enables you to associate your LOV with different items. The Available Items column displays all the Items available in your Discoverer module. Expand the nodes to select the items that references your list of values. Use the right arrow to move your selections to the Selected Items column and click Next.

Reviewing the Summary

In the summary page, review the defining and referencing items selected for the LOV. Click Back to make any changes or click Finish to finish creating the LOV.

You can locate the LOV on the Projects Navigator in the Oracle Discoverer module under the Lists of Values node. Oracle Warehouse Builder enables you to edit the name, description, and defining and referencing items associated with an LOV using the Edit List of Values dialog box.

Editing the Name

Use the Name editor to edit the name and description of the list of values.

Editing the Defining Items

Use the Defining Item editor to edit the item that generates the list of values in Discoverer. The item that is the defining item is highlighted. To edit this and specify that another item is necessary to generate the LOV, select the new item.

Editing the Referencing Items

Use the Referencing Items editor to edit the items that reference the list of values. The Selected column lists the items that the list of values references. To add more items to which the list of values references, select the item in the Available column and use the right arrow to move it to the Selected column. To remove items that the list of values currently references, select the item from the Selected column and use the left arrow to move it to the Available column.

Editing the Options

Use the Advanced editor to specify advanced options for the list of values. The advanced options are as follows:

• Retrieve Values in groups of: Use this option to specify the number of values that are retrieved in group. The default value is 100 which means that the values are retrieved in groups of 100.

• Sort the values and remove duplicates: Select this option to remove duplicate values from the list of values and to order the values. This ensures that the LOV always shows unique, ordered values.

• Show values in "Select Items" page of Worksheet Wizard: Select this option to enable users to expand the List of Values when selecting items to include in a query.

• Require user to always search for values: Select this option to display the Search dialog box every time the List of Values is expanded.

• Cache List of Values during each connection: Select this option to store the list of values when the List of Values is expanded for the first time. This improves performance because otherwise, every time the List of Values is expanded, the values are fetched from the database.

Naming the Alternative Sort Order

In the Name and Description page, enter a name and optional description for the alternative sort order.

Select the Set as Drill to Detail box if you also want to set this as a Drill to Detail. When you deploy these definitions to Discoverer, an Item Class that can be used both as an Alternative Sort Order and as a Drill to Detail is created.

Defining Item for the Alternative Sort Order

The Defining Item page enables you to select the Item that contains the values to be sorted. Expand the nodes to select an item and click Next.

Defining Order Item for the Alternative Sort Order

Use the Defining Order Item page to select an Item, in the same Item Folder, that defines the order in which the values of the Item you selected on the Defining Item page are displayed. Expand the nodes to select the item and click Next.

Referencing Items for the Alternative Sort Order

The Referencing Items page enables you to associate your Alternative Sort Order with different items. The Available column lists all the Items in the Discoverer module. Expand the nodes to select the items that references your Alternative Sort Order. Use the right arrow to move your selections to the Selected column and click Next.

Referencing Selection Panel for the Alternative Sort Order

This panel enables you to shuttle across an item that references an item class. You can either change the reference or decide not to shuttle the item across.

Reviewing the Summary

In the summary page, review the alternative sort order definition. Click Back if to make any changes or click Finish to finish creating the alternative sort order.

You can locate the alternative sort order on the Projects Navigator in the Oracle Discoverer module under the Alternative Sort Order node. Oracle Warehouse Builder enables you to edit the name, description, and the defining and referencing items associated with an alternative sort order using the Edit dialog box.

Editing the Alternative Sort Order Name

Use the Name tab to edit the name and description of the alternative sort order.

Editing the Defining Item

Use the Defining Item tab to edit the item that contains the values to be sorted. This editor displays the Item that currently defines the alternative sort order highlighted. To change this selection, click the item that you now want to use to define the alterative sort order.

Editing the Defining Order Item

The Defining Order Item tab displays the Item Folder with the item that currently defines the order in which the values of the Item selected on the Defining Item editor are displayed. You can change this selection by clicking a new item from the tree.

Editing the Referencing Items

The Referencing Items tab lists the items that references your Alternative Sort Order in the Selected column. To add more items to this list, select the item in the Available column and use the right arrow to move the item to the Selected column. To remove an item that is selected, move the item from the Selected column to the Available column using the left arrow.

Editing the Options

Use the Options tab to specify advanced options for the alternative sort order. The options you can set are as follows:

• Retrieve values in groups of: Use this option to specify the number of values that are retrieved in group. The default value is 100 which means that the values are retrieved in groups of 100.

• Sort the values and remove duplicates: Select this option to remove duplicate values from the alternative sort order and to order the values. This ensures that the alternative sort order always shows unique, ordered values.

• Show values in "Select Items" page of the Worksheet Wizard: Select this option to enable users to expand the alternative sort order when selecting items to include in a query.

• Require user to always search for values: Select this option to display the Search dialog box every time the Alternative Sort Order is expanded.

• Cache list of values during each connection: Select this option to store the Alternative Sort Order when it is expanded for the first time. This improves performance because otherwise, every time the Alternative Sort Order is expanded, the values are fetched from the database.

Create Drill to Detail Dialog Box

Name: Enter a name for the drill to detail definition.

Description: Provide an optional description for the drill to detail.

The Available column at the bottom of the dialog box lists the Item Folders in the Oracle Discoverer module. Select a referencing item from this set and use the right arrow to move it to the Selected column.

Naming the Registered Function

In the Name and Description page, enter a name and optional description for the registered function.

From the Select the return type of the function list, select a return type for the function. Select Available to User to indicate if a Discoverer end-user can use this registered function in calculations.

Specifying the Function Parameters

Specify the function parameters by clicking on a row and entering a name for the parameter. From the Data Type list, select the data type for the parameter. Use the Description field to enter an optional description.

Reviewing the Summary

In the Summary page, review the registered function definition. Click Back to make any changes or click Finish to finish creating the registered function.

You can locate the registered function on the Projects Navigator in the Oracle Discoverer module under the Registered Functions node. Oracle Warehouse Builder enables you to edit the name, description, and parameters of the registered function using the Edit dialog box.

Renaming a Registered Function

Use the Name tab to edit the name and the description of the registered function.

Modifying the Parameters of a Registered Function

Use the Parameters tab to edit the parameters of the registered function. You can edit the name, type, and description of a parameter. Add new parameters by clicking on an empty row and specifying the name of the parameter and its data type. You can move the parameters using the arrow keys.To delete a parameter, right-click the gray cell (which displays the number) to the left of the parameter name and select Delete.

Defining Oracle Business Intelligence Location

The OBI module location refers to a UDML file on the OBIEE server. At the time of specifying the location, specify a UDML file in the OBIEE server as the location. You must also specify the mode of file transfer from the local system to the OBIEE server. Provide the following details in the Edit Oracle BI Location dialog box:

• Name: Provide a location name.

• Description: Provide an optional description.

• Transport Type: Select from FILE, FTP, HTTP, and HTTPS.

• Version: Select the OBIEE version.

Depending on the mode of file transfer, you must provide the following details:

FILE

• Root Path: Directory of the UDML file.

• File Name: The name of the UDML file.

FTP

• Host Name: The system credentials where the OBIEE server resides.

• Host Login Port: Login port number, which is initially set to 0. You must change this according to your local configuration.

• Transfer Format: Select from ASCII and IMAGE.

• Host Login User: User name to run FTP.

• Host Login Password: User password to run the FTP command.

• File Name: The name of the UDML file along with the complete path.

HTTP and HTTPS

• Host Name: The system credentials where the OBIEE server resides.

• Host Login Port: Login port number, which is initially set to 0. You must change this according to your local configuration.

• Host Login User: User name for the HTTP/HTTPS command.

• Host Login Password: User password for the HTTP/HTTPS command.

• File Name: The name of the UDML file along with the complete path.

Name Editor

The Name editor enables you to edit the name and description for the Logical Table.

Source Items Editor

The Source Items editor displays the available source items for the Logical Table.

The Available column displays database tables in the current project and logical tables in the OBI module.

When you are editing an existing logical table, the Selected column displays the source items that were selected at the time of creating the logical table. To select different items as the source, use the left arrow to return the items from the Selected column to the Available column. Then use the right arrow to move the new source item from the Available column to the Selected column.

Your selected objects can contain items from multiple Logical Tables.

To change the selected items, then use the left arrow to return the previously selected items. Now select an initial folder item from any of the available Logical Tables within the same OBI module. You can then select additional folder items with the previously selected item.

Items Editor

Items Editor displays the details and properties of all Items in a Logical Table. You can view, create, and edit the following for an Item:

Joins Editor

Joins enable you to associate data between two logical tables. During data analysis, you must retrieve information that resides in multiple logical tables. Joins enable end users to perform business analysis and run reports across multiple logical tables. After you create joins between logical tables and deploy them to your OBIEE Repository, they are available for analysis in OBIEE Answers or Dashboards.

The Joins editor displays the relationships or joins between two logical tables. You can define new joins by clicking on a new row and providing the required information. You can delete a join by right-clicking the box on the left of each join row and selecting Delete.

Figure 9-4 shows the contents of the Joins editor for the logical table EMP.

Figure 9-4 Creating and Editing Joins

Description of "Figure 9-4 Creating and Editing Joins"

On the Joins page, click a row in the Joins field. Provide the following information:

• Join Name: Enter a name for the join you are creating.

• Master Logical Table: Select the Logical Table that is the Master. In the above example, you select the logical table DEPARTMENTS as your master. This implies that you select an item from the DEPARTMENT Logical Table to join with the two items you selected from the Logical Table EMP.

• Detail always has Master: Select this box to indicate if your detail Logical Table always have this master.

• One to one: Select this box to indicate a one-to-one relationship between the two Logical Tables.

• Outer join: Indicate from the list whether there is an outer join in this relationship and its type.

• Description: Optionally describe the join.

For each join, you can specify the Join Components by clicking in the field below and providing the following information:

• Local Item: This list is populated with the Items contained in the current Item Folder. Select an Item from this list.

• Operator: Select the relationship between the Local Item you selected and the Remote Item you select from the Master Logical Table.

• Remote Item: Select an Item from your Master Logical Table to join with the Local Item from your local Logical Table.

Also see "Adding Joins Using the Graphical Navigator" for an alternate method to add joins.

Composition Editor

The Composition editor enables you to view the components of a Logical Table. It displays the Logical Table and the objects from which it is referencing.

Using the Create Logical Table Wizard

To create a Logical Table using the Create Logical Table Wizard:

1. Expand the OBI module in which you want to create a logical table.

2. Right-click Logical Tables and select New Logical Table.

   Oracle Warehouse Builder opens the Create Logical Table Wizard.

3. Follow the wizard steps by clicking Next.

Naming and Describing the Logical Table

In the Name and Description page, enter a name and optional description for the Logical Table.

Selecting Source Items

You can select from tables, views, or materialized views from any of the database modules, to be referenced by the Logical Table. If you select multiple tables, then these must be joined by a foreign key. Expand the selected object and proceed to select columns within the selected object, to your selected items. You can multi-select these referenced items by pressing the Ctrl key and using the right arrow to move them to the list of selected Items.

You can also select from other Logical tables within the same OBI module. You can select the initial items from Logical Table A within an OBI module. You can then select additional folder items from another Item Folder B within the same module.

Selecting the Foreign Key

If there are multiple foreign keys between the tables selected as the item sources, the Foreign Key Selection page is displayed. The list on this page displays all the joins between the tables. Select the join to be used for the table being created.

Reviewing the Summary

In the Summary page, review the details you have provided for the Logical Table including the items to be included in the table. Click Back to make any changes or click Finish to create the Logical Table.

You can locate the Logical Table on the Projects Navigator under the Logical Tables node in your OBI module. This Logical Table contains all the selected items. You can edit the Logical Table properties, create joins, and edit item properties. The Graphical Navigator can also be used to edit a Logical Table.

After creating the Logical Table, you can use the following editors:

• "Name Editor"

• "Source Items Editor"

• "Items Editor"

• "Joins Editor"

• "Composition Editor"

For more information about how you specify the details on each editor, refer to the description of these editors in the "Editing a Logical Table".

Using the Graphical Navigator to Create Logical Tables

An alternate way of creating logical tables is to use the Graphical Navigator. You can use the editor menu or the editor canvas of the Graphical Navigator to create a logical table.

Adding Joins Using the Graphical Navigator

An alternate way to create a Join is to use the Graphical Navigator. To add a Join:

1. Ensure that the logical table is available on the Graphical Navigator.

2. Right-click Joins, and select Add a Join.

   The Add Join dialog box is displayed.

3. Specify a name for the join, and click OK.

4. Open the Joins editor for the logical table and specify the conditions for the join. See "Joins Editor" for more details.

Using the Graphical Navigator, you can also create a join between items in two Logical Tables. Ensure that the Logical Tables are visible on the Graphical Navigator. To create a join between two Items, drag an Item from one Logical Table to the required Item in the other Logical Table. Similarly, you can also drag an Item from the Items node to the Joins node to create a local item. You can also drag an Item to the Joins node of another Item folder to create a remote item.

Synchronizing Logical Tables

Logical Tables are defined based on existing tables, views, or materialized views. When the definition of the underlying object changes, you can update the Logical Table definition by synchronizing it with the object on which it is based.

To synchronize a Logical Table:

1. Expand the Logical Tables node in the Projects Navigator.

2. Right-click the Logical Table and select Open.

   The editors for the Logical Table are displayed.

3. Click the Composition editor to view the Logical Table.

4. On the canvas, right-click the Logical Table and select Synchronize.

   The Synchronize Item Folder dialog box is displayed.

5. Review the details displayed on this dialog box and click OK.

   Oracle Warehouse Builder synchronizes the Logical Table with the data object on which the item is based.

Using the Synchronize Logical Table Dialog Box

The Synchronize Logical Table dialog box enables you to update the Logical Table with any changes made to the data types used in the database object on which the Logical Table is based. This dialog box displays the details of the changes to be made to the Logical Table.

The Synchronize Logical Table dialog box contains three columns: Object, Reason, and Action. The Object column lists the component in the underlying database object that has changed. The Reason column displays a brief description of the reason for the synchronization. The Action column displays the action that is taken to synchronize the Logical Table. The available actions are Update and None. If you select None for a component, no synchronization is performed for that object. Only definitions that have an Action set to Update are synchronized.

For example, the Logical Table DEPT_ITMF is derived from the DEPT table. After the Logical Table is created, you modify the DEPT table and change the data type of the column LOCATION from VARCHAR2 to NUMBER. When you synchronize the Logical Table DEPT_ITMF, the Synchronize Logical Table dialog box displays LOCATION in the Object column. The Reason column displays "Datatype mismatch". The Action column displays Update.

Click OK to perform the actions listed on the Synchronize Logical Table dialog box and update the Logical Table definition. If you do not want to perform the actions listed on this dialog box, click Cancel.

Naming the Dimension Drill Path

In the Name and Description page, enter a name and optional description for the Dimension Drill Path.

Specifying Drill Levels

Use the Drill Levels page to define a drill level and specify the Logical Table it references. Optionally, you can provide a description for the Drill Levels. To define drill levels, click a row and provide the following information:

• Drill Level: Enter a name for the drill level.

• Logical Table: From the field, select the Logical Table it references.

• Description: Provide an optional description for the drill level.

When you select a referencing Logical Table for the Drill Level, the wizard lists the available Items within that Logical Table under the Drill Level Items field at the bottom.

In this field, you can specify one or more items to act as drill items. Select the Use as Drill Item option for each Item you want to include as a drill item in the level. An Item can be a drill item in a single level only.

Figure 9-2 displays the Drill Levels page of the Create Dimension Drill Path Wizard.

Figure 9-5 Creating Drill Levels

Description of "Figure 9-5 Creating Drill Levels"

Specifying the Drill Level Key

In the Drill Level Keys section, select a Drill Level and specify a name for Drill Level Key and optionally provide a description for the key. In the Drill Level Key Items, select the Item that must used as the key.

Reviewing the Summary

In the summary page, review the drill levels you are creating. Click Back to make any changes or click Finish to create the dimension drill path.

You can locate the dimension drill path on the Projects Navigator under your OBI module. Oracle Warehouse Builder enables you to edit a dimension drill path using the Edit Dimension Drill Path dialog box.

Properties of a Dimensional Drill Path

For dimension drill paths, you can configure the following property from the Property Inspector:

Time Dimension: Select this option if it represents a time dimension.

Editing the Dimension Drill Path Name

The Name editor enables you to edit the name and the description of the dimension drill path.

Reviewing the Drill Levels in the Dimension Drill Path

Use the Drill Levels editor to edit the drill levels that you defined. The Drill Levels section lists the drill levels along with the logical tables that they reference. The Logical Table column displays the logical table that a dimension drill path references. You can modify this by selecting the new logical table from the list.

The Drill Level Items section displays the items that act as drill items. You can modify this list by selecting more items that act as drill items. You can also move a level up in same subtree, move a level out of the current tree, move a level to the root level, copy a level out of the current tree, remove a level, remove a level cascade, and remove a level instance, for example when there are multiple instances of the level in the network.

Use the Level Keys editor to select the Drill Level, and edit the Drill Level Keys and Drill Level Key Items.

Using the Create Catalog Folder Wizard

To create a Catalog Folder using the Create Catalog Folder Wizard:

1. Expand an OBI module.

2. Right-click Catalog Folders and select New Catalog Folder.

   Oracle Warehouse Builder opens the Create Catalog Folder Wizard.

3. Follow the wizard steps by clicking Next.

Naming the Catalog Folder

In the Name and Description page, enter a name and optional description for the Catalog Folder.

Selecting the Source

In the Source page, all the Logical Tables and Dimension Drill Paths available within the OBI module are displayed. You can multi-select the objects by pressing the Ctrl key and using the right arrow to move them to the list of Selected objects.

Reviewing the Summary

In the summary page, review the Logical Tables or the Dimension Drill Paths that you selected. Click Back to make any changes or click Finish to finish creating the Catalog Folder.

After the Catalog Folder is created, you can locate it on the Projects Navigator under the Catalog Folders node with references to the selected Logical Tables stored in it.

To make changes to your Catalog Folder definitions after you create them, use the Edit Catalog Folder dialog box. For details, see "Editing a Catalog Folder".

Using the Graphical Navigator

Alternatively, you can use the main menu or the canvas to create a catalog folder.

Name Editor

Name Editor enables you to edit the name and description of a Catalog Folder.

Source Editor

Source Editor displays the source of those Logical Tables and Dimension Drill Paths that have been included in the Catalog Folder. You can add new Logical Tables and Dimension Drill Paths to the Catalog Folder or remove existing ones using this editor.

Contents Editor

Contents Editor displays the contents of the Catalog Folder. You can add Logical Tables and Dimension Drill Paths using the Content editor. For example, to add a Logical Table, right-click anywhere on the editor and select Add, Logical Table. The Add a New or Existing Logical Table dialog box is displayed. Follow the steps listed in "Steps to Create a Logical Table". You can also add a Logical Table or a Dimension Drill Path by dragging and dropping it from the Projects Navigator into the Contents editor. This creates a shortcut to the object. You can double-click the object to access it directly from the Contents editor.

Setting Global Rules

You can specify the following parameters:

• Preserve user changes: Select to preserve any manual changes to the display properties name and description.

• Log level: Specify the level of detail you want to see in the message log by selecting one of the options from the list. You can choose to record only errors, warnings, information, or trace debug information.

• Validate before derive: Select the box to validate the selected objects before deriving them.

• Abort on error: Select the box to stop the derivation if it encounters an error.

• Capitalize: To capitalize the names of the derived objects, select from the list based on your requirements.

• Replace underscores with spaces: Select the box to replace the underscores in the names with spaces after derivation.

You can specify the following rule for Relational objects:

• Bound Table Suffix: Specify a suffix for the bound tables you want to derive.

• Default Aggregate (Oracle Discoverer only): Specify the default aggregate function to be applied to numeric measures.

• Remove Column name prefixes: Select the option to remove the text immediately before an underscore in the column name. The prefix is removed provided the same prefix is used for all columns.

• Sort items by name: Select this option to sort the items alphabetically.

You can specify the following rules for Dimensions:

• Always build Item Folders/Logical Tables for the dimension: Select this option to force the Perform Derivation Wizard to create Item Folders for the derived dimension definitions.

• Build Item Folders/Logical Tables for the levels: Select this option to force the Perform Derivation Wizard to create Item Folders for the derived dimension levels.

• Drill Paths on Item Folders/Logical Tables for the levels: Select this option if you want the Perform Derivation Wizard to create Drill Paths on Item Folders being created for each dimension level. This option applies only if item folders are created for each level.

• Prefix Items with Level Name: Select this option to prefix the item names with the dimension level names.

• Prefix separator: If you choose to prefix the item names with the dimension level names, then indicate a prefix separator. The default is an underscore.

• Sort Items by name: Select this option to sort the items alphabetically.

• Derive Dimension Roles: Select this option to Perform Derivation Wizard to derive additional item folders for each role.

You can specify the following rules for Cubes:

• Sort items by name: Select this option to sort the items alphabetically.