13 Defining Oracle Data Objects

After you finish designing your data warehouse or data mart, you are ready to design your target system. Most of your target schema modelling takes place within the Data Object Editor. This chapter shows you how to use the Data Object Editor to create data objects.

This chapter contains the following topics:

About Data Objects

The Oracle module contains nodes for each type of data object that you can define in Warehouse Builder. In the Project Explorer, under the Oracle node, expand the module node to view all the supported data objects.

Warehouse Builder supports relational and dimensional data objects. Relational objects, like relational databases, rely on tables and table-derived objects to store and link all of their data. The relational objects you define are physical containers in the database that are used to store data. It is from these relational objects that you run queries after the warehouse has been created. Relational objects include tables, views, materialized views, and sequences. You can also create optional structures associated with relational objects such as constraints, indexes, partitions, and attribute sets.

Dimensional objects contain additional metadata to identify and categorize your data. When you define dimensional objects, you describe the logical relationships that help store the data in a more structured format. Dimensional objects include dimensions and cubes. This chapter provides specific information about each type of dimensional object and how they are used in Warehouse Builder.

In addition to relational and dimensional objects, Warehouse Builder supports intelligence objects. Intelligence objects are not part of Oracle modules. They are displayed under the Business Intelligence node in the Project Explorer. Intelligence objects enable you to store definitions of business views. You can deploy these definitions to analytical tools such as Oracle Discoverer and perform ad hoc queries on the warehouse. For more information about intelligence objects, see "Integrating with Business Intelligence Tools".

Table 13-1 describes the types of data objects you can use in Warehouse Builder.

Table 13-1 Data Objects in Warehouse Builder

Data Object Type Description

Tables

Relational

The basic unit of storage in a relational database management system. Once a table is created, valid rows of data can be inserted into it. Table information can then be queried, deleted, or updated. To enforce defined business rules on a table's data, integrity constraints can be defined for a table.

See "Using Tables" for more information.

External Tables

Relational

External tables are tables that represent data from non-relational flat files in a relational format. Use an external table as an alternative to using a flat file operator and SQL* Loader.

See "Using External Tables" for more information.

Views

Relational

A view is a custom-tailored presentation of data in one or more tables. Views do not actually contain or store data; they derive their data from the tables on which they are based. Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view affect the base tables of the view. Use views to simplify the presentation of data or to restrict access to data.

See "Using Views" for more information.

Materialized Views

Relational

Materialized views are pre-computed tables comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table. Use materialized views to improve query performance.

See "Using Materialized Views" for more information.

Sequences

Relational

Sequences are database objects that generate lists of unique numbers. You can use sequences to generate unique surrogate key values.

See "Using Sequences" for more information.

Dimensions

Dimensional

A general term for any characteristic that is used to specify the members of a data set. The three most common dimensions in sales-oriented data warehouses are time, geography, and product. Most dimensions have hierarchies.

See "About Dimensions" and "About Time Dimensions" for more information.

Cubes

Dimensional

Cubes contain measures and links to one or more dimension tables. They are also known as facts.

See "About Cubes" for more information.

Advanced Queues

Relational

Advanced Queues enable message management and communication required for application integration.

Currently, you cannot create advanced queues using Warehouse Builder. You can only import advanced queues that were exported into an .mdl file using a previous version of the product.

Queue Tables

Relational

Queue tables are tables that store queues. Each queue table contains a payload whose data type can be an object type or RAW.

You cannot create a queue table using Warehouse Builder. A queue table is imported as part of an advanced queue payload.

Object Types

Relational

An object type is made up of one or more user-defined types or scalar types.

See "Creating Object Types" for more information.

Varrays

Relational

A varray is an ordered collection of elements.

See "Creating Varrays" for more information.

Nested Tables

Relational

A nested table complements the functionality of the varray data type. A nested table permits a row to have multiple 'mini-rows' of related data contained within the one object.

See "Creating Nested Tables" for more information.


Supported Data Types

Table 13-2 displays the data types you can use to create and edit columns.

Table 13-2 Data Types

Data Type Description

BINARY_DOUBLE

Stores double-precision IEEE 754-format single precision floating point numbers. Used primarily for high-speed scientific computation. Literals of this type end with d. For example, 3.0235d.

BINARY_FLOAT

Stores single-precision IEEE 754-format single precision floating point numbers. Used primarily for high-speed scientific computation. Literals of this type end with f. For example, 2.07f.

BLOB

Stores large binary objects in the database, in-line or out-of-line. Every BLOB variable stores a locator, which points to a large binary object. The size of a BLOB cannot exceed four gigabytes.

CHAR

Stores fixed-length character data to a maximum size of 4000 characters. How the data is represented internally depends on the database character set. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding.

CLOB

Stores large blocks of character data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every CLOB variable stores a locator, which points to a large block of character data. The size of a CLOB cannot exceed four gigabytes.

DATE

Stores fixed-length date times, which include the time of day in seconds since midnight. The date defaults to the first day of the current month; the time defaults to midnight. The date function SYSDATE returns the current date and time.

FLOAT

Stores a single-precision, floating-point, number. FLOAT can be loaded with correct results only between systems where the representation of a FLOAT is compatible and of the same length.

INTEGER

A NUMBER subtype that stores integer values with a maximum precision of 38 decimal digits.

INTERVAL DAY TO SECOND

Stores intervals of days, hours, minutes, and seconds.

INTERVAL YEAR TO MONTH

Stores intervals of years and months.

LONG

Stores fixed-length character strings. The LONG data type is like the VARCHAR2 data type, except that the maximum length of a LONG value is 2147483647 bytes (two gigabytes).

MDSYS.SDOAGGRTYPE

Stores the geometric description of a spatial object and the tolerance. Tolerance is used to determine when two points are close enough to be considered as the same point.

MDSYS.SDO_DIM_ARRAY

Stores an array of type MDSYS.SDO_DIM_ELEMENT.

MDSYS.SDO_DIM_ELEMENT

Stores the dimension name, lower boundary, upper boundary and tolerance.

MDSYS.SDO_ELEM_INFO_ARRAY

Stores an array of type MDSYS.SDO_ORDINATE_ARRAY.

MDSYS.SDO_GEOMETRY

Stores Geographical Information System (GIS) or spatial data in the database. For more information, refer to the Oracle Spatial Users Guide and Reference.

MDSYS.SDO_ORDINATE_ARRAY

Stores the list of all vertices that define the geometry.

MDSYS.SDO_POINT_TYPE

Stores two dimensional and three dimensional points.

NCHAR

Stores fixed-length (blank-padded, if necessary) national character data. Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16).

NCLOB

Stores large blocks of NCHAR data in the database, in-line or out-of-line.

NUMBER

Stores real numbers in a fixed-point or floating-point format. Numbers using this data type are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers, as well as zero, in a NUMBER column.

NVARCHAR2

Stores variable-length Unicode character data. Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16).

RAW

Stores binary data or byte strings. For example, a RAW variable might store a sequence of graphics characters or a digitized picture. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data.

SYS.ANYDATA

An Oracle-supplied type that can contain an instance of a given type, with data, plus a description of the type. ANYDATA can be used as a table column data type and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types.

SYS.LCR$_ROW_RECORD

This type represents a data manipulation language (DML) change to a row in a table. This type uses the LCR$_ROW_LIST type.

TIMESTAMP

Extends the DATE data type and stores the year, month, day, hour, minute, and second. The default timestamp format is set by the Oracle initialization parameter NLS_TIMESTAMP_FORMAT.

TIMESTAMP WITH LOCAL TIMEZONE

Extends the TIMESTAMP data type and includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. You can also use named time zones, as with TIMESTAMP WITH TIME ZONE.

TIMESTAMP WITH TIMEZONE

Extends the data type TIMESTAMP and includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time.

VARCHAR

Stores a length-value data type consisting of a binary length subfield followed by a character string of the specified length. The length is in bytes unless character-length semantics are used for the data file. In that case, the length is in characters.

VARCHAR2

Stores variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 data type takes a required parameter that specifies a maximum size up to 4000 characters.

XMLFORMAT

This is an object type that is used to specify formatting arguments for SYS_XMLGEN() and SYS_XMLAGG() functions.

XMLTYPE

An Oracle-supplied type that can be used to store and query XML data in the database. It has member functions you can use to access, extract, and query the XML data using XPath expressions. XPath is another standard developed by the W3C committee to traverse XML documents.


Naming Conventions for Data Objects

The rules for naming data objects depend on the naming mode you set for Warehouse Builder in the Naming Preferences section of the Preferences dialog box. Warehouse Builder maintains a business and a physical name for each object stored in a workspace. The business name for an object is its descriptive logical name and the physical name is the name used when Warehouse Builder generates code. See "Naming Preferences" for details on how to specify a naming mode.

When you name or rename data objects, use the following naming conventions.

Naming Data Objects

In the physical naming mode, the name can be from 1 to 30 alphanumeric characters. Blank spaces are not allowed. Do not use any of the reserved words as a name of an object.

In the business naming mode, the limit is 200 characters. The name should be unique across the object category that owns the object. For example, since all tables belong to a module, table names should be unique across the module to which they belong. Similarly, module names should be unique across the project to which they belong.

Describing Data Objects

Edit the description of the data object as necessary. The description can be between 2 and 2,000 alphanumeric characters and can contain blank spaces. Specifying a description for a data object is optional.

About the Data Object Editor

The Data Object Editor provides a centralized interface to create, edit, configure, validate, and deploy Oracle data objects. You can use the Data Object Editor with relational, dimensional, and business intelligence objects. You can also view the data stored in these objects.

The Data Object Editor enables you to build your warehouse schema designs. It also provides an intuitive user interface that supports fast entry of design details. The Data Object Editor contains a menu bar, multiple toolbars, and multiple panels. All the panels are dockable. You can resize the panels or relocate them anywhere in the editor window. You can also choose to display or hide any of the panels. For more information about the Data Object Editor components, refer to the online help.

To relocate a panel, hold down the mouse button on the panel title, drag to the new location and release the mouse button. Resize a panel by placing your mouse on the panel border, pressing the mouse button when the double sided arrow appears, and dragging your mouse to indicate the desired size.

Figure 13-1 displays the Data Object Editor.

Figure 13-1 Data Object Editor Window

Description of Figure 13-1 follows
Description of "Figure 13-1 Data Object Editor Window"

Use the Data Object Editor to:

  • Create, edit, and delete relational and dimensional objects.

  • Create, edit, and delete the following business intelligence objects: Business Areas and Item Folders.

  • Define relationships between Oracle data objects.

  • Validate, generate, and deploy Oracle data objects.

  • Define and edit all aspects of a data object such as its columns, constraints, indexes, partitions, data rules, and attribute sets.

  • View impact analysis and lineage information for a data object.

  • Define implementation details for dimensional objects with a relational implementation.

  • View the data stored in a data object.

Starting the Data Object Editor

Use one of the following methods to start the Data Object Editor:

  • Select a data object in the Project Explorer. From the Design Center menu select Edit, then Open Editor.

  • Right-click a data object in the Project Explorer and select Open Editor.

  • Double-click a data object in the Project Explorer.

Data Viewer

The Data Viewer enables you to view the data stored in the data object. For example, the data viewer for a table enables you to view the table data. You can access the Data Viewer using one of the following methods:

  • From the Project Explorer, right-click a data object and select Data.

  • In the Data Object Editor for the data object, navigate to the Data Viewer tab of the Details panel. Click Execute Query.

The Data Viewer tab contains the following buttons: Execute Query, Get More, Where Clause, and More. The More button is displayed at the bottom of the tab.

Click Execute Query to execute a query on the data object and fetch its data.

By default, the Data Viewer displays the first hundred rows of data. To retrieve the next set of rows, click Get More. Alternatively, you can click More to perform the same action.

Click Where Clause to specify a condition that is used to restrict the data displayed by the Data Viewer. Clicking this button displays the Where Clause dialog box. Use this dialog box to specify the condition used to filter data. You can use this option for tables and views only.

The columns and column names displayed in the Data Object Editor are taken directly from the location in which the actual table is deployed. If the table definition in the Data Viewer does not match with what you see in the Data Object Editor, it is because the changes you made in the editor have not yet been deployed.

Using the Data Object Editor to Create Data Objects

Use the Data Object Editor to create relational, dimensional, and certain business intelligence objects. There are multiple methods of creating data objects using the Data Object Editor.

Use one of the following editor components to create a data object:

Creating Data Objects Using the Menu Bar

To create a data object using the menu bar:

  1. If it is not already open, open the Data Object Editor.

  2. Navigate to the tab that corresponds to the type of data object that you want to create.

    For example, to create a table, select the Relational tab. To create a business area, select the Business Intelligence tab. To create dimensions and cube, select the Dimensional tab.

  3. From the Diagram menu, select Add, then select the type of data object to create.

    Warehouse Builder displays the Add a New or Existing <Object> dialog box. For more information about this dialog box, click Help.

    Notice that the list of data objects in the Add menu contains some disabled items. Only the data objects that you can create from the current editor context are enabled.

  4. Select the Create a new <object> option.

    For example, to add a table, select the Create a new Table option.

  5. Specify the name of the data object using the New <Object> Name field.

    The New <Object> Name field displays a default name for the object. You can choose to retain this default name or specify a different name.

  6. Click OK.

    Warehouse Builder adds a node for the new data object to the canvas.

  7. Use the tabs of the Details panel to define the data object.

Creating a Data Object Using the Canvas

To create a data object using the canvas:

  1. If it is not already open, open the Data Object Editor.

  2. Navigate to the tab that corresponds to the type of data object that you want to create.

    For example, to create a materialized view, select the Relational tab. To create a dimension, select the Dimensional tab.

  3. Right-click whitespace (blank area) on the canvas.

    Warehouse Builder displays a shortcut menu containing the types of data objects you can create.

  4. Select the option corresponding to the type of object you want to create.

    For example, to create a materialized view, select the Add a Materialized View option.

    Warehouse Builder displays the Add a New or Existing <Object> dialog box. For more information about this dialog box, click Help.

  5. Select the Create a new <object> option.

    For example, to add a cube, select the Create a new Cube option.

  6. Specify the name of the data object using the New <Object> Name field.

    The New <Object> Name field displays a default name for the object. You can choose to retain this default name or specify a different name.

  7. Click OK.

    Warehouse Builder adds a node for the new data object to the canvas.

  8. Use the tabs of the Details panel to define the data object.

Creating a Data Object Using the Data Object Editor Palette

To create a data object using the Palette:

  1. If it is not already open, open the Data Object Editor.

  2. Navigate to the tab that corresponds to the type of data object that you want to create.

    For example, to create a view, select the Relational tab. To create a cube, select the Dimensional tab.

  3. Drag and drop the operator that corresponds to the type of object that you want to create on to the canvas.

    For example, to create a view, drag and drop the View operator from the palette on to the canvas.

    Warehouse Builder displays the Add a New or Existing <Object> dialog box. For more information about this dialog box, click Help.

  4. Select the Create a new <object> option.

    For example, to add a cube, select the Create a new Cube option.

  5. Specify the name of the data object using the New <Object> Name field.

    The New <Object> Name field displays a default name for the object. You can choose to retain this default name or specify a different name.

  6. Click OK.

    Warehouse Builder adds a node for the new data object to the canvas.

  7. Use the tabs of the Details panel to define the data object.

Using the Data Object Editor to Edit Oracle Data Objects

You edit a relational, dimensional, or business intelligence objects (item folders and business areas only) using the Data Object Editor. Use one of the following methods to open the editor for a data object:

  • In the Project Explorer, double-click the data object that you want to edit.

    For example, to edit the SALES cube, double-click the SALES cube in the Project Explorer.

  • In the Project Explorer, right-click the data object to be edited and select Open Editor.

    For example, to edit the SALES cube, right-click the SALES cube in the Project Explorer and select Open Editor.

After the Data Object Editor is displayed, use the tabs in the Details panel to edit the data object definition. For information about the Data Object Editor tabs for each data object, see:

Using Constraints

You can optionally create constraints on relational data objects such as tables, views, and materialized views.

About Constraints

Constraints are used to enforce the business rules you want to associate with the information in a database. Constraints prevent the entry of invalid data into tables. Business rules specify conditions and relationships that must always be true, or must always be false. In Warehouse Builder, you can create constraints for tables, views, and materialized views.

For example, if you define a constraint for the salary column of the employees table as Salary < 10,000, this constraint enforces the rule that no row in this table can contain a numeric value greater than 10,000 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, then Oracle displays an error message. Keep in mind that constraints slow down load performance.

You can define the following constraints for tables, views, and materialized views:

  • Unique Key (UK): A UNIQUE key constraint requires that every value in a column or set of columns (key) be unique. No two rows of a table can have duplicate values in a specified column or set of columns. A UK column can also contain a null value.

  • Primary Key (PK): A value defined on a key (column or set of columns) specifying that each row in the table can be uniquely identified by the values in the key (column or set of columns). No two rows of a table can have duplicate values in the specified column or set of columns. Each table in the database can have only one PK constraint. A PK column cannot contain a null value.

  • Foreign Key (FK): A rule defined on a key (column or set of columns) in one table that guarantees that the values in that key match the values in a PK or UK key (column or set of columns) of a referenced table.

  • Check Constraint: A user-defined rule for a column (or set of columns) that restricts inserts and updates of a row based on the value it contains for the column (or set of columns). A Check condition must be a Boolean expression which is evaluated using the values in the row being inserted or updated. For example, the condition Order Date < Ship Date will check that the value of the Order Date column is always less than that of the Ship Date column. If not, there will be an error when the table is loaded and the record will be rejected. A check condition cannot contain subqueries and sequences or SYSDATE, UID, USER, or USERENV SQL functions. While check constraints are useful for data validation, they slow down load performance.

Creating Constraints

Use the Constraints tab of the Data Object Editor to create constraints. You can create the following types of constraints: primary key, foreign key, unique key, and check constraint.

To create constraints on a table, view, or materialized view:

  1. Open the Data Object Editor for the data object to which you want to add constraints.

    In the Project Explorer, double-click the data object on which you want to define a constraint. Alternatively, you can right-click the data object in the Project Explorer and select Open Editor.

  2. Navigate to the Constraints tab.

  3. Depending on the type of constraint you want to create, refer to one of the following sections:

Creating Primary Key Constraints

To define a primary key constraint:

  1. On the Constraints tab, click the Add Constraint button.

    A blank row is displayed with the cursor positioned in the Name column.

  2. Enter the name of the constraint in the Name column.

  3. In the Type column, select Primary Key.

    Press the tab key to navigate out of the Type column or use the mouse and click the empty space in the Constraints tab.

  4. Click the Add Local Column button.

    A new row is added below the current row that contains the constraint name and constraint type. This new row displays a list in the Local Columns column.

  5. In the Local Columns list of the new row, select the name of the column that represents the primary key.

    To create a composite primary key, repeat steps 4 and 5 for each column that you want to add to the primary key.

Creating Foreign Key Constraints

To define a foreign key constraint:

  1. On the Constraints tab, click the Add Constraint button.

    A blank row is displayed with the cursor positioned in the Name field.

  2. Enter the name of the constraint in the Name column.

  3. In the Type column, select Foreign Key.

    Press the tab key to navigate out of the Type column or use the mouse and click the empty space in the Constraints tab.

  4. In the References column, click the Ellipsis button.

    The Key Selector dialog box is displayed. Use this dialog box to select the table and the column that the current column references.

  5. In the Key Selector dialog box, select the primary key constraint that the foreign key references.

    For example, the DEPARTMENTS table has a primary key called DEPT_PK defined on the department_id column. To specify that the column department_id of the EMPLOYEES table is a foreign key that references the primary key DEPT_FK, select DEPT_FK under the node that represents the DEPARTMETNS table in the Key Selector dialog box.

  6. Click OK.

    You now return to the Constraints tab of the Data Object Editor and the foreign key constraint is added.

Creating Unique Key Constraints

To define a unique key constraint:

  1. On the Constraints tab, click the Add Constraint button.

    A blank row is displayed with the cursor positioned in the Name field.

  2. Enter the name of the constraint in the Name column and press the Enter key.

    You can also press the Tab key or click any other location in the editor.

  3. In the Type column, select Unique Key.

    Press the tab key to navigate out of the Type column or use the mouse and click the empty space in the Constraints tab.

  4. Click the Add Local Column button.

    A new row is added below the current row that contains the constraint name and constraint type. This new row displays a list in the Local Columns column.

  5. In the Local Columns list of the new row, select the name of the column on which a unique key should be created.

Creating Check Constraints

To define a check constraint:

  1. On the Constraints tab, click the Add Constraint button.

    A blank row is displayed with the cursor positioned in the Name field.

  2. Enter the name of the constraint in the Name column and press the Enter key.

    You can also press the Tab key or click any other location in the editor.

  3. In the Type column, select Check Constraint.

    Press the tab key to navigate out of the Type column or use the mouse and click the empty space in the Constraints tab.

  4. In the Condition column, enter the condition to be applied for the check constraint. For example, salary > 2000. If you leave this field blank, an error is generated during validation and you cannot generate valid code for this constraint.

    The column name referenced in the check condition must exactly match the physical name defined for the table in its property sheet. Warehouse Builder does not check the syntax of the condition during validation. This may result in errors during deployment. If this happens, check the Repository Browser for details.

Editing Constraints

You can edit constraints using the Constraints tab of the Data Object Editor. You can modify the following for a constraint:

  • Rename a constraint

  • Change the type

  • Modify the check condition

  • Modify the referenced column for a foreign key constraint

  • Modify the primary key column for a primary key

Using Indexes

Use indexes to enhance query performance of your data warehouse. In Warehouse Builder, you can create indexes for tables and materialized views. For the sake of brevity, in the following sections, the word table refers to all objects for which you can define indexes.

Indexes are important for speeding queries by quickly accessing data processed in a warehouse. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Indexes have the following characteristics:

  • Indexes provide pointers to the rows in a table that contain a given key value.

  • Index column values are stored presorted.

  • Because the database stores indexes in a separate area of the database, you can create and drop indexes at any time without effecting on the underlying table.

  • Indexes are independent of the data in the table. When you delete, add, or update data, the indexes are maintained automatically.

To learn more about indexes and indexing strategies, see Oracle Database Data Warehousing Guide.

Creating Indexes

In general, you define indexes by using the Indexes tab in the Data Object Editor. To start the Data Object Editor, navigate to the table or other data object on the Project Explorer and double-click it or right-click and select Open Editor. When you select an index type, Warehouse Builder displays the appropriate template enabling you to define the index.

For all types of indexes except bitmap indexes, you can determine whether it inherits the partitioning method of the underlying table. An index that inherits its partitioning method is known as a local index while an index with its own partitioning method is known as a global index. For additional information, see "Index Partitioning".

You can create the following types of indexes in Warehouse Builder:

  • Unique: These indexes ensure that no two rows of a table have duplicate values in the key column or composite key columns.

  • Normal: Also known as non unique indexes, these are b-tree type indexes that do not impose restrictions against duplicate values in the key column or composite key columns.

  • Bitmap: These indexes are primarily used for data warehousing applications to enable the querying of large amounts of data. These indexes use bitmaps as key values instead of a list of row ids. Bitmaps are effective when the values for the index key comprise a small list. For example, AGE_GROUP could be a good index key but AGE would not.

    Bitmaps enable star query transformations which are cost-based query transformations aimed at efficiently executing star queries. A prerequisite of the star transformation is that a bitmap index must be built on each of the foreign key columns of the cube or cubes.

    When you define a bitmap index in Warehouse Builder, set its scope to LOCAL and partitioning to NONE.

  • Function-based: These indexes compute and store the value of a function or expression you define on one or more columns in a table. The function can be an arithmetic expression that contains a PL/SQL function, package function, C callout, or SQL function.

  • Composite: Also known as concatenated indexes, these are indexes on multiple columns. The columns can be in any order in the table and need not be adjacent to each other.

    To define a composite index in Warehouse Builder, create the index as you would any other index and assign between 2 and 32 index columns.

  • Reverse: For each indexed column except for the rowid column, this index reverses the bytes in the columns. Since rowid is not reversed, this index maintains the column order.

    To define a reverse index in Warehouse Builder, create the index as you would any other index and then go to the configurations window and set Index Sorting listed under the Performance Parameters to REVERSE.

Using Partitions

Partitions enable you to efficiently manage very large tables and indexes by dividing them into smaller, more manageable parts. Partitions improve query and load performance because operations work on subsets of data. Use partitions to enhance data access and improve overall application performance, especially for applications that access tables and indexes with millions of rows and many gigabytes of data.

In Warehouse Builder, you can define partitions for tables, indexes, materialized views, and MOLAP cubes. For the sake of brevity, in the following sections, the word table refers to all objects for which you can define partitions. The following sections discuss partitioning for all the objects previously listed with the exception of partitions MOLAP cubes which is described separately.

You define partitions for these objects by using the Partitions tab in the Data Object Editor. To start the Data Object Editor, navigate to the object on the Project Explorer and double-click it or right-click and select Edit. Depending on the type of partition you create, you may also need to configure tablespaces for the partitions in the Configuration panel.

You can create the following types of partitions:

  • Range Partitioning: Use range partitioning to create partitions based on a range of values in a column. When you use range partitioning with a date column as the partition key, you can design mappings that instantly update target tables, as described in "Improved Performance through Partition Exchange Loading".

  • Hash Partitioning: Use hash partitioning to direct the Oracle Database to evenly divide the data across a recommended even number of partitions. This type of partitioning is useful when data is not historical and there is no obvious column or column list.

  • Hash By Quantity Partitioning: As a means of quickly defining hash partitioning, use Hash by Quantity partitioning. This the same as hash partitioning except that you specify only a partition key and the number of partitions. The partitions are created and named automatically. You can then configure the partitions to share the same tablespace list.

  • List Partitioning: Use list partitioning to explicitly assign rows to partitions based on a partition key you select. This enables you to organize the data in a structure not available in the table.

  • Composite Partitioning: You can use Warehouse Builder to specify a composite of either range-hash, range-hash by quantity, or range-list partitioning. The Oracle Database first performs the range partitioning and then further divides the data using the second partitioning you select. For example, in range-list partitioning, you can base partitions on the sales transaction date and then further divide the data based on lists of states where transactions occurred.

  • Index Partitioning: You can define an index that inherits the partitioning method of its underlying table. Or, you can partition an index with its own partitioning strategy.

Range Partitioning

Range partitioning is the most common type of partitioning and is often used to partition data based on date ranges. For example, you can partition sales data into monthly partitions.

To use range partitioning, go to the Partitions tab in the Data Object Editor to specify a partition key and assign a name and value range for each partition you want to create. Figure 13-3 shows an example of a table partitioned into four range partitions based on the instructions below:

To partition data by ranges:

  1. On the Partitions tab in the Data Object Editor, click the first cell under Type and select Range.

    If necessary, click the plus sign to the left of Type to expand the template for the range partition.

    Figure 13-2 Partition Tab with Range Partition Selected

    Description of Figure 13-2 follows
    Description of "Figure 13-2 Partition Tab with Range Partition Selected"

  2. Select a partition key.

    Warehouse Builder lists all the columns for the object you selected. You can select a column of any data type; however, DATE is the most common partition key for range partitioning.

    You can base the partition key on multiple key columns. To add another key column, select the partition key node and click Add.

  3. Define the partitions.

    To assist you in defining the partitions, the template offers two partitions that you can edit but not delete. P1 represents the first partition and PDEFAULT represents the last partition. If you want to partition data based on month, you could rename P1 to Jan and PDEFAULT to Dec.

    The last partition is set to the keyword MAXVALUE, which represents a virtual infinite value that sorts higher than any other value for the data type, including the null value.

    To add more partitions between the first and last partitions, click the Partitions node and select Add.

    In Values, specify the greatest value for the first range and all the additional ranges you create. These values are the less than values. For example, if the first partition is for the first month of the year, then for that partition to contain values less than February 1st, type that date in the format DD/MM/YYYY.

Example of Range Partitioning

Figure 13-3 shows how to define a partition for each quarter of a calendar year.

Figure 13-3 Example Table with Range Partitioning

Description of Figure 13-3 follows
Description of "Figure 13-3 Example Table with Range Partitioning"

You can also partition data for each month or week. When you design mappings using such a table, consider enabling Partition Exchange Loading (PEL). PEL is a data definition language (DDL) operation that swaps existing partitions on the target table with new partitions. Since it is not a data manipulation language (DML) operation, the exchange of partitions occurs instantaneously.

Hash Partitioning

Hash partitioning assigns data to partitions based on a hashing algorithm that Oracle Database applies to a partitioning key you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size. Hash partitioning is a good and easy-to-use alternative to range partitioning when data is not historical and there is no obvious column or column list where logical range partition pruning can be advantageous.

To partition data based on the hash algorithm:

  1. On the Partitions tab in the Data Object Editor, click the cell below Type and select Hash.

    If necessary, click the plus sign to the left of Type to expand the template for defining hash partitions.

    Figure 13-4 Hash Partitioning

    Description of Figure 13-4 follows
    Description of "Figure 13-4 Hash Partitioning"

  2. Select a partition key.

    Warehouse Builder lists all the columns for the object you selected. You can select a column of any data type.

  3. Define the partitions.

    Warehouse Builder provides two partitions that you can rename. Click the Partitions node and select Add to add as many partitions as necessary.

    Oracle Database uses a linear hashing algorithm and, to prevent data from clustering within specific partitions, you should define the number of partitions by a power of two (for example, 2, 4, 8).

Hash By Quantity Partitioning

Use hash by quantity to quickly define hash partitioning. You define a partition key and the number of partitions and the partitions are automatically created and named. You can then configure the partitions to share the same tablespace list.

