2
Getting Started on a Project

This chapter describes how to start using Warehouse Builder. It also introduces you to the Warehouse Builder Console and some basic Warehouse Builder tasks.

This chapter includes the following topics:

• Logging on to a Repository

• About the Warehouse Builder Console

• Working with Projects

• Creating Objects

• Editing Objects

• Ending a Warehouse Builder Session

Logging on to a Repository

The following instructions show you how to start the Warehouse Builder client.

To log on to a repository:

1. From the Start menu, select Oracle and then Warehouse Builder Client.

   The Warehouse Builder Logon dialog displays.

Figure 2-1 Warehouse Builder Logon

Text description of the illustration logon.gif

2. Click Connection Info and enter the following connection information to connect to an Oracle8i/9i database instance:
   • Host Name

   • Port Number

   • Oracle SID

3. Click OK.

4. Enter the name and password of the repository owner

5. Click Logon.

   Warehouse Builder displays the Project dialog.

Figure 2-2 Project Dialog

Text description of the illustration ppickeri.gif

This repository contains only the shell project My Project.

When a repository contains multiple projects, you can select a project from the drop-down list. This list includes all projects in the repository.

Note: If the Oracle8i/9i database instance is inactive, Warehouse Builder displays a connection error message.

6. Click OK.

   Warehouse Builder displays the console window in the Project view. This window remains open during the session.

About the Warehouse Builder Console

The Warehouse Builder console has the following components:

• Menu bar

• Console toolbar

• Wizards buttons

• Tree View buttons

• Navigation tree window

• Utilities buttons

• Recycle Bin/Clipboard buttons

Figure 2-3 shows the console in the Project view.

Figure 2-3 Warehouse Builder Console

Text description of the illustration consolei.gif

Console Toolbar

From the console toolbar, you can:

• Commit changes to the repository.

• Search the repository for an object.

• Look at the properties of an object.

• Open the Help system.

Figure 2-4 Console Toolbar

Text description of the illustration admtolb.gif

You can also perform these actions from the menus.

Navigation Tree Views

The Warehouse Builder console has the following views:

• Projects

• Transformations

• Administration

Figure 2-5 Warehouse Builder View Buttons

Text description of the illustration admmodic.gif

When the Warehouse Builder client starts, the console begins in the Project view and displays the navigation tree for the project selected when you logged on. To change views during a session, click one of the view buttons.

Project Tree

In the Project environment, you can:

• Create and modify definitions for repository objects such as dimensions, facts, relational tables, materialized views, sequences, and views.

• Create, modify, and import definitions for data sources, such as relational schema objects and flat files.

• Create and modify transformation operations.

• Create and modify mappings to direct the flow of data from sources to transformations to target schemas.

• Configure and validate definitions.

• Generate, deploy, and run scripts based on the definitions to create and load a target warehouse or staging area schema.

• Export metadata for a project, its modules, or object definitions.

Figure 2-6 Sample Project Tree

Text description of the illustration connavgc.gif

Transformation Tree

You can create transformation libraries and transformations within libraries when the console is in the Transformation environment. Figure 2-7 shows the navigation tree for the Transformation Library.

Figure 2-7 Transformation Tree

Text description of the illustration connavtr.gif

The tree provides access to the built-in Oracle Library as well as the transformation operations created by developers for your warehouse.

Administration Tree

In the Administrative environment, you can:

• Create, modify, and display the properties of a Project.

• Import metadata from a Warehouse Builder export file.

Figure 2-8 shows the Administration tree. The tree contains Projects and the Integrators supplied with Warehouse Builder. The Integrators can retrieve metadata from applications based on relational databases, flat files, and proprietary systems.

Figure 2-8 Administration Tree

Text description of the illustration connavad.gif

Utilities

Utilities are executables that are separate from Warehouse Builder. You can run a utility from the Warehouse Builder console using the Utilities icon.

To run a utility from the Warehouse Builder console:

1. Click the Utilities icon.

   Warehouse Builder expands the icon into the Utilities drawer.

Figure 2-9 Utilities Drawer

