3
Defining Oracle Data Objects

The wizard creates the external table and inserts its name in the navigation tree.

Using the External Table Editor

Warehouse Builder provides an editor dialog and right-click pop up options similar to regular tables.

Figure 3-10 shows the External Table Editor.

Figure 3-10 External Table Editor

Text description of the illustration exttabed.gif

You can print the external table diagram, validate external table definitions, synchronize the external table definitions, and invoke Warehouse Builder Browser to run reports on the selected external table. For more information on options available in both the table editor and external table editor, refer to "Using the Table Editor"

Reconciling an External Table Definition with a Record in a File

One option available in the external table editor and not the table editor is the reconcile option. Use Reconcile to update the external table definition with metadata changes made to the file associated with the external table.

1. Launch the external table editor by right-clicking on an external table from the navigation tree and selecting Editor.

   Warehouse Builder displays the external table editor.

2. From the menu, select Object then Reconcile.

   The external table editor displays the reconcile dialog as shown in Figure 3-11. Use the reconcile dialog to specify a record in a flat file.

Figure 3-11 Reconcile Dialog for External Tables

Text description of the illustration reconcil.gif

3. In Search, type in a file name and select Go to find a specific flat file. Or scroll through the list and select a flat file.

4. For files with multiple record types, select a record from Record.

   You must select a record. Match strategies, Reconcile strategies, and OK are not available until you specify a record to reconcile against.

5. Set the Match strategies. Based on your selection, Warehouse Builder searches for matches and updates the external table with the information from the flat file.

   Matching by Object Identifier: This strategy compares the field IDs of that the external table columns references with the field IDs in the flat file.

   Matching by Physical Name: This strategy compares the physical names in the external table with the physical names in the flat file.

   Matching by Position: This strategy matches by position, regardless of physical names and IDs. The first external table attribute is reconciled with the first record in the file, the second with the second, and so on. Use this strategy when you want to reconcile the external table with a new record. If the external table has more attributes than the flat file, the excess attributes are removed from the external table.

6. Set the Reconcile Strategies. Use these settings to indicate how Warehouse Builder handles differences in metadata between the existing external table definition and the record you specified.

   Merge: Warehouse Builder combines the metadata from the existing external table definition and the record you specified.

   Replace: Warehouse Builder deletes metadata from the external table definition if it does not match the metadata from the record you specified. The resulting reconciled external table contains metadata that matches the file record metadata.

Editing External Table Definitions

From the External Table Editor, you can access the External Table Properties window to edit the name, description, columns, file and location.

To edit external table properties, select Object then Properties from the External Table Editor menu or right-click the name of a table from the navigation tree and select Properties. The External Table properties window displays. The tabs and properties that you can edit depend on how you defined the external table in the repository.

In most cases, the External Table Properties window displays as shown in Figure 3-12 with the following four tabs:

• Name: Use the Name tab to rename the external table. The same rules for renaming tables apply to external tables. For more information, see "Renaming a Table"

• Columns: Use the Columns tab to add or edit columns. The same rules for adding columns to tables apply to external tables. For more information, see "Editing Table Definitions".

• File: Use the File tab to view the name of the flat file that provides the metadata for the external table. If the source flat file has multiple record types, the File tab also displays the record name associated with the external table. You can update this relationship or change it to a different file and record by reconciling the external table. For more information, see "Reconciling an External Table Definition with a Record in a File".

• Locations: Use the Location tab to view or change the flat file location, as shown in Figure 3-12.

Figure 3-12 External Table Properties Window

Text description of the illustration extabedi.gif

The File tab displays under the following conditions:

• You used the New External Table Wizard to create the external table and you specified a file name.

• You did not specify a file name in the New External Table Wizard, but you reconciled the external table definition with a file and record.

Access Parameters

Access parameters define how to read from the flat file. In some cases, the External Table Properties window displays the Access Parameters tab instead of the File tab.

The tab for the access parameters displays under the following conditions:

• You imported an external table from another repository. In this case, you can view and edit the access parameters.

• You created an external table in an Oracle database and imported its definition into Warehouse Builder. In this case, you can view and edit the access parameters.

• You used the New External Table Wizard to create an external table and did not specify a reference file. The access parameters will be empty. Before generating the external table, you must reconcile the external table definition with a flat file record or manually enter the access parameters into the properties sheet.

The access parameters describe how fields in the source datafile are represented in the external table as columns. For example, if the datafile contained a field emp_id with a datatype of INTEGER(2), the access parameters could indicate that field be converted to a character string column in the external table.

Although you can make changes to the access parameters that affect how Warehouse Builder generates and deploys the external table, it is not recommended. Warehouse Builder does not validate the changes. For more information on the access parameters clause, see Oracle9i Database Utilities Manual.

Using Views

In Warehouse Builder, you can define views and materialized views. This section describes views. For information on materialized views, see "Using Materialized Views".

The following sections provide information about using views:

• About Views

• Creating a View Definition

• Editing a View Definition

About Views

Views are used to simplify the presentation of data or restrict access to data. Often the data that users are interested in is stored across multiple tables with many columns. When you create a view, you create a query stored to retrieve only the relevant data or only data that the user has permission to access.

In Warehouse Builder, a view can be defined to model a query on your target data. This query information is stored in the Warehouse Builder repository. You can later use these definitions to generate .ddl scripts in Warehouse Builder that can be deployed to create views in your target system.

Creating a View Definition

When you create a view with the New View Wizard, the wizard prompts you to type the following information:

• View name and description

• Aliases for columns

• Queries that define the view (optional)

• Logical constraints (optional)

1. From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.

2. Expand the target module where you want to create the view.

3. Right-click View and select Create View.

   Warehouse Builder displays the welcome page for the wizard.

   Note: You can also define a View from the Mapping Editor and model your own query.

4. Click Next.

   The wizard displays the Name page. Type the a name and description for the view. The description is optional.

5. Click Next.

   The wizard displays the Columns page, as shown in Figure 3-13.

Figure 3-13 Columns Page

Text description of the illustration wizvwcol.gif

6. To define a column, click Add.

   Type the column name and select the data type.

   Repeat this procedure for each column.

7. Click Next.

   The wizard displays the Query Text page. You can either type the query definition or click Next to continue.

   If you type a query definition into the Query Text page, be sure to type a valid statement. Warehouse Builder does not validate the text in the Query Text page and will attempt to deploy a view even if the syntax is invalid.

   Figure 3-14 shows a sample query text.

Figure 3-14 Query Text

Text description of the illustration wizvewqr.gif

8. Click Next.

   The wizard displays the Define Constraints page, as shown in Figure 3-15.

   As an option, use this page to define logical constraints for a view. Although Warehouse Builder does not use these constraints when enumerating DDL for the view, these constraints can be useful when the view serves as a data source in a mapping. The Mapping Editor can use the logical foreign key constraints to include the referenced dimensions as secondary sources in the mapping.

   For general information about constraints, see "Editing Constraints".

Figure 3-15 Define Constraints Page

Text description of the illustration wzvewdkt.gif

9. Click Next.

   Warehouse Builder displays the Finish page. Verify the description. If you need to modify the definition, click Back.

10. Click Finish.

    The wizard creates a definition for the view, stores this definition in the repository, and inserts its name in the navigation tree.

Editing a View Definition

To rename a view, right-click the view name and select Rename. Type the new name over the highlighted object name.

You can display the view in the View Editor. Use the property sheet to edit the view.

To open the View Editor, right-click the view and select Edit. The editor diagrams the view and its references, as shown in Figure 3-16.

Figure 3-16 View Editor

Text description of the illustration vedsktop.gif

To open the View properties sheet, right-click the view and select Properties. You can modify the view definition by editing the property sheet. For examples on editing a property sheet, see "Editing Table Definitions".

Using Materialized Views

In Warehouse Builder, you can define views and materialized views. This section discusses materialized views. For information on conventional views, see "Using Dimensions".

The following sections provide information about using materialized views:

• About Materialized Views

• Creating a Materialized View Definition

• Editing a Materialized View Definition

• Configuring Materialized Views

About Materialized Views

In Warehouse Builder, you can create materialized views to improve query performance.When you create a materialized view, you create a set of query commands that aggregate or join data from multiple tables. Materialized views provide precalculated data that can be reused or replicated to remote data marts. For example, data about company sales is widely sought throughout an organization.

When you create a materialized view in Warehouse Builder, you can configure it to take advantage of the query rewrite and fast refresh features available in Oracle9i. For information on query rewrite and fast refresh, "Fast Refresh for Materialized Views"

Creating a Materialized View Definition

When you create a materialized view with the New Materialized View Wizard, the wizard prompts you to type the following information:

• Materialized view name

• Materialized view description

• Column names

• Queries that define the materialized view

• Logical constraints (optional)

1. From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.

2. Expand the target module where you want to create the materialized view.

3. Right-click Materialized View and select Create Materialized View.

   Warehouse Builder displays the welcome page for the New Materialized View wizard.

   Note: You can also define a Materialized View from the Mapping Editor.

4. Click Next.

   The wizard displays the Name page.

5. Type the name and description for the view. The description is optional.

6. Click Next.

   The wizard displays the Columns page, as shown in Figure 3-17.

Figure 3-17 Columns Page

Text description of the illustration wizmvwco.gif

7. To define a column, click Add.

   Type the column name and select the data type. Repeat this procedure for each column.

8. Click Next.

   The wizard displays the Query Text page. You can either type the query definition or click Next to proceed to the Define Constraints page.

   If you type a query definition into the Query Text page, be sure to type a valid statement. For column names, use the same names that you specified on the Columns page in the previous step. If you change a column name on the columns page, you must manually change the name on the Query Text page. Warehouse Builder does not validate the text in the Query Text page and will attempt to deploy a view even if the syntax is invalid.

   Figure 3-18 shows sample query text.