To partition data based on the hash algorithm:

  1. On the Partitions tab in the Data Object Editor, click the cell below Type and select Hash by Quantity.

    If necessary, click the plus sign to the left of Type to expand the template for defining hash by quantity partitions.

    Figure 13-5 Hash by Quantity Partitioning

    Description of Figure 13-5 follows
    Description of "Figure 13-5 Hash by Quantity Partitioning"

  2. Define the number of partitions. The default value is two partitions.

    Oracle Database uses a linear hashing algorithm and, to prevent data from clustering within specific partitions, you should define the number of partitions by a power of two (for example, 2, 4, 8).

  3. Select a partition key.

    Warehouse Builder lists all the columns for the object you selected. You can select a column of any data type.

  4. In the configuration window, define the Partition Tablespace List and Overflow Tablespace List.

List Partitioning

List partitioning enables you to explicitly assign rows to partitions. You can achieve this by specifying a list of discrete values for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

To partition data based on a list of values:

  1. On the Partitions tab in the Data Object Editor, click the cell below Type and select the partitioning method List.

    If necessary, click the plus sign to the left of Type to expand the template for defining list partitions.

    Figure 13-6 Partition Tab with List Partition Selected

    Description of Figure 13-6 follows
    Description of "Figure 13-6 Partition Tab with List Partition Selected"

  2. Select a partition key.

    Warehouse Builder lists all the columns for the object you selected. You can select a column of any data type.

  3. Define the partitions.

    PDEFAULT is set to the keyword DEFAULT and includes all rows not assigned to any other partition. A partition that captures all unassigned rows is essential for maintaining the integrity of the data.

    To assist you in defining the partitions, the template offers two partitions that you can edit but not delete. P1 represents the first partition and PDEFAULT represents the last partition.

    To add more partitions between the first and last partitions, click the Partitions node and select Add.

    In Values, type a comma separated list of values for each partition that correspond to data in the partition key you previously selected. For example, if the partition key is COUNTRY_ID, you could create partitions for Asia, Eastern Europe, Western Europe, and so on. Then, for the values for each partition, list the corresponding COUNTRY_IDs for each country in the region, as shown in Figure 13-7.

Example of List Partitioning

Figure 13-7 shows a table with data list partitioned into different regions based on the COUNTRY_ID column. Each partition has a single comma separated list.

Figure 13-7 List Partitioning Based on a Single Key Column

Description of Figure 13-7 follows
Description of "Figure 13-7 List Partitioning Based on a Single Key Column"

Figure 13-8 shows a table with data partitioned based on key columns REGION and SALES_DIVISION. Each partition includes two comma separated lists enclosed by single quotes. In this example, N, NE, S, SW, W, and NW correspond to REGION while PRD1, PRD2, PRD3, and so on correspond to SALES_DIVISION.

Figure 13-8 List Partitioning Based on Multiple Key Columns

Description of Figure 13-8 follows
Description of "Figure 13-8 List Partitioning Based on Multiple Key Columns"

Composite Partitioning

Composite partitioning methods include range-hash, range-hash by quantity, and range-list partitioning. The Oracle Database first performs the range partitioning and then further divides the data using the second partitioning you select.

The steps for defining composite partition methods are similar to defining simple partition methods such as range, hash, and list but include additional options.

To range partition data and then subpartition based on list, hash, or hash by quantity:

  1. On the Partitions tab in the Data Object Editor, click the cell below Type and select one of the composite partitioning methods.

    If necessary, click the plus sign to the left of Type to expand the template.

  2. Select a partition key and define partitions as described in "Range Partitioning".

    Figure 13-9 Range-List Partitioning with List Defined under Subpartition Template

    Description of Figure 13-9 follows
    Description of "Figure 13-9 Range-List Partitioning with List Defined under Subpartition Template"

  3. Select a column for the subpartition key.

  4. Under the subpartition template node, define the values for the second partitioning method as described in "About the Subpartition Template".

  5. Define custom subpartitions. (optional)

    For range-list partitions, you can specify custom subpartitions that override the defaults you defined under the subpartition node. For details, see "Creating Custom Subpartitions".

  6. Configure the Partition Tablespace List and Overflow Tablespace List in the configuration window.

About the Subpartition Template

Use the subpartition template to specify the second partitioning method in composite partitioning. The steps you take depend on the type of composite partition you select.

For range-hash by quantity, type in a number of subpartitions only.

For range-hash, the subpartition template enables you to type names for the subpartitions only.

For range-list, name the lists and type in the comma separated values. Be sure to preserve the last subpartition as set to DEFAULT.

Creating Custom Subpartitions

Using the subpartition template is the most convenient and likely the most common way to define subpartitions. Entries you specify under the subpartition template apply uniformly to all the partitions under the partition node. However, in some cases, you may want to override the subpartition template.

For range-hash by quantity, select a partition and then click Add Hash Count. Warehouse Builder expands the partition node to enable you to specify the number of hash subpartitions that uniquely apply to that partition.

For range-hash, select a partition and then click Add Subpartition. Warehouse Builder expands the partition node and you can name subpartitions for that partition only.

For range-list, select a partition and then click Add Subpartition. Warehouse Builder expands the partition node to enable you to specify list subpartitions for that partition only. Be sure to preserve the last subpartition as set to DEFAULT.

Figure 13-10 Subpartitions Overriding Subpartition Template

Description of Figure 13-10 follows
Description of "Figure 13-10 Subpartitions Overriding Subpartition Template"

Index Partitioning

For all types of indexes except bitmap indexes, you can determine whether the index inherits the partitioning method of the underlying table. An index that inherits the partitioning method of the underlying table is known as a local index while an index with its own partitioning method is known as a global index.

Local Index

Local indexes are partitioned on the same columns and have the same definitions for partitions and subpartitions specified on the underlying table. Furthermore, local indexes share the same tablespaces as the table.

For example, if you used range-list partitioning to partition a table of sales data by quarter and then by region, a local index is also partitioned by quarter and then by region.

Bitmap indexes can only be defined as local indexes to facilitate the best performance for querying large amounts of data.

To define an index as local in Warehouse Builder set the Scope to LOCAL and Partitioning to NONE.

Global Index

A global index is one in which you can partition the index independently of the partition strategy applied to the underlying table. You can choose between range or hash partitioning. The global index option is available for all indexes except bitmap indexes.

In releases before Oracle 10g, Oracle recommended that you not use global indexes for data warehouse applications because deleting partitions on the table during partition maintenance would invalidate the entire index and result in having to rebuild the index. Beginning with Oracle 10g, this is no longer a limitation as global indexes are no longer negatively affected by partitioning maintenance.

Nonetheless, local indexes are likely to be the preferred choice for data warehousing applications due to ease in managing partitions and the ability to parallelize query operations.

A global index is useful when you want to specify an index partition key other than any of the table partition keys. In such cases, ensure that there are no duplicate rows in the index key column and select unique for the index type.

Index Performance Considerations

As you decide which type of index to use, consider that indexes rank in performance in the following order:

  1. Unique and local index

  2. Unique and global index

  3. All other non unique indexes (normal, bitmap, function based) and local index

Configuring Partitions

For some but not all partitioning methods, you must configure partition tablespaces.

You can access the parameters for partitions either from the Project Explorer or the Data Object Editor. In the Project Explorer, right-click the table, select Configure, and scroll down to view Partition Parameters. Or, in the Data Object Editor, select the Configuration panel and expand the Partition Parameters node.

Partition Tablespace List

Type a comma separated list of tablespaces when you partition by any of the following methods: Hash by Quantity, Range-List, Range-Hash, or Range-Hash by Quantity.

If you neglect to specify partition tablespaces, the default tablespaces associated with the table are used and the performance advantage for defining partitions is not realized.

Overflow Tablespace List

Type a comma separated list of tablespaces when you partition by the method Hash by Quantity. If you provide a list of tablespaces less than the number of partitions, the Oracle Database cycles through those tablespaces.

If you neglect to specify overflow tablespaces, the default tablespaces associated with the table are used and the performance advantage for defining partitions is not realized when the limits for the partition tablespaces are exceeded.

Using Tables

Tables are metadata representations of relational storage objects. They can be tables from a database system such as Oracle tables or even tables from an SAP system. The following sections provide information about creating and using tables:

Creating Table Definitions

The table you create captures the metadata used to model your target schema. This table definition specifies the table constraints, indexes, partitions, attribute sets, and metadata about the columns and data types used in the table. This information is stored in the workspace. You can later use these definitions to generate .ddl scripts that can be deployed to create physical tables in your target database. These tables can then be loaded with data from chosen source tables.

Use the Data Object Editor to create a table. Follow the steps listed below:

  1. From the Project Explorer, expand the Databases node and then the Oracle node.

  2. Expand the module where you want to create the table.

  3. Right-click Tables and select New.

    Warehouse Builder displays the Data Object Editor. Use the following tabs in the Table Details panel of the Data Object Editor to define the table.

    The Data Viewer tab enables you to view the data stored in the workspace table. For more information about the Data Viewer tab, see "Data Viewer".

    After you finish defining the table using these tabs, the table definitions are created and stored in the workspace. The new table name in also added to the Project Explorer.

Note:

You can also create a table from the Mapping Editor.

Name Tab

Follow the rules in Naming Conventions for Data Objects to specify a name and an optional description.

Columns Tab

Use the Columns tab to define the columns in the table. This tab displays a table that you use to define columns. Each row in the table corresponds to the definition of one table column. Warehouse Builder generates the column position in the order in which you type in the columns. To re-order columns, see "Reordering Columns in a Table".

Enter the following details for each column:

  • Name: Type the name of the column. The column name should be unique within the table. Reserved words are not allowed.

  • Data Type: Select the data type of the column from the Data Type list. Warehouse Builder assigns a default data type for each column based on the column name. For example, if you create a column named start_date, the data type assigned is DATE. You can change the default assignment if it does not suit your data requirement.

    For a list of supported Oracle Database data types, see "Supported Data Types".

  • Length: Specify the length of the attribute. Length is applicable for character data types only.

  • Precision: Specify the total number of digits allowed for the column. Precision is applicable for numeric data types only.

  • Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable for numeric data types only.

  • Seconds Precision: Used for TIMESTAMP data types only. Specify the number of digits in the fractional part of the datetime field.

  • Not Null: Select this field to specify that the column should not contain null values. By default, all columns in a table allow nulls.

  • Default Value: Specify the default value for this column. If no value is entered for this column while loading data into the table, the default value is used. If you specify a value for this column while loading data, the default value is overridden and the specified value is stored in the column.

  • Description: Type an optional description for the column.

Constraints Tab

Use the Constraints tab to create constraints on the table columns. You can create primary keys, foreign keys, unique keys, and check constraints. For more information about creating constraints, see "Creating Constraints".

Indexes Tab

Use the Indexes tab to create indexes on the table. You can create the following types of indexes: unique, normal, bitmap, function-based, composite, and reverse.

For more information about creating these indexes, see "Creating Indexes".

Partitions Tab

Use the Partitions tab to create partitions for the table. You can create the following types of partitions: range, hash, hash by quantity, list, composite, and index.

For more information about these partitions and how to create each type of partition, see "Using Partitions".

Attribute Sets Tab

Use the Attribute Sets tab to create attribute sets for the table. You can create user-defined and bridge attribute sets. For more information about creating attribute sets for a table, see "Using Attribute Sets".

Data Rules Tab

Use the Data Rules tab to apply data rules to a table. Data rules enable you to determine legal data within a table and legal relationships between tables. When you apply a data rule to a table, Warehouse Builder ensures that the data in the table is according to the specified data rule. For more information about data rules, see "About Data Rules".

Before you apply a data rule to a table, the data rule should have been defined in the workspace. For information about creating data rules, see "Creating Data Rules".

To apply a data rule to a table, click the Apply Rule button on the Data Rules tab. The Apply Data Rule Wizard is displayed. Use this wizard to select the data rule and the column to which the data rule should be applied. For more information about using the Apply Data Rule Wizard, see "Applying Data Rules to Objects".

The Applied Rules section of the Data Rules tab displays the data rules that are bound to the table. For a data rule to be applied to a table, ensure that the check box to the left of the data rule name is selected. Deselecting this option will result in the data rule not being applied to the table.

The Binding column of the Bindings section displays the table column to which the data rule is bound.

Editing Table Definitions

Use the Data Object Editor to edit table definitions. To launch the Data Object Editor, right-click the name of the table in the Project Explorer and select Open Editor. Alternatively, you can double-click the name of the table in the Project Explorer.

The following sections describe the table definitions that you can edit.

Renaming a Table

Navigate to the Name tab of the Data Object Editor. Click the Name field and enter the new name for the table. You can also modify the description stored in the Description field.

Adding, Modifying, and Removing Table Columns

Adding a column: Navigate to the Columns tab. Click the Name field in an empty row and enter the details that define a column. For more information, see "Columns Tab".

Modifying a column: Use the Columns tab of the Data Object Editor to modify column definitions. You can modify any of the attributes of the column definition. For more information, see "Columns Tab".

Removing a column: Navigate to the Columns tab. Right-click the gray cell to the left of the column name you want to remove and select Delete.

Adding, Modifying, and Deleting Table Constraints

Navigate to the Constraints tab of the Data Object Editor.

For details on adding and editing constraints, see "Creating Constraints" and "Editing Constraints" respectively.

To delete a constraint, select the row that represents the constraint by clicking the gray cell to the left of the column name. Click Delete at the bottom of the tab.

Adding, Editing, and Deleting Attribute Sets

For details about adding attribute sets, see "Creating Attribute Sets". See "Editing Attribute Sets" for instructions on how to edit an attribute set.

To delete an attribute set, navigate to the Attribute Sets tab. Right-click the cell to the left of the attribute set that you want to remove and select Delete.

Reordering Columns in a Table

By default, columns in a table are displayed in the order they are created. This order is also propagated to the DDL script generated to create the table. If this default ordering does not suit your application needs, or if you want to further optimize query performance, you can reorder the columns.

To change the position of a column:

  1. If the Data Object Editor is not already open for the table, open the editor.

    You can do this by double-clicking the name of the table in the Project Explorer. Alternately, you can right-click the name of the table in the Project Explorer and select Open Editor.

  2. On the Columns tab, select the gray square located to the left of the column name.

    The entire row is highlighted.

  3. Use the buttons on the left of the Columns tab to move the column to the required position.

    The position of the column is now updated.

  4. Close the Data Object Editor.

For the change in the column position to be reflected in the table stored in the workspace you must deploy the changed table definition.

Using Views

You can define views and materialized views. This section describes views. For information about materialized views, see "Using Materialized Views".

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.

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

For information about using views, refer to:

Creating View Definitions

A view definition specifies the query used to create the view, constraints, attribute sets, data rules, and metadata about the columns and data types used in the view. This information is stored in the workspace. You can generate the view definition to create .ddl scripts. These scripts can be deployed to create the physical views in your database.

The Data Object Editor enables you to create a view definition. Use the following steps to create a view definition:

  1. From the Project Explorer, expand the Databases node and then the Oracle node.

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

  3. Right-click Views and select New.

    Warehouse Builder displays the Data Object Editor for the view.

    Note:

    You can also define a View from the Mapping Editor and model your own query.
  4. Define the view by specifying information about the following Data Object Editor tabs:

  5. Close the Data Object Editor.

    Warehouse Builder creates a definition for the view, stores this definition in the workspace, and inserts its name in the Project Explorer.

Name Tab

Follow the rules in Naming Conventions for Data Objects to specify a name and an optional description.

Columns Tab

Use the Columns tab to define the columns in the view. This tab displays a table that you use to define the view columns. Each row in this table corresponds to one view column definition. For each view column, enter the following details: Name, Data Type, Length, Precision, Scale, Seconds Precision, Not Null, Default Value, and Description. For an explanation of these fields see "Columns Tab".

Query Tab

Use the Query tab to define the query used to create the view. A view can contain data from tables that belongs to a different module than the one to which the view belongs. You can also combine data from more then one table using joins.

Ensure that the query statement you type is valid. Warehouse Builder does not validate the text in the Query tab and will attempt to deploy a view even if the syntax is invalid.

Constraints Tab

Use this page to define logical constraints for a view. Although these constraints are not used 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.

Note:

You cannot create check constraints for views.

For more information about creating constraints, see "About Constraints".

Attribute Sets Tab

Use the Attribute Sets tab to define attribute sets for the view. For more information about attribute sets and how to create them, see "Using Attribute Sets".

Data Rules Tab

Use the Data Rules tab to specify the data rules that are applied to the view. For more information about the Data rules tab, see "Data Rules Tab".

Data Viewer Tab

The Data Viewer tab enables you to view the data stored in the underlying database table on which the view is based. For more on this tab, see "Data Viewer".

Editing View Definitions

Use the Data Object Editor to edit view definitions. To open the Data Object Editor, right-click the view in the Project Explorer and select Open Explorer. The following sections describe the view definitions that you can edit.

Renaming a View

Navigate to the Name tab of the Data Object Editor. Click the Name field and enter the new name for the view. You can also modify the description stored in the Description field. Type the new name over the highlighted object name.

Alternately, you can rename a view by right-clicking the view name in the Project Explorer and selecting Rename.

Adding, Editing, and Removing View Columns

Adding a column: Navigate to the Columns tab. Click the Name field in an empty row and enter the details that define a column. For more information about these details, see "Columns Tab".

Removing a column: Navigate to the Columns tab. Right-click the gray cell to the left of the column name you want to remove and select Delete.

Adding, Editing, and Deleting View Constraints

Navigate to the Constraints tab of the Data Object Editor. For details on adding and editing constraints, see "Creating Constraints" and "Editing Constraints" respectively.

To delete a constraint, on the Constraints tab, select the row that represents the constraint. Click Delete at the bottom of the tab.

Adding, Editing, and Removing Attribute Sets

For details about adding attribute sets, see "Creating Attribute Sets". See "Editing Attribute Sets" for instructions on how to edit an attribute set.

To delete an attribute set, navigate to the Attribute Sets tab. Right-click the cell to the left of the attribute set that you want to remove and select Delete.

Using Materialized Views

You can define views and materialized views. This section discusses materialized views. For information about conventional views, see "Using Views".

The following sections provide information about using materialized views:

About Materialized Views

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, you can configure it to take advantage of the query rewrite and fast refresh features available in Oracle Database. For information about query rewrite and fast refresh, "Fast Refresh for Materialized Views".

Creating Materialized View Definitions

A materialized view definition specifies the query used to create the materialized view, constraints, indexes, partitions, attribute sets, data rules, and metadata about the columns and data types used in the materialized view. You can generate the view definition to obtain .ddl scripts that are used to deploy the materialized view.

The Data Object Editor enables you to create materialized views. To create a materialized view:

  1. From the Project Explorer 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 New.

    Warehouse Builder displays the Data Object Editor for this materialized view.

    Note:

    You can also define a Materialized View from the Mapping Editor.
  4. Specify information about the following tabs of the Data Object Editor to create the materialized view definition:

  5. Close the Data Object Editor.

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

Name Tab

Follow the rules in Naming Conventions for Data Objects to specify a name and an optional description.

Columns Tab

Use the Columns tab to define the materialized view columns. This tab displays a table that enables you to define the columns. A row in the table corresponds to one column in the materialized view. For each column specify the following details: Name, Data Type, Length, Precision, Scale, Seconds Precision, Not Null, Default Value, and Description. For more details, see "Columns Tab".

Query Tab

Use the Query tab to define the query used to create the materialized view. Ensure that you type a valid query in the Select Statement field. 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 in the Query tab. Warehouse Builder does not validate the text in the Query tab and will attempt to deploy a materialized view even if the syntax is invalid.

Constraints Tab

Use the Constraints tab to define constraints for the materialized view. Defining constraints is optional. These constraints are for logical design purposes only and are not used when enumerating DDL for the view. For information about creating constraints, see "Creating Constraints".

