| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
After you finish designing your data warehouse or data mart, you are ready to design your target system using Warehouse Builder. 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:
You edit a relational, dimensional, or business intelligence (item folders and business areas only) objects 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 SALE 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, refer to the following sections:
Constraints enable you to enforce the business rules you want to associate with the information in a database. They prevent the entry of invalid data into tables. For more information about types of constraints, see "About 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 constraints.
Use the following steps to create constraints on a table, view, or materialized view:
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.
Navigate to the Constraints tab.
Depending on the type of constraint you want to create, refer to one of the following sections:
To define a primary key constraint:
On the Constraints tab, click the Add Constraint button.
A blank row is displayed with the cursor positioned in the Name column.
Enter the name of the constraint in the Name column.
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.
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 drop-down list in the Local Columns column.
In the Local Columns drop-down 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.
To define a foreign key constraint:
On the Constraints tab, click the Add Constraint button.
A blank row is displayed with the cursor positioned in the Name field.
Enter the name of the constraint in the Name column.
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.
In the References column, click the Ellipsis button.
The Key Selector dialog is displayed. Use this dialog to select the table and the column that the current column references.
In the Key Selector dialog, 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.
Click OK.
You now return to the Constraints tab of the Data Object Editor and the foreign key constraint is added.
To define a unique key constraint:
On the Constraints tab, click the Add Constraint button.
A blank row is displayed with the cursor positioned in the Name field.
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.
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.
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 drop-down list in the Local Columns column.
In the Local Columns drop-down list of the new row, select the name of the column on which a unique key should be created.
To define a check constraint:
On the Constraints tab, click the Add Constraint button.
A blank row is displayed with the cursor positioned in the Name field.
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.
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.
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.
You 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.
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.
You can create the following types of partitions:
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 in Warehouse Builder, 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 12-2 shows an example of a table partitioned into four range partitions based on the following instructions.
To partition data by ranges, complete the following steps:
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 shown in Figure 12-1.
Figure 12-1 Partition Tab with Range Partition Selected
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.
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.
Figure 12-2 shows how to define a partition for each quarter of a calendar year. You could 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.
Figure 12-2 Example Table with Range Partitioning
Hash partitioning assigns data to partitions based on a hashing algorithm that Oracle 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, complete the following steps:
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.
Select a partition key.
Warehouse Builder lists all the columns for the object you selected. You can select a column of any data type.
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).
Use hash by quantity to quickly define hash partitioning. You define a partition key and the number of partitions and Warehouse Builder then creates and names the partitions. You can then configure the partitions to share the same tablespace list.
To partition data based on the hash algorithm, complete the following steps:
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 as shown in Figure 12-3.
Figure 12-3 Hash by Quantity Partitioning
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).
Select a partition key.
Warehouse Builder lists all the columns for the object you selected. You can select a column of any data type.
In the configuration window, define the Partition Tablespace List and Overflow Tablespace List.
List partitioning enables you to explicitly assign rows to partitions. You do 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. Figure 12-5 shows an example of a table partitioned into list partitions based the following instructions.
To partition data based on a list of values, complete the following steps:
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 as shown in Figure 12-4.
Figure 12-4 Partition Tab with List Partition Selected
Select a partition key.
Warehouse Builder lists all the columns for the object you selected. You can select a column of any data type.
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 12-5.
Figure 12-5 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 12-5 List Partitioning Based on a Single Key Column
Figure 12-6 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, corresponds to SALES_DIVISION.
Figure 12-6 List Partitioning Based on Multiple Key Columns
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:
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.
Select a partition key and define partitions as described in "Range Partitioning".
In Figure 12-7, the partition key is SALE_DATE and its associated range partitions are QTR_1, QTR_2, QTR_3, and QTR_4.
Figure 12-7 Range-List Partitioning with List Defined under Subpartition Template
Select a column for the subpartition key.
Under the subpartition template node, define the values for the second partitioning method as described in "About the Subpartition Template".
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".
Configure the Partition Tablespace List and Overflow Tablespace List in the configuration window.
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.
Figure 12-7 shows a list subpartition based on the REGION key column and subpartitions for groups of countries. Warehouse Builder divides each partition (such as QTR_1 and QTR_2) into subpartitions (such as ASIA, WEST_EUR,).
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 12-8 shows that partition QTR_1 is subpartitioned into lists for UK, EUR, and ALL_OTHERS while the other quarters are partitioned according to the subpartition template.
Figure 12-8 Subpartitions Overriding Subpartition Template
For all types of indexes except bitmap indexes, you can determine whether or 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.
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 as shown in Figure 12-9.
Figure 12-9 Bitmap Index with Scope Set to Local
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 previous releases, 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 the 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 that you want to specify an index partition key other than any of the table partition keys. In that case, ensure that there are no duplicate rows in the index key column and select unique for the index type.
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 window and scroll down to view Partition Parameters as shown in Figure 12-10.
Figure 12-10 Partition Parameters in the Data Object Editor Configuration Window
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, Warehouse Builder uses the default tablespaces associated with the table 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, Warehouse Builder uses the default tablespaces associated with the table and the performance advantage for defining partitions is not realized when the limits for the partition tablespaces are exceeded.
In Warehouse Builder, 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 in Warehouse Builder:
The table you create in Warehouse Builder 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 Warehouse Builder repository. You can later use these definitions to generate .ddl scripts in Warehouse Builder that can be deployed to create physical tables in your target database. These tables can then be loaded with data from chosen source tables.
You use the Data Object Editor to create a table. Use the following the steps:
From the Project Explorer, expand the Databases node and then the Oracle node.
Expand the module where you want to create the table.
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 repository table. For more information on the Data Viewer tab, see "Data Viewer".
After you finish defining the table using these tabs, Warehouse Builder creates and stores the table definition in the repository. It also inserts the new table name in the Project Explorer.
Note:
You can also create a table from the Mapping Editor.Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.
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 drop-down 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.
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 on creating constraints, see "Creating Constraints".
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 on creating these indexes, see "Creating Indexes".
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 on these partitions and how to create each type of partition, see "Using Partitions".
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 "About Attribute Sets".
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 repository. 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".
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.
You 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.
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 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 grey cell to the left of the column name you want to remove and select Delete.
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 grey cell to the left of the column name. Click Delete at the bottom of the tab.
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.
By default, columns in a table are displayed in the order they are created. This order is also propagated to the DDL script generated by Warehouse Builder 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:
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.
On the Columns tab, select the gray square located to the left of the column name.
The entire row is highlighted.
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.
Close the Data Object Editor.
For the change in the column position to be reflected in the table stored in the repository you must deploy the changed table definition.
In Warehouse Builder, you can define views and materialized views. This section describes views. For information on materialized views, see "Using Materialized Views".
Views are used to simplify the presentation of data or restrict access to data. Often the data that users are interested in is stored across multiple tables with many columns. When you create a view, you create a query stored to retrieve only the relevant data or only data that the user has permission to access.
In Warehouse Builder, a view can be defined to model a query on your target data. This query information is stored in the Warehouse Builder repository. You can later use these definitions to generate .ddl scripts in Warehouse Builder that can be deployed to create views in your target system.
For information about using views, refer to the following:
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 Warehouse Builder repository. 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:
From the Warehouse Builder Project Explorer expand the Databases node and then the Oracle node.
Expand the target module where you want to create the view.
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.Define the view by specifying information on the following Data Object Editor tabs:
Close the Data Object Editor.
Warehouse Builder creates a definition for the view, stores this definition in the repository, and inserts its name in the Project Explorer.
Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.
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".
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.
Use this page to define logical constraints for a view. Although Warehouse Builder does not use these constraints when enumerating DDL for the view, these constraints can be useful when the view serves as a data source in a mapping. The Mapping Editor can use the logical foreign key constraints to include the referenced dimensions as secondary sources in the mapping.
Note:
You cannot create check constraints for views.For more information about creating constraints, see "About Constraints".
Use the Attribute Sets tab to define attribute sets for the view. For more information on attribute sets and how to create them, see "About Attribute Sets".
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".
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".
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.
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 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 on these details, see "Columns Tab".
Removing a column: Navigate to the Columns tab. Right-click the grey cell to the left of the column name you want to remove and select Delete.
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.
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.
In Warehouse Builder, you can define views and materialized views. This section discusses materialized views. For information on conventional views, see "Using Views".
The following sections provide information about using materialized views:
In Warehouse Builder, you can create materialized views to improve query performance. When you create a materialized view, you create a set of query commands that aggregate or join data from multiple tables. Materialized views provide precalculated data that can be reused or replicated to remote data marts. For example, data about company sales is widely sought throughout an organization.
When you create a materialized view in Warehouse Builder, you can configure it to take advantage of the query rewrite and fast refresh features available in Oracle Database. For information on query rewrite and fast refresh, "Fast Refresh for Materialized Views".
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.
You use the Data Object Editor enables to create materialized views. Follow these steps to create a materialized view:
From the Warehouse Builder Project Explorer expand the Databases node and then the Oracle node.
Expand the target module where you want to create the materialized view.
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.Specify information on the following tabs of the Data Object Editor to create the materialized view definition:
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.
Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.
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".
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.
Use the Constraints tab to define constraints for the materialized view. Defining constraints is optional. These constraints are for logical design purposes only and Warehouse Builder does not use these constraints when enumerating DDL for the view. For information on creating constraints, see "Creating Constraints".
Note:
You cannot create check constraints for views.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 on creating indexes, see "Creating Indexes".
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.
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 on how to define attribute sets, see "Creating Attribute Sets".
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.
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 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 on these details, see "Columns Tab".
Removing a column: Navigate to the Columns tab. Right-click the grey cell to the left of the column name you want to remove and select Delete.
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.
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.
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, Warehouse Builder generates a predefined attribute set containing all the columns in that table. In addition, Warehouse Builder generates predefined attribute sets for each defined constraint. Predefined attribute sets cannot be modified or deleted.
You 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:
From the Project Explorer, right-click the table name and select Open Editor.
The Data Object Editor for the table is displayed.
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.
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.
In the Type drop-down list, select the type of attribute set as USER_DEFINED or BRIDGE_TYPE.
Optionally, you can enter a description for the attribute set using the Description column.
In the Attributes of the selected attribute set section, click 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.
You can click Select All to select all the displayed columns in the attribute set. Click Deselect All to exclude all the columns from the attribute set. To remove a column from the attribute set, click the check box again to remove the check mark.
If you selected BRIDGE-TYPE, click Advanced.
Warehouse Builder displays the Advanced Attribute Set Properties dialog.
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: Click this check box 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.
Click OK to close the Advanced Attribute Set Properties dialog.
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 drop-down 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 grey 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 grey 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 grey cell to the left of an attribute and use the buttons to move the attribute up or down in the order.
To delete an attribute set, right-click the grey cell to the left of the attribute set name and select Delete.
A sequence is a database object that generates a serial list of unique numbers. You can use sequences to generate unique primary key values and to coordinate keys across multiple rows or tables. Sequence values are guaranteed to be unique. When you create a sequence in Warehouse Builder, you are creating sequence definitions that are saved in the repository. Sequence definitions can be used in mappings to generate unique numbers while transforming and moving data to your target system.
The following sections provide information about using sequences:
A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudo columns. Each new sequence number is incremented by a reference to the pseudo column NEXTVAL, while the current sequence number is referenced using the pseudo column CURRVAL. When you define a sequence, Warehouse Builder creates these attributes.
In Warehouse Builder, you can also import sequence definitions from existing source systems using the Import Object Wizard.
To create a new sequence:
From the Warehouse Builder Project Explorer, expand the warehouse module node.
Right-click Sequences and select New from the pop-up menu.
Warehouse Builder displays the Create Sequence Wizard.
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.
Click OK.
Warehouse Builder stores the definition for the sequence and inserts its name in the Project Explorer.
You use the Edit Sequence dialog 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 is displayed. This dialog contains two tabs: Name Tab and Columns Tab.
Click these tabs to perform the following tasks:
Rename a sequence
Edit sequence columns
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.
The Columns tab displays the sequence columns CURRVAL and NEXTVAL. You can edit the descriptions of these columns.
User-defined datatypes use Oracle built-in datatypes and other user-defined datatypes as the building blocks of object types that model the structure and behavior of data in applications. The built-in datatypes are mostly scalars and does not provide you the same flexibility that modelling an application specific data structure does.Let us take 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.
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
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.
To create an object type:
In Project Explorer, expand the Databases node and then the Oracle node.
Expand the module in which you want to create the object type.
Expand the User Defined Types node.
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:
Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.
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.
To edit an object type:
In Project Explorer, expand the Databases node and then the Oracle node.
Expand the module where you want to create the object type.
Expand the User Defined Types node.
Expand the Object Types node
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.
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;
Since Varrays is an ordered set of elements it can be considered that the first address in the list is the primary address, the remaining addresses are the secondary addresses.
To create a varray:
From the Project Explorer, expand the Databases node and then the Oracle node.
Expand the module where you want to create the varray.
Expand the User Defined Types node.
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:
Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.
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.
To edit a varray, use the following steps:
From the Project Explorer, expand the Databases node and then the Oracle node.
Expand the module where you want to edit the Varray type.
Expand the User Defined Types node.
Expand the Varrays node.
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.
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. For example, customer references.
To create a nested table:
From the Project Explorer, expand the Databases node and then the Oracle node.
Expand the module where you want to create the nested table.
Expand the User Defined Types node.
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.
Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.
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.
To edit a nested table, use the following steps:
From the Project Explorer, expand the Databases node and then the Oracle node.
Expand the module where you want to edit the Nested Table.
Expand the User Defined Types node.
Expand the Nested Tables node.
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.
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:
In this phase, you assign physical deployment properties to the object definitions you created in Warehouse Builder 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. Figure 12-11 displays the Configuration Properties dialog for an Oracle module. You can set properties for target modules or for individual design objects such as tables, dimensions, views, or mappings. The following sections show you how to assign physical properties to your logical design model.
Figure 12-11 Configuration Properties Dialog
Each target module provides top level configuration options for all the objects contained in that module.
From the Warehouse Builder Project Explorer, expand Databases, expand Oracle, and right-click a target module name and select Configure.
Warehouse Builder displays the Configuration Properties dialog.
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 ellipses to display another properties dialog.
Configure the parameters listed in the following sections.
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.
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, Warehouse Builder uses the tablespace value configured at the target module level, during code generation. If you configure a tablespace for each index at the object level, Warehouse Builder overwrites the tablespace value configured at the target module level.
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, Warehouse Builder uses the value configured at the target module level, during code generation. If you configure a tablespace for each individual object, Warehouse Builder overwrites the tablespace value configured at the target module level.
End of Line: Defines the end of line markers for flat files. This is dependent on the platform to which you are deploying your warehouse. For UNIX, use \n, and for NT, use \r\n.
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 Oracle8i, Warehouse Builder generates row-based code.
Each release of Warehouse Builder 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 Oracle8i as the PL/SQL Generation Mode, you cannot access some Oracle9i Warehouse Builder components such as Table Functions and External Tables. For a list of Oracle Warehouse Builder components not compatible with prior releases of the Oracle Database, refer to the Oracle Warehouse Builder Release Notes.
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\.
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 18, "Importing Data From Third Party 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.
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. Warehouse Builderdisplays the Configuration Properties dialog. Set the configuration parameters listed in the following sections.
Identification
Deployable: Select this option to indicate that you want to deploy this table. Warehouse Builder generates scripts only for table constraints marked deployable.
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, Warehouse Builder generates the table based on the tablespace value set in the target module configuration properties. If you configure the tablespace for individual objects, Warehouse Builder overwrites the tablespace value configured for the target module.
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-delimited 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.
Shadow Table
To configure the physical properties for a materialized view:
From the Project Explorer, right-click a materialized view name and select Configure.
The Configuration Property window is displayed.
Follow the configuration guidelines listed for tables. For more information, see "Configuring Tables".
Configure the Materialized View Parameters listed in the following section.
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. Choose 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: Choose PRIMARY_KEY to create a primary key materialized view. Choose ROWID to create a ROWID materialized view. The default setting is PRIMARY_KEY.
FOR UPDATE: Choose 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-delimited 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.
Shadow Table
Shadow Table Name: Indicates the name of the shadow 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 shadow 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.
Hash Partition Parameters
You can configure a materialized view in Warehouse Builder 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 in Warehouse Builder, 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 on additional restrictions, refer to the Oracle9i Data Warehousing Guide.
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 on views, refer to the following sections:
Warehouse Builder generates a script 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:
Right-click the name of a sequence and select Configure.
The Configuration Properties dialog is displayed.
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.
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.
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