Text description of the illustration admutld.gif

2. Click the utility icon.

   Warehouse Builder opens the utility program.

The Utilities Drawer is configured with the following utilities:

• SQL*PLUS

• Runtime Assistant

• Oracle Enterprise Manager

• Oracle Workflow

You can configure these icons to refer to the utilities installed at your site.

The following instructions describe how to display utility properties, add a new utility, update the configuration of an existing utility, and delete an existing utility.

Displaying Utility Properties

The Preferences sheet contains utility properties.

To display the Preferences sheet:

1. From the Project menu, select Preferences.

   Warehouse Builder displays the Preferences dialog.

2. Click the Utilities tab.

   Warehouse Builder displays the Utility sheet.

Figure 2-10 Utilities Sheet

Text description of the illustration 02getst2.gif

Adding a Utility

To add a utility to the Utilities Drawer:

1. On the Utility sheet, type over existing information to enter the new utility information:
   • Name of the utility.

   • Location of the utility. Click Browse to search for program folders and files.

   • Location of the icon for the utility. Click Browse to search for icon folders and files.

   • Description of the utility.

2. Click Add.

3. Click OK.

Updating a Utility

To update the configuration of a utility:

1. On the Utilities sheet, select the utility name from the Contents list.

   Warehouse Builder displays the configuration information.

2. Modify the configuration details:
   • Name of the utility.

   • Location of the utility. Click Browse to search for program folders and files.

   • Location of the icon for the utility. Click Browse to search for icon folders and files.

   • Description of the utility.

3. Click Update.

4. Click OK.

Removing a Utility

To remove a utility from the Utilities Drawer:

1. On the Utilities sheet, select the utility name from the Contents list.

2. Click Remove.

3. Click OK.

   Warehouse Builder removes the utility from the Utility Drawer.

Recycle Bin and Clipboard

The Recycle Bin and Clipboard buttons are located in the lower left corner of the Warehouse Builder Console. The Recycle Bin stores deleted objects with the option of restoring them at a later time. The Clipboard stores one cut or copied object in Warehouse Builder that can be pasted at a later time.

Use the Clipboard/Recycle tab from the Preferences dialog to specify how to handle the contents of the Recycle Bin and Clipboard when you exit Warehouse Builder. For more information, see "Clipboard/Recycle Preferences".

Figure 2-11 Recycle Bin and Clipboard Icons

Text description of the illustration recycbin.gif

Recycle Bin

The Recycle Bin stores objects you delete from the navigation tree. Use the Recycle Bin dialog to restore deleted objects back to the navigation tree (repository), or to clear all the objects from the Recycle Bin.

Note: When you delete an object, you are prompted to specify whether to save the object in the Recycle Bin.

You can also restore deleted objects into a different repository as a way of moving objects across repositories. In order to do this, your Recycle Bin preferences must be set to persist the Recycle Bin contents across multiple sessions. When you open the new repository, the Recycle Bin contents are the same. Select an object and click Undo Delete. The object will be added to the current repository. An object that is restored using the Undo Delete function retains its unique object identifier (UOID) but loses its foreign key references.

Because the Warehouse Builder Recycle Bin is stored on the client machine, it cannot be shared by multiple users. You can set up how to handle the contents of your Recycle Bin across sessions by setting up your Recycle Bin Preferences. For more information, see "Clipboard/Recycle Preferences". The Recycle Bin is not affected by commit and rollback actions.

Table 2-1 lists the actions available for the Recycle Bin.

Table 2-1 Recycle Bin Functions

Function	Description
Delete	Deletes the selected object from the navigation tree and the Repository and stores it in the Recycle Bin until it is cleared. When you select this option, you are prompted to specify whether or not to save the object in the Recycle Bin.
Undo Delete	Restores a deleted object in the Recycle Bin in the navigation tree and the repository. If you have the Recycle Bin open, you can select a deleted object to be restored. If you use the edit menu option, only the last deleted object can be restored. To restore additional objects, open the Recycle Bin, select an object and use the Undo Delete button.
Clear	Empties the contents of the Recycle Bin. Once this has been done, you cannot restore any previously deleted objects if your changes have been committed.