Note:

You cannot create check constraints for views.

Indexes Tab

Use the Indexes tab to define indexes on the materialized view. Defining indexes is optional. You can create the following types of indexes: Unique, Normal, Bitmap, Function-based, Composite, Reverse.

For information about creating indexes, see "Creating Indexes".

Partitions Tab

Use the Partitions tab to define partitions on the materialized view. Partitioning a materialized view is optional. You can perform Index Partitioning, Range Partitioning, Hash Partitioning, Hash By Quantity Partitioning, List Partitioning, or Composite Partitioning.

Attribute Sets Tab

Use the Attribute Sets tab to define attribute sets for the materialized view. Defining attribute sets is optional. The types of attribute sets you can create are user-defined and bridge. For information about how to define attribute sets, see "Creating Attribute Sets".

Data Rules Tab

Use the Data Rules tab to specify data rules that should be applied to the materialized view data.

Editing Materialized View Definitions

Use the Data Object Editor to edit a materialized view definition. To open the Data Object Editor, right-click the materialized view and select Open Editor. The following sections describe the definitions that you can edit for a materialized view.

Renaming Materialized Views

Double-click the Name field on the Name tab of the editor. This selects the name. Type the new name.

Alternately, in the Project Explorer, right-click the materialized view name and select Rename. Type the new name over the highlighted object name.

Adding, Editing, and Deleting Materialized View Columns

Adding a column: Navigate to the Columns tab. Click the Name field in an empty row and enter the details for the column. For more information about these details, see "Columns Tab".

Removing a column: Navigate to the Columns tab. Right-click the gray cell to the left of the column name you want to remove and select Delete.

Adding, Editing, and Deleting Materialized View Constraints

Navigate to the Constraints tab of the Data Object Editor. For details on adding and editing constraints, see "Creating Constraints" and "Editing Constraints" respectively.

To delete a constraint, on the Constraints tab, select the row that represents the constraint. Click Delete at the bottom of the tab.

Adding, Editing, and Deleting Attribute Sets

For details about adding attribute sets, see "Creating Attribute Sets". See "Editing Attribute Sets" for instructions on how to edit an attribute set.

To delete an attribute set, navigate to the Attribute Sets tab. Right-click the cell to the left of the attribute set that you want to remove and select Delete.

Using Attribute Sets

An attribute set contains a chosen set of columns in the order you specify. Attribute sets are useful while defining a mapping or during data import and export. Warehouse Builder enables you to define attribute sets for tables, views, and materialized views. For the sake of brevity, in the following sections, the word table refers to all objects for which you can define attribute sets

For each table, a predefined attribute set is generated containing all the columns in that table. In addition, predefined attribute sets are generated for each defined constraint. Predefined attribute sets cannot be modified or deleted.

Creating Attribute Sets

Use the Attribute Sets tab of the Data Object Editor to create attribute sets. You can create the following types of attribute sets:

  • User-defined: Optional attribute sets that can be created, modified, or deleted in the Table Properties window.

  • Bridge: Optional attribute sets that can be can be exported to and viewed in another application such as Oracle Discoverer. You can create, modify, or delete bridge-type attribute sets in the Table Properties window. An object can only have one bridge-type attribute set.

To add an attribute set to a table:

  1. From the Project Explorer, right-click the table name and select Open Editor.

    The Data Object Editor for the table is displayed.

  2. Select the Attribute Sets tab.

    This tab contains two sections: Attribute sets and Attributes of the selected attribute set.

    The Attribute sets section displays the attribute sets defined for the table. It contains three columns that define each attribute set: Name, Type, and Description.

    The Attributes of the selected attribute set section lists all the attributes in the table. The attributes that are selected using the Include column are the ones that are part of the attribute set that is selected in the Attribute sets of the entity section.

  3. In the Attribute sets of the entity section, click the Name field of an empty row and enter a name for the attribute set.

    In physical mode, you must type a name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type up to 200 valid characters. Spaces are allowed. The attribute set name must be unique within the object.

    Notice that all the table attributes are displayed in the Attributes of the selected attribute set section.

  4. In the Type list, select the type of attribute set as USER_DEFINED or BRIDGE_TYPE.

  5. Optionally, you can enter a description for the attribute set using the Description column.

  6. In the Attributes of the selected attribute set section, select Include for each attribute you want to include in the attribute set. The order in which you select the columns determines their initial order in the attribute set.

    Select Select All to select all the displayed columns in the attribute set. Select Deselect All to exclude all the columns from the attribute set. To remove a column from the attribute set, deselect Include.

  7. If you selected BRIDGE-TYPE, click Advanced.

    Warehouse Builder displays the Advanced Attribute Set Properties dialog box.

  8. For each attribute in the bridge-type attribute set, specify the following properties. These properties determine how the objects appear and display in Oracle Discoverer.

    Hidden: Select Hidden to hide unused or obsolete columns when the table is viewed in another application. In the Discoverer Administration Edition, hidden columns are grayed out. In the Discoverer Plus Edition, hidden columns are not displayed.

    Aggregation: Select an aggregation for numerical attributes SUM, MIN, MAX, AVG, COUNT, or DETAIL for no aggregation. The default is SUM.

    Position: Select the default attribute position: DATA POINT, PAGE, SIDE, TOP, or TOP/SIDE. The default is DATA POINT.

    Item Class: Check for TRUE or uncheck for FALSE. The default is FALSE.

    Heading: Type the heading text.

    Format: Type the text for the format field.

  9. Click OK to close the Advanced Attribute Set Properties dialog box.

Editing Attribute Sets

Use the Attribute Sets tab of the Data Object Editor to edit attribute sets. You can perform the following actions when you edit an attribute set. Before you edit an attribute set, ensure that the Data Object Editor is open for the object that contains the attribute set. Also, navigate to the Attribute Sets tab of the Data Object Editor.

  • Rename the attribute set

    Click the name of the attribute set in the Name column of the Attribute sets of the entity section and type the new name.

  • Modify the type of attribute set

    Use the Type list in the Attribute sets of the entity section to modify the type of attribute set.

  • Add or remove attributes from the attribute set

    Adding attributes to an attribute set: Select the attribute set to which you want to add attributes by clicking the gray cell to the left of the attribute set name in the Attribute sets of the entity section. In the Attributes of the selected attribute set section, click Include for each attribute that you want to include in the attribute set.

    Removing attributes from an attribute set: Select the attribute set from which you want to remove attributes by clicking the gray cell to the left of the attribute set. In the Attributes of the selected attribute set section, click Include for the attribute that you want to remove from the attribute set.

  • Change the order in which the attributes appear in the attribute set

    Use the buttons to the left of the Attributes of the selected attribute set section to change the order of the attributes in the attribute set. Click the gray cell to the left of an attribute and use the buttons to move the attribute up or down in the order.

  • Delete the attribute set

    Right-click the gray cell to the left of the attribute set name and select Delete.

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, you are creating sequence definitions that are saved in the workspace. 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

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, these attributes are created.

You can also import sequence definitions from existing source systems using the Import Object Wizard.

Creating a Sequence Definition

To create a new sequence:

  1. From the Project Explorer, expand the warehouse module node.

  2. Right-click Sequences and select New from the menu.

    Warehouse Builder displays the Create Sequence Wizard.

  3. Use the Name tab to specify a name and an optional description for the table. In addition to the rules listed in "Naming Conventions for Data Objects", the name must be unique across the module.

  4. Click OK.

    Warehouse Builder stores the definition for the sequence and inserts its name in the Project Explorer.

Editing Sequence Definitions

Use the Edit Sequence dialog box to edit a sequence definition. You can edit the name, description, and column notes of a sequence.

To edit sequence properties, right-click the name of the sequence from the Project Explorer and select Open Editor. Or double-click the name of the sequence. The Edit Sequence dialog box is displayed. This dialog box contains two tabs: Name Tab and Columns Tab.

Click these tabs to perform the following tasks:

  • Rename a sequence

  • Edit sequence columns

Name Tab

Follow the rules in Naming Conventions for Data Objects to specify a name and an optional description.

You can also rename a sequence by right-clicking the sequence name in the Project Explorer and selecting Rename.

Columns Tab

The Columns tab displays the sequence columns CURRVAL and NEXTVAL. You can edit the descriptions of these columns.

Editing Sequence Column Descriptions

To edit the column descriptions of a sequence:

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

    The Sequence Editor dialog box opens.

  2. Select the Columns tab.

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

Using User-Defined Types

User-defined data types use Oracle built-in data types and other user-defined data types as the building blocks of object types that model the structure and behavior of data in applications. The built-in data types are mostly scalars and do not provide the same flexibility that modelling an application specific data structure does.Consider a simple example of a customers table. The Customers address information is usually modeled as four or five separate fields, each with an appropriate scalar type. User defined types allow for a definition of 'address' as a composite type and also to define validation on that type.

A user-defined data type extends the modeling capabilities of native data types. User defined data types specify both the underlying persistent data (attributes) and the related behaviors (methods).

With user-defined types, you can create better models of complex entities in the real world by binding data attributes to semantic behavior.

Creating User-Defined Types

User-defined types are built from a combination of one or more simple data types. Integers, characters, and byte strings are examples of simple data types.

This section provides an overview of the following user data types

  • Objects

  • Varrays

  • Nested Tables

About Object Types

Object types are abstractions of the real-world entities, such as purchase orders, that application programs deal with. It is a heterogeneous user defined type. It is made up of one or more user defined types or scalar types.

An object type is a schema object with the following components:

  • Name: A name identifies the object type uniquely within that schema.

  • Attributes: An attribute is used to create the structure and state of the real-world entity that is represented by an object. Attributes can be built-in types or other user-defined types.

  • Methods: A method contains functions or procedures that are written in PL/SQL or Java and stored in the database, or written in a language such as C and stored externally. Methods are code-based representations of the operations that an application can perform on the real-world entity.

    Note:

    Methods are currently not supported.

For example, the address type definition can be defined as follows:

CREATE TYPE ADDRESS AS OBJECT ( street_name varchar2(240) , door_no varchar2(30) , po_box_no number , city varchar2(35) , state varchar2(30), country varchar2(30)).

Once the type has been defined it can be used across the schema for any table that requires the type definition 'address' as one of its fields.

Creating Object Types

To create an object type:

  1. In Project Explorer, expand the Databases node and then the Oracle node.

  2. Expand the module in which you want to create the object type.

  3. Expand the User Defined Types node.

  4. Right-click Object Types and select New.

    The Data Object Editor is displayed. Use the following tabs on the ObjectType Properties panel of the Data Object Editor to define the object type:

Name Tab

Follow the rules in Naming Conventions for Data Objects to specify a name and an optional description.

Columns Tab

Use the Columns tab to define the columns in the object type. This tab displays a list of attributes that you can use to define columns. Each row in the attribute corresponds to the definition of one object column.

Specify the following details for each column:

  • Name: Enter the name of the column. The column name must be unique within the object type. Reserved words are not allowed.

  • Data Type: Select the data type of the column from the Data Type list. Warehouse Builder assigns a default data type for the column based on the column name. For example, if you create a column named start_date, the data type assigned is DATE. You can change the default assignment if it does not suit your data requirement.

    See also:

    "Supported Data Types" for a list of supported Oracle Database data types.
  • Length: Specify the length of the column. Length is applicable for character data types only.

  • Precision: Specify the total number of digits allowed for the column. Precision is applicable for numeric data types only.

  • Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable for numeric data types only.

  • Seconds Precision: Specify the number of digits in the fractional part of the datetime field. Seconds precision is used for TIMESTAMP data types only.

  • Not Null: Select this field to specify that the column should not contain NULL values. By default, all columns in a table allow nulls. This column is not applicable for Object types.

  • Default Value: Specify the default value for this column. If no value is entered for this column while data is stored in the table, then the default value is used. If you specify a value for this column while loading data, then the default value is overridden and the specified value is stored in the column. This column is not applicable for Object types.

  • Description: Type a description for the column. This is optional.

Editing Object Types

To edit an object type:

  1. In Project Explorer, expand the Databases node and then the Oracle node.

  2. Expand the module where you want to create the object type.

  3. Expand the User Defined Types node.

  4. Expand the Object Types node.

  5. Right-click the object type you want to edit and select Open Editor.

The Data Object Editor is displayed. Use the Name and Columns tabs as defined in the Creating Object Types section to edit the definition of the object type.

About Varrays

A varray is an ordered collection of data elements. The position of each element in a varray is stored as an index number. You can use this number to access particular elements. When you define a varray, you specify the maximum number of elements it can contain. You can change this number later. Varrays are stored as opaque objects (such as RAW or BLOB).

If the customer has more than one address , for example three addresses, then you can create another type , a table type, that holds three addresses. The following example creates a table of address type:

TYPE address_store is VARRAY(3) of address;

A Varray is an ordered set of elements. Therefore the first address in the list is considered as the primary address, and the remaining addresses are considered as the secondary addresses.

Creating Varrays

To create a varray:

  1. From the Project Explorer, expand the Databases node and then the Oracle node.

  2. Expand the module where you want to create the varray.

  3. Expand the User Defined Types node.

  4. Right-click Varrays and select New.

    The Data Object Editor is displayed. Use the following tabs on the Varray Details panel of the Data Object Editor to define the object type:

Name Tab

Follow the rules in Naming Conventions for Data Objects to specify a name and an optional description.

Details Tab

Use the Details tab to specify the value for the following fields:

  • Length: Specify the length of the varray element. Length is applicable for character data types only.

  • Precision: Specify the total number of digits allowed for the varray element. Precision is applicable for numeric data types only.

  • Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable for numeric data types only.

  • Seconds Precision: Specify the number of digits in the fractional part of the datetime field. Seconds precision is used for TIMESTAMP data types only.

  • Size: Specify the size of the varray.

Editing Varrays

To edit a varray, use the following steps:

  1. From the Project Explorer, expand the Databases node and then the Oracle node.

  2. Expand the module where you want to edit the Varray type.

  3. Expand the User Defined Types node.

  4. Expand the Varrays node.

  5. Right-click the varray you want to edit and select Open Editor.

The Data Object Editor is displayed. Use the Name and Details tabs as defined in the Creating Varrays section to edit the definition of the varray.

About Nested Tables

A nested table is an unordered collection of data elements. Nested tables enable you to have any number of elements. There is no maximum number of elements specified in the definition of the table. The order of the elements is not preserved. All the operations, such as SELECT, INSERT, and DELETE, that you perform on ordinary tables can be performed on nested tables. Elements of a nested table are stored in a separate storage table containing a column that identifies the parent table row or object to which each element belongs. The elements may be built-in types or user-defined types. You can view a nested table as a single-column table, or if the nested table is an object type, as a multi-column table, with a column for each attribute of the object type.

Nested Tables are used to store an unordered set of elements that do not have a predefined size. An example of this would be customer references.

Creating Nested Tables

To create a nested table:

  1. From the Project Explorer, expand the Databases node and then the Oracle node.

  2. Expand the module where you want to create the nested table.

  3. Expand the User Defined Types node.

  4. Right-click Nested Tables and select New.

    The Data Object Editor is displayed. Use the following tabs on the Nested Table Details panel of the Data Object Editor to define the object type.

Name Tab

Follow the rules in Naming Conventions for Data Objects to specify a name and an optional description.

Details Tab

Use the Details tab to specify the value for the following fields:

  • Length: Specify the length of the nested table element. Length is applicable for character data types only.

  • Precision: Specify the total number of digits allowed for the nested table element. Precision is applicable for numeric data types only.

  • Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable for numeric data types only.

  • Seconds Precision: Specify the number of digits in the fractional part of the datetime field. Seconds precision is used for TIMESTAMP data types only.

Editing Nested Tables

To edit a nested table, use the following steps:

  1. From the Project Explorer, expand the Databases node and then the Oracle node.

  2. Expand the module where you want to edit the Nested Table.

  3. Expand the User Defined Types node.

  4. Expand the Nested Tables node.

  5. Right-click the nested table you want to edit and select Open Editor.

The Data Object Editor is displayed. Use the Name and Details tabs as defined in the Creating Nested Tables section to edit the definition of the nested table.

Configuring Data Objects

Earlier in the design phase, you defined a logical model for your target system using Warehouse Builder design objects. This section discusses how you assign physical properties to those design objects.

This section includes:

Configuring Design Objects

In this phase, you assign physical deployment properties to the object definitions by configuring properties such as tablespaces, partitions, and other identification parameters. You also configure runtime parameters such as job names, and runtime directories.

Set these physical properties using the Configuration Properties dialog box. The following sections show you how to assign physical properties to your logical design model.

Configuring Target Modules

Each target module provides top level configuration options for all the objects contained in that module.

To configure a Target Module:

  1. From the Project Explorer, expand Databases, expand Oracle, and right-click a target module name and select Configure.

    Warehouse Builder displays the Configuration Properties dialog box.

  2. Choose the parameters you want to configure and click the space to the right of the parameter name to edit its value.

    For each parameter, you can either select an option from a list, type a value, or click the Ellipsis button to display another properties dialog box.

  3. Configure the parameters listed in the following sections.

Identification

Main Application Short Name: This parameter is obsolete and is no longer used.

Application Short Name: This parameter is obsolete and is no longer used.

Location: Represents the location with which the module is associated. If the module is a source module, this value represents the location from which the data is sourced. If the module is a target module, this value represents the location to which the generated code and object data is deployed.

Top Directory: Represents the name of the directory to in which the generated code is stored. The default value for this parameter is ..\..\codegen\. You can change this value to any directory in which you want to store generated code.

Deployable: Select this option to indicate that the objects contained in the module can be deployed.

Streams Administrator: This parameter will be used in future releases.

Tablespace Defaults

Default Index Tablespace: Defines the name of each tablespace where indexes are created. The default is null. If you configure an index tablespace at the target module level and not at the object level, the tablespace value configured at the target module level is used during code generation. If you configure a tablespace for each index at the object level the tablespace value configured at the target module level is overwritten.

Default Object Tablespace: Defines the name of each tablespace where objects are created, for example, tables, views, or materialized views. The default is null. If you configure object tablespace at the target module level and not at the individual object level, the value configured at the target module level is used during code generation. If you configure a tablespace for each individual object, the tablespace value configured at the target module level is overwritten.

Generation Preferences

End of Line: Defines the end of line markers for flat files. This depends on the platform to which you are deploying your warehouse. For UNIX, use \n, and for NT, use \r\n.

Deployment System Type

PL/SQL Generation Mode: Defines the target database type. Code generation is based on the your choice in this field. For example, select Oracle 9i to ensure the use of Oracle 9i code constructs. If you select Oracle 8i, row-based code is generated.

Each release introduces new functionality, some of which you may use only in conjunction with the latest version of the Oracle Database. For example, if you select Oracle 8i as the PL/SQL Generation Mode, you cannot access some Oracle 9i Warehouse Builder components such as Table Functions and External Tables. For a list of components not compatible with prior releases of the Oracle Database, see Oracle Warehouse Builder Release Notes.

Run Time Directories

Receive Directory: Not currently used. The default is receive\.

Input Directory: Not currently used. The default is input\.

Invalid Directory: Directory for Loader error and rejected records. The default is invalid\.

Work Directory: Not currently used. The default is work\.

Sort Directory: Not currently used. The default is sort\.

Log Directory: Log directory for the SQL*Loader. The default is log\.

Archive Directory: Not currently used. The default is archive\.

Generation Target Directories

DDL Directory: Type a location for the scripts that create database objects in the target schema. The default is ddl\.

DDL Extension: Type a file name extension for DDL scripts. The default is .ddl.

DDL Spool Directory: Type a buffer location for DDL scripts during the script generation processing. The default is ddl\log.

LIB Directory: Type a location for the scripts that generate Oracle functions and procedures. The default is lib\.

LIB Extension: Type a suffix to be appended to a mapping name. The default is .lib.

