| Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide 11g Release 2 (11.2) Part Number E10935-05 |
|
|
PDF · Mobi · ePub |
| Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide 11g Release 2 (11.2) Part Number E10935-05 |
|
|
PDF · Mobi · ePub |
This chapter provides an overview of designing target schemas. The data in your data warehouse is stored in target schemas. This data is in the form of data objects such as tables, views, dimensions, and cubes. In a traditional data warehousing implementation, there is typically only one target schema, which is the data warehouse target. You can design both relational and dimensional target schemas.
This chapter contains the following topics:
A target schema contains the data objects that contain your data warehouse data. To design a target schema, you create any of the dimensional or relational objects listed in Table 2-1.
You can design a relational target schema or a dimensional target schema. In this section, the term dimensions refers to both regular dimensions and Slowly Changing Dimensions (SCDs).
See Also:
Oracle Warehouse Builder Concepts for glossary on terminology.To design your target schema:
Create the target module that contains the data objects for your data warehouse.
See "Creating Target Modules" for details about how to create a target module.
Define the target schema. The schema can be a relational target schema or a dimensional target schema.
For more details, see "Designing Relational Target Schemas" or "Designing Dimensional Target Schemas".
A target module is a container that holds the metadata definitions of all your data warehouse objects. Each target module corresponds to a target location that represents the physical location where the objects are stored.
See Also:
Oracle Warehouse Builder Sources and Targets Guide for more information about the targets supported by Oracle Warehouse Builder.To create a target module:
Expand the project node under which you want to create the target module and then expand the node representing the type of target to create.
A separate node is displayed for each type of target that you can create. To create a target schema in the Oracle Database, expand the Databases node and then the Oracle node. To create a flat file target, expand the Files node.
Right-click the type of target to create and select New type Module.
For example, to create an Oracle module, expand the Databases node, right-click the Oracle node, and then select New Oracle Module. To create a flat file module, right-click the Files node and select New Flat File Module.
On the Welcome page of the Create Module Wizard click Next.
On the Name and Description page, enter information for the following fields and then click Next.
Name: Enter a name for the target module.
Description: Enter an optional description for the target module.
The name and description must conform to the naming standards specified in "Naming Conventions for Data Objects".
On the Connection Information page, enter the details of the physical location where your data warehouse objects are stored and click Finish.
If you have created a location that corresponds to the physical location where the data objects are stored, then select this location from the Location list.
The target module is created and added to the Projects Navigator. You can now create your data objects in this target module.
You can create user folders to organize all or some objects in your module based on specific object characteristics. For example, you create user folders to group tables based on their functionality (sales, marketing, administration).
See Also:
Oracle Warehouse Builder Sources and Targets Guide for more information about creating user folders within modulesA relational target schema is one that contains relational data objects such as tables, views, materialized views, and sequences. All the warehouse data is stored in these objects.
To design a relational target schema:
If you have not done so, then create an Oracle module that contains the objects for your target schema. Ensure that this module is associated with the location created to store the target objects.
Define the relational data objects that are part of the target schema.
Relational data objects include tables, views, materialized views, and sequences. You may have imported some existing target objects. To create additional data objects, see "Creating Relational Data Objects". You can define additional structures pertaining to relational objects such as constraints, indexes, and partitions.
This step only creates the definitions of the objects in the workspace. To create the objects in the target schema, you must deploy these objects.
Configure the data objects.
In this step, you set the physical properties of the data objects. For example, you specify the name of the tablespace in which a table should be created. Each data object has a set of default configuration parameters. You can modify these default values.
Validate the data objects. You can only validate Oracle data objects, not data objects in other databases such as DB2 or SQL Server.
Validation verifies the metadata definitions and configuration parameters of data objects. Correct any errors that you encounter during the validation.
Generate code that creates these data objects in the target schema. You can generate code only for Oracle data objects.
Generation produces code that is required to create the data objects created in Step 2 in the target schema.
A dimensional target schema uses dimensional objects to store the data warehouse data. Dimensional objects include dimensions and cubes. Dimensional objects transform the visualization of the target schema from a table-oriented environment to a more business-focused environment. This helps you obtain answers to complex analytical queries quickly and efficiently.
You can create a dimensional target schema only in an Oracle module.
To design a dimensional target schema:
If you have not done so, then create the Oracle module that contains your dimensional objects. Ensure that the location associated with this module refers to the target schema.
Define the dimensions required in your target schema as described in "Creating Dimensions".
That this step only creates the definitions of the dimensions in the workspace. To create the objects in the target schema, you must deploy these dimensions.
Define the time dimensions required in your target schema as defined in "Creating Time Dimensions".
Data warehouses use time dimensions extensively to store temporal data.
Define the cubes required for your target schema as described in "Creating Cubes".
Configure the dimensions and cubes.
Configure the dimensional objects that you created in Steps 2, 3, and 4 to set physical properties for these objects. You can accept the default properties or modify them.
See Also:
"Configuring Dimensions" for information about configuring dimensions
"Configuring Cubes" for information about configuring cubes
Validate the dimensions and cubes.
In this step, you verify the metadata definitions and configuration parameters of the dimensional objects created in Steps 2, 3, and 4. Correct any errors resulting from the validation.
Generate code that creates these dimensions and cubes in the target schema.
Configuration defines the physical characteristics of data objects. For example, you can define a tablespace and set performance parameters in the configuration of a table. Or you can specify the type of implementation for dimensional objects. You can change the configuration of an object any time before deployment.
You can define multiple configurations for the same set of objects. This feature is useful when deploying to multiple environments, such as test and production.
See Also:
Oracle Warehouse Builder Installation and Administration Guide for more information about creating multiple configurations.All objects have a Deployable parameter, which is set to True by default. To prevent an object from being deployed, set this parameter to False.
You configure objects by using the Projects Navigator. Right-click the object in the Projects Navigator and select Configure. A new tab is displayed containing the configuration parameters of the selected object. Specify values for the required configuration parameters.
Validation is the process of verifying metadata definitions and configuration parameters. These definitions must be valid before you generate and deploy scripts.
Oracle Warehouse Builder runs a series of validation tests to ensure that data object definitions are complete and that scripts can be generated and deployed. When these tests are complete, the results are displayed. Oracle Warehouse Builder enables you to open object editors and correct any invalid objects before continuing. In addition to being a standalone operation, validation also occurs implicitly when you generate or deploy objects.
To detect possible problems and deal with them as they arise, validate in two stages: after creating data object definitions, and after configuring objects for deployment. Validating objects after configuration is more extensive than validating objects as they are defined.
Note:
Validate objects as you create and configure them to resolve problems as they arise. The same error-checking processes are run whether you are validating the design or the configuration.When you validate an object after it has been defined, the metadata definitions for the objects that you have designed are checked for errors. For example, if you create a table, Oracle Warehouse Builder requires that columns be defined. When this object is validated, Oracle Warehouse Builder verifies that all components of the table have been defined. If these components are missing, then validation messages are displayed in the Log window.
If you validate an object after it has been configured, then the metadata definitions are rechecked for errors and configuration parameters are checked to ensure that the object is generated and deployed without any problems. You can then edit invalid objects.
You can validate a single object or multiple objects simulate. You can also validate objects that contain objects, such as modules and projects. In this case, all data objects contained by that object are validated. Use the Projects Navigator to validate data objects.
Validate the Data Objects by Using the Projects Navigator
In the Projects Navigator, select the data object and click Validate. Or select Validate from the File menu. You can select multiple objects by holding down the Ctrl key while selecting objects.
or
In the Projects Navigator, select the data object or data objects. To select multiple objects, hold down the Ctrl key while selecting objects. Right-click the data object and select Validate. If you selected multiple objects, then ensure that the Ctrl key is pressed when you right-click.
Note:
You can only select multiple objects of the same type. For example, you can select eight tables, but you cannot select five tables and three views.When you validate data objects, the validation results are displayed in the Log window. A new tab named Results is displayed for each validation. This tab contains the validation results of the selected objects.
You can view validation results for objects even after you close the Results tab. Select the object in the Projects Navigator and from the View menu, select Validation Messages. The Log window displays the validation results for the selected object. You can also select a container object, such as a module node or the Tables node, to view validation results. This action displays validation messages for the all the contained objects.
The results of validating data objects are displayed in the Log window. From this window, you can access the editor for an object and rectify any errors in its definition.
To edit an invalid definition:
In the Log window, click the Results tab corresponding to the object you want to edit. Select the node representing the object and click Go To Source on the Log window toolbar.
or
In the Log window, click the Results tab displaying the validation results of the object to edit. Expand the Validation node under the object node to display the validation messages. Double-click this validation message to open the editor that edits the object. Or, right-click the message and select Go to Source.
After you edit the object to correct problems, save the changes by clicking Save All on the toolbar, and then revalidate the object.
When you generate data objects, Oracle Warehouse Builder produces the code required to create the data objects in the target schema. Usually, data objects generate a SQL script. The SQL script may contain a mixture of DDL statements and PL/SQL blocks for creating objects using APIs (such as dimension and cube generation). You can view the generated scripts and also store them to a file system.
When you generate code for a data object, Oracle Warehouse Builder first validates the object and then generates code. You may skip the validation step and directly generate code for your data objects. However, it is recommended that you validate objects before you generate them. This validation enables you to discover and correct any errors in data object definitions before the code is generated.
To generate a single data object:
In the Projects Navigator, select the data object and click Generate.
or
In the Projects Navigator, select the data object and then select Generate from the File menu.
or
In the Projects Navigator, right-click the data object and select Generate.
To generate code for multiple data objects:
In the Projects Navigator, select the data objects by holding down the Ctrl key and click Generate.
or
In the Projects Navigator, select the data objects and, while continuing to hold down the Ctrl key, right-click and select Generate.
You can select and generate code simultaneously for multiple objects only if all the data objects are of the same type. For example, you can generate code simultaneously for a set of 12 tables. However, you cannot generate code for three tables and two dimensions.
Use collections to generate code for multiple data objects which are of different types and belong to different modules. You can create a collection, add all the objects to generate simultaneously to the collection, and then generate the collection.
If you make any changes to your data object definitions, then you can generate modified scripts for your data object. Select the data object in the Projects Navigator and click Generate.
Before you view the modified scripts, close any open tabs containing previous generation scripts. Opening the same script twice without closing the original editor window displays incorrect results.
When you generate objects, the generation results are displayed in the Log window, if the validation completed successfully. The results of each generation operation are displayed in a separate Results tab.
To view the generated scripts:
In the Log window, click the Results tab that contains the generation results for the required object.
Expand the object node and then the Validation node to view the validation results.
After the data object is validated successfully, Oracle Warehouse Builder generates scripts.
Expand the Scripts node under the object node.
A list of the scripts generated for the selected data object is displayed.
Select a specific script and, in the Log window toolbar, click View Script. Or right-click a specific script and select Go to Source.
The selected script is displayed in a read-only code viewer.
To save generated scripts:
In the Log window, click the Results tab that contains the generation results for the required object.
Expand the object node and then the Scripts node.
A list of the generated scripts is displayed, under the Scripts node, for the object that you selected.
Select a specific script and, on the toolbar, click Save Script As.
The Save dialog box opens and you can select a location where you want to save the script file.
|
 Copyright © 2000, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
