| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
After you finish gathering the requirements for your data warehouse or data mart, you are ready to design your target system using Warehouse Builder. Most of your target schema modelling takes place within the Oracle warehouse module. This chapter shows you how to create an Oracle warehouse module and define the data objects within that module.
This chapter contains the following topics:
Warehouse Builder stores the definitions for your target schema in warehouse modules. These definitions can be created using the Warehouse Builder wizards or by importing them from external sources. This section shows you how to create a warehouse module. The following sections show you how to create and import definitions for data objects in the warehouse module.
To create a warehouse module:
From the Warehouse Builder navigation tree, expand the Databases node.
To create a source module for Oracle data sources, right-click the Oracle node and select Create Oracle Module.
At the welcome page for the New Module Wizard, click Next.
The wizard displays the Name page.
Specify the following information:
Name of the module.
The status of the module: Development, Quality Assurance, or Production. This is for descriptive purposes only.
Warehouse Target as the Module Type.
An optional description.
Click Next.
At the Connection Information page, you have the option to provide the database link information necessary to import metadata into the warehouse module. You can choose to skip this page and provide this information when you import the metadata.
First select your metadata source:
Oracle Data Dictionary: Select to import metadata from an Oracle database.
Oracle Designer Repository: Select to import metadata from an Oracle Designer repository.
From the Database Link field, select from a list of previously created database links. Or create a new database link by provide the following information:
Owner: The source database user who is creating this database link.
Username: Name of a user with access to the source database using the database link.
Connect String: Name of the system where the source database resides.
Schema: Name of the schema where the source database resides.
Click Next.
At the Location page, you have the option to select a location from the drop-down menu or click New to create a new location.
Locations define information about the database schema or target tool where you will be deploying objects. Locations are specific to a type of module such as Oracle Database, SAP, or flat file. For more information, see "Defining Locations".
This step is optional. You can choose to create a location for this module later when you are deploying the object.
Click Next.
At the Finish page, which summarizes the information you provided on each of the wizard pages, check the checkbox if you want to directly start the Import Metadata Wizard.
Click Finish.
The wizard creates the warehouse module and inserts its name in the project navigation tree. If you checked the check box, Warehouse Builder starts the Import Metadata Wizard. See "Importing Metadata into Target Modules".
Locations represent specific database schemas and target tools. They are specific to types of modules such as an Oracle or non-Oracle Database, SAP, or file system and are organized into the navigation tree under these modules. When you create a location, a logical definition containing location type and version is stored. When the location is registered, the physical connection information is requested and stored in the Runtime Repository.
Each location defined within a project can be registered separately within each Runtime Repository, and each registration can reference different physical information. Using this approach, you can design and configure a target system one time, and deploy it many times with different physical characteristics. This is useful if you need to create multiple versions of the same system such as development, test, and production.
When you create modules, you must specify a location for each module. You can assign the same location to multiple modules, but you must create a location for each distinct database schema or tool to which you want to deploy. For Oracle database modules, you must also define connectors to any modules it is referencing. These connectors, when deployed, may generate database links as necessary for designed data movement. Within an Oracle database module, a connector referencing a file system can also be created. This kind of connector will be generated as a database directory, which are used by external tables.
To create a new location:
Select a project, and expand the navigation tree to display Locations.
There are four main types of locations: database, file system, applications, and process flow, as shown in Figure 3-1.
Select a Locations node from under the type of module you want to create a location.
From the Object menu, select Create Location or right-click Locations and select Create Location.
At the New Location Wizard Welcome Page, click Next to continue.
In the Name page, define the following information for the location:
Name: Provide a name for the location. Maximum Length: 30 Characters.
Optional Description: Provide an optional description for the location. Maximum Length: 400 Characters.
There are no pre-assigned default values for a new location.
Click Next to continue.
In the Details Page, define the following information for the location:
Location Type: Select a type from the drop-down list for the location you are creating. The default is determined by the type of module for which the location is being created.
Version: Select a version for the type of location you are creating from the drop-down list.
Click Next to continue.
At the Finish Page, verify the definition of the new location. This page lists the name, type, and version.
Click Finish to create the location as defined. The location is created and added under the appropriate Locations node.
To edit a location, right-click the location from the navigation tree and select Properties. Warehouse Builder displays the properties window, as shown in Figure 3-2.
This page displays the properties of the selected location. Use the following two tabs to view and edit the properties. Click OK to save changes or Cancel to close the window.
Connectors define connections between Oracle database module locations and other defined module locations in the navigation tree. They are only located under the Oracle database Locations node in the navigation tree. Connectors indicate that there is a path to transport data from one location to another location.Connectors are owned by their containing Oracle database module, and they reference one other location.
When you create a connector in the navigation tree, a logical definition is stored in the Warehouse Builder repository. When objects in a location with a defined connector are deployed, a database link or directory object may be referenced if necessary. Only one connector can be defined in each direction between any two specific locations. Connectors are also created implicitly in the Mapping Editor as sources and targets are placed on the canvas. As you place sources on the canvas, a connector is created automatically between the mapping location and the source location if one does not exist already. This connector indicates that there is a path to transport data from the source location to the mapping location.If you use a flat file as a source in a mapping, no connector is created automatically; you must define a connector from the flat file source to the relational target yourself.
Note:
Connectors that reference a file system are realized as database directories. To create and drop directories, you must be granted the "create any directory" and "drop any directory" privileges. These are high privileges and are not granted behind the scenes.To create a connector:
Select a project and expand the navigation tree to display all Oracle database modules.
Expand the Locations node.
Right-click the location for which you want to create a connector.
From the Object menu select Create Connector or right-click the location and select Create Connector.
At the New Connector Wizard Welcome Page, click Next to continue.
In the Name Page, define the following information for the connector:
Name: Provide a name for the connector. Maximum Length: 30 Characters.
Optional Description: Provide an optional description for the connector. Maximum Length: 400 Characters.
There are no pre-assigned default values for a new connector.
Click Next to continue.
In the Details Page, specify the referenced location for the connector:
Database: Select this option to link to a database location. Then select the specific location name from the drop-down list.
File System: Select this option to link to a file location. Then select the specific location name from the drop-down list.
Application: Select this option to link to a application location. Then select the specific location name from the drop-down list.
Unspecified: Select this option to link to an unspecified type of location.
Click Next to continue.
In the Finish Page, verify the definition of the new connector. This page lists the name and referenced location.
Click Finish to create the connector as defined.
The connector is created and added under the location. Continue creating connectors for each location as necessary.
To edit a connector, right-click the connector from the navigation tree and select Properties. Warehouse Builder displays the properties window, as shown in Figure 3-3.
This page displays the properties of the selected connector. Use the following two tabs to view and edit the properties. Click OK to save changes or Cancel to close the window.
Name: Displays the location name. Maximum Length: 30 Characters.
Description: Displays an optional description for the location. Maximum Length: 400 Characters.
Database: Select this option to link to a database location. Then select the specific location name from the drop-down list.
File System: Select this option to link to a file location. Then select the specific location name from the drop-down list.
Application: Select this option to link to a application location. Then select the specific location name from the drop-down list.
Unspecified: Select this option to link to an unspecified type of location.
After you create a warehouse module, you can locate it by expanding the Databases node, then the Oracle node in the navigation tree. Now expand the warehouse module to view all the data objects supported by Warehouse Builder, as shown in Figure 3-4.
Warehouse Builder supports relational and dimensional data objects. Relational objects, like relational databases, rely on tables and table-derived objects to store and link all of their data. The relational objects you define are physical containers in the database that are used to store data. It is from these relational objects that you run queries after the warehouse has been created. Relational objects include tables, views, materialized views, and sequences. This chapter provides specific information about each type of relational object and how it is used in Warehouse Builder.
Dimensional objects contain additional metadata to identify and categorize your data. When you define dimensional objects, you describe the logical relationships that help store the data in a more structured format. Dimensional objects include dimensions and cubes. This chapter provides specific information about each type of dimensional object and how they are used in Warehouse Builder.
Table 3-1 describes the types of data objects you can use in Warehouse Builder.
Table 3-1 Data Objects in Warehouse Builder
| Data Object | Type | Description |
|---|---|---|
|
Tables |
Relational |
The basic unit of storage in a relational database management system. Once a table is created, valid rows of data can be inserted into it. Table information can then be queried, deleted, or updated. To enforce defined business rules on a table's data, integrity constraints and triggers can also be defined for a table. See Using Tables. |
|
External Tables |
Relational |
External tables are tables that represent data from non-relational flat files in a relational format. Use an external table as an alternative to using a flat file operator and SQL* Loader. See Using External Tables. |
|
Views |
Relational |
A view is a custom-tailored presentation of data in one or more tables. Views do not actually contain or store data; they derive their data from the tables on which they are based. Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view affect the base tables of the view. Use views to simplify the presentation of data or to restrict access to data. See Using Views. |
|
Materialized Views |
Relational |
Materialized views are pre-computed tables comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table. Use materialized views to improve query performance. See Using Materialized Views. |
|
Sequences |
Relational |
Sequences are database objects that generate lists of unique numbers. You can use sequences to generate unique surrogate key values. See Using Sequences. |
|
Advanced Queues |
Relational |
Advanced Queues enable message management and communication required for application integration. See Using Advanced Queues. |
|
Dimensions |
Dimensional |
A general term for any characteristic that is used to specify the members of a data set. The 3 most common dimensions in sales-oriented data warehouses are time, geography, and product. Most dimensions have hierarchies. See Using Dimensions. |
|
Cubes |
Dimensional |
Cubes contain measures and links to one or more dimension tables. They are also known as Facts. See Using Cubes. |
In Warehouse Builder, tables are metadata representations of relational storage objects. They can be tables from a database system such as Oracle tables or even tables from an SAP system. The following sections provide information about creating and using tables in Warehouse Builder:
The table you create in Warehouse Builder captures the metadata used to model your target schema. This table definition specifies the table constraints, indexes, and metadata about the columns and data types used in the table. This information is stored in the Warehouse Builder repository. You can later use these definitions to generate .ddl scripts in Warehouse Builder that can be deployed to create physical tables in your target database. These tables can then be loaded with data from chosen source tables.
Use the Table Wizard to create a table definition. This section describes the main pages of the New Table Wizard.
Note:
You can also create a table from the Mapping Editor.To create a table:
From the Warehouse Builder navigation tree, expand the Databases node and then the Oracle node.
Expand the module where you want to create the table.
Right-click TABLES and select Create Table.
The welcome page for the New Table Wizard displays the steps involved in creating a table.
Click Next to continue to name the table.
In physical naming mode, you must type a unique name between 1 and 28 valid characters. Spaces are not allowed. In logical mode, you can type a name up to 4000 characters long. Spaces are allowed. The name must be unique within the module. For more information, see "Naming Preferences".
After you name the table, you can continue using the wizard to define the table properties, or you can click Finish to create the table and set-up or edit these properties later using the Table Editor.
Type in a description for the table.
The description can be a maximum of 4000 characters. This is optional.
Click Next to continue and define the columns, as shown in Figure 3-5.
Type in a column name. Warehouse Builder generates the column position in the order in which you type in the columns. To re-order columns, see "Reordering Columns in a Table".
Select a data type for the column from the drop-down list under Data Type. For details on data types, see"Adding Columns". Warehouse Builder supports the following Oracle Database data types:
CHAR
DATE
FLOAT
NUMBER
VARCHAR
VARCHAR2
Type the precision, length, or scale as appropriate.
Warehouse Builder enables you to enter values relevant to the data type you select. For example, for CHAR data types you must specify length. For NUMBER data types you must specify precision and scale.
Specify NULL or NOT NULL. By default, all columns in a table allow nulls. A NOT NULL constraint prohibits a column of a table from containing null values.
Type a description of the column in the Description field (optional).
Repeat these steps 8 through 13 for each column.
Click Next to continue and define constraints.
In the Constraints page, you have the option to define constraints if required. For more information on adding constraints, see "Editing Constraints".
Click Next.
Warehouse Builder displays all of the table properties defined in the New Table Wizard. Verify the name, descriptions and various table properties.
Click Finish.
The New Table Wizard creates and stores a definition for the table in the repository and inserts the new table name in the navigation tree.
After you create a table in Warehouse Builder, you can use the Table Editor to view the table structure, table columns, and any related tables. To open the Table Editor, right-click the name of a table and select Editor the name of the table. Figure 3-6 shows the Table Editor.
Use the Table Editor Menu, the Table Editor Navigation bar, or the right-click pop up options to edit properties, print the table diagram, validate table definitions, synchronize the table definitions, and invoke Warehouse Builder Browser to run reports on the selected table.
When you open the Table Editor, it displays a diagram of the selected table and the columns defined for that table. The columns in this table may reference columns in another table through foreign key relationships. You can display those referenced or related tables by selecting View, then Show Related Objects. The Table Editor displays the related tables and the linking lines show the relationships. You can rearrange the displayed tables by selecting a table and moving it around the canvas.
Before you generate DDL scripts to create a table, you can validate your table definitions from the Table Editor. Select Object, then Validate. Warehouse Builder displays the Validation Results dialog indicating whether your definitions were valid or not. If your definitions are valid, you can generate scripts to create that object in the your target database. For details on validating definitions and code generation, refer to Chapter 12, "Validating Objects".
To view metadata reports on any table, select View, then Reports. You can also view Lineage and Impact Analysis reports on a table to show the lineage for the data in the table or the impact of any changes made to the table. To view one of these reports, select View, then Lineage or Impact Analysis. For information on installing and configuring the Warehouse Builder Browser, refer to Chapter 17, "Metadata Browsing and Reporting".
From the Table Editor, you can access the Table Properties window to edit the name, description, columns, constraints, and attribute sets in a table.
To edit table properties, select Object then Properties from the Table Editor menu or right-click the name of a table from the navigation tree and select Properties. The Table properties window displays four tabs: Name, Columns, Constraints, and Attribute Sets.
Click these tabs to perform the following tasks:
Rename a Table
Add and Remove Columns
Reorder Columns
Add and Remove Constraints
Edit Constraints
Delete Constraints
Add Attribute Sets
You can rename a table without editing its description by right-clicking the name of the table on the Warehouse Builder navigation tree and selecting Rename.
To edit the name and description of a table:
Right-click the name of a table and select Properties.
In the Table Properties window, select the Name tab and edit the following properties.
Name: Type a new name for the table. In physical naming mode, you must type a unique name containing between 1 and 30 valid characters. Spaces are not allowed. In logical naming mode, you can type a name up to 200 characters long. Spaces are allowed. The name must always be unique within the module.
Description: Type or modify the table description within this field. The description can be a maximum of 4000 characters. This field is optional.
To add a new column:
From the navigation tree, right-click the name of an object and select Properties.
The Table Properties window displays.
Select the Columns tab, as shown in Figure 3-7.
Click Add.
A blank row displays in the columns field.
Type the following information to define the new column:
Name: Type a name for the column. In physical mode, you must type a name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type a unique name up to 4000 characters in length. The column name must be unique within the table. Spaces are allowed.
Position: By default, the columns are sorted in the order they are created. However, Warehouse Builder enables you to reorder the columns. See "Reordering Columns in a Table".
Data Type: You must select the data type of the column from the drop-down list. This is a required field. The following data types are available in Warehouse Builder:
Length: Define the length of the column. Length is defined only for character data types. Depending on the data type you selected, this field may be required, non-editable, or optional.
Precision: Define the total number of digits allowed for the column. Precision is defined only for numeric data types. Depending on the data type you selected, this field may be required, non-editable, or optional.
Scale: Define the total number of digits to the right of the decimal point. Scale is defined only for numeric data types. Depending on the data type you selected, this field may be required, non-editable, or optional.
Not Null: By default, all columns in a table allow nulls. Null means the absence of a value. A check mark against this field indicates that the column cannot contain a NULL or empty value. For example, you can define a NOT NULL constraint to require that a value be input in the last_name column for every row of the employees table. This is an optional field.
Note: Type a description for the column up to 4000 characters long. This is an optional field.
The following data types can be used to create columns:
CHAR: Select the CHAR data type to store fixed-length character data. For length, specify a maximum size up to 4000 characters. How the data is represented internally depends on the database character set. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding.
DATE: Select the DATE data type to store fixed-length date times, which include the time of day in seconds since midnight. The date defaults to the first day of the current month; the time defaults to midnight. The date function SYSDATE returns the current date and time.
FLOAT: Select the FLOAT data type when the data is a single-precision, floating-point, number. FLOAT can be loaded with correct results only between systems where the representation of a FLOAT is compatible and of the same length.
NUMBER: Select the NUMBER data type to store real numbers in a fixed-point or floating-point format. Numbers using this data type are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers, as well as zero, in a NUMBER column.
VARCHAR: A VARCHAR field is a length-value data type. It consists of a binary length subfield followed by a character string of the specified length. The length is in bytes unless character-length semantics are used for the datafile. In that case, the length is in characters.
VARCHAR2: Select VARCHAR2 data type to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 data type takes a required parameter that specifies a maximum size up to 4000 characters.
By default, columns in a table are displayed in the order they are created. This order is also propagated to the DDL script generated by Warehouse Builder to create the table. If this default ordering does not suit your application needs, or if you want to further optimize query performance, you can reorder the columns.
To change the position of a column:
From the navigation tree, right-click a table name and select Properties.
The Properties window displays.
Select the Columns tab.
The Columns page displays all the columns defined for the object.
Select the gray square located to the left of the column name.
The entire row is highlighted. Wait until the cursor appears as crosshairs.
Drag the row up or down and drop it into its new position.
The position of the column is now updated.
Click OK.
Constraints are used to enforce the business rules you want to associate with the information in a database and to prevent the entry of invalid data into tables. For example, if you define a constraint for the salary column of the employees table as Salary < 10,000, this constraint enforces the rule that no row in this table can contain a numeric value greater than 10,000 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, then Oracle displays an error message. Keep in mind that constraints slow down load performance.
The following constraints can be defined on tables:
Unique Key (UK): A UNIQUE key constraint requires that every value in a column or set of columns (key) be unique. No two rows of a table can have duplicate values in a specified column or set of columns. A UK column can also contain a null value.
Primary Key (PK): A value defined on a key (column or set of columns) specifying that each row in the table can be uniquely identified by the values in the key (column or set of columns). No two rows of a table can have duplicate values in the specified column or set of columns. Each table in the database can have only one PK constraint. A PK column cannot contain a null value.
Foreign Key (FK): A rule defined on a key (column or set of columns) in one table that guarantees that the values in that key match the values in a PK or UK key (column or set of columns) of a referenced table.
Check Constraint: A user-defined rule for a column (or set of columns) that restricts inserts and updates of a row based on the value it contains for the column (or set of columns). A Check condition must be a Boolean expression evaluated using the values in the row being inserted or updated. For example, the condition Order Date < Ship Date will check that the value of the Order Date column is always less than that of the Ship Date column. If not, there will be an error when the table is loaded and the record will be rejected. A check condition cannot contain subqueries and sequences or SYSDATE, UID, USER, or USERENV SQL functions. While check constraints are useful for data validation, they slow down load performance.
You can define UK, PK, FK, and Check constraints in Warehouse Builder. Keep in mind the following rules:
When you create a UK constraint, you can later change the constraint type to PK using the Table Properties window.
When you create a PK constraint, you can later change the constraint type to UK using the Table Properties window.
When you create a FK constraint, you cannot change the constraint type. You must first drop the constraint and then create a new one using the Table Properties window.
When you create a Check constraint, you cannot change the constraint type. You must first drop the constraint and then create a new one using the Table Properties window.
If you want to change a Check or PK constraint to a FK or Check, you must first drop the constraint and then create a new one using the Table Properties window.
To add constraints to a table:
Open the Properties window for an object by right-clicking its name and selecting Properties.
The Properties window displays.
Select the Constraints tab, as shown in Figure 3-8.
Click the Add button next to the Constraint field.
A blank row displays in the Constraints field.
Provide the following information to define a constraint:
Name: Type a name for the constraint. In physical mode, you must type a name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type a name up to 4000 characters long. Spaces are allowed. The constraint name must always be unique within the module.
Type: Select the type of constraint from the drop-down list: check constraint, foreign key, unique key, or primary key.
Specify the Referenced Application.
If you are creating a foreign key constraint, you must select the name of the referenced module that contains the referenced table from the drop-down list. This can be a different module from your current location. This field is required for FKs and not allowed for other constraints.
Specify the Referenced Table.
If you are creating a foreign key constraint, you can select the name of the referenced table that contains the referenced key, from the drop-down list. This field is required for FKs and not allowed for other constraints.
Specify the Referenced Key.
If you are creating a foreign key constraint, you select the name of the referenced key, from the drop-down list. This field is required for FKs and not allowed for other constraints.
Specify the Check Condition.
If you are creating a check constraint, you must type a condition or rule in this field. For example, Status = Active. If you leave this field blank, an error is generated during validation and you cannot generate valid code for this constraint.
he column name referenced in the check condition must exactly match the physical name defined for the table in its property sheet. Warehouse Builder does not check the syntax of the condition during validation. This may result in errors during deployment. If this happens, check the Runtime Audit Browser for details.
Click OK to close the Properties window.
To delete a constraint:
Right-click a table name and select Properties.
The Table Properties window displays.
Select the Constraints tab.
All the constraints defined for the table are displayed.
Select the Constraint you want to delete.
Click Remove.
The Delete Confirmation dialog displays.
Click OK to remove the constraint.
Warehouse Builder enables you to define attribute sets, or groups of columns, for every table. An attribute set contains a chosen set of columns in the order you specify. Attribute sets are useful while defining a mapping or during data import and export.
For each table, Warehouse Builder generates a predefined attribute set containing all the columns in that table. In addition, Warehouse Builder generates predefined attribute sets for each defined constraint. Predefined attribute sets cannot be modified or deleted.
You can create the following types of attribute sets:
User-defined: Optional attribute sets that can be created, modified, or deleted in the Table Properties window.
Bridge-type: Optional attribute sets that can be can be exported to and viewed in another application such as Oracle Discoverer. You can create, modify, or delete bridge-type attribute sets in the Table Properties window. An object can only have one bridge-type attribute set.
To add an attribute set to a table:
From the navigation tree, right-click the table name and select Properties.
The Properties window displays.
Select the Attribute Sets tab.
Warehouse Builder displays the attribute sets defined for the table.
Click Add.
A blank row displays in the Attribute sets of the entity field.
Provide the following information to define the attribute set:
Name: Type a name for the attribute set. In physical mode, you must type a name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type up to 200 valid characters. Spaces are allowed. The attribute set name must be unique within the object.
Type: Select the type of attribute set from the drop-down list, USER_DEFINED or BRIDGE_ TYPE.
Description: Type a description for the attribute set. This is an optional field.
In the Attributes of the selected attribute set, click Include for each attribute you want to include. The order in which you select the columns determines their initial order in the attribute set.
You can click Select All to include all the columns in the attribute set or Deselect All to exclude all the columns from the attribute set. To remove a column from the attribute set, click the check box again to remove the check mark.
Tip:
To change the position of an attribute in the attribute set, click the gray square located to the left of the attribute, drag it up or down, and drop it in its new location. When you re-open the Table Properties Attribute Set tab, it will display the order of the selected attributes followed by that of the non-selected attributes.If you selected BRIDGE-TYPE, click Advanced.
Warehouse Builder displays the Advanced Attribute Set Properties dialog.
For each attribute in the bridge-type attribute set, specify the following properties. These properties determine how the objects appear and display in Oracle Discoverer.
Hidden: Click this check box to hide unused or obsolete columns when the table is viewed in another application. In the Discoverer Administration Edition, hidden columns are grayed out. In the Discoverer Plus Edition, hidden columns are not displayed.
Aggregation: Select an aggregation for numerical attributes SUM, MIN, MAX, AVG, COUNT, or DETAIL for no aggregation. The default is SUM.
Position: Select the default attribute position: DATA POINT, PAGE, SIDE, TOP, or TOP/SIDE. The default is DATA POINT.
Item Class: Check for TRUE or uncheck for FALSE. The default is FALSE.
Heading: Type the heading text.
Format: Type the text for the format field.
Click OK to close the Advanced Attribute Set Properties dialog.
Click OK to close the Properties window.
External tables are database objects in the Oracle Database database. You cannot use external tables with any other database type or any Oracle database previous to the Oracle Database release.
External tables are tables that represent data from flat files in a relational format. They are read-only tables that behave similarly to regular source tables in Warehouse Builder. When you create and define an external table, the metadata for the external table is saved in the Warehouse Builder repository. You can use these external table definitions in mappings to design how you want to move and transform data from flat file sources to your targetS.
The following sections provide information about external tables:
For related information, see the following topics:
Oracle Database Database Utilities Manual
An external table is a read-only table that is associated with a single record type in external data such as a flat file. External tables represent data from non-relational source in a relational table format. When you use an external table in a mapping, columns properties are based on the SQL properties you defined when importing the flat file. For more information on SQL properties for flat files, see "SQL Properties" .
When you use an external table as a source in a mapping, you can use it as you would a regular table. Warehouse Builder generates SQL code to select rows from the external table. You can also get parallel access to the file through the table.
Note:
External tables are source tables only. If you connect an External Table operator as a target in the Mapping Editor, Warehouse Builder returns a validation error when you run the mapping.You can introduce data from a flat file into a mapping either through an external table or a flat file operator. To decide between the two options, consider how the data must be transformed.
When you use an external table, Warehouse Builder generates SQL code. If the data is to be joined with other tables or requires complex transformations, use an external table.
When you use a flat file operator, Warehouse Builder generates SQL*Loader code. In cases where large volumes of data are to be extracted and little transformation is required, you can use the flat file operator. From the flat file operator, you could load the data to a staging table, add indexes, and perform transformations as necessary. The transformations you can perform on data introduced by a flat file operator are limited to SQL*Loader transformations only.
In Warehouse Builder, you can use an external table to combine the loading and transformation within a single set-based SQL DML statement. You do not have to stage the data temporarily before inserting it into the target table.
For more information on differences between external tables and SQL*Loader (flat file operators in Warehouse Builder), see the Oracle Database Database Utilities Manual.
After you use the Flat File Sample Wizard to import metadata, you can create an external table based on a single flat file record type. For information on importing flat file data, see "Using the Flat File Sample Wizard".
To create a new external table definition:
From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.
Expand the target module where you want to create the external table.
Right-click the External Tables node and select Create External Table.
Warehouse Builder displays the Welcome page.
Click Next.
The wizard displays the Name page.
Type a name and optional description for the external table.
Click Next.
The wizard displays the File Selection page, as shown in Figure 3-9.
The wizard lists all the flat files available in the repository. Select a file upon which to base the external table. To search through long lists of files, type the first few letters of the file name and click Find.
If you cannot find a file, make sure you imported the metadata for the file. For information on importing flat files, see "Using the Flat File Sample Wizard".
If you select a file that contains multiple record types, you must also select the record type name at the bottom of the File Selection page. An external table can represent only one record type.
Click Next.
The wizard displays the Locations page.
You can select a location from the drop down box which lists the locations associated with flat files. Alternatively, you can leave the location unspecified. If you do not specify a location in the wizard, you can later specify a location on the external table properties sheet. For more information, see "Defining Locations".
Tip:
You must create and deploy a connector between the locations for the flat file and the Oracle module before you can deploy the external table.Click Next.
The wizard displays the Finish page. This page summarizes the information you entered on each of the wizard pages. Verify the information.
Click Finish.
The wizard creates the external table and inserts its name in the navigation tree.
Warehouse Builder provides an editor dialog and right-click pop up options similar to regular tables.
Figure 3-10 shows the External Table Editor.
You can print the external table diagram, validate external table definitions, synchronize the external table definitions, and invoke Warehouse Builder Browser to run reports on the selected external table. For more information on options available in both the table editor and external table editor, refer to "Using the Table Editor".
One option available in the external table editor and not the table editor is the reconcile option. Use Reconcile to update the external table definition with metadata changes made to the file associated with the external table.
To reconcile an external table definition with a record in a file:
Launch the external table editor by right-clicking on an external table from the navigation tree and selecting Editor.
Warehouse Builder displays the external table editor.
From the menu, select Object then Reconcile.
The external table editor displays the reconcile dialog as shown in Figure 3-11. Use the reconcile dialog to specify a record in a flat file.
In Search, type in a file name and select Go to find a specific flat file. Or scroll through the list and select a flat file.
For files with multiple record types, select a record from Record.
You must select a record. Match strategies, Reconcile strategies, and OK are not available until you specify a record to reconcile against.
Set the Match strategies. Based on your selection, Warehouse Builder searches for matches and updates the external table with the information from the flat file.
Matching by Object Identifier: This strategy compares the field IDs of that the external table columns references with the field IDs in the flat file.
Matching by Physical Name: This strategy compares the physical names in the external table with the physical names in the flat file.
Matching by Position: This strategy matches by position, regardless of physical names and IDs. The first external table attribute is reconciled with the first record in the file, the second with the second, and so on. Use this strategy when you want to reconcile the external table with a new record. If the external table has more attributes than the flat file, the excess attributes are removed from the external table.
Set the Reconcile Strategies. Use these settings to indicate how Warehouse Builder handles differences in metadata between the existing external table definition and the record you specified.
Merge: Warehouse Builder combines the metadata from the existing external table definition and the record you specified.
Replace: Warehouse Builder deletes metadata from the external table definition if it does not match the metadata from the record you specified. The resulting reconciled external table contains metadata that matches the file record metadata.
From the External Table Editor, you can access the External Table Properties window to edit the name, description, columns, file and location.
To edit external table properties, select Object then Properties from the External Table Editor menu or right-click the name of a table from the navigation tree and select Properties. The External Table properties window displays. The tabs and properties that you can edit depend on how you defined the external table in the repository.
In most cases, the External Table Properties window displays as shown in Figure 3-12 with the following four tabs:
Name: Use the Name tab to rename the external table. The same rules for renaming tables apply to external tables. For more information, see "Renaming a Table".
Columns: Use the Columns tab to add or edit columns. The same rules for adding columns to tables apply to external tables. For more information, see "Editing Table Definitions".
File: Use the File tab to view the name of the flat file that provides the metadata for the external table. If the source flat file has multiple record types, the File tab also displays the record name associated with the external table. You can update this relationship or change it to a different file and record by reconciling the external table. For more information, see "Reconciling an External Table Definition with a Record in a File".
Locations: Use the Location tab to view or change the flat file location, as shown in Figure 3-12.
The File tab displays under the following conditions:
You used the New External Table Wizard to create the external table and you specified a file name.
You did not specify a file name in the New External Table Wizard, but you reconciled the external table definition with a file and record.
Access parameters define how to read from the flat file. In some cases, the External Table Properties window displays the Access Parameters tab instead of the File tab.
The tab for the access parameters displays under the following conditions:
You imported an external table from another repository. In this case, you can view and edit the access parameters.
You created an external table in an Oracle database and imported its definition into Warehouse Builder. In this case, you can view and edit the access parameters.
You used the New External Table Wizard to create an external table and did not specify a reference file. The access parameters will be empty. Before generating the external table, you must reconcile the external table definition with a flat file record or manually enter the access parameters into the properties sheet.
The access parameters describe how fields in the source datafile are represented in the external table as columns. For example, if the datafile contained a field emp_id with a datatype of INTEGER(2), the access parameters could indicate that field be converted to a character string column in the external table.
Although you can make changes to the access parameters that affect how Warehouse Builder generates and deploys the external table, it is not recommended. Warehouse Builder does not validate the changes. For more information on the access parameters clause, see Oracle Database Database Utilities Manual.
In Warehouse Builder, you can define views and materialized views. This section describes views. For information on materialized views, see "Using Materialized Views".
The following sections provide information about using views:
Views are used to simplify the presentation of data or restrict access to data. Often the data that users are interested in is stored across multiple tables with many columns. When you create a view, you create a query stored to retrieve only the relevant data or only data that the user has permission to access.
In Warehouse Builder, a view can be defined to model a query on your target data. This query information is stored in the Warehouse Builder repository. You can later use these definitions to generate .ddl scripts in Warehouse Builder that can be deployed to create views in your target system.
When you create a view with the New View Wizard, the wizard prompts you to type the following information:
View name and description
Aliases for columns
Queries that define the view (optional)
Logical constraints (optional)
To create a view definition:
From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.
Expand the target module where you want to create the view.
Right-click View and select Create View.
Warehouse Builder displays the welcome page for the wizard.
Note:
You can also define a View from the Mapping Editor and model your own query.Click Next.
The wizard displays the Name page. Type the a name and description for the view. The description is optional.
Click Next.
The wizard displays the Columns page, as shown in Figure 3-13.
To define a column, click Add.
Type the column name and select the data type.
Repeat this procedure for each column.
Click Next.
The wizard displays the Query Text page. You can either type the query definition or click Next to continue.
If you type a query definition into the Query Text page, be sure to type a valid statement. Warehouse Builder does not validate the text in the Query Text page and will attempt to deploy a view even if the syntax is invalid.
Figure 3-14 shows a sample query text.
Click Next.
The wizard displays the Define Constraints page, as shown in Figure 3-15.
As an option, use this page to define logical constraints for a view. Although Warehouse Builder does not use these constraints when enumerating DDL for the view, these constraints can be useful when the view serves as a data source in a mapping. The Mapping Editor can use the logical foreign key constraints to include the referenced dimensions as secondary sources in the mapping.
For general information about constraints, see "Editing Constraints".
Click Next.
Warehouse Builder displays the Finish page. Verify the description. If you need to modify the definition, click Back.
Click Finish.
The wizard creates a definition for the view, stores this definition in the repository, and inserts its name in the navigation tree.
To rename a view, right-click the view name and select Rename. Type the new name over the highlighted object name.
You can display the view in the View Editor. Use the property sheet to edit the view.
To open the View Editor, right-click the view and select Edit. The editor diagrams the view and its references, as shown in Figure 3-16.
To open the View properties sheet, right-click the view and select Properties. You can modify the view definition by editing the property sheet. For examples on editing a property sheet, see "Editing Table Definitions".
In Warehouse Builder, you can define views and materialized views. This section discusses materialized views. For information on conventional views, see "Using Dimensions".
The following sections provide information about using materialized views:
In Warehouse Builder, you can create materialized views to improve query performance.When you create a materialized view, you create a set of query commands that aggregate or join data from multiple tables. Materialized views provide precalculated data that can be reused or replicated to remote data marts. For example, data about company sales is widely sought throughout an organization.
When you create a materialized view in Warehouse Builder, you can configure it to take advantage of the query rewrite and fast refresh features available in Oracle Database. For information on query rewrite and fast refresh, "Fast Refresh for Materialized Views".
When you create a materialized view with the New Materialized View Wizard, the wizard prompts you to type the following information:
Materialized view name
Materialized view description
Column names
Queries that define the materialized view
Logical constraints (optional)
To create a materialized view definition:
From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.
Expand the target module where you want to create the materialized view.
Right-click Materialized View and select Create Materialized View.
Warehouse Builder displays the welcome page for the New Materialized View wizard.
Note:
You can also define a Materialized View from the Mapping Editor.Click Next.
The wizard displays the Name page.
Type the name and description for the view. The description is optional.
Click Next.
The wizard displays the Columns page, as shown in Figure 3-17.
To define a column, click Add.
Type the column name and select the data type. Repeat this procedure for each column.
Click Next.
The wizard displays the Query Text page. You can either type the query definition or click Next to proceed to the Define Constraints page.
If you type a query definition into the Query Text page, be sure to type a valid statement. For column names, use the same names that you specified on the Columns page in the previous step. If you change a column name on the columns page, you must manually change the name on the Query Text page. Warehouse Builder does not validate the text in the Query Text page and will attempt to deploy a view even if the syntax is invalid.
Figure 3-18 shows sample query text.
Click Next.
The wizard displays the Define Constraints page. As an option, you can define constraints for the materialized view, as shown in Figure 3-19.
These constraints are for logical design purposes only and Warehouse Builder does not use these constraints when enumerating DDL for the view. For general information about constraints, see "Editing Constraints".
Click Next.
The wizard displays the Finish page. Verify the summary, and if you need to modify the definition, click Back.
Click Finish.
The wizard creates a definition for the materialized view, stores this definition in the database module, and inserts its name in the warehouse module navigation tree.
To rename a materialized view, right-click the view name and select Rename. Type the new name over the highlighted object name.
You can view the materialized view in the Materialized View Editor. Use the property sheet to edit the materialized view.
To open the Materialized View Editor, right-click the materialized view and select Editor. The editor diagrams the materialized view and its references.
To open the Materialized View properties sheet, right-click the materialized view and select Properties. You can modify the view definition by editing the property sheet.
For examples on editing a property sheet, see "Editing Table Definitions".
A sequence is a database object that generates a serial list of unique numbers. You can use sequences to generate unique primary key values and to coordinate keys across multiple rows or tables. Sequence values are guaranteed to be unique. When you create a sequence in Warehouse Builder, you are creating sequence definitions that are saved in the repository. Sequence definitions can be used in mappings to generate unique numbers while transforming and moving data to your target system.
The following sections provide information about using sequences:
A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudo columns. Each new sequence number is incremented by a reference to the pseudo column NEXTVAL, while the current sequence number is referenced using the pseudo column CURRVAL. When you define a sequence, Warehouse Builder creates these attributes.
In Warehouse Builder, you can also import sequence definitions from existing source systems using the Import Object Wizard. For more information, see "Importing Metadata into Target Modules".
To create a new sequence:
From the Warehouse Builder navigation tree, expand the warehouse module node.
Right-click Sequence and select Create Sequence from the pop-up menu.
Warehouse Builder displays the New Sequence Wizard.
Enter the following information:
Name: Type a name for the column. In physical mode, you must type a name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type a unique name up to 4000 characters in length. The column name must be unique within the table. Spaces are allowed.
Description: Optionally type a description for the sequence. The description cannot exceed 4000 characters.
Click OK.
Warehouse Builder stores the definition for the sequence and inserts its name in the navigation tree.
After you define a sequence in Warehouse Builder, you can use the Sequence Editor to view columns. To open the Sequence Editor, right-click the name of a sequence and select Editor. Figure 3-20 shows the Sequence Editor.
Use the Sequence Editor Menu, the Sequence Editor Navigation bar, or the right-click pop up options to print the sequence diagram, validate sequence definitions, synchronize the sequence definitions, and invoke Warehouse Builder Browser to run reports on the selected sequence.
To view metadata reports on any sequence, select View, then Reports. For information on installing and configuring the Warehouse Builder Browser, refer to the Oracle Warehouse Builder Installation and Configuration Guide.
From the Sequence Editor, you can access the Sequence Properties window to edit the name, description, and column notes of a sequence.
To edit sequence properties, select right-click the name of a sequence from the navigation tree and select Properties or double-click the name of the sequence. The Sequence Properties window displays two tabs: General and Columns.
Click these tabs to perform the following tasks:
Rename a sequence
Edit sequence columns
To edit the name and description of a sequence:
Right-click the name of a sequence and select Properties.
The Sequence Properties window opens.
Select the General tab:
Name: Type a new name for the sequence. In physical mode, you must type a unique name between 1 and 30 valid characters. Spaces are not allowed. In logical mode, you can type a name up to 200 characters long. Spaces are allowed. The name must be unique within the module.
Description: Type or modify the sequence description within this field. The description can be a maximum of 4000 characters. This field is optional.
Tip:
If you want to rename a sequence without editing its description, right-click the name of the sequence from the navigation tree and select Rename.Oracle Advanced Queues (AQs) provide a database-integrated message queuing system that plays a central role in enterprise data integration. Warehouse Builder enables you to import Advanced Queue definitions and use AQs to move data from your sources to the target system.
For more information on Oracle Advanced Queuing, see Oracle Database Application Developer's Guide- Advanced Queuing.
The integration of Oracle Advanced Queues (AQs) in Warehouse Builder enables the following:
Use of AQs as data sources or targets in your data warehouse design.
Integration of MQ Series and TIBCO applications as data sources.
Use of AQs to propagate source data changes.
This section includes the following topics:
For related information, see:
Web enabled business applications often communicate with one another through message queues. Advanced Queuing leverages the functions of the Oracle database to persistently store these messages, to propagate them between queues on different machines and databases, and to transmit them using Oracle Net Services, HTTP(S), and SMTP. AQs enable message management and communication required for application integration. Warehouse Builder supports AQs as data sources for your warehouse design.
The following types of AQs are supported in Warehouse Builder:
Multiconsumer AQ. The associated queue table must be created as a multiple consumer queue table in the database.
Normal AQs (Exception AQs are not supported).
Persistent AQs (non-persistent AQs are not supported).
This section discusses the following key concepts related to AQs.
Payload is the data stored in a queue. It can be unstructured, such as, the data type RAW, or structured. Payloads can be structured by using Oracle object types, also known as ADTs or user defined types. Complex object types including embedded attributes, collection attributes, and XMLType and SYS.AnyData attributes are not currently supported in Warehouse Builder.
AQs require that the payload type be either an Oracle object type or RAW. To be used in Warehouse Builder, the payload must be defined as an object type containing attributes with scalar data types supported by Warehouse Builder. RAW data types are not supported.
A message is the smallest unit of information entered into or retrieved from a queue. Messages contain control information and payload data. The control information contains message properties or metadata used by AQs to manage messages. Payload data is the information stored in the queue. A message can reside in only one queue.
Currently, AQs can only be imported into Warehouse Builder from an Oracle source schema. For information on how to create an AQ in your source schema, see "Creating Advanced Queues Using SQL".
The imported AQ metadata contains the AQ name, description, the payload object type name, payload structure, the schema where object type is defined, and the name of the associated queue table. These definitions are imported from the agent AQ and not from the source AQ. The payload object type must reside in the same schema as the AQ. If the payload object type is not defined in the same schema as the AQ, then the AQ is imported but the payload type is not imported. In this case, the AQ will not be associated with any payload type and will be invalid. You will receive an error when you validate this AQ. Object types can be created or deleted through scripting and public API.
To import AQs into Warehouse Builder:
In the navigation tree, right click the target module and select Import.
The Database Link Information dialog displays if you have not defined a database link.
Create a new database link to the system from where you are importing the AQ definitions or select a previously created database link.
Click OK.
The Import Metadata Wizard welcome page displays.
Click Next.
The Filter Information page displays.
Select Advanced Queue as the type of object you want to import.
Limit the search of the data dictionary in one of the following methods:
Type a search pattern. For example, you can type a warehouse project name followed by a percentage sign (%) to import objects that begin with that project name. Use a percentage sign (%) as a wild card match for multiple characters and an underscore (_) as a wild card match for a single character.
Type the maximum number of objects to retrieve.
Click Next.
Warehouse Builder displays the names of objects that meet the filter conditions and displays the Object Selection page.
From the Available Objects list, expand the ADVANCED QUEUE node and select the queues you want to import.
Click the arrow buttons to move the selected queues to the Selected Objects list. Warehouse Builder only supports the import of persistent multiconsumer queues.
If you are re-importing definitions, previously imported objects appear in bold.
Click Next.
The Summary and Import page displays. This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reconciled or created. Verify the contents of this page and optionally add descriptions for each of the objects.
Click Finish.
Warehouse Builder imports the definitions for the selected queues and displays the results in the Import Results dialog. When you import a queue, its payload is also imported. You can expand the queue node to view the attributes of the payload for a queue.
The payload object type must reside in the same schema as the AQ. If the payload object type is not defined in the same schema as the AQ, then the AQ is imported but the payload type is not imported. In this case, the AQ will not be associated with any payload type and will be invalid. You will receive an error when you validate this AQ
Verify that the definitions have been created and click OK to complete the import.
Click Undo if you do not want to import the definitions. Or click Save to save the definitions to a local drive. Warehouse Builder stores the imported definitions under the AQ node in the warehouse module navigation tree.
Note:
At runtime, a temporary table must be available to use the AQ as a source. The create table script is generated when you deploy the AQ.Two advanced queues can share the same payload type. If you import a queue (Q2) whose payload has already been imported with another queue (Q1), then the attributes of Q2 are reconciled with the previously imported attributes.
To reimport advanced queues:
Follow the preceding steps using the Import Metadata Wizard. In the Summary and Import page, the Advanced Reconcile Options button is now enabled.
Click Advanced Reconcile Options to select a reconciliation option.
The Advanced Reconciliation Options dialog displays, as shown in Figure 3-21. Use this dialog to reconcile the reimported metadata with the existing definitions in the Warehouse Builder repository. You can choose to preserve object type attributes and AQ descriptions in the repository.
Select options for reconciling the object type:
Preserve repository added attributes: Check this option to preserve attributes added to the repository that are not present in the object to be imported.
Select options for reconciling the AQ:
Preserve existing descriptions: Check this option to preserve previously imported AQ definitions in the repository.
Click OK after selecting your options.
Note:
By default, all options are checked. Uncheck the boxes to have these repository objects replaced and not preserved.Click OK to accept the changes. Click Undo to cancel the import.
Warehouse Builder stores the imported definitions under the AQ node in the warehouse module navigation tree.
For related information, see the following sections:
After you import AQs into Warehouse Builder, you can view their properties by right-clicking the AQ name and selecting Properties from the pop-up menu. The Advanced Queue Properties window displays three tabs: Name, Payload, and Payload Structure. The imported AQ properties cannot be edited.
Note:
The object types imported with an AQ cannot be deleted from the client UI. They can only be deleted through scripting.You can view the following properties by clicking each of the tabs:
Name: Name and description of the imported AQ.
Payload: Name and description of the payload imported with the AQ.
Payload Structure: Attributes of the payload imported with the AQ, such as, Name, Position, Data Type, and Length as shown in Figure 3-22.
The following steps outline how to create AQs in your Oracle source system using SQL:
SQL> grant aq_administrator_role, aq_user_role to scott;
(Grants the required roles to the user who will administer or use the AQ. These roles must be granted by the system administrator or the user with SYSDBA privileges, SYS or SYSTEM)
SQL> connect scott/tiger;
(Connect with the user name and password of the user who has been granted the privileges, for example, scott/tiger)
SQL> create type employee as object (empno number, ename varchar2(30));
(Create the object structure on which the AQ will be based, for example, employee)
SQL> execute dbms_aqadm.create_queue_table(queue_table => 'EMPLOYEE_QUEUE_TBL', multiple_consumers=>true queue_payload_type => 'EMPLOYEE');
(Create the AQ table. Because Warehouse Builder currently supports only multiconsumer queues, multiple_consumers => true)
SQL> execute dbms_aqadm.create_queue('EMPLOYEE_QUEUE', 'EMPLOYEE_QUEUE_TBL');
(Create the AQ)
SQL> execute dbms_aqadm.start_queue('EMPLOYEE_QUEUE');
(Start the AQ)
The following steps outline how to create AQs in your Oracle source system using Oracle Enterprise Manager:
Grant the AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE to the user who will administer or use the AQ. Expand the navigation tree by expanding the Database node, then Security, then Users, and then Role (must have SYSDBA privileges).
Create the object type. From the Object menu, select Create, and then Object Type. Specify the attributes and create the object type.
Create the AQ table. Expand the navigation tree by expanding the Databases node, then Database Name, then Schema, then Advanced Queues, and then double click Queue Tables to create the AQ table.
Create the AQ by selecting the Object menu, then Create, and then Queue.
Start the AQ by expanding the Databases node, then Database Name, then Schema, then Advanced Queues, then Queue Tables, then Schema Name, then Queue Table Name. Highlight the queue name then from the Object menu select Queue Start.
For more details, see the Oracle Enterprise Manager User's Guide.
Dimensions are the primary organizational unit of data in a star schema. Warehouse Builder uses dimensions to organize and index data for cubes. Examples of some commonly used dimensions are Customer, Product, and Time.
When you define a dimension, you need to define its hierarchies, levels, and level relationships. The levels represent the level of aggregation and the hierarchies describe parent-child relationships among a set of levels. Dimension hierarchies are logical structures that use ordered levels as a means of organizing data.
Query performance can be improved using Dimensions because users often analyze data by drilling down on known hierarchies. An examples of a hierarchy is the time hierarchy of Year, Quarter, Month, Day. Oracle Database makes use of defined hierarchies by rewriting queries to retrieve data from summary rather than detail tables. The rewritten queries have improved performance.
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 cubes.
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 Oracle Database server.
A dimension definition includes a dimension object definition and a dimension table definition. This section provides information about the dimension object.
Table 3-2 summarizes the rules for dimensions.
Table 3-2 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 value. For example, a city rolls up to one and only one 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 implements 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). |
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
H2: YearL > QuarterL > 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.
When you create a definition for a hierarchy, Warehouse Builder creates an identifier for each level of the hierarchy and a unique key constraint on the lowest level. Warehouse Builder uses the identifiers during the generation phase to build a DDL script to create the dimension object.
When you create a foreign key reference on a cube that points to a dimension, Warehouse Builder shows the unique key constraint and the other identifier keys as candidates for the referenced column. A cube 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.
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 > 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 attributes.
To create definitions for a dimension, use the New Dimension Wizard. You name the dimension and define a primary key constraint. 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 3-3 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 3-3 Example of a Dimension Object
| Level | Prefix | Attribute | Data Type | Description |
|---|---|---|---|---|
|
class |
cl |
class_id |
number |
Level identifier or key |
|
class |
cl |
class_desc |
varchar(20) |
Description of product class |
|
family |
fa |
family_id |
number |
Level identifier or key |
|
family |
fa |
family_desc |
varchar(20) |
Description of product family |
|
product |
pd |
prod_WH |
number |
Base level or warehouse key |
|
product |
pd |
item_desc |
varchar(35) |
Description of the product |
|
product |
pd |
product_upc |
varchar(11) |
Universal product code (natural key) |
|
product |
pd |
item_source |
varchar(30) |
Supplier for product |
|
product |
pd |
packaging |
varchar(20) |
Packaging for the product |
To create a dimension definition:
From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.
Expand the target module where you want to create the dimension.
Right-click Dimensions and select Create Dimension.
Warehouse Builder displays the welcome page for the New Dimension Wizard.
Click Next.
The wizard displays the Name page, as shown in Figure 3-24.
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 dimension name is used.
A description of the dimension (optional).
Click Next.
The wizard displays the Levels page. Dimensions contain at least one level. You can define a default level to satisfy this requirement and include additional levels as required. Figure 3-25 shows the Levels page.
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.
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 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 cube foreign keys may already refer to the generated level. Unique key names generated after you edit the prefix use the new prefix.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 key column for the level. This attribute is used in the create dimension statement to define the level, and the defined level is used in the determines clause to specify other columns within that level (dependent columns). See the Oracle Database SQL Reference and the Oracle Database Data Warehousing Guide for more information.
Type a name for the attribute.
Select a data type for the attribute from the drop-down list under Data Type. Warehouse Builder supports the following Oracle Database data types:
CHAR
DATE
FLOAT
NUMBER
VARCHAR
VARCHAR2
Enter the precision, length, or scale as appropriate for the data type.
Type a description of the attribute.
Click Add.
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, as shown in Figure 3-26.
If you want to rename the ID column, select ID in the Level Attributes text box.
Type a new name in the Name text box.
Click Update.
Click Next.
The wizard displays the Hierarchies page.
Type a name and prefix for each hierarchy.
Type a description of the hierarchy.
Click Next.
The wizard displays the Level Relationships page, as shown in Figure 3-27.
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.
Click Next.
The wizard displays the Finish page. Verify the description.
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 base level of aggregation for the dimension. 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.
You can edit the definition for a dimension object with the Dimension Editor or by editing entries in the dimension property sheet.
To display the Dimension Editor, right-click a dimension in the navigation tree and select Editor. Figure 3-28 shows the Dimension Editor.
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:
Fully expand the Level where you want to add the new attribute.
Drop the Attribute icon on the Level.
Warehouse Builder adds an attribute (attribute1) in the level with the number data type.
Enter a name for the attribute.
To change the data type, double-click the attribute name.
The Dimension Editor displays the dimension property sheet.
Select the Level Attributes tab, as shown in Figure 3-29.
Select a data type from the drop-down list.
Change the length, scale, or precision depending on the data type selected.
Click Update.
To print the diagram, click the Print icon on the Dimension Editor toolbar.
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.
From the Warehouse Builder navigation tree, right-click the dimension and select Properties.
Warehouse Builder displays the property sheet for the dimension object, as shown in Figure 3-30.
The dimension object property sheet has the following tabs:
Name
Levels
Level Attributes
Hierarchies
Level Relationships
To display the dimension table property sheet:
Open the Dimension Editor.
Select Table Properties from the Edit menu.
Warehouse Builder displays the dimension table property sheet. For information about the table property sheet, see "Editing Table Definitions".
Cubes, also known as facts, contain measures and link to one or more dimensions. Most cube measures are additive. Common additive measures include sales, units, and cost.
Cubes 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.
When dimensions are designed with warehouse keys, the cube row length is usually reduced because warehouse keys are shorter than their natural counterparts. The result is less storage space wasted in the cube.
A typical cube 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 cube is a data list, the foreign key reference columns do not uniquely identify each row in the cube.
A set of foreign key reference columns that link the table with its dimensions.
When you create a definition for a cube, 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.
This section describes how to create and update a definition for a cube. You create a definition for a cube using the New Cube 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.
Use the New Cube Wizard to create definitions for a cube. This information includes details regarding foreign key references, measures, and the data types of all the table columns.
To create a cube definition:
From the Warehouse Builder navigation tree expand the Databases node and then the Oracle node.
Expand the target module where you want to create the cube.
Right-click Cubes and select Create Cube.
Warehouse Builder displays the Welcome page for the New Cube Wizard.
Click Next.
The wizard displays the Name page.
Enter the following:
The name of the cube
A description of the cube (optional)
Click Next.
The wizard displays the Define Foreign Keys page.
Select the name of a dimension from the Dimension drop-down list.
Select the base key level from the Level drop-down list.
Select the primary key column constraint defined on the dimension from the Unique Key drop-down list.
Click Add.
The wizard inserts the foreign key reference constraint in the text box that lists the foreign keys.
Repeat these steps for each foreign key constraint. Select the lowest level of aggregation for the foreign key reference target.
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.
Note:
The Cube Wizard displays the name for each generated PK constraint on 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 cube foreign key references.
Check the box next to Create segmented unique key from foreign keys.
Click Next.
The wizard displays the Define Measures page, as shown in Figure 3-31.
Click Add.
Type the name of the measure.
Select the data type of the measure.
Repeat these steps for each measure in the cube.
Click Next.
The wizard displays the Finish page. This page summarizes the cube. Click Back to modify any of the elements.
Click Finish.
The wizard creates a definition for a cube, stores it in the warehouse module, and inserts its name in the navigation tree.
A cube object has two property sheets: one for the cube object and another for the table. You can update cube object properties by editing the property sheets. In addition, you can add foreign key references or measures to a cube object using the Cube Editor. You can also use the Cube Editor to change cube properties and foreign key relationships with dimensions.
To open the Cube Editor, right-click a cube name and select Edit from the pop-up menu. Warehouse Builder displays the Cube Editor containing a tool palette and a diagram of the cube and the related dimensions.
Figure 3-32 shows the Cube Editor.
To print the diagram, click the printer icon on the Cube Editor toolbar.
To display the cube object property sheet:
From the Cube menu, select Cube Properties or click the Properties icon.
Right-click the cube and select Properties.
The properties include the object name and description, foreign key references, measures, and attribute sets.
From the Cube 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).
Warehouse Builder enables you to import data object definitions into target modules using the Import Metadata Wizard. These definitions assist you in modelling your target system. For Oracle target modules, you can import definitions for tables, views, external tables, sequences, advanced queues, and PL/SQL transformation packages. For other target modules, such as SAP systems, you can import table definitions.
To import object definitions into an Oracle target module:
Right-click an Oracle module name and select Import.
The welcome page for the Import Metadata Wizard displays.
Click Next.
The Filter Information page displays.
Limit the search of the data dictionary in one of the following methods:
Select tables, views, external tables, sequences, advanced queues, or PL/SQL transformation packages.
Type a search pattern. For example, you can type a warehouse project name followed by a % to import objects that begin with that project name. Use % as a wild card match for multiple characters and _ as a wild card match for a single character.
Click Next.
Warehouse Builder retrieves names that meet the filter conditions from the data dictionary and displays the Object Selection page as shown in Figure 3-33.
Select items to import from the Available Objects list and click the arrow to move them to the Selected Objects list.
To move all items to the Selected Objects list, click the double arrow.
To move an object and the objects it references, select the name of the object and check One Level.
To move a single object and all the objects it references directly or indirectly, select the name of the object and check All Levels.
If you are re-importing definitions, previously imported objects appear in bold.
Click Next.
The Summary and Import page displays. This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reconciled or created. Verify the contents of this page and add descriptions for each of the objects.
Click Finish.
The Import Results page displays.
Click OK to accept the changes. Click Undo to cancel the import.
Warehouse Builder stores the definitions in the target module.
After you have created or imported object definitions to model your target system, you can configure these objects for deployment. For more information, see Chapter 5, "Configuring Data Objects". You can also define how to extract, transform, and load the data from your sources to the target systems. For more information, see Chapter 6, "Designing Mappings".