Figure 2-12 Recycle Bin

Text description of the illustration recbinco.gif

Clipboard

Warehouse Builder enables you to use cut, copy, and paste to edit objects in your repository. When you select an object and choose Cut or Copy from the Edit menu, the object is stored in the clipboard. You can then choose to Paste that object within the same repository or in a different repository. When you click the clipboard icon, the clipboard contents display in the Clipboard dialog.

Figure 2-13 Clipboard Dialog

Text description of the illustration clipbrd.gif

You can either use the Edit menu to select these actions or use the quick keys listed in Table 2-2.

Table 2-2 Editing Quick Keys

Function	Quick Keys	Description
Cut	Ctrl+X	Removes the selected object from the navigation tree and stores it in the clipboard.
Copy	Ctrl+C	Copies the selected object from the navigation tree and stores it in the clipboard.
Paste	Ctrl+V	Creates a copy of the object in the clipboard and inserts it into the navigation tree.

Clipboard/Recycle Preferences

To set Clipboard/Recycle preferences:

1. From the Project menu, select Preferences.

2. Select the Clipboard/Recycle tab.

Figure 2-14 Clipboard/Recycle Sheet

Text description of the illustration prefclip.gif

3. Select the check boxes to determine how you want to handle persistence of the clipboard and recycle bin upon exiting Warehouse Builder.

   Clipboard persistence properties: the clipboard stores the object you last cut or copied from the Warehouse Builder tree. Because the clipboard is specific to your Warehouse Builder client, you can set a preference indicating whether you want the object stored in it for only one Warehouse Builder session or across all Warehouse Builder sessions. The default setting is to empty the clipboard each time you log out of a session.

   If you want to paste the cut or copied object into another repository, or during your next Warehouse Builder session, you must uncheck the Clear Clipboard on exit box.

   Recycle bin persistence properties: the recycle bin stores all the objects you delete from the Warehouse Builder tree. Because the recycle bin is specific to your Warehouse Builder client, you can set a preference indicating whether you want the objects stored in it for only one Warehouse Builder session or across all Warehouse Builder sessions. The default setting is to empty the recycle bin each time you log out of a session.

   If you choose to persist the deleted objects in the recycle bin, you can restore them to the repository during another session. The recycle bin is unaffected by Commit and Rollback actions. To preserve the objects in the recycle bin across sessions, you must uncheck the Empty Recycle Bin on Exit box.

Working with Projects

A Warehouse Builder Project is a repository structure that stores and organizes the definitions of a data warehouse. These definitions include data sources, target warehouse objects, mappings of source data to various targets, transformation operations, and configuration parameters.

About Warehouse Builder Projects

Warehouse Builder projects are divided into containers called modules. Modules contain objects. Figure 2-15 shows the structure of a Project.

Figure 2-15 Warehouse Builder Project Structure

Text description of the illustration 02getst3.gif

The navigation tree shows the modules within the project. A project contains the following types of modules, represented by different icons:

• Warehouse target modules

• Relational database source modules

• File source modules

Creating a Project

The initial Warehouse Builder repository contains the following:

• The Oracle Library of transformation operations

• A Global Shared Library

• A project shell named My Project

The project shell is required for the initial logon sequence and can be deleted after you create your own projects.

Note: You cannot delete the currently active project.

To create a new project:

1. Click the icon for the Administration view.

   Warehouse Builder displays the Administration tree.

2. Click the plus sign next to ADMINISTRATION.

   Warehouse Builder displays the Projects and Integrators branches in the tree.

3. Right-click Projects and select Create Project.

   Warehouse Builder displays the welcome page for the New Project wizard.

4. Click Next.

   Warehouse Builder displays the Name page.

5. Enter the Project name and a description and then click Finish.

   Warehouse Builder creates a project and inserts its name under PROJECTS.

Figure 2-16 Projects Branch in the Administration Tree

Text description of the illustration admnavne.gif

Module Contents

Source modules contain definitions of:

• Relational database objects

• Flat files

• Proprietary structures

Target warehouse modules contain definitions of:

• Dimensions

• Facts

• Tables

• Mappings

• Transformations

• Materialized Views

• Views

• Sequences

• Scripts generated by Warehouse Builder

About Multi-User Access

Multi-user access ensures that only one user has write privileges to an object while allowing read-only access on the object to other users. The lock is maintained for the duration of any transaction started by the write-privileged user. The lock is released when the user commits the changes or performs a rollback and closes all editors associated with the object.

Note: If you generate code at any time during your Warehouse Builder session, multi-user locking will remain in effect until you commit your changes.

You lock an object when you open the editor, property sheet, or dialog for the object. You acquire a folder-in-use lock when you access an object in a Warehouse Builder hierarchy. This lock prevents other users from deleting a higher object in the hierarchy. For example, if you are editing an object in a module, other users cannot delete the module.

Read/Write Mode

To enter read/write mode for an object and lock the object, you open an editor, property sheet, or dialog. By default, you access objects in read/write mode.

Editors, property sheets, and dialogs indicate in the title bar that the object is in read/write mode.

Figure 2-17 Read/Write Indicator in the Title Bar

Text description of the illustration readwrit.gif

To end the read/write mode for an object and unlock the object for others, do one of the following:

• Close the object editors and commit or rollback the changes.

• Exit Warehouse Builder.

Read-Only Mode

If you attempt to open an object that is locked by another user, Warehouse Builder displays a message that prompts you either to cancel the request or access the object in read-only mode.

If you choose to continue in read-only mode, the editor indicates in the title bar that the object is locked and shows the ID of the user locking the object. You can move objects around on the editor canvas or expand and collapse the object icons. You cannot edit the object in read-only mode. If you try to edit an object in read-only mode, an error message dialog displays. On a property sheet opened in read-only mode, the OK button is disabled.

Synchronization

Whenever a Warehouse Builder object is locked by a user, other users can view the changes by using synchronize.

Warehouse Builder includes the following synchronization:

• Automatic Synchronization: Warehouse Builder automatically synchronizes objects when you access them in read/write mode.

• Tree Synchronization: You can synchronize objects by selecting Synchronize from the View menu. This method displays changes to objects that do not show up with automatic synchronization.

Creating Objects

This section describes:

• Using the Warehouse Builder Wizards

• Object Names

Using the Warehouse Builder Wizards

Warehouse Builder provides wizards for creating objects. You open a wizard by right-clicking on an object type and selecting Create. Wizards contain pages. The first page is the welcome page. This page lists the number of pages in the wizard and describes the task performed on each page.

Figure 2-18 Wizard Welcome Page

Text description of the illustration wzwelpag.gif

You can configure wizards to skip the welcome pages by unchecking the Show this page the next time check box. To re-enable the welcome page, open the Preferences sheet from the Project menu and check the Display Welcome page on all wizards box.

To navigate through the pages of a wizard, click the Back and Next buttons. Click Cancel to exit without saving your work. You can move around a page using a mouse or using the Tab key. Use Control-Tab to move the cursor out of a long description text box.

For help on a specific topic in a wizard, click Help at the bottom of the wizard page.

Object Names

Warehouse Builder maintains a logical and a physical name for each object stored in the repository. A logical name is a descriptive business name for an object.

When you generate DDL scripts for a named object, the physical names are used. Physical names must conform to the syntax rules for basic elements as defined in the Oracle8i/9i SQL Reference.

Because project names are not used in SQL code generation, their names need not conform to the syntax rules for physical names. The length of a project name must not exceed 30 characters.

Names must be unique within their category:

• Module names must be unique within a project.

• Warehouse object names must be unique within a warehouse module. This includes the names of:
  • Dimensions

  • Facts

  • Mappings

  • Materialized views

  • Sequences

  • Views

• Transformation names must be unique within a transformation library.

Logical Name Mode

You can create a logical name for an object or change the logical name of an existing object when Warehouse Builder is in logical name mode. Warehouse Builder editors, wizards, and property sheets display the logical names of objects in this mode.

