4
Defining Dimensional Targets

This chapter describes how to define dimensional objects within a warehouse target module.

This chapter includes the following topics:

• About Star Schemas

• Creating Dimension Definitions

• Creating Fact Table Definitions

About Star Schemas

A typical dimensional model used as the target schema for a data warehouse contains a fact table and dimensions. This is known as a star schema. It can also contain materialized views and a conventional view.

Figure 4-1 shows a fact table linked to a set of dimensions over foreign key constraints.

Figure 4-1 Sample Star Schema

Text description of the illustration desalgrf.gif

For information on star schemas and dimensional models, see the Oracle8i/9i Data Warehousing Guide.

You should create schema objects in the following order:

1. Dimensions

2. A fact table

3. Views

Dimensions are created first and objects that reference the dimensions second. You can create the definition for the fact table first by omitting the foreign key reference columns and adding them later after the dimensions have been defined. When a fact table relies on the server to verify foreign key references, this same order must be followed.

About Dimension Tables

Dimension tables contain descriptive details that give meaning to the numbers that populate fact tables.

Query performance can be improved because users often analyze data by drilling down on known hierarchies. Examples of hierarchies are a temporal hierarchy of Year, Quarter, Month, Day and a brand hierarchy of Category, Brand, Product. Oracle8i/9i makes use of defined hierarchies by rewriting queries to retrieve data from summary rather than detail tables. The rewritten queries run much faster.

Typical dimension tables have the following characteristics:

• A single column primary key populated with values called warehouse keys.

• Warehouse keys that provide administrative control over the dimension, support techniques that preserve dimension history, and reduce the size of fact tables.

• One or more hierarchies that are explicitly defined as dimension objects. Hierarchies defined by a Create Dimension statement maximize the number of query rewrites by the Oracle8i/9i server.

About Fact Tables

Fact tables contain measures and link to one or more dimension tables. Most fact table measures are additive. Common additive measures include dollars, units, and cost. Fact tables can include non-additive and semi-additive measures such as margins, averages, and balances. Event fact tables do not contain measures.

Fact tables are linked to dimension tables over foreign key constraints. These constraints are critical in a data warehousing environment where data integrity is paramount. The constraints enforce referential integrity during the daily operation of the data warehouse. Referential integrity constraints can cause the data loading to slow down because they require an enormous number of look-up operations.

When dimensions are designed with warehouse keys, the fact table row length is usually reduced because warehouse keys are shorter than their natural counterparts. The result is improved query performance.

A typical fact table contains:

• A primary key defined on the set of foreign key reference columns or, in the case of a data list, on an artificial key or a set of warehouse key columns. When the fact table is a data list, the foreign key reference columns do not uniquely identify each row in the fact table.

• A set of foreign key reference columns that link the table with its dimensions.

When you create a definition for a fact table, you must define its measures and its foreign key references. To define a foreign key reference, you include the name of the referenced dimension and its primary key column.

About Materialized Views

Materialized views are summary tables used by the Oracle8i/9i server to rewrite queries so they execute faster. A query that retrieves product totals from a monthly summary table will run much faster than a query that retrieves data from a detail table and then calculates the monthly subtotals. Because the first query retrieves far fewer rows and performs fewer calculations, it executes much faster.

End users do not know whether the materialized views exist or how to rewrite queries. The server automatically does this work. The Oracle8i/9i server can also refresh materialized views as the detail data changes.

When you create materialized views, you can enable query rewrite. To maximize the number of query rewrites, create dimension and fact tables so they include the physical characteristics of dimensions and facts described previously. See the Oracle8i/9i Data Warehousing Guide for more information about query rewrites.

About Conventional Views

Conventional views simplify and secure data within a data warehouse without requiring additional physical storage space. For example, a view could calculate and return standard deviations for brand summaries rather than a list of details. This simplifies access for users. Another view could restrict access to the sales values of specific products. This type of view simplifies management of the data warehouses for the database administrator.