Figure 3-18 Sample Query Text

Text description of the illustration wizmvwqr.gif

9. Click Next.

   The wizard displays the Define Constraints page. As an option, you can define constraints for the materialized view, as shown in Figure 3-19.

   These constraints are for logical design purposes only and Warehouse Builder does not use these constraints when enumerating DDL for the view. For general information about constraints, see "Editing Constraints"

Figure 3-19 Define Constraints Page

Text description of the illustration wizmvwcs.gif

10. Click Next.

    The wizard displays the Finish page. Verify the summary, and if you need to modify the definition, click Back.

11. Click Finish.

The wizard creates a definition for the materialized view, stores this definition in the database module, and inserts its name in the warehouse module navigation tree.

Editing a Materialized View Definition

To rename a materialized view, right-click the view name and select Rename. Type the new name over the highlighted object name.

You can view the materialized view in the Materialized View Editor. Use the property sheet to edit the materialized view.

To open the Materialized View Editor, right-click the materialized view and select Editor. The editor diagrams the materialized view and its references.

To open the Materialized View properties sheet, right-click the materialized view and select Properties. You can modify the view definition by editing the property sheet.

For examples on editing a property sheet, see "Editing Table Definitions"

Using Sequences

A sequence is a database object that generates a serial list of unique numbers. You can use sequences to generate unique primary key values and to coordinate keys across multiple rows or tables. Sequence values are guaranteed to be unique. When you create a sequence in Warehouse Builder, you are creating sequence definitions that are saved in the repository. Sequence definitions can be used in mappings to generate unique numbers while transforming and moving data to your target system.

The following sections provide information about using sequences:

• About Sequences

• Creating a Sequence Definition

• Using the Sequence Editor

• Editing Sequence Definitions

About Sequences

A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudo columns. Each new sequence number is incremented by a reference to the pseudo column NEXTVAL, while the current sequence number is referenced using the pseudo column CURRVAL. When you define a sequence, Warehouse Builder creates these attributes.

In Warehouse Builder, you can also import sequence definitions from existing source systems using the Import Object Wizard. For more information, see "Importing Metadata into Target Modules".

Creating a Sequence Definition

1. From the Warehouse Builder navigation tree, expand the warehouse module node.

2. Right-click Sequence and select Create Sequence from the pop-up menu.

   Warehouse Builder displays the New Sequence Wizard.

3. Enter the following information:

   Name: Type a name for the column. In physical mode, you must type a name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type a unique name up to 4000 characters in length. The column name must be unique within the table. Spaces are allowed.

   Description: Optionally type a description for the sequence. The description cannot exceed 4000 characters.

4. Click OK.

   Warehouse Builder stores the definition for the sequence and inserts its name in the navigation tree.

Using the Sequence Editor

After you define a sequence in Warehouse Builder, you can use the Sequence Editor to view columns. To open the Sequence Editor, right-click the name of a sequence and select Editor. Figure 3-20 shows the Sequence Editor.

Figure 3-20 Sequence Editor

Text description of the illustration seq_edit.gif

Use the Sequence Editor Menu, the Sequence Editor Navigation bar, or the right-click pop up options to print the sequence diagram, validate sequence definitions, synchronize the sequence definitions, and invoke Warehouse Builder Browser to run reports on the selected sequence.

Viewing Reports

To view metadata reports on any sequence, select View, then Reports. For information on installing and configuring the Warehouse Builder Browser, refer to the Oracle9i Warehouse Builder Installation and Configuration Guide.

Editing Sequence Definitions

From the Sequence Editor, you can access the Sequence Properties window to edit the name, description, and column notes of a sequence.

To edit sequence properties, select right-click the name of a sequence from the navigation tree and select Properties or double-click the name of the sequence. The Sequence Properties window displays two tabs: General and Columns.

Click these tabs to perform the following tasks:

• Rename a sequence

• Edit sequence columns

Renaming a Sequence

1. Right-click the name of a sequence and select Properties.

   The Sequence Properties window opens.

2. Select the General tab:

   Name: Type a new name for the sequence. In physical mode, you must type a unique name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type a name up to 200 characters long. Spaces are allowed. The name must be unique within the module.

   Description: Type or modify the sequence description within this field. The description can be a maximum of 4000 characters. This field is optional.

   Tip: If you want to rename a sequence without editing its description, right-click the name of the sequence from the navigation tree and select Rename.

Editing Sequence Column Descriptions

1. Right-click the name of a sequence and select Properties.

   The Sequence Properties window opens.

2. Select the Columns tab.

3. Scroll to the Note field and type or modify the description for the selected column.

Using Advanced Queues

Oracle Advanced Queues (AQs) provide a database-integrated message queuing system that plays a central role in enterprise data integration. Warehouse Builder enables you to import Advanced Queue definitions and use AQs to move data from your sources to the target system.

