|Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)
|PDF · Mobi · ePub|
Warehouse Builder is also a design tool that enables you to design your data warehouse. Target schemas contain all the necessary data objects in your data warehouse 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 target schemas, both relational and dimensional, using the Data Object Editor.
This chapter includes the following topics:
To create a target schema, you create any of the dimensional or relational objects listed in Table 13-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).
A 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.
If you have not already done so, create an Oracle module that will contain the objects for your target schema. Ensure that the location associated with this module refers to the target schema.
Create the relational data objects.
Relational objects that you can create include tables, views, materialized views, and sequences. You can create additional structures pertaining to relational objects such as constraints, indexes, and partitions. You may have already imported some existing target objects. To create additional data objects, refer to "Defining Oracle Data Objects".
Note that 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 properties. You can choose to modify these default values.
Validate the data objects.
Validation verifies the metadata definitions and configuration properties of data objects. Correct any errors that are encountered during the validation.
Generate code that will create these data objects in the target schema.
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-focussed environment. This helps you obtain answers to complex analytical queries quickly and more efficiently.
If you have not already done so, create the Oracle module that will contain your dimensional objects. Ensure that the location associated with this module refers to the target schema.
Create the dimensions required in your target schema.
Create time dimensions.
Data warehouses use time dimensions extensively to store temporal data. See "Creating Time Dimensions".
Create the cubes required for the target schema.
See "Creating Cubes".
Configure the dimensions and cubes.
Configure the dimensional objects you created in steps 2, 3, and 4 to set physical properties for these objects. You can accept the default properties or modify them.
Validate the dimensions and cubes.
In this step, you verify the metadata definitions and configuration properties of the dimensional objects created in steps 2, 3, and 4. Correct any errors resulting from the validation.
Generate code that will create these dimensions and cubes in the target schema.
Note:Defining dimensional objects only creates the definitions of these objects in the workspace. To create these objects in the target schema, you must deploy these objects
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 prior to 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:"Creating Additional Configurations" in the Oracle Warehouse Builder Installation and Administration Guide.
All objects have a Deployable parameter, which is selected by default. To prevent an object from being deployed, clear this parameter.
You can configure objects using the Data Object Editor or the Project Explorer. To configure an object using the Data Object Editor, use the Configuration panel of the editor. This panel displays the configuration details for the object currently selected on the canvas. You can even drill down to, say and index in a table in the Selected Objects tab of the Explorer panel to see those configuration details.
To configure an object using the Project Explorer:
In the Project Explorer, select the object and click the Configure icon.
Right-click the object and select Configure.
The Configuration Properties dialog box is displayed.
Select a parameter to display its description at the bottom of the right panel.
Enter your changes and click OK.
Validation is the process of verifying metadata definitions and configuration parameters. These definitions must be valid before you proceed to generation and deployment of scripts.
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 display. Warehouse Builder enables you to open object editors and make corrections to any invalid objects before continuing. In addition to being a standalone operation, validation also takes place implicitly when you generate or deploy objects.
To detect possible problems and deal with them as they arise, you can validate in two stages: after creating data object definitions, and after configuring objects for deployment. In this case, validating objects after configuration is more extensive than validating object definitions.
Tip: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 configuration.
When you validate an object after it has been defined, the metadata definitions for the objects you have designed are checked for errors. For example, if you create a table, Warehouse Builder requires that columns be defined. When this object is validated, Warehouse Builder verifies that all components of the table have been defined. If these components are missing, validation messages display in the Validation Results window.
If you validate an object after it has been configured, metadata definitions are re-checked for errors and configuration parameters are checked to ensure that the object will be generated and deployed without any problems. You can then make edits to invalid objects.
You can validate a single object or multiple objects at a time. 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 Project Explorer or the Data Object Editor to validate data objects.
When you validate objects, Warehouse Builder displays the Validation Results window that contains the results of the validation. For more information about this dialog box, click Help and then Topic.
In the Project Explorer, select the data object and click the Validate icon. You can select multiple objects by holding down the Ctrl key while selecting objects.
In the Project Explorer, 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, ensure that the Ctrl key is pressed when you right-click.
Right-click the icon representing the data object on the Data Object Editor canvas and select Validate.
Select the object on the canvas and either click the Validate icon or select Validate from the Object menu.
The results of validating data objects are displayed in the Validation Results window. From this window, you can access the editors for objects and rectify errors in their definition, if any.
To edit invalid definitions:
In the Validation Results window, double-click an invalid object from the tree or from the validation messages grid.
An editor for the selected object is displayed.
Edit the object to correct problems.
Close the editor when you are finished and re-validate.
When you generate data objects, Warehouse Builder produces the code required to create the data objects in the target schema. Warehouse Builder generates the following types of scripts:
DDL scripts: Creates or drops database objects.
SQL*Loader control files: Extracts and transports data from file sources.
ABAP Scripts: Extracts and loads data from SAP systems.
You can view the generated scripts and also store them to a file system.
When you generate code for a data object, 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 enables you to discover and correct any errors in data object definitions before the code is generated.
Use the Project Explorer or the Data Object Editor to generate code for data objects. When you generate objects, Warehouse Builder displays the Generation Results window that contains the results of the generation. For more information about this window, click Help and then Topic.
To generate a single data object, select the data object and click the Generate icon. Or right-click the data object and select Generate.
To generate code for multiple objects, select the objects by holding down the Ctrl key and click the Generate icon. Or select the data objects and, while continuing to hold down the Ctrl key, right-click and select Generate.
Open the Data Object Editor for the data object by right-clicking the object and selecting Open Editor. The canvas displays a node that represents the data object.
Right-click the data object node on the canvas and select Generate.
Select the data object node on the canvas. Click the Generate icon or select Generate from the Object menu.
To view the generated scripts:
From the Generation Results window, select an object in the navigation tree on the left of the Generation Results window.
Select the Scripts tab on the right of this window.
The Scripts tab contains a list of the generated scripts for the object you selected.
Select a specific script and click View Code.
The selected script displays in a code viewer, which is read-only.
To save generated scripts:
From the Generation Results window, select an object from the navigation tree on the left.
Select the Scripts tab from the bottom section of the window.
The Scripts tab contains a list of the generated scripts for the object you selected.
Select a specific script and click Save As.
The Save dialog box opens and you can select a location where you want to save the script file.