Although the procedure for creating a definition for a view is similar to creating a materialized view, the objects themselves are different. A materialized view occupies storage space; a view does not. A materialized view speeds queries; a view has a different purpose. Materialized views are designed for the server and users do not know about their existence; views are displayed to users.

Creating Dimension Definitions

This section describes how to create dimensions using the New Dimension Wizard. Use the New Time Dimension Wizard to create a time dimension. This section also describes how to modify the definitions using property sheets.

Rules for Dimension Objects

A dimension definition includes a dimension object definition and a dimension table definition. This section provides information about the dimension object.

Table 4-1 summarizes the rules for dimensions.

Table 4-1 Warehouse Builder Rules for Dimension Objects 

Rule	Description
Denormalized	A generated dimension object is defined on a single table. Warehouse Builder does not currently support the definition of a dimension object on a set of normalized tables.
Functional Dependence	Child values must uniquely determine their parent's value. For example, a city determines a state, a month determines a quarter, and a product determines a brand. These relationships must obtain in the physical data else queries can return incorrect result sets.
Unique Key Generation	Warehouse Builder generates a unique key constraint only on the lowest level of a hierarchy.
Foreign Key References	A table can reference only the lowest level of a hierarchy (where the unique key constraint is always defined).

About Levels and Hierarchies

Dimension objects consist of a set of levels and a set of hierarchies defined over those levels. The levels represent levels of aggregation. Hierarchies describe parent-child relationships among a set of levels.

For example, a typical calendar dimension could contain five levels. Two hierarchies can be defined on these levels:

H1: YearL > QuarterL > MonthL > WeekL > DayL

H2: YearL > WeekL > DayL

The hierarchies are described from parent to child, so that Year is the parent of Quarter, Quarter the parent of Month, and so forth.

About Unique Key Constraints

When you create a definition for a hierarchy, Warehouse Builder creates an identifier key for each level of the hierarchy and a unique key constraint on the lowest level. Warehouse Builder uses the identifier keys during the generation phase to build a DDL script to create the dimension object.

When you create a foreign key reference constraint on a fact table that points to a dimension, Warehouse Builder shows the unique key constraint and the other identifier keys as candidates for the referenced column. A fact table can reference only the lowest level of a hierarchy because it contains a unique key constraint. If you select any other level, the definition is invalid.

Figure 4-2 Fact Properties Dialog Showing the Foreign Keys Tab

Text description of the illustration fpfkrlev.gif

About Mixed Levels of Aggregation

An application can require two hierarchies that start at different levels of aggregation. For example, you can have the following hierarchies:

H1: YearL > QuarterL > MonthL > WeekL > DayL

H2: YearL > WeekL > DayL

H3: YearL > QuarterL > MonthLowL

To model this mixed case using Warehouse Builder:

• The dimension table must contain the additional column MonthLow.

• The MonthLow column must be populated with unique values.

• A separate MonthLow level must be defined for the dimension.

For this set of hierarchies, Warehouse Builder generates six level identifiers and two unique key constraints. One unique constraint is defined on the Days column and the other on the MonthLow column. Because DayL and MonthLowL are at the bottoms of their respective hierarchies, they can serve as targets of foreign key references.

Warehouse Builder generates a dimension as a single denormalized table with a set of levels and hierarchies defined on that table. Each level can have any number of columns.

Creating a Dimension Definition

To create definitions for a dimension, use the New Dimension Wizard. You name the dimension table and define a primary key constraint on its warehouse key column. When you define each column, Oracle recommends setting the constraint to NOT NULL to prevent inconsistent result sets and to maximize the number of query rewrites.

You also define the dimension hierarchy and its levels of aggregation. Table 4-2 provides an example of a dimension table with each level of aggregation, a prefix for each level, and the attributes defined on each level. The levels occur in parent to child order: class is the parent of family and family is the parent of product.

Table 4-2 Example of a Dimension Object  

Level	Prefix	Attribute	Data Type	Description
class	cl	class_id	number	Level identifier or key
		class_desc	varchar(20)	Description of product class