For more information on Oracle Advanced Queuing, see Oracle9i Application Developer's Guide- Advanced Queuing.

The integration of Oracle Advanced Queues (AQs) in Warehouse Builder enables the following:

• Use of AQs as data sources or targets in your data warehouse design.

• Integration of MQ Series and TIBCO applications as data sources.

• Use of AQs to propagate source data changes.

This section includes the following topics:

• About Advanced Queues

• Creating Definitions for Advanced Queues

• Viewing Advanced Queue Properties

• Creating Advanced Queues

For related information, see:

• Configuring Advanced Queues

• Mapping Advanced Queue Operator

About Advanced Queues

Web enabled business applications often communicate with one another through message queues. Advanced Queuing leverages the functions of the Oracle database to persistently store these messages, to propagate them between queues on different machines and databases, and to transmit them using Oracle Net Services, HTTP(S), and SMTP. AQs enable message management and communication required for application integration. Warehouse Builder supports AQs as data sources for your warehouse design.

The following types of AQs are supported in Warehouse Builder:

• Multi-consumer AQ. The associated queue table must be created as a multiple consumer queue table in the database.

• Normal AQs (Exception AQs are not supported).

• Persistent AQs (non-persistent AQs are not supported).

This section discusses the following key concepts related to AQs.

Payloads

Payload is the data stored in a queue. It can be unstructured, such as, the data type RAW, or structured. Payloads can be structured by using Oracle object types, also known as ADTs or user defined types. Complex object types including embedded attributes, collection attributes, and XMLType and SYS.AnyData attributes are not currently supported in Warehouse Builder.

AQs require that the payload type be either an Oracle object type or RAW. To be used in Warehouse Builder, the payload must be defined as an object type containing attributes with scalar data types supported by Warehouse Builder. RAW data types are not supported.

Messages

A message is the smallest unit of information entered into or retrieved from a queue. Messages contain control information and payload data. The control information contains message properties or metadata used by AQs to manage messages. Payload data is the information stored in the queue. A message can reside in only one queue.

Creating Definitions for Advanced Queues

Currently, AQs can only be imported into Warehouse Builder from an Oracle source schema. For information on how to create an AQ in your source schema, see "Creating Advanced Queues".

Importing AQ Definitions

The imported AQ metadata contains the AQ name, description, the payload object type name, payload structure, the schema where object type is defined, and the name of the associated queue table. These definitions are imported from the agent AQ and not from the source AQ. The payload object type must reside in the same schema as the AQ. If the payload object type is not defined in the same schema as the AQ, then the AQ is imported but the payload type is not imported. In this case, the AQ will not be associated with any payload type and will be invalid. You will receive an error when you validate this AQ. Object types can be created or deleted through scripting and public API.

1. In the navigation tree, right click the target module and select Import.

   The Database Link Information dialog displays if you have not defined a database link.

2. Create a new database link to the system from where you are importing the AQ definitions or select a previously created database link.

3. Click OK.

   The Import Metadata Wizard welcome page displays.

4. Click Next.

   The Filter Information page displays.

5. Select Advanced Queue as the type of object you want to import.

6. Limit the search of the data dictionary in one of the following methods:

   Type a search pattern. For example, you can type a warehouse project name followed by a percentage sign (%) to import objects that begin with that project name. Use a percentage sign (%) as a wild card match for multiple characters and an underscore (_) as a wild card match for a single character.

   Type the maximum number of objects to retrieve.

7. Click Next.

   Warehouse Builder displays the names of objects that meet the filter conditions and displays the Object Selection page.

8. From the Available Objects list, expand the ADVANCED QUEUE node and select the queues you want to import.

9. Click the arrow buttons to move the selected queues to the Selected Objects list. Warehouse Builder only supports the import of persistent multi-consumer queues.

   If you are re-importing definitions, previously imported objects appear in bold.

10. Click Next.

    The Summary and Import page displays. This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reconciled or created. Verify the contents of this page and optionally add descriptions for each of the objects.

11. Click Finish.

    Warehouse Builder imports the definitions for the selected queues and displays the results in the Import Results dialog. When you import a queue, its payload is also imported. You can expand the queue node to view the attributes of the payload for a queue.

    The payload object type must reside in the same schema as the AQ. If the payload object type is not defined in the same schema as the AQ, then the AQ is imported but the payload type is not imported. In this case, the AQ will not be associated with any payload type and will be invalid. You will receive an error when you validate this AQ

12. Verify that the definitions have been created and click OK to complete the import.

    Click Undo if you do not want to import the definitions. Or click Save to save the definitions to a local drive. Warehouse Builder stores the imported definitions under the AQ node in the warehouse module navigation tree.

    Note: At runtime, a temporary table must be available to use the AQ as a source. The create table script is generated when you deploy the AQ.

Reimporting Advanced Queue Definitions

Two advanced queues can share the same payload type. If you import a queue (Q2) whose payload has already been imported with another queue (Q1), then the attributes of Q2 are reconciled with the previously imported attributes.

1. Follow the above steps using the Import Metadata Wizard. In the Summary and Import page, the Advanced Reconcile Options button is now enabled.

Figure 3-21 AQ Advanced Reconcile Options

Text description of the illustration aqadvrec.gif

The Advanced Reconciliation Options dialog displays, as shown in Figure 3-21. Use this dialog to reconcile the reimported metadata with the existing definitions in the Warehouse Builder repository. You can choose to preserve object type attributes and AQ descriptions in the repository.

Select options for reconciling the object type:

Preserve repository added attributes: Check this option to preserve attributes added to the repository that are not present in the object to be imported.

Select options for reconciling the AQ:

Preserve existing descriptions: Check this option to preserve previously imported AQ definitions in the repository.

Warehouse Builder stores the imported definitions under the AQ node in the warehouse module navigation tree.

For related information, see the following sections:

• Mapping Advanced Queue Operator

• Configuring Advanced Queues

Viewing Advanced Queue Properties

After you import AQs into Warehouse Builder, you can view their properties by right-clicking the AQ name and selecting Properties from the pop-up menu. The Advanced Queue Properties window displays three tabs: Name, Payload, and Payload Structure. The imported AQ properties cannot be edited.

You can view the following properties by clicking each of the tabs:

• Name: Name and description of the imported AQ.

• Payload: Name and description of the payload imported with the AQ.

• Payload Structure: Attributes of the payload imported with the AQ, such as, Name, Position, Data Type, and Length as shown in Figure 3-22.

Figure 3-22 AQ Properties Payload Structure Tab

Text description of the illustration aqproper.gif

Creating Advanced Queues

Using SQL

The following steps outline how to create AQs in your Oracle source system using SQL:

SQL> grant aq_administrator_role, aq_user_role to scott;

(Grants the required roles to the user who will administer and/or use the AQ. These roles must be granted by the system administrator or the user with SYSDBA privileges, SYS or SYSTEM)

SQL> connect scott/tiger;

(Connect with the user name and password of the user who has been granted the privileges, for example, scott/tiger)

SQL> create type employee as object (empno number, ename varchar2(30));

(Create the object structure on which the AQ will be based, for example, employee)

SQL> execute dbms_aqadm.create_queue_table(queue_table => 'EMPLOYEE_QUEUE_TBL', multiple_consumers=>true queue_payload_type => 'EMPLOYEE');

(Create the AQ table. Because Warehouse Builder currently supports only multiconsumer queues, multiple_consumers => true)

SQL> execute dbms_aqadm.create_queue('EMPLOYEE_QUEUE', 'EMPLOYEE_QUEUE_TBL');

(Create the AQ)

SQL> execute dbms_aqadm.start_queue('EMPLOYEE_QUEUE');

(Start the AQ)

Using Oracle Enterprise Manager

The following steps outline how to create AQs in your Oracle source system using Oracle Enterprise Manager:

1. Grant the AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE to the user who will administer or use the AQ. Expand the navigation tree by expanding the Database node, then Security, then Users, and then Role (must have SYSDBA privileges).

2. Create the object type. From the Object menu, select Create, and then Object Type. Specify the attributes and create the object type.

3. Create the AQ table. Expand the navigation tree by expanding the Databases node, then Database Name, then Schema, then Advanced Queues, and then double click Queue Tables to create the AQ table.

4. Create the AQ by selecting the Object menu, then Create, and then Queue.

5. Start the AQ by expanding the Databases node, then Database Name, then Schema, then Advanced Queues, then Queue Tables, then Schema Name, then Queue Table Name. Highlight the queue name then from the Object menu select Queue Start.

   For more details, see the Oracle Enterprise Manager User's Guide.

Using Dimensions

Dimensions are the primary organizational unit of data in a star schema. Warehouse Builder uses dimensions to organize and index data for cubes. Examples of some commonly used dimensions are Customer, Product, and Time.

When you define a dimension, you need to define its hierarchies, levels, and level relationships. The levels represent the level of aggregation and the hierarchies describe parent-child relationships among a set of levels. Dimension hierarchies are logical structures that use ordered levels as a means of organizing data.

Query performance can be improved using Dimensions because users often analyze data by drilling down on known hierarchies. An examples of a hierarchy is the time hierarchy of Year, Quarter, Month, Day. Oracle9i makes use of defined hierarchies by rewriting queries to retrieve data from summary rather than detail tables. The rewritten queries have improved performance.

Typical dimension tables have the following characteristics:

• A single column primary key populated with values called warehouse keys.

• Warehouse keys that provide administrative control over the dimension, support techniques that preserve dimension history, and reduce the size of cubes.

• One or more hierarchies that are explicitly defined as dimension objects. Hierarchies defined by a Create Dimension statement maximize the number of query rewrites by the Oracle9i server.