LIB Spool Directory: Type a location for the scripts that generate user-defined functions and procedures. The default is lib\log\.

PL/SQL Directory: Type a location for the PL/SQL scripts. The default is pls\.

PL/SQL Run Parameter File: Type a suffix for the parameter script in a PL/SQL job. The default is _run.ini.

PL/SQL Spool Directory: Type a buffer location for PL/SQL scripts during the script generation processing. The default is pls\log\.

PL/SQL Extension: Type a file name extension for PL/SQL scripts. The default is .pls.

Staging File Directory: For all ABAP configuration related to SAP tables, see Chapter 6, "Integrating with Applications".

ABAP Extension: File name extension for ABAP scripts. The default is .abap.

ABAP Run Parameter File: Suffix for the parameter script in an ABAP job. The default is _run.ini.

ABAP Spool Directory: The location where ABAP scripts are buffered during script generation processing.

LOADER Directory: Type a location for the control files. The default is ctl\.

LOADER Extension: Type a suffix for the loader scripts. The default is .ctl.

LOADER Run Parameter File: Type a suffix for the parameter initialization file. The default is _run.ini.

Configuring Tables

Warehouse Builder generates DDL scripts for each table defined in a target module. Follow these steps to configure a table.

To configure the physical properties for a table, right-click the name of a table and select Configure. The Configuration Properties dialog box displays. Set the configuration parameters listed in the following sections.

Identification

  • Deployable: Select this option to indicate that you want to deploy this table. Scripts are generated only for table constraints marked deployable.

  • Error Table Only: Select this option to perform generation or deployment actions only on the error table associated with a table. Use this option when you want to add an error table to an existing database table. This setting only controls the actions of the Deployable parameter, but does not override it.

    Unselect this option to deploy the error table along with the primary table.

Storage Parameters

Storage parameters enable you to define how the table is stored in the database. This category contains parameters such as BUFFER_POOL, FREELIST GROUPS, FREELISTS, INITIAL, MINEXTENTS, MAXEXTENTS, NEXT, and PCTINCREASE.

The Tablespace parameter defines the name of each tablespace where the table is created. The default value is null. If you accept the default value of null, the table is generated based on the tablespace value set in the target module configuration properties. If you configure the tablespace for individual objects, the tablespace value configured for the target module is overwritten.

Parallel

  • Parallel Access Mode: Enables parallel processing when the table has been created. The default is PARALLEL.

  • Parallel Degree: Indicates the degree of parallelism. This is the number of parallel threads used in the parallel operation.

Performance Parameters

  • Buffer Cache: Indicates how Oracle should store rows in the buffer cache.

  • Data Segment Compression: Indicates whether data segments should be compressed. Compressing reduces disk use. The default is NOCOMPRESS.

  • Logging Mode: Indicates whether the DML actions are logged in the redo log file. To improve performance, set this parameter to NOLOGGING. The default is LOGGING.

  • Statistics Collection: Indicates if statistics should be collected for the table. Specify MONITORING if you want modification statistics to be collected on this table.

  • Row-level Dependency: Indicates whether row-level dependency tracking.

  • Row Movement: Indicates if the Oracle server can move a table row.

Partition Parameters

  • Partition Tablespace List: Specify a comma-separated list of tablespaces. For simple partitioned objects, it is used for a HASH BY QUANTITY partition tablespace. For composite partitioned tables, it is used for sub-partition template to store the list of tablespaces.

  • Overflow Tablespace List: Specify a comma separated list of tablespaces for overflow data. For simple-partitioned objects, it is used for HASH BY QUANTITY partition overflow tablespaces. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, then Oracle cycles through the names of the tablespaces.

Error Table

  • Error Table Name: Indicates the name of the error table that stores the rows that were not loaded into the table during a load operation.

  • Tablespace: Indicates the name of the tablespace in which the error table is stored.

Configuring Materialized Views

To configure the physical properties for a materialized view:

  1. From the Project Explorer, right-click a materialized view name and select Configure.

    The Configuration Property window is displayed.

  2. Follow the configuration guidelines listed for tables. For more information, see "Configuring Tables".

  3. Configure the Materialized View Parameters listed in the following section.

Materialized View Parameters

The following are parameters for materialized views:

Materialized View Parameters

  • Start With: Indicates the first automatic refresh time. Specify a datetime value for this parameter.

  • Refresh On: The options are COMMIT and DEMAND. Specify COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. Specify DEMAND to indicate that the materialized view should be refreshed on demand. You can do this by using one of the refresh procedures of the DBMS_MVIEW package. The default setting is DEMAND.

  • Query Rewrite: Indicates if the materialized view is eligible for query rewrite. The options are ENABLE and DISABLE. The default is DISABLE.

    Enable: Enables query rewrite. For other query rewrite requirements, see "Fast Refresh for Materialized Views".

    Disable: Disables query rewrite. You can disable query rewrite when you know that the data in the materialized view is stale or when you want to make changes to the query statement.

  • Default Rollback Segment: The options are DEFAULT, DEFAULT MASTER, DEFAULT LOCAL, and NONE. The default setting is DEFAULT LOCAL. Specify DEFAULT to indicate that the Oracle Database should choose which rollback segment to use. Specify DEFAULT MASTER for the remote rollback segment to be used at the remote site. Specify DEFAULT LOCAL for the remote rollback segment to be used for the local refresh group that contains the materialized view. Specify NONE to name both master and local segments.

  • NEXT (date): Indicates the interval between automatic refreshes. Specify a datetime value for this parameter.

  • Using Constraints: The options you can select for this parameter are TRUSTED or ENFORCED. Select TRUSTED to allow Oracle to use dimension and constraint information that has been declared trustworthy by the DBA but has not been validated by Oracle. ENFORCED is the default setting.

  • REFRESH: Indicates the refresh method. The options are Complete, Fast, Force, and Never. The default setting is Force.

    Complete: The Oracle server truncates the materialized view and re-executes the query upon refresh.

    Fast: Uses materialized views to only apply changes to the base table data. There are a number of requirements for fast refresh to operate properly. For more information, see "Fast Refresh for Materialized Views".

    Force: The Oracle server attempts to refresh using the fast mode. If unable to refresh in fast mode, the Oracle server re-executes the query upon refresh.

    Never: Prevents the materialized view from being refreshed.

  • WITH: Select PRIMARY_KEY to create a primary key materialized view. Select ROWID to create a ROWID materialized view. The default setting is PRIMARY_KEY.

  • FOR UPDATE: Select Yes to allow a subquery, primary key, rowid, or object materialized view to be updated. The default setting is No.

  • Master Rollback Segment: Indicates the name of the remote rollback segment to be used at the remote master site for the materialized view.

  • Base Tables: Specify a comma-separated list of base tables referenced by the materialized view. Separate each table name with a comma. If a table name is not in upper case, enclose the name in double quotes.

  • Local Rollback Segment: Specify a named remote rollback segment to be used for the local refresh group of the materialized view. The default is null.

  • BUILD: Indicates when the materialized view is populated. The options are Immediate (default), Deferred, and Prebuilt.

    Immediate: Populates the materialized view when it is created.

    Deferred: Delays the population of the materialized view until the next refresh operation. You can select this option when you are designing a materialized view and the metadata for the base tables is correct but the data is not.

    Prebuilt: Indicates that the materialized view is prebuilt.

Performance Parameters:

Logging Mode: Indicates whether the DML actions are logged in the redo log file. To improve performance, set this parameter to NOLOGGING. The default is LOGGING.

Error Table

  • Error Table Name: Indicates the name of the error table that stores the rows that were not loaded into the table during a load operation.

  • Tablespace: Indicates the name of the tablespace in which the error table is stored.

Parallel

  • Parallel Access Mode: Enables parallel processing when the table has been created. The default is PARALLEL.

  • Parallel Degree: Indicates the degree of parallelism. This is the number of parallel threads used in the parallel operation.

Identification

  • Deployable: Select TRUE to indicate if you want to deploy this materialized view. Warehouse Builder generates scripts only for materialized views marked deployable.

  • Error Table Only: Select this option to perform generation or deployment actions only on the error table associated with the materialized view. Use this option when you want to add an error table to an existing materialized view. This setting only controls the actions of the Deployable parameter, but does not override it.

    Unselect this option to deploy the error table along with the materialized view.

Hash Partition Parameters

  • Hash Partition Tablespace List: Indicates the tablespace that stores the partition or sub partition data. To specify multiple tablespaces, use a comma separated list.

Fast Refresh for Materialized Views

You can configure a materialized view to refresh incrementally. When you update the base tables for a materialized view, the database stores updated record pointers in the materialized view log. Changes in the log tables are used to refresh the associated materialized views.

To ensure incremental refresh of materialized views, verify the following conditions:

  • The Refresh parameter must be set to 'Fast' and the Base Tables parameter must list all base tables.

  • Each base table must have a PK constraint defined. Warehouse Builder generates a create statement based on the PK constraint and utilizes that log to refresh the dependent materialized views.

  • The materialized view must not contain references to non-repeating expressions such as SYSDATE, ROWNUM, and non-repeatable PL/SQL functions.

  • The materialized view must not contain references to RAW and LONG RAW data types.

  • There are additional restrictions for materialized views with statements for joins, aggregations, and unions. For information about additional restrictions, see Oracle Database Data Warehousing Guide.

Configuring Views

Warehouse Builder generates a script for each view defined in a target module. You can configure whether to deploy specific views or not by setting the Deployable parameter to TRUE or FALSE.

For more information about views, refer to:

Configuring Sequences

A script is generated for each sequence object. A sequence object has a Start With and Increment By parameter. Both parameters are numeric.

To configure the physical properties for a sequence:

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

    The Configuration Properties dialog box is displayed.

  2. Configure the following Sequence parameters:

    Increment By: The number by which you want to increment your sequence.

    Start With: The number at which you want the sequence to start.

  3. Configure the following Identification parameters:

    Deployable: Select this option to indicate that you want to deploy this sequence. Warehouse Builder only generates scripts for sequences marked deployable.