family	fa	family_id	number	Level identifier or key
		family_desc	varchar(20)	Description of product family
product	pd	prod_WH	number	Base level or warehouse key
		item_desc	varchar(35)	Description of the product
		product_upc	varchar(11)	Universal product code (natural key)
		item_source	varchar(30)	Supplier for product
		packaging	varchar(20)	Packaging for the product

To create a dimension definition:

1. Right-click Dimensions and select Create Dimension.

   Warehouse Builder displays the welcome page for the New Dimension Wizard.

2. Click Next.

   The wizard displays the Name page.

3. Type the following:
   • A name for the dimension.

   • A prefix.

     The prefix is used to generate a unique name for the unique key constraint on the base level key column. If the prefix is blank, the table name is used.

   • A description of the dimension (optional).

Figure 4-3 Name Page

Text description of the illustration wizdimna.gif

4. Click Next.

   The wizard displays the Levels page.

Figure 4-4 Levels Page

Text description of the illustration wizdimla.gif

Note: Dimensions contain at least one level of aggregation. You can define a default level of aggregation to satisfy this requirement and include additional levels as required.

5. Define levels of aggregation in the dimension. Enter the following:
   • The name of the level.

   • A prefix for the level. The default prefix is the name of the level.

   • A description of the level.

6. Click Add to add the level. Continue this process until you have defined each level of aggregation.

   Prefixes are useful because they:

   • Reduce the number of attributes you must enter manually. The wizard automatically generates an ID attribute for each level and assigns it the name levelprefix_ID.

   • Allow you to reuse attribute names. This is a common practice when you build dimensions for higher levels of aggregation.

     Note: The dimension prefix is used to form the names of level unique keys. After a unique key name is generated, changing the dimension prefix does not change the names of existing levels because fact foreign keys may already refer to the generated level. Unique key names generated after you edit the prefix use the new prefix.

   7. Click Next.

      The wizard displays the Level Attributes page. A level can have one or more attributes. The wizard generates an ID attribute for each level.

      The ID attribute for a level identifies the level. The attribute is the level's logical key column. This attribute is used in the CREATE DIMENSION statement to define the level, and the defined level is used in the statement's DETERMINES clause to specify other columns within that level (dependent columns). See the Oracle8i/9i SQL Reference and the Oracle8i/9i Data Warehousing Guide for more information.

   8. Provide the following information:
      1. Select a level of aggregation from the drop-down list.

      2. Type a name for the attribute.

      3. Select a data type for the attribute from the drop-down list under Data Type. Warehouse Builder supports the following Oracle8i/9i data types:
         • CHAR

         • DATE

         • FLOAT

         • NUMBER

         • VARCHAR

         • VARCHAR2

      4. Enter the precision, length, or scale as appropriate for the data type.

      5. Type a description of the attribute.

   9. Click Add.

      Figure 4-5 Level Attributes Page

      

      Text description of the illustration wizdimat.gif

      You can define another attribute for the selected level or select another level and define its attributes. Continue this process until you have defined all the attributes for each level.

      If you want to rename the ID column, select ID in the Level Attributes text box.

      1. Type a new name in the Name text box.

      2. Click Update.

      10. Click Next.

          The wizard displays the Hierarchies page.

      11. Define the hierarchy:
          • Type a name and prefix for each hierarchy.

          • Type a description of the hierarchy.

      12. Click Next.

          The wizard displays the Level Relationships page.

      Figure 4-6 Level Relationships Page

      

      Text description of the illustration wizdimle.gif

      13. Define the levels within a hierarchy:
          • Select a hierarchy from the drop-down list.

          • Move the names of levels for a selected hierarchy from Available Levels to Selected Levels.

          • Arrange the levels so that they show the parent to child order.

      14. Click Next.

          The wizard displays the Finish page. Verify the description.

      15. Click Finish.

          The wizard creates a definition for the dimension.

      The wizard generates a unique key (UK) constraint for a dimension table on the ID column that represents the dimension's base level of aggregation. Dimensional designs often call for a primary key (PK) rather than a UK constraint. After you complete a definition for a dimension, you can change the UK to a PK constraint. See "Changing Key Constraints" for information.

      Updating Dimension Definitions

      You can update the definition for a dimension object with the Dimension Editor or by editing entries in the dimension property sheet.

      Using the Dimension Editor

      To display the Dimension Editor, right-click a dimension in the navigation tree and select Edit.

      Figure 4-7 Dimension Editor

      

      Text description of the illustration deopen.gif

      The Dimension Editor displays a toolbox and the dimension object.

      To add an element to the dimension object, drop an icon from the toolbox onto a dimension element.

      To add an attribute to a level:

      1. Fully expand the Level where you want to add the new attribute.

      2. Drop the Attribute icon on the Level.

         Warehouse Builder adds an attribute (attribute1) in the level with the number data type.

      3. Enter a name for the attribute.

      4. To change the data type, double-click the attribute name.

         The Dimension Editor displays the dimension property sheet.

      5. Select the Level Attributes tab.

      6. Select a data type from the drop-down list.

      7. Change the length, scale, or precision depending on the data type selected.

      8. Click Update.

      Figure 4-8 Level Attributes Tab of Dimension Properties Sheet

      

      Text description of the illustration deprpla.gif

      To print the diagram, click the Print icon on the Dimension Editor toolbar.

      Using the Property Sheets

      The dimension object and the dimension table both have property sheets. In the dimension object property sheet, you edit the levels and hierarchies. In the dimension table property sheet, you edit the columns and constraints.

      To display the dimension object property sheet:

      • In the Dimension Editor, from the Edit menu, select Properties or click the Properties icon.

      • In the Warehouse Module Editor, right-click the dimension and select Properties.

      Warehouse Builder displays the property sheet for the dimension object.

      Figure 4-9 Dimension Object Properties Sheet

      

      Text description of the illustration deproprp.gif

      The dimension object property sheet has the following tabs:

      • Name

      • Levels

      • Level Attributes

      • Hierarchies

      • Level Relationships

      To display the dimension table property sheet:

      1. Open the Dimension Editor.

      2. Select Table Properties from the Edit menu.

         Warehouse Builder displays the dimension table property sheet. For information about the table property sheet, see "Using the Table Property Sheet" .

      Creating a Time Dimension Definition

      This section describes how to use the New Time Dimension Wizard. The Time Dimension Wizard generates a SQL insert statement to populate the time dimension. You do not need to extract data from a data source. You must set the start and end dates for the statement that generates the data. See "Guidelines for Configuring Dimensions, Facts, and Tables" for information on configuring the dates.

      The Time Dimension Wizard uses predefined names and prefixes for levels, hierarchies, and attributes. The attributes have predefined data types. After you create the definition, you can update the property sheet.

      Note:: Time dimensions differ considerably and many designs depend on multiple time dimensions. The New Time Dimension Wizard covers a limited number of cases. If the wizard does not meet your needs, use the New Dimension Wizard instead.

      To create a time dimension definition:

      1. In the Warehouse Module Editor, right-click Dimensions and select Create Time Dimension.

         Warehouse Builder displays the welcome page for the New Time Dimension Wizard.

      2. Click Next.

      3. Type the following:
         • A name for the dimension.

         • A prefix.

         • A description of the dimension.

      4. Click Next.

         The Levels page contains predefined levels of aggregation and a prefix for each level.

      Figure 4-10 Levels Page

      

      Text description of the illustration wztdmlev.gif

      5. Check the levels of aggregation required to support your dimensional model.

      6. Click Next.

         The wizard displays the Level Attributes page. This page contains a set of predefined attributes for each level.

      Figure 4-11 Level Attributes Page

      

      Text description of the illustration wztdmatt.gif

      • Select a level of aggregation from the drop-down list.

      • Check the required attributes for that level.

      Continue this process to select attribute sets for each level.

      • Click Next.

        The Hierarchies page contains a set of predefined attributes for each level.

      Figure 4-12 Hierarchies Page

      

      Text description of the illustration wztdmhie.gif

      8. Check the predefined hierarchies required by the time dimension.

      9. Add custom hierarchies required by the time dimension:
         1. Click Add.

         2. Name the hierarchy.

         3. Describe the hierarchy.

      10. Click Next.

          The wizard displays the Level Relationships page.

      11. Define the levels for each custom hierarchy by moving the level from Available Levels to Selected Levels. The wizard automatically orders the levels.

      12. Click Next.

          The wizard displays the Finish page.

      13. Click Finish.

          The wizard stores the definition in the warehouse module.

      The New Time Dimension Wizard generates the attributes you select plus additional attributes and constraints. In addition to the selected attributes, the wizard generates:

      • An ID attribute for each level.

      • A Smart_key attribute for the base level.

      • A UK constraint on the ID attribute for each level, which you can modify but not remove. See "Changing a Constraint".

      • A PK constraint on the Smart_key attribute.

      You can edit the names of constraints, levels, attributes, and hierarchies in the dimension property sheet. For additional information on editing a dimension object, see "Updating Dimension Definitions".

      Creating Fact Table Definitions

      This section describes how to create and update a definition for a fact table. You create a definition for a fact table using the New Fact Table Wizard, and you update the definition by editing its property sheet. You can also import definitions for tables from another database source or an Oracle Designer Repository.

      You use the New Fact Table Wizard to create definitions for a fact table. This information includes details regarding foreign key references, measures, and the data types of all the table's columns.

      Creating a Definition for a Fact Table

      This section describes how to create a fact table definition.

      To create a fact table definition:

      1. Right-click FACTS and select Create Fact.

         Warehouse Builder displays the Welcome page for the New Fact Wizard.

      2. Click Next.

         The wizard displays the Name page.

      3. Enter the following:
         • The name of the fact table

         • A description of the fact table (optional)

      4. Click Next.

         The wizard displays the Define Foreign Keys page.

      Figure 4-13 Define Foreign Keys Page

      

      Text description of the illustration wzfac.gif

      5. Define the foreign key references:
         1. Select the name of a dimension from the Dimension drop-down list.

         2. Select the base key level from the Level drop-down list.

         3. Select the primary key column constraint defined on the dimension from the Unique Key drop-down list.

         4. Click Add.

            The wizard inserts the foreign key reference constraint in the text box that lists the foreign keys.

         5. Repeat these steps for each foreign key constraint. Select the lowest level of aggregation for the foreign key reference target.

            Note: The Fact Wizard displays the name for each generated PK constraint on a dimension's level columns. Only the lowest level PK constraint is an actual physical constraint. You cannot modify the name or data type of the foreign key reference columns. You can only do this by editing the definition for the referenced table. If you add a column to a PK or UK constraint on a dimension, you must also update the fact table foreign key references.

      You can change the name of the generated foreign key by selecting the name and typing over it. The name must be unique within the project.

      Figure 4-14 Foreign Keys

      

      Text description of the illustration wzfac2.gif

      6. Check the box next to Create segmented unique key from foreign keys.

      7. Click Next.

         The wizard displays the Define Measures page.

      Figure 4-15 Define Measures Page

      

      Text description of the illustration wizfctme.gif

      8. Define the measures for the fact table:
         1. Click Add.

         2. Type the name of the measure.

         3. Select the data type of the measure.

         4. Repeat these steps for each measure in the fact table.

      9. Click Next.

         The wizard displays the Finish page. This page summarizes the fact table. Click Back to modify any of the elements.

      10. Click Finish.

          The wizard creates a definition for a fact table, stores it in the warehouse module, and inserts its name in the warehouse module's navigation tree.

      Updating a Fact Table Definition

      A fact object has two property sheets: one for the fact object and another for the fact table. You can update a fact object's properties by editing the property sheets. In addition, you can add foreign key references or measures to a fact object using the Fact Editor. You can also use the Fact Editor to change fact properties and foreign key relationships with dimensions.

      Using the Fact Editor

      To open the Fact Editor, right-click a fact table in the Warehouse Module Editor and select Edit from the pop-up menu.

      Warehouse Builder displays the Fact Editor containing a tool palette and a diagram of the fact table and the related dimensions.

      Figure 4-16 Fact Editor

      

      Text description of the illustration fesal.gif

      To print the diagram, click the printer icon on the Fact Editor toolbar.

      To display the fact object property sheet:

      • From the Fact menu, select Fact Properties or click the Properties icon.

      • Right-click the fact and select Properties.

      The properties include the object's name and description, foreign key references, measures, and attribute sets.

      Figure 4-17 Fact Properties Sheet

      

      Text description of the illustration felivtbl.gif

      From the Fact Properties sheets, you can:

      • Change the name and description of the object.

      • Add or Remove a foreign key reference constraint.

        This Foreign Keys sheet shows all the UK constraints defined on a dimension: the base level of aggregation and each higher level of aggregation. Warehouse Builder generates DDL only for the constraint defined on the base level of aggregation.

      • Change the name of a foreign key reference constraint.

      • Add, Remove, or edit a measure (name, data type, and description).

      Edit the table property sheet if you want to change the physical properties of a fact.

      To display the fact table property sheet, right-click the fact and select Table Properties from the pop-up.

      Figure 4-18 Table Properties Sheet

      

      Text description of the illustration deprotab.gif

      From the Table Properties sheets, you can:

      • Change the name and description of the object.

      • Add a new column, edit the name, data type, column position, and description of an existing column, or remove a column. For a description on positioning columns, see "Ordering the Columns".

      • Add, remove, or edit a foreign key reference constraint.

      • Add, remove, or edit attribute sets.

        Note: Although you can change a UK constraint to a PK constraint, you cannot change an FK constraint to another kind of constraint. You must first remove the constraint and then create a new one.

      Changing Key Constraints

      The Oracle8i/9i server can automatically refresh materialized views provided that a materialized view log file is created for the underlying fact table. To build the log file, the fact table must have a PK constraint. You can change the composite UK constraint defined on a fact table to a PK constraint.

      Figure 4-19 Constraints Tab of the Table Properties Sheet

      

      Text description of the illustration deprpcon.gif

      To change key contraints:

      1. Open the Table properties sheet.

      2. Select the Constraints tab.

      3. Select the generated segmented key name.

      4. Select Primary Key from the Type drop-down list.

      5. Change the name of the constraint to reflect its new property.

      6. Click OK.

      Importing Definitions

      You can import definitions for tables, views and sequences using the Import Metadata Wizard. See "Importing Definitions from a Database" for instructions.

      Adding Transformations

      Transformations are pre-built PL/SQL functions, procedures, package functions, and package procedures. They take input data, perform operations on it, and produce output data. Custom transformations are used to define an operation outside of the Oracle Library. You create a custom transformation using the New Transformation Wizard.

      The following sections describe the transformation libraries and how to create custom transformations.

      About Transformations

      Warehouse Builder supports the following transformation types:

      • User Transformation Package: This category contains package functions and procedures that you define.

      • Predefined Transformations: These categories exist in the Oracle Library and consist of built-in and seeded functions and procedures.

      • Functions: The functions category is automatically created in every warehouse module. This category contains any standalone functions used as transformations. These functions can be defined by the user or imported from a database. A function transformation takes 0-n input parameters and produces a result value.

      • Procedures: The procedures category is automatically created in every warehouse module. This category contains any standalone procedures used as transformations. These procedures can be defined by the user or imported from a database. A procedure transformation takes 0-n input parameters and produces 0-n output parameters.

      • Imported Package: This category is created by importing a PL/SQL package. The package body may be modified. The package header, which is the signature for the function or procedure, cannot be modified. The package can be viewed in the transformation library property sheet.

      About Transformation Parameters

      Most transformations have parameters. The input parameter specifies a source of data, the output specifies a result. More complex transformations often have multiple input, input/output, and output parameters.

      About Oracle Transformation Libraries

      Each time you create a warehouse module, Warehouse Builder creates a Transformation Library for that module containing transformation operations. This library contains the standard Oracle Library and an additional library for each warehouse module defined within the repository.

      Transformation Libraries consist of the following types:

      • Global Shared Library: a collection of re-usable transformations categorized as functions and procedures defined within your repository.

      • Oracle Library: a collection of pre-defined functions from which you can define procedures for your Global Shared Library.

      When you create a custom transformation, add it to the Global Shared Library to share across warehouse modules. If the transformation is specific to one module, add it to the transformation library within that module.

      Global Shared Library

      The Global Shared Library stores transformations that are shared across a repository. The default categories are:

      • Functions: This category stores standalone functions.

      • Procedures: This category stores standalone procedures.

      Oracle Library

      The Oracle Library includes a set of standard transformations organized into categories including:

      • Administration

      • Character

      • Conversion

      • Date

      • Numeric

      • Other

      • XML

      Accessing Transformation Libraries

      You can access the Transformation Libraries from Expression Builder, the Add Transformation dialog, or the New Transformation Wizard. You can also access Transformation Libraries from the navigation tree in the Warehouse Builder Console.

      Creating Transformation Libraries

      You can create transformation libraries to organize transformations. The following steps show you how to create transformation libraries within the warehouse module using the New Transformation Library Wizard.

      To create a transformation library:

      1. Expand the navigation tree for the active warehouse module.

      2. Right-click Transformation Libraries and then select Create Transformation Library.

         Warehouse Builder displays the New Transformation Library Wizard welcome page.

      3. Click Next.

         The wizard displays the Name page.

      4. Enter a name and description for the library.

         A library name can have from 2 to 40 alphanumeric characters but no spaces.

      5. Click Finish.

         The wizard inserts the name of the library in the module's navigation tree.

      Defining Custom Transformations

      Custom transformations are used to define an operation outside of the Oracle Library. Definitions for custom transformations are stored in a warehouse module Global Shared Library in the Transformations branch that can be subdivided into categories in the Module Editor.

      Create new transformations using the New Transformation Wizard. To create a transformation, enter the SQL or PL/SQL code that the transformation executes.

      To define a custom transformation:

      1. Open the Transformation node on the Navigation Tree in the Warehouse Module Editor

      2. Select a transformation category for the type of transformation you want to create.

      3. Right-click on the category and select Create Transformation from the pop-up menu.

         Warehouse Builder opens the New Transformation Wizard.

      4. Enter a name in the Name field and a description documenting what the transformation does.

      5. Select a transformation type from the drop-down list.

      6. Click Next.

      Figure 4-20 Transformation Name Page

      

      Text description of the illustration transnam.gif

      7. Define each parameter for the transformation on the Parameters page.
         1. Click Add.

         2. Enter a name for the Parameter in the Name column.

         3. Specify the type, the order, whether it is an Input, Output, or Input/Output parameter, and whether the parameter is required.

         Figure 4-21 Transformation Parameter Page

         

         Text description of the illustration transpar.gif

         8. Click Next.

         9. Enter the PL/SQL code for the parameter on the Implementation page.

         Figure 4-22 Transformation Implementation Page

         

         Text description of the illustration transimp.gif

         • Click Code Editor to display the code editor. The code editor has line numbers, find, deploy, and syntax checking.

         Figure 4-23 Code Editor for a New Transformation

         

         Text description of the illustration transcod.gif

         • After values have been specified for each parameter, if necessary, click Back to make corrections.

         10. Click Finish.

         Editing Transformation Properties

         You can edit a transformation on the transformation properties sheet. Make sure you edit properties consistently. For example, if you change the name of a parameter, then you must change the name in the implementation code.

         Importing PL/SQL Packages

         Using the Import Wizard, you can import PL/SQL functions, procedures, and packages into a Warehouse Builder project. When Warehouse Builder generates a script for the extract and load job, it generates the added constraint within the PL/SQL routine that implements the mapping. At runtime, you can accept the default value or supply a different one.

         When you submit the script using Oracle Enterprise Manager, you can then modify the runtime parameter value.

         The following steps describe how to import PL/SQL packages from other sources into Warehouse Builder.

         To import a PL/SQL function, procedure, or package:

         1. Open the Warehouse Module Editor.

         2. From the Module menu, select Import.

            Warehouse Builder displays the Import Metadata Wizard Welcome page.

         3. Click Next.

         4. Select PL/SQL Transformation in the Object Type field of the Filter Information page.

         Figure 4-24 PL/SQL Transformation Selection

         

         Text description of the illustration refilter.gif

         5. Click Next.

            The Import Metadata Wizard displays the Object Selection page.

         Figure 4-25 Object Selection Page

         

         Text description of the illustration reobjeca.gif

         6. Select a function, procedure, or package from the Available Objects list. Move the objects to the Selected Objects list by clicking the single arrow button to move a single object or the double arrow button to move multiple objects.

         7. Click Next.

            The Import Metadata Wizard displays the Summary and Import page.

         Figure 4-26 Summary and Import Page

         

         Text description of the illustration reobject.gif

         8. Verify the import information. Click Back to revise your selections.

         9. Click Finish to import.

            The Import Results dialog displays.

         Figure 4-27 Import Results

         

         Text description of the illustration reimport.gif

         10. Click OK.

             Click Undo to cancel the import process.

         The imported PL/SQL information appears in the Module Editor under Transformation Libraries.

         Figure 4-28 Warehouse Module with Imported Transformations

         

         Text description of the illustration retree.gif

         When you use the imported PL/SQL:

         • You can edit, save, and deploy the imported PL/SQL functions and procedures.

         • You cannot edit imported PL/SQL packages.

         • Wrapped PL/SQL objects are not readable.

         • Imported packages can be viewed and modified in the category property sheet.

         • You can edit the imported package body but not the imported package specification.

         Defining Business Areas

         A business area is a logical grouping of data within a warehouse module. These areas define links to a subset of the module's objects. You can define multiple business areas within a warehouse module. The business tree displays a warehouse module's business areas.

         Business areas are useful when the logical warehouse contains a large number of objects. Define business areas when you need to examine subsets of warehouse objects or export subsets of objects to a decision support tool such as Oracle Discoverer or Oracle Express.

         Oracle Discoverer and Oracle Express use business areas to provide their users with access to the data they need for ad hoc queries, decision support, and presentation of results. You can use the Warehouse Builder Transfer Wizard to export business areas from Warehouse Builder to Discoverer and Express.

         You can create multiple business areas that share the same links. After you create a business area, you can update its definitions using the standard Warehouse Builder editors, such as the Dimension, Fact, Table, and Mapping editors. You can also update an object definition by editing its property sheet. For additional information on updating definitions, see the previous examples in this chapter.

         Note: When you create a business area, you do not create any new objects. The business area only organizes existing objects into identifiable subsets.

         To create a definition for a subset of warehouse objects in a business area:

         1. Open the warehouse module.

         2. Click the Business Tree tab.

            Warehouse Builder displays the navigation tree for the business areas.

         Figure 4-29 Create Business Area

         

         Text description of the illustration wme_ba3.gif

         3. Right-click on the warehouse module and select Create Business Area from the pop-up menu.

            Warehouse Builder displays the Business Area dialog.

         Figure 4-30 Business Area Dialog

         

         Text description of the illustration bad_5.gif

         4. Type a name and description for the business area.

         5. Select the objects to include within the business area. You can select the check box for a type to select all objects of that type.

         6. Click OK.

            Warehouse Builder creates the business area.

         Figure 4-31 Business Tree

         

         Text description of the illustration wme_ba6.gif