Rules for Dimension Objects

A dimension definition includes a dimension object definition and a dimension table definition. This section provides information about the dimension object.

Table 3-2 summarizes the rules for dimensions.

Table 3-2 Warehouse Builder Rules for Dimension Objects  

About Levels and Hierarchies

Dimension objects consist of a set of levels and a set of hierarchies defined over those levels. The levels represent levels of aggregation. Hierarchies describe parent-child relationships among a set of levels.

For example, a typical calendar dimension could contain five levels. Two hierarchies can be defined on these levels:

H1: YearL > QuarterL > MonthL > WeekL

H2: YearL > QuarterL > WeekL > DayL

The hierarchies are described from parent to child, so that Year is the parent of Quarter, Quarter the parent of Month, and so forth.

About Unique Key Constraints

When you create a definition for a hierarchy, Warehouse Builder creates an identifier for each level of the hierarchy and a unique key constraint on the lowest level. Warehouse Builder uses the identifiers during the generation phase to build a DDL script to create the dimension object.

When you create a foreign key reference on a cube that points to a dimension, Warehouse Builder shows the unique key constraint and the other identifier keys as candidates for the referenced column. A cube can reference only the lowest level of a hierarchy because it contains a unique key constraint. If you select any other level, the definition is invalid.

Figure 3-23 Cube Properties Dialog Showing the Foreign Keys Tab

Text description of the illustration fpfkrlev.gif

About Mixed Levels of Aggregation

An application can require two hierarchies that start at different levels of aggregation. For example, you can have the following hierarchies:

H1: YearL > QuarterL > MonthL > DayL

H2: YearL > WeekL > DayL

H3: YearL > QuarterL > MonthLowL

To model this mixed case using Warehouse Builder:

• The dimension table must contain the additional column MonthLow.

• The MonthLow column must be populated with unique values.

• A separate MonthLow level must be defined for the dimension.

For this set of hierarchies, Warehouse Builder generates six level identifiers and two unique key constraints. One unique constraint is defined on the Days column and the other on the MonthLow column. Because DayL and MonthLowL are at the bottoms of their respective hierarchies, they can serve as targets of foreign key references.

Warehouse Builder generates a dimension as a single denormalized table with a set of levels and hierarchies defined on that table. Each level can have any number of attributes.

Creating a Dimension Definition

To create definitions for a dimension, use the New Dimension Wizard. You name the dimension and define a primary key constraint. When you define each column, Oracle recommends setting the constraint to NOT NULL to prevent inconsistent result sets and to maximize the number of query rewrites.

You also define the dimension hierarchy and its levels of aggregation. Table 3-3 provides an example of a dimension table with each level of aggregation, a prefix for each level, and the attributes defined on each level. The levels occur in parent to child order: class is the parent of family and family is the parent of product.

Table 3-3 Example of a Dimension Object  

1. From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.

2. Expand the target module where you want to create the dimension.

3. Right-click Dimensions and select Create Dimension.

   Warehouse Builder displays the welcome page for the New Dimension Wizard.

4. Click Next.

   The wizard displays the Name page, as shown in Figure 3-24.

5. Type the following:

   A name for the dimension.

   A prefix.

   The prefix is used to generate a unique name for the unique key constraint on the base level key column. If the prefix is blank, the dimension name is used.

   A description of the dimension (optional).

Figure 3-24 Name Page

Text description of the illustration wizdimna.gif

6. Click Next.

   The wizard displays the Levels page. Dimensions contain at least one level. You can define a default level to satisfy this requirement and include additional levels as required. Figure 3-25 shows the Levels page.

Figure 3-25 Levels Page

Text description of the illustration wizdimle.gif

7. Define levels of aggregation in the dimension. Enter the following:

   The name of the level.

   A prefix for the level. The default prefix is the name of the level.

   A description of the level.

8. Click Add to add the level. Continue this process until you have defined each level of aggregation.

   Prefixes are useful because they:

   Reduce the number of attributes you must enter manually. The wizard generates an ID attribute for each level and assigns it the name levelprefix_ID.

   Allow you to reuse attribute names. This is a common practice when you build dimensions for higher levels of aggregation.

   Note: The dimension prefix is used to form the names of level unique keys. After a unique key name is generated, changing the dimension prefix does not change the names of existing levels because cube foreign keys may already refer to the generated level. Unique key names generated after you edit the prefix use the new prefix.

9. Click Next.

   The wizard displays the Level Attributes page. A level can have one or more attributes. The wizard generates an ID attribute for each level.

   The ID attribute for a level identifies the level. The attribute is the key column for the level. This attribute is used in the create dimension statement to define the level, and the defined level is used in the determines clause to specify other columns within that level (dependent columns). See the Oracle9i SQL Reference and the Oracle9i Data Warehousing Guide for more information.

10. Select a level of aggregation from the drop-down list.

11. Type a name for the attribute.

