| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
Earlier in the design phase, you defined a logical model for your target system using Warehouse Builder design objects. This chapter discusses how you assign physical properties to those design objects.
This chapter first describes how you create indexes and partitions for design objects such as tables, materialized views, dimensions, and cubes defined in Warehouse Builder. Next, this chapter discusses how you assign physical properties to Warehouse Builder object definitions.
This chapter includes:
In Warehouse Builder, indexes and partitions can be created for Tables, Materialized Views, Dimensions, and Cubes. Indexes and partitions are created to enhance query performance of your data warehouse.
This section includes:
Indexes are important for speeding queries by quickly accessing data processed in a warehouse. They can be created on one or more columns of a table to speed SQL statement execution on that table. Indexes have the following characteristics:
Index column values are stored presorted.
Because indexes are stored in a separate area of the database, they can be created or dropped at any time without any effect on the underlying table.
When data is deleted, added, or updated, the indexes are maintained automatically. They are independent of the data in the table.
B*-Tree and Bitmap indexes are particularly useful in data warehousing. To learn more about indexes and indexing strategies, see the Oracle Database Data Warehousing Guide.
In Warehouse Builder, after you define the data objects, such as tables, materialized views, dimensions, and cubes, you can create indexes on them according to the requirements of your target system. After creating the indexes, you need to configure them with physical properties to enable deployment. The following sections show you how to create and configure indexes in Warehouse Builder.
You can create and configure indexes on cubes, dimensions, tables, and materialized views.
To create indexes:
From the Warehouse Builder navigation tree, right-click the appropriate design object and select Configure.
The Configuration Properties dialog displays.
From the Configuration Properties dialog, select the field to the right side of Indexes.
Click the ... button.
The Indexes dialog displays as shown in Figure 5-1.
Type a name for the index in the Name field and click Add.
Repeat this step for each index you want to create.
Click OK if the indexes are correct. If not, click Cancel.
You are now ready to configure your index for deployment.
Warehouse Builder utilizes the bitmap indexing feature available in the Oracle database to provide pointers to the rows in a table that contain a given key value. In data warehousing, bitmaps are created to enable star query transformations. The star transformation is a cost-based query transformation 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. For more information, see the Oracle Database Data Warehousing Guide.
To create bitmap indexes:
From the Warehouse Builder navigation tree, right-click the appropriate design object and select Configure.
The Configuration Properties dialog displays.
From the Configuration Properties dialog, select the field to the right side of Indexes.
Click the ... button.
The Indexes dialog displays as shown in Figure 5-2.
Type a name for the index in the Name field and click Add.
Repeat this step for each index you want to create.
If you are creating bitmap indexes, click Generate.
Bitmap indexes are created on all foreign key columns. Before bitmap indexes are generated, Warehouse Builder checks the table for foreign keys. Warehouse Builder does not generate bitmap indexes if:
No foreign keys: There are no foreign keys on the table. In this case, Warehouse Builder indicates that no index can be created because there are no foreign keys on the table.
No columns: There are foreign keys on the table but there are no columns defined for them.
Indexes exist: There are foreign keys on the table and some of them already have indexes. If there is already a bitmap index created on one of the columns, then the column name and the name of the existing index displays in the Impact Report dialog along with a message that no new bitmap index can be created for these columns.
The Impact Report for Bitmap Index Creation dialog displays.
Click OK if the indexes are correct. If not, click Cancel.
Follow the steps listed in the following section to configure your index for deployment.
After you create indexes, you must define their parameters using the Configuration Properties dialog.
To configure indexes:
In the Configuration Properties dialog, expand the Indexes node to display the configuration parameters.
Configure the following parameters for indexes:
Log to Redo Log File: Indicates whether the index creation logs in the redo log file.
Parallel: If set to PARALLEL, this parameter enables parallel processing when a table is created.
Tablespace: Specify the tablespace where the index is created.
Set the Index Type parameter to BITMAP,UNIQUE, or leave it blank for a non-unique B*-tree index. If you are configuring a Bitmap index, the bitmap option is automatically selected.
Local Index: A local index reflects the structure of its underlying table. When configured TRUE, this parameter specifies that the index is partitioned on the same columns, with the same number of partitions, and the same partition bounds as its underlying table.
Deployable: Set to TRUE to indicate that you want to deploy this index.
Index Columns: Click the ... button to display the Index Columns dialog. From the Choices field, select the columns to be included in the index. Click the right arrows to move them to the Included list. Click OK.
Close the Configuration Properties dialog.
To rename an index:
From the Configuration Properties dialog, expand Indexes.
Click the column to the right of the index you want to rename and click the ... button.
The Indexes dialog displays.
Type a new name for the index in the Rename field and click OK.
The new index name displays under Indexes in the Configuration Properties dialog.
Partitions enable you to efficiently manage very large tables and indexes by dividing them into smaller, more manageable parts. Partitions can be created to improve query and load performance and to simplify the management of physical storage. Because partitions can be managed individually and can operate independently of other partitions, they provide a structure that can be better tuned for performance. DML statements can access and manipulate individual partitions rather than entire tables or indexes.
In Warehouse Builder, you can create two types of partitions:
Range: Range partitioning is based on ranges of partition key values that you establish for each partition. Only data with a particular set of values is contained in each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.
Hash: When you implement hash partitioning, you choose a partitioning key and the number of partitions. Oracle9i divides the data evenly across all the partitions. Choose hash partitioning to control the physical placement of data across a fixed number of partitions. Hash partitioning is useful when data is not historical and there is no obvious column or column list
You can create and then configure partitions for Tables, Materialized Views, Cubes, and Dimensions. After creating the partitions, you need to configure them with physical properties to enable deployment. The following sections show you how to create and configure partitions in Warehouse Builder.
To create partitions in Warehouse Builder, you need to follow these steps:
Determine the type of partition you want to create: Hash or Range.
Create a partition key entry and indicate the type of partition you are creating. A partition key contains the set of columns that determine how to partition a table. See "Creating a Partition Key Entry".
Create a Range or Hash partition. See "Creating Hash Partitions" and "Creating Range Partitions".
Configure the Local Index parameter for Indexes to indicate if you want to partition them or not. See "Partitioning Indexes".
The following sections outline these steps in detail.
To create a partition key entry:
From the Warehouse Builder navigation tree, right-click the appropriate design object and select Configure.
The Configuration Properties dialog displays.
From the Configuration Properties dialog, select the field to the right side of Partition Keys.
Warehouse Builder displays the Partition Keys dialog.
From the Choices field, select the columns you want to include.
Click the right arrow buttons to move the selected columns to the Included field.
Click OK.
From the Configuration Properties dialog, expand the Partition Keys node and expand a partition key name.
Select the type of partition you want to create: RANGE or HASH.
Range partition information always overwrites Hash partition information.
If you choose to create a Hash partition, follow the steps following this section. If you choose to create a Range partition, see "Creating Range Partitions".
To create a hash partition:
From the Configuration Properties dialog, expand the Hash Partition Parameters node.
Specify the number of Hash subpartitions in the Hash SubPartition Number field.
Provide a tablespace name for the partition in the Hash Partition Tablespace field.
To create a range partition:
Expand the Range Partitions node in the Configuration Properties dialog.
Create the range partitions by clicking the ... button.
The Range Partitions dialog displays.
Type a partition names in the Name field and click Add.
To delete a partition name from the list, select a partition name and click Delete.
Click OK.
The range partitions are displayed under the Range Partitions node in the Configuration Properties dialog.
Expand a range partition name to configure the following parameters:
Date Less Than: Type the non-inclusive upper limit for the current partition. This entry is an ordered list of literal values corresponding to column_list in the partition_by_range_clause. You can substitute the keyword MAXVALUE for any literal in value_list. MAXVALUE specifies a maximum value that sorts higher than any other value, including NULL.
Warehouse Builder uses the partition name and the Value Less Than property to generate the DDL script for partitioning the table. The Value Less Than property defines the contents of the partition.
Tablespace: Type the name of the physical attribute of the tablespace associated with the partition. If the value is not specified, then the tablespace for this partition will take the default value of the tablespace specified by the table.
Deployable: Select TRUE to indicate that you want to deploy this partition. Warehouse Builder only generates scripts for partitions marked deployable.
To rename a range partition:
Open the Configuration Properties dialog for the appropriate data object.
Expand the Partition node.
Click the field next to the partition name you want to edit and click the ... button.
The Partitions dialog displays.
Highlight the value in the Rename field and type a new name.
Click OK.
A local index is created to reflect the structure of the underlying table. It is partitioned on the same columns as the underlying table, creating the same number of partitions or subpartitions, and partition bounds as the corresponding partitions of the underlying table.
To create a local index:
From the Configuration Properties dialog, expand the Indexes node.
Expand the Index Type node.
Set the Local Index parameter to True if you want to create a partitioned index.
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.
These physical properties are set using the Configuration Properties window. Properties can be set for for target modules or for each individual design object such as tables, dimensions, views, or mappings. The following sections show you how to assign physical properties to your logical design model.
Each target module provides top level configuration options for all the objects contained in that module.
From the Warehouse Builder navigation tree, expand Databases, expand Oracle, and right-click a target module name and select Configure.
Warehouse Builder displays the Configuration Properties dialog as shown in Figure 5-3.
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 ... to display another properties dialog.
Configure the following Storage Tablespace parameters:
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.
Configure the following Generation Preferences:
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, selecting Oracle Database ensures the use of Oracle Database code constructs, selecting Oracle8i would generate row-based code.
Oracle Warehouse Builder introduces new functionality available for Oracle9i databases and higher only. If you select Oracle8i for the PL/SQL Generation Mode, some Oracle9i Warehouse Builder functionalities such as Table Functions and External Tables are not available. For a list of Oracle Warehouse Builder not compatible with releases prior to Oracle9i, see the Oracle Warehouse Builder Release Notes .
Configure the following preferences for the 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\.
Configure the following 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 Extension: Type a file name extension for 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\.
ABAP Directory: For all ABAP configuration related to SAP tables, see Appendix 21, "Using SAP R/3 Data in Warehouse Builder".
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.
Configure the following Identification parameters:
Main Application Short Name
Application Short Name
Schema Owner
Connect String
Remote Host Name
Port
Service Name
Top Directory
Deployable
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 as shown in Figure 5-4.
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.
Analyze Table: Estimate Percent: This value represents the sample size as a percentage of total rows. When set to a nonzero value, Warehouse Builder generates a DDL script to analyze the table.
Tablespace: 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.
Deployable: Select TRUE to indicate if you want to deploy this table constraint. Warehouse Builder generates scripts only for table constraints marked deployable.
Create and configure indexes as described in "Configuring External Tables" and "Configuring Indexes".
Configure the following properties for an external table:
Note:
When you import an external table into the repository and when you manually define access parameters for an external table, some external table configuration properties are overruled by settings on the Access Parameters tab in the External Table Properties window.To configure the physical properties for an external table:
Select an external table from the navigation tree.
From the Edit menu, select Configure. You can also click the Configure icon from the tool bar.
The Configuration Property window displays as shown in Figure 5-5.
To configure a property, click the white space and make a selection from the drop down box.
If you imported the external table into the repository or created the external table without specifying a source file, do not configure these properties. Access specification properties are overruled by settings on the Access Parameters tab in the External Table Properties window.
Under Access Specification, you can indicate the following file names and locations Warehouse Builder uses to load the external table through SQL*Loader.
Bad File: If you specify a name and location for a bad file, Warehouse Builder directs the Oracle database to write to that file all records that were not loaded due to errors. For example, records written to the bad file include those not loaded due to a datatype error in converting a field into a column in the external table. If you specify a bad file that already exists, the existing file is overwritten.
Discard File: If you specify a name and location for a discard file, Warehouse Builder directs the Oracle database to write to that file all records that were not loaded based on a SQL *Loader load condition placed on the file. If you specify a discard file that already exists, the existing file is overwritten.
Log File: If you specify a name and location for a log file, Warehouse Builder directs the Oracle database to log messages related to the external table to that file. If you specify a log file that already exists, new messages are appended.
For each of these files, you can either specify a file name and location, select Do not use, or select Use default location.
Under Reject, you can indicate how many rejected rows to allow. By default, the number of rejected rows allowed is unlimited. If you set Rejects are unlimited to false, enter a number in Number of rejects allowed.
Parallel: Enables parallel processing. If you are using a single system, set the value to NONPARALLEL to improve performance. If you are using multiple systems, accept the default PARALLEL.The access driver attempts to divide data files into chunks that can be processed separately. The following file, record, and data characteristics make it impossible for a file to be processed in parallel:
Sequential data sources (such as a tape drive or pipe).
Data in any multibyte character set whose character boundaries cannot be determined starting at an arbitrary byte in the middle of a string. This restriction does not apply to any datafile with a fixed number of bytes for each record.
Records with the VAR format
If you imported the external table into the repository or created the external table without specifying a source file, do not configure these properties. Data characteristics properties are overruled by settings on the Access Parameters tab in the External Table Properties window.
Under Data Characteristics you can set the following properties:
Endian: The default for the Endian property is Platform. This indicates that Warehouse Builder assumes the endian of the flat file matches the endian of the platform on which it resides. If the file resides on a Windows platform, the data is handled as little-endian data. If the file resides on Sun Solaris or IBM MVS, the data is handled as big-endian. If you know the endian value for the flat file, you can select big or little-endian. If the file is UTF16 and contains a mark at the beginning of the file indicating the endian, Warehouse Builder uses that endian.
String Sizes in: This property indicates how Warehouse Builder handles data with multibyte character sets, such as UTF16. By default, Warehouse Builder assumes the lengths for character strings in the datafile are in bytes. You can change the selection to indicate that strings sizes are specified in characters.
If you imported the external table into the repository or created the external table without specifying a source file, do not configure these properties. Field editing properties are overruled by settings on the Access Parameters tab in the External Table Properties window.
Under Field Editing, you can indicate the type of whitespace trimming to be performed on character fields in the datafile. The default setting in Warehouse Builder is to perform no trim. All other trim options can reduce performance. You can also set the trim option to trim blanks to the left, right, or both sides of a character field.
Another option is set the trim to perform according to the SQL*Loader trim function. If you select SQL*Loader trim, fixed-length files are right trimmed and delimited files specified to have enclosures are left trimmed only when a field is missing an enclosure.
You can indicate how to handle missing fields in a record. If you set the option Trim Missing Values Null to true, fields with missing values are set to NULL. If you set the property to false, fields with missing values are rejected and sent to specified bad file.
You must add at least one data file to an external table to associate the external table with more than one flat file.
To add a data file:
Select the field to the right of Data Files and click the ... button.
The Data Files dialog displays.
Enter a name for the flat file and select OK.
Warehouse Builder displays the flat file under the Data Files property.
Expand the newly added flat file and configure the following properties:
Data File Location: Location for the flat file.
Data File Name: Name of the flat file.
Follow these steps to configure an AQ in Warehouse Builder.
To configure an Advanced Queue:
From the Warehouse Builder console, highlight the name of the Advanced Queue.
From the Object menu, select Configure.
The Configuration Properties dialog displays, as shown in Figure 5-6.
Configure the following Storage Space parameter for the AQ:
Tablespace: Name of the tablespace where the AQ and its corresponding table will be created.
Configure the following additional parameters:
Deployable: Select TRUE if you want to generate scripts and deploy this AQ. Warehouse Builder only generates for AQs marked deployable.
Queue Table Name: Provide the name of the AQ table that is used to persist the messages in the AQ.
Configure the following Generation Options:
Generate Object Type: Indicate whether you want to deploy the object type associated with the AQ.
Generate Queue Table: Indicate whether you want to deploy the queue table associated with the AQ.
When you are deploying the second AQ that shares the same object type or queue table with other AQs, you must set the generation options for these properties as FALSE. If not, the deployment of the AQ will fail.
Generate Advanced Queue: Indicate whether you want to deploy the AQ.
Generate Temporary Table: Indicate whether you want to deploy the temporary table associated with the AQ. Even if the AQ already exists in your target system, the temporary table must be deployed by setting this option as True.
The deployment of the queue table, the AQ, and the temporary table can be viewed under Advanced Queue in the Runtime Audit Browser.
When you configure a dimension, you configure both the dimension and the underlying table.
To configure the physical properties for a dimension:
From the navigation tree, right-click the a dimension name and select Configure from the pop-up menu.
The Configuration Property window displays.
Follow the configuration guidelines listed for tables. For more information, see "Configuring Tables".
Configure the following generation options for dimensions. With these parameters you can choose whether or not to generate the dimension and its underlying table.
Generate Table: This can be set to true or false using the drop-down menu. Set to true to generate the underlying table. Set to false if you do not want to generate the underlying table.
Generate Dimension: This can be set to true or false using the drop-down menu. Set to true to generate the dimension object. Set to false if you do not want to generate the dimension object.
For more information on dimensions, see the following section:
When you configure a cube, you configure both the cube and the underlying table.
To configure the physical properties for a cube:
From the navigation tree, right-click a cube name and select Configure from the pop-up menu.
The Configuration Property window displays.
Follow the configuration guidelines listed for tables. For more information, see "Configuring Tables".
Although there are no additional configuration parameters for cubes, the following are some guidelines for configuring a cube.
Foreign Key constraints exist for every dimension.
Bitmap indexes have been generated for every foreign key column to its referenced dimension.
For more information on cubes, see the following sections:
To configure the physical properties for a materialized view:
From the navigation tree, right-click a materialized view name and select Configure.
The Configuration Property window displays as shown in Figure 5-7.
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:
Immediate: (Default) 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.
Complete: (Default) Oracle Database 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: Oracle Database attempts to refresh using the fast mode. If unable to refresh in fast mode, Oracle Database re-executes the query upon refresh.
Enable: (Default) 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.
To configure Base Tables, you must type the names of the 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. By default, this field is empty.
For related information, see "Using Materialized Views".
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 Oracle Database 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, see 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 displays, as shown in Figure 5-8.
Configure the following properties for a sequence:
Increment By: The number you want to increment your sequence by.
Start With: The number you want the sequence to start with.
Configure the following Identification parameters:
Deployable: Select TRUE to indicate that you want to deploy this sequence. Warehouse Builder only generates scripts for sequences marked deployable.
For related information, see the following sections:
