Release 2 (v9.0.2)
Part Number A95949-01
 Home |
 Solution Area |
 Contents |
 Index |
| Oracle9i Warehouse Builder User's Guide Release 2 (v9.0.2) Part Number A95949-01 |
|
This chapter describes how to generate a physical warehouse instance from a logical warehouse design. You begin this process by configuring the properties of the target warehouse module. You can then validate the definitions and generate the scripts used to create the warehouse objects and the mapping scripts used to load the warehouse. When you deploy the scripts, the warehouse objects are created and the mapping scripts are stored in the warehouse instance. This chapter also describes how to upgrade the warehouse.
This chapter includes the following topics:
When you configure a physical instance, you specify which objects in the logical warehouse design to physically deploy. You also define the physical characteristics of the warehouse objects. Physical characteristics are determined by a set of parameters stored in the configuration properties inspector for the object. Most parameters have default values.
To configure a data warehouse, you must define the physical properties listed in Table 9-1, which summarizes the properties you can configure for each object deployed to the physical warehouse.
| Warehouse Object | Properties |
|---|---|
|
Warehouse Module |
Database link definitions, generation preferences, the job name to register with Oracle Enterprise Manager, runtime and target directory names, and warehouse module identification properties. |
|
Dimension |
Indexes, partitions, partition keys, constraints, columns, performance, parallel, partition parameters, table space used to store, generation options for time dimensions, generation options, and identification. |
|
Fact |
Indexes, partitions, partition keys, constraints, columns, performance, parallel, partition parameters, table space used to store, and identification. |
|
Mapping |
These parameters are described in Chapter 8. |
|
Materialized View |
Indexes, partitions, partition keys, performance, parallel, partition parameters, materialized view parameters, table space used to store, and identification. |
|
Sequence |
Sequence Parameters and Identification. |
|
Table |
Indexes, partitions, partition keys, constraints, columns, performance, parallel, partition parameters, table space used to store, and identification. |
|
Transformation Libraries |
Identification. |
|
View |
Identification. |
|
Note: Database links, indexes, and partitions are physical objects. They are not created during the logical design. They must be created in the Configuration Properties inspector before you can configure their parameters. See "Creating Database Links, Indexes, and Partitions" for more information. |
To set up configuration properties:
The Configuration Properties inspector displays.
Depending on the parameter, you can either select an option from a drop-down list or type a value.
Table 9-2 describes the categories of parameters in the Configuration Properties inspector for Warehouse Modules.
Warehouse Builder generates two scripts for a dimension: one to create the dimension object and the other to create the underlying table. Additional scripts are generated when indexes are defined on the dimension table, or when the definition is generated with the Time Dimension Wizard. The configuration parameters for a dimension specify how these scripts are generated and whether the dimension is deployed.
Table 9-3 defines the configurations parameters for dimensions, facts, and tables. For information on creating indexes, partitions, and partition keys see "Creating Database Links, Indexes, and Partitions".
| Category | Parameter | Description |
|---|---|---|
|
Performance Parameters |
Log to Redo Log File |
Recovery requirements for a data warehouse are usually less strenuous than for a transaction system. Set to nologging to improve performance. The default is logging. |
|
Performance Parameters |
Analyze Table: Estimate Percent |
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. To improve performance, use a high number for a large dimension table and a low number for a small dimension table. The default is 99. |
|
Parallel |
Parallel |
Enables parallel processing when the table is created. If you are using a single CPU or a non-Oracle database, set to nonparallel to improve performance. The default is parallel. |
|
Partition Parameters |
Hash SubPartition Number and Store in Tablespace |
Defines the partition key and method. Select Hash or Range. These parameters only apply if you create hash partitions. For more information, see "Creating Partitions". |
|
Storage Space |
Tablespace |
Defines the name of each tablespace. A dimension table can reside in one or more tablespaces if partitions are defined. If you do not use partitions, then the storage space resides on a single tablespace. |
|
Generation Options for Time Dimension |
Start and End Date |
When you create a definition for a dimension using the Time Dimension Wizard, Warehouse Builder generates an insert statement to load the underlying table. The Start and End Date parameters determine the range of dates. |
|
Generation Options |
Generate Table |
Specifies whether or not to create the dimension table. The default is true. |
|
Generation Options |
Generate Dimension |
Specifies whether or not to generate a script to create the object. Set this parameter to true only when the warehouse requires the hierarchy and level information. Create this information for an instance that relies on materialized views that reference this dimension. |
|
Identification |
Name, Previous Deployment Name and Deployable |
Contains general information about the dimension, fact, or table including the name, the name that it was last deployed with, and whether it is deployable. |
Warehouse Builder generates one script to create a materialized view and another for its foreign key reference constraints. This is the same generation process used for views and tables. Additional scripts are generated when indexes are defined on the view.
The Configuration Properties inspector for a materialized view contains most of the same categories as dimensions, facts, and tables. See Table 9-3 for the parameter descriptions. Table 9-4 defines parameters specific to materialized views.
Warehouse Builder generates a script for each sequence object. A sequence object has a Start With and Increment By parameter. Both parameters are numeric.
Warehouse Builder generates a script for each view defined in a warehouse module. You can configure whether to deploy specific views or not.
Physical objects such as database links, indexes, partitions, and partition keys cannot be created when you are designing the warehouse. If you want to create these objects in your database when you deploy, you must define the configuration properties before deploying the warehouse. These objects can only be created in relation to certain objects. Database links can only be created in the warehouse module Configuration Properties inspector. Indexes, partitions, and partition keys can only be created in the dimension, fact, table, and materialized view Configuration Properties inspectors.
Target schemas pull data from database sources using PL/SQL packages. These packages rely on database links to connect with the sources. A definition for a database link must be created, configured, and deployed for each database source. The database link is deployed in the target schema.
Each time you create a mapping that pulls data from a database source, Warehouse Builder verifies that a definition for the required database link exists. If the definition does not exist, Warehouse Builder creates one from the connection information stored in a source module for a mapping. This means, the default parameter values for a database link correspond with the connection information in a source module.
When you deploy a physical instance, the database links generated by Warehouse Builder must point to the correct sources. For example, if a test application system was used to develop the logical warehouse but the physical instances use real data stored in production systems, then you must reconfigure the definitions to reflect this change.
Definitions for database links are visible in the Configuration Properties inspector for the warehouse module. You can specify the host machine using a SQL*Net connect string or by providing the host name, port, and ID of the database instance.
You can create bitmap indexes on facts, dimensions, tables, and materialized views.
To create bitmap indexes:
The Configuration Properties inspector for the entity displays.
The Indexes dialog displays.
Repeat this step for each index you want to create.
The Impact report for Bitmap Index creation dialog displays.
Before indexes are generated, Warehouse Builder checks the table for foreign keys. Warehouse Builder does not generate indexes if:
In this case, Warehouse Builder informs you that no index can be created because there are no foreign keys on the table.
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.
After you create indexes, you can edit their parameters using the Configuration Properties inspector.
To configure bitmap indexes:
Table 9-5 describes the remaining parameters for indexes.
The Index Columns dialog displays.
For information about indexing strategies, see:
Partitions are created on tables to improve query and load performance and to simplify the management of physical storage. Transportable tablespaces are introduced for performance reasons and to transport data across Oracle8i/9i databases. A transportable tablespace is the fastest means to move a large volume of data between two Oracle8i/9i databases. Dimension tables and materialized views occupy storage space and are candidates for intelligent partitioning.
You can create and configure definitions for partitions from the Configuration Properties inspector. A table can be partitioned using hash, range, or a combination of hash and range partitions.
Table 9-6 describes the parameter settings for partitions.
In order to use Partition Exchange Loading, you need to set key constraints on DATE columns. This column must be part of a primary or unique key on the table, an it must be the first column if it is segmented. This key must be index enabled and that index must be set on the same columns as the primary or unique key. See "Configuring Partition Exchange Loading (PEL)" for information on PEL.
Table 9-7 describes the partition key parameters.
To set partitions on a table:
The Partitions dialog appears.
You can delete a partition name from the list by highlighting it and clicking Delete.
A partition naming convention enables the PEL to automatically construct the correct partition name based on freshly loaded data. The partition name must contain information on all the time levels leading to the intended granularity. This convention also enables Warehouse Builder to calculate the Value Less Than property automatically. Table 9-8 describes the PEL naming convention.
If the partition name does not match one of the above patterns, then the default value is TO_DATE ('01-jan-1999','dd-mon-yyyy').
Using the partition name and the Value Less Than property, Warehouse Builder generates a DDL script for setting the partitioned table. The Value Less Than property defines the contents of the partition.
To edit the partition values:
To create a hash partition, specify the hash key, number of partitions, and tablespace names. Oracle8i/9i partitions the storage space and stores rows according to a hash algorithm.
To create a set of range partitions, you specify a range key, names for the partitions, and a set of tablespaces. Oracle8i/9i then stores rows in a partition according to the specified ranges.
You can create a set of range partitions that have hashed subpartitions. Create a range partition key, a set of partitions, and then create a hash partition key to specify the number of logical partitions.
You can increase the speed at which data loads by using the Oracle8i/9i server partitioning swapping capability. The PEL technique performs data loading by exchanging the partition identities of a target table with a temporary table. Data loads into the temporary table in parallel or using direct path units. If this load completes, then the temporary table holding the new data takes over the identity of one empty partition from the user-defined target table. At the same time, the empty partition assumes the identity of the source table. The exchange process is a DDL operation and involves no data movement. PEL is useful for:
If you have an Oracle8i warehouse module, then you must use local indexes in order to take advantage of PEL. Local indexes require that all indexes be partitioned like the table. When the temporary table is swapped into the target table using the PEL, so are the identities of index segments. There is no need to manipulate actual index blocks.
To set partition exchange loading:
This property must match the partition granularity of your target table.
After you configure a set of definitions for a physical instance, you need to validate them before you generate and deploy objects and scripts to a physical instance. This section describes how to validate and generate a selected set of definitions.
Validation works as an error reporting tool for the data warehouse loading scripts. When you generate deployment code, Warehouse Builder validates it before deploying it to the repository. A set of configured definitions must be validated before scripts are generated for these reasons:
You can create incorrect definitions. For example, if you add a foreign key constraint to a fact table that does not reference a column in a dimension table, Warehouse Builder stores the definition in the warehouse module. The definition fails the validation process until the reference is satisfied.
You can create definitions with data type mismatches. When the validation process runs, a warning message displays.
The Import Utility can include definitions that are malformed for a number of reasons. For more information, see "Metadata Import Utility".
Validation can apply to objects that are not generated. For example, if you may need to validate a source module to understand a foreign key constraint violation. Only target modules can generate code.
If you validate and generate in read-only mode, then the generated code does not remain in the repository. An alert dialog informs you if you are generating in read-only mode. You can validate a single definition, a set of definitions, or all the definitions stored in a warehouse module.
To validate a set of definitions:
Warehouse Builder validates the selected object definitions and displays the results in the Validation Results dialog.
The dialog lists summary information for each object selected for validation. The summary information includes:
Click View to display the validation results in a text editor. This can be useful if you want to save these results to a file.
Click Refresh to refresh your validation results. This can be useful if you are running validations in more that one window.
The validation messages lists individual messages for the highlighted object:
Error: Displays if the script causes code generation or deployment failure.
Warning: Displays if the script may cause failure during generation, deployment, or runtime.
Success: Displays if the script does not cause any known problems.
Click Details to view additional details on the highlighted object.
Click Edit to open an Editor in Warehouse Builder to edit the highlighted object. For example, if you have a table highlighted and you click Edit, the Table Editor opens with the highlighted table displayed.
During the validation process, Warehouse Builder assigns a warning or an error code to each malformed expression within an individual definition.
During this phase, Warehouse Builder validates and generates the scripts required to create and populate the instance.
Warehouse Builder generates the following types of scripts for a target warehouse:
When you generate objects, a single script is generated for each physical object you want to create. For example, there is one script for each index you are creating.
This is useful if you need to re-deploy a single object at a later time without deploying the entire warehouse again.
To generate scripts for a set of objects in a warehouse module:
You can select an individual object, a set of objects, or all the objects in the module.
The Generation Mode dialog displays.
The scripts are generated and the Generation Results dialog displays.
The following sections summarize the scripts generated for each object in the warehouse module.
When a data warehouse pulls data from its database sources using database links, the following occurs:
For example, Warehouse Builder generates the following script to create a database link to a source.
ALTER SESSION ENABLE PARALLEL DDL; CREATE DATABASE LINK SOURCE CONNECT TO source IDENTIFIED BY manager USING 'source';
This database link is deployed to the physical instance by executing the script.
Warehouse Builder can generate multiple DDL scripts for each dimension depending on whether indexes are configured. The scripts can include a DDL script for:
After generating the DDL, the Generation Results dialog displays.
The Generation Results dialog contains a list of scripts that create the following objects:
You can view any of the generated scripts by selecting the dimension name and clicking View. The example below shows the code generated to add hierarchies onto an existing table:
ALTER SESSION ENABLE PARALLEL DDL; CREATE FORCE DIMENSION customers_DIM LEVEL customer IS CUSTOMERS.cs_cust_WH LEVEL account IS CUSTOMERS.at_account_ID LEVEL segment IS CUSTOMERS.sg_segment_ID HIERARCHY customer_sums ( customer CHILD OF account CHILD OF segment ) ATTRIBUTE customer DETERMINES (cs_loctype,cs_ship_to_num) ATTRIBUTE account DETERMINES (at_account_desc) ATTRIBUTE segment DETERMINES (sg_segment_desc);
Warehouse Builder can generate multiple DDL scripts for each materialized view depending on whether indexes are configured for the view. The scripts can include DDL scripts for:
After generating the DDL, the Generation Results dialog displays. You can view the generated code and deploy the materialized view from this dialog.
If the view you are generating is part of a mapping, Warehouse Builder does not generate a DDL script for the view. Warehouse Builder generates a regular package assuming the view can be updated.
Check to ensure the view can be updated before including it in the map. Views and materialized views are generated in the same way as tables.
Warehouse Builder generates multiple scripts to implement each PL/SQL mapping:
The Generation Results dialog includes a Run button. Click Run to load data into the target warehouse. See Chapter 10, "Managing Loads and Updates" for information on how to schedule and monitor database loading using Oracle Enterprise Manager and Oracle Workflow. See Chapter 11, "Administration" for information on analyzing audit and error messages using the Runtime Audit Viewer.
Warehouse Builder generates a single DDL script for each sequence defined by the warehouse module. Each script creates a sequence object in the physical instance.
Warehouse Builder generates multiple DDL scripts for each table, including DDL scripts for:
After generating the DDL, the Generation Results dialog displays. You can view the generated code and deploy the dimensions from this dialog.
After the scripts have been generated, the Generated Scripts dialog displays with two tabs:
The Schema Objects tab displays objects that you can deploy to create your warehouse.
Select from these actions:
The Transforms tab displays mappings that you can deploy to create your warehouse. Select an object and choose from these actions:
You can select which scripts to deploy to your warehouse. You deploy all scripts when you create the warehouse. If you decide to create additional objects, such as indexes, at a later time, you can generate and deploy those scripts without re-deploying the entire warehouse.
Warehouse Builder writes the generated script to a subdirectory that corresponds with the script language as defined in the Generation Results or Generated Scripts dialog. The script is written to the file:
\Top Directory\ddl\database_link_name.ddl
For example, the generated scripts for the Customers dimension are written to the DDL subdirectory in the following form:
e:\target\ddl\Customers.ddl
e:\target\ddl\Customers_DIM.ddl
Where the Top Directory was configured as e:\target.
To create and populate the physical instance of the data warehouse, you run the generated scripts to deploy objects in the target schema, deploy the scripts to an operating system directory, register the scripts with Oracle Enterprise Manager and Oracle Workflow, and then schedule jobs to initially load the warehouse.
To deploy generated job scripts:
The Generated Scripts dialog displays with the Schema Objects tab on top.
The Connection Information dialog displays.
The Database Deployment dialog displays. Use this to verify the objects you are deploying.
The deployment process writes the scripts to the file system configured as Top Directory in the warehouse module. Once the process is complete, the Database Deployment dialog displays again with the results of the deployment displayed in the lower-half of the dialog.
The scripts have now been deployed to the database.
After you generate the scripts for SAP mappings (choose Module then Generate), Warehouse Builder opens the Generation Results dialog. Use this dialog to deploy the ABAP code to the SAP target system.
Choose from these actions:
View Code: Views the generated code in a code editor dialog. You can edit, print, or save the file through this editor.
Save as File: Displays the File System Deployment dialog. Click Save to save the generated scripts to a file system.
Run: Displays the Connection Information dialog. Enter the following information to connect to the runtime library where you want to deploy the ABAP code.
The Connection Information (SAP Runtime) dialog for SAP targets displays the system settings for the target. Enter the following information to connect to the SAP system where you want to deploy the ABAP code.
Once you have entered the complete connection information to your SAP target system, the Run the Objects dialog displays. When you click Run, Warehouse Builder automatically performs the following procedure:
You can execute this procedure manually. When you generate an ABAP script for an SAP mapping, Warehouse Builder creates an ABAP program that you can run from the SAP GUI. Running this program creates two files for the SAP mapping:
Copy the data files and control files for all of your SAP mappings to your Warehouse Builder staging area, and then run the SQL*Loader control files to process the data files into your data warehouse.
Deployment of PL/SQL scripts for SAP transparent tables is the same as deployment of PL/SQL scripts for Oracle database sources. The PL/SQL scripts run in your Oracle data warehouse and perform remote queries to extract table data from the SAP application.
You can upgrade a warehouse physical instance after the initial load. You can drop, reconfigure, rename, and upgrade the objects in the instance to reflect changes in your environment. Warehouse Builder enables you to propagate incremental changes from your logical warehouse design to your physical instance, without having to drop objects or lose existing data. For example, you have tables containing data in your physical instance. If you modify the definition of these tables within Warehouse Builder by adding an index, changing a constraint, or renaming a column, you can directly reconcile these changes with the tables in your physical instance with a warehouse upgrade. Your changes are applied in a manner that preserves the existing rows of data within the tables in the physical instance.
Warehouse Builder analyzes the existing contents of the warehouse and synchronizes them with changes in the Warehouse Builder repository. Next, Warehouse Builder provides an Impact Report detailing the plan for the upgrade and generates necessary SQL and DDL scripts to manage this transition. Before executing the scripts, review the Impact Report to decide whether to perform the upgrade.
You can create, add, update, and rename the following Warehouse objects:
|
Dimension Table |
Materialized View |
Constraint (UK, FK, and check constraint) |
|
Dimension Object |
Materialized View log |
Indexes |
|
Hierarchy |
Table |
Database link |
|
Fact Table |
View |
|
a
A SQL summary of the changes is provided by Warehouse Builder. Warehouse Builder then generates a Tcl script to deploy the changes.
To generate and execute upgrade scripts:
You can select an individual object, a set of objects, or all the objects in the module.
The Generation Mode dialog displays.
The Connection Information dialog displays.
The Generation Progress panel displays indicating the progress of the script generation for the selected objects.
After the process is complete, the Generation Results dialog displays an Impact Report listing all objects to be upgraded using the newly generated scripts. For more information on the options available on this dialog, "Post-Generation Tasks".
The Upgrade dialog displays the script that upgrades the objects that were selected.
While upgrading, the Execution progress area of the dialog reports the upgrade progress.
The physical instance in your target warehouse is upgraded.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|