12. Select a data type for the attribute from the drop-down list under Data Type. Warehouse Builder supports the following Oracle9i data types:

    CHAR

    DATE

    FLOAT

    NUMBER

    VARCHAR

    VARCHAR2

13. Enter the precision, length, or scale as appropriate for the data type.

14. Type a description of the attribute.

15. Click Add.

    You can define another attribute for the selected level or select another level and define its attributes. Continue this process until you have defined all the attributes for each level, as shown in Figure 3-26.

    If you want to rename the ID column, select ID in the Level Attributes text box.

    Type a new name in the Name text box.

    Click Update.

    Figure 3-26 Level Attributes Page

    

    Text description of the illustration wizdimat.gif

16. Click Next.

    The wizard displays the Hierarchies page.

17. Define the hierarchy:

    Type a name and prefix for each hierarchy.

    Type a description of the hierarchy.

18. Click Next.

    The wizard displays the Level Relationships page, as shown in Figure 3-27.

Figure 3-27 Level Relationships Page

Text description of the illustration wizdimla.gif

19. Define the levels within a hierarchy:

    Select a hierarchy from the drop-down list.

    Move the names of levels for a selected hierarchy from Available Levels to Selected Levels.

    Arrange the levels so that they show the parent to child order.

20. Click Next.

    The wizard displays the Finish page. Verify the description.

21. Click Finish.

    The wizard creates a definition for the dimension.

The wizard generates a unique key (UK) constraint for a dimension table on the ID column that represents the base level of aggregation for the dimension. Dimensional designs often call for a primary key (PK) rather than a UK constraint. After you complete a definition for a dimension, you can change the UK to a PK constraint.

Editing Dimension Definitions

You can edit the definition for a dimension object with the Dimension Editor or by editing entries in the dimension property sheet.

Using the Dimension Editor

To display the Dimension Editor, right-click a dimension in the navigation tree and select Editor. Figure 3-28 shows the Dimension Editor.

Figure 3-28 Dimension Editor

Text description of the illustration deopen.gif

The Dimension Editor displays a toolbox and the dimension object.

To add an element to the dimension object, drop an icon from the toolbox onto a dimension element.

1. Fully expand the Level where you want to add the new attribute.

2. Drop the Attribute icon on the Level.

   Warehouse Builder adds an attribute (attribute1) in the level with the number data type.

3. Enter a name for the attribute.

4. To change the data type, double-click the attribute name.

   The Dimension Editor displays the dimension property sheet.

5. Select the Level Attributes tab, as shown in Figure 3-29.

6. Select a data type from the drop-down list.

7. Change the length, scale, or precision depending on the data type selected.

8. Click Update.

Figure 3-29 Level Attributes Tab of Dimension Properties Sheet

Text description of the illustration deprpla.gif

To print the diagram, click the Print icon on the Dimension Editor toolbar.

Using the Property Sheets

The dimension object and the dimension table both have property sheets. In the dimension object property sheet, you edit the levels and hierarchies. In the dimension table property sheet, you edit the columns and constraints.

• In the Dimension Editor, from the Edit menu, select Properties or click the Properties icon.

• From the Warehouse Builder navigation tree, right-click the dimension and select Properties.

Warehouse Builder displays the property sheet for the dimension object, as shown in Figure 3-30.

Figure 3-30 Dimension Object Properties Sheet

Text description of the illustration deproprp.gif

The dimension object property sheet has the following tabs:

• Name

• Levels

• Level Attributes

• Hierarchies

• Level Relationships

1. Open the Dimension Editor.

2. Select Table Properties from the Edit menu.

   Warehouse Builder displays the dimension table property sheet. For information about the table property sheet, see "Editing Table Definitions".

Using Cubes

Cubes, also known as facts, contain measures and link to one or more dimensions. Most cube measures are additive. Common additive measures include sales, units, and cost.

Cubes are linked to dimension tables over foreign key constraints. These constraints are critical in a data warehousing environment where data integrity is paramount. The constraints enforce referential integrity during the daily operation of the data warehouse.

When dimensions are designed with warehouse keys, the cube row length is usually reduced because warehouse keys are shorter than their natural counterparts. The result is less storage space wasted in the cube.

A typical cube contains:

• A primary key defined on the set of foreign key reference columns or, in the case of a data list, on an artificial key or a set of warehouse key columns. When the cube is a data list, the foreign key reference columns do not uniquely identify each row in the cube.

• A set of foreign key reference columns that link the table with its dimensions.

When you create a definition for a cube, you must define its measures and its foreign key references. To define a foreign key reference, you include the name of the referenced dimension and its primary key column.

Creating Cube Definitions

This section describes how to create and update a definition for a cube. You create a definition for a cube using the New Cube Wizard, and you update the definition by editing its property sheet. You can also import definitions for tables from another database source or an Oracle Designer Repository.

Use the New Cube Wizard to create definitions for a cube. This information includes details regarding foreign key references, measures, and the data types of all the table columns.

1. From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.

2. Expand the target module where you want to create the cube.

3. Right-click Cubes and select Create Cube.

   Warehouse Builder displays the Welcome page for the New Cube Wizard.

4. Click Next.

   The wizard displays the Name page.

5. Enter the following:

   The name of the cube

   A description of the cube (optional)

6. Click Next.

   The wizard displays the Define Foreign Keys page.

7. Select the name of a dimension from the Dimension drop-down list.

8. Select the base key level from the Level drop-down list.

9. Select the primary key column constraint defined on the dimension from the Unique Key drop-down list.

10. Click Add.

    The wizard inserts the foreign key reference constraint in the text box that lists the foreign keys.

11. Repeat these steps for each foreign key constraint. Select the lowest level of aggregation for the foreign key reference target.

    You can change the name of the generated foreign key by selecting the name and typing over it. The name must be unique within the project.

    Note: The Cube Wizard displays the name for each generated PK constraint on level columns. Only the lowest level PK constraint is an actual physical constraint. You cannot modify the name or data type of the foreign key reference columns. You can only do this by editing the definition for the referenced table. If you add a column to a PK or UK constraint on a dimension, you must also update the cube foreign key references.

12. Check the box next to Create segmented unique key from foreign keys.

13. Click Next.

    The wizard displays the Define Measures page, as shown in Figure 3-31.

    Figure 3-31 Define Measures Page

    

    Text description of the illustration wizfctme.gif

14. Click Add.

15. Type the name of the measure.

16. Select the data type of the measure.

17. Repeat these steps for each measure in the cube.

18. Click Next.

    The wizard displays the Finish page. This page summarizes the cube. Click Back to modify any of the elements.

19. Click Finish.

    The wizard creates a definition for a cube, stores it in the warehouse module, and inserts its name in the navigation tree.

Editing a Cube Definition

A cube object has two property sheets: one for the cube object and another for the table. You can update cube object properties by editing the property sheets. In addition, you can add foreign key references or measures to a cube object using the Cube Editor. You can also use the Cube Editor to change cube properties and foreign key relationships with dimensions.

Using the Cube Editor

To open the Cube Editor, right-click a cube name and select Edit from the pop-up menu. Warehouse Builder displays the Cube Editor containing a tool palette and a diagram of the cube and the related dimensions.

Figure 3-32 shows the Cube Editor.

Figure 3-32 Cube Editor

Text description of the illustration fesal.gif

To print the diagram, click the printer icon on the Cube Editor toolbar.

• From the Cube menu, select Cube Properties or click the Properties icon.

• Right-click the cube and select Properties.

The properties include the object name and description, foreign key references, measures, and attribute sets.

From the Cube Properties sheets, you can:

• Change the name and description of the object.

• Add or Remove a foreign key reference constraint.

• This Foreign Keys sheet shows all the UK constraints defined on a dimension: the base level of aggregation and each higher level of aggregation. Warehouse Builder generates DDL only for the constraint defined on the base level of aggregation.

• Change the name of a foreign key reference constraint.

• Add, Remove, or edit a measure (name, data type, and description).

Importing Metadata into Target Modules

Warehouse Builder enables you to import data object definitions into target modules using the Import Metadata Wizard. These definitions assist you in modelling your target system. For Oracle target modules, you can import definitions for tables, views, external tables, sequences, advanced queues, and PL/SQL transformation packages. For other target modules, such as SAP systems, you can import table definitions.

1. Right-click on an Oracle module name and select Import.

   The welcome page for the Import Metadata Wizard displays.

2. Click Next.

   The Filter Information page displays.

3. Limit the search of the data dictionary in one of the following methods:

   Select tables, views, external tables, sequences, advanced queues, or PL/SQL transformation packages.

   Type a search pattern. For example, you can type a warehouse project name followed by a % to import objects that begin with that project name. Use % as a wild card match for multiple characters and _ as a wild card match for a single character.

4. Click Next.

   Warehouse Builder retrieves names that meet the filter conditions from the data dictionary and displays the Object Selection page as shown in Figure 3-33.

Figure 3-33 Import Metadata Wizard Object Selection Page

Text description of the illustration impobjec.gif

To move all items to the Selected Objects list, click the double arrow.

To move an object and the objects it references, select the name of the object and check One Level.

To move a single object and all the objects it references directly or indirectly, select the name of the object and check All Levels.

If you are re-importing definitions, previously imported objects appear in bold.

The Summary and Import page displays. This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reconciled or created. Verify the contents of this page and add descriptions for each of the objects.

The Import Results page displays.

Warehouse Builder stores the definitions in the target module.

After you have created or imported object definitions to model your target system, you can configure these objects for deployment. For more information, see Chapter 5, "Configuring Data Objects". You can also define how to extract, transform, and load the data from your sources to the target systems. For more information, see Chapter 6, "Designing Mappings".