A logical name must conform to these rules:

• The length of a name cannot exceed 200 characters.

• The name must be unique within its category.

• All source modules reflect the case of the imported source and are subject to the double-quotes rules as defined in the Oracle8i/9i SQL Reference.

• Copy operations from a source to a target in a mapping do not propagate case.

When you create a logical name, Warehouse Builder generates a valid physical name that resembles the logical name. If you create a logical name that duplicates an existing physical name, Warehouse Builder appends an underscore and a number to the logical name.

Physical Name Mode

You can create a physical name for an object or change the physical name of an existing object when Warehouse Builder is in the Physical name mode. Warehouse Builder editors, wizards, and property sheets display physical names of objects in this mode. Physical names are converted to uppercase.

A physical name must:

• Contain no more than 28 characters. Two characters are used by Warehouse Builder.

• Conform with the basic syntax rules for schema objects defined by the Oracle8i/9i SQL Reference.

Warehouse Builder prevents you from entering an invalid physical name. For example, you cannot enter a duplicate name, a name with too many characters, or a name that is a reserved word.

Setting the Name Mode

To create or change a logical name for an object, Warehouse Builder must be in logical name mode. To create or change a physical name for an object, Warehouse Builder must be in physical name mode.

The default naming preferences for Warehouse Builder are:

• Mode: Physical name mode.

• Propagation: Propagate physical name to logical name.

Icons for the name and propagation modes are located in the lower-right corner of the editors. These icons indicate the current mode.

To set the name mode:

1. From the Project menu, select Preferences.

   Warehouse Builder displays the Preferences dialog.

2. Select the Naming tab.

   Warehouse Builder displays the Naming sheet.

Figure 2-19 Naming Preferences

Text description of the illustration proprfna.gif

3. Select physical or logical name mode.

   You can switch the naming mode at any time during a session.

4. Select how the names propagate.

5. Click OK.

Warehouse Builder saves your naming preferences across sessions. The name mode preference is stored in a file on the client workstation. If you use Warehouse Builder from another workstation, your preferences will be different.

Editing Objects

After you create modules and objects, you can edit them using editors and property sheets.

Object Editors

Object editors provide a separate window with menu items and a toolbox to edit the object. To display an editor, right-click on the object and select Editor. Figure 2-20 shows an example of a Dimension Editor.

Figure 2-20 Dimension Editor

Text description of the illustration dimedit.gif

Property Sheets

Selecting the Property icon from the console toolbar displays the property sheet for the selected object. Each object stored in a project has a property sheet that defines the object. Property sheets vary by object. Figure 2-21 shows an example of a property sheet for a dimension.

Figure 2-21 Property Sheet for a Dimension

Text description of the illustration prpdimcu.gif

Definitions for objects such as dimensions, tables, facts, and mappings are created using a wizard or imported from an external source. After a definition has been created for an object and stored in a project, you can update the definition by editing its property sheet.

Finding Objects

Objects stored in a Warehouse Builder repository are organized in a navigation tree. To find an object in the repository, you can click Find and type the name to search for. Use the asterisk (*) as a wildcard character to match zero or more characters.

Figure 2-22 Object Find Dialog

Text description of the illustration find.gif

Ending a Warehouse Builder Session

To end a Warehouse Builder session, you commit your work and exit Warehouse Builder.

Committing Your Work

Warehouse Builder does not commit newly created objects or modifications to the repository until you click the Commit icon on the toolbar, or exit Warehouse Builder and click Yes on the Commit Confirmation dialog.

You can commit changes at any time during a Warehouse Builder session or when you end your session.

To commit changes during a session, go to the console window and click the Commit icon on the toolbar. Warehouse Builder displays the Commit Confirmation dialog. Click Yes to commit the changes.

Exiting Warehouse Builder

To exit Warehouse Builder:

1. Go to the Console window.

2. From the Project menu, select Exit.

   If you have not committed your changes when you exit Warehouse Builder, the Commit Confirmation dialog displays. Click Yes to commit any changes you have made.