Release 2 (v9.0.2)
Part Number A95949-01
 Home |
 Solution Area |
 Contents |
 Index |
| Oracle9i Warehouse Builder User's Guide Release 2 (v9.0.2) Part Number A95949-01 |
|
This chapter covers information relating to the repository and metadata stored within it. When you first install or upgrade Warehouse Builder, you can use the following wizards to set up Warehouse Builder:
You can either run these during the installation or upgrade, or run them later. See the Oracle9i Warehouse Builder Installation Guide for instructions.
This chapter includes the following topics:
Use the Warehouse Builder Metadata Loader (MDL) to export objects from a Warehouse Builder repository to a file and to import objects from a file into Warehouse Builder repository. With this method, you can move metadata into a different repository. The MDL supports the exporting and importing of different types of objects, such as tables, facts, dimensions, materialized views, mappings, transformation categories, transformations, sequences, and information belonging to the objects including columns, constraints, parameters, and named attribute sets. You can export an entire project or a subset of objects from a project. You can execute the export and import utilities using a command line in an MS-DOS window.
Warehouse Builder enables multiple users to access the same repository at the same time by applying locks to objects that are being modified. Locking affects metadata import. When you import metadata, Warehouse Builder applies locks to all individual objects you are importing. When the import is complete, the changes are committed.
Large MDL exports and imports can require a large amount of memory. For large exports and imports, if the default -Xmx setting of 256M in exp.bat, imp.bat, or owbclient.bat is not enough, MDL fails due to lack of memory. Increasing the -Xmx setting in the appropriate bat file can fix this problem.
When importing a large MDL file that results in a large number of updates or adds, Warehouse Builder cannot have the capacity to lock all of the objects affected depending on the Warehouse Builder repository configuration. In this case, Warehouse Builder attempts to switch to single user mode. Also, if more than 300 locks or seventeen percent of the total enqueue resources for the Warehouse Builder repository, whichever is less, are required to import an MDL data file, Warehouse Builder attempts to automatically switch to single user mode. This allows the MDL to avoid performance degradation when using a large number of locks. This also helps limit the potential of running out of enqueue resources. If the MDL switches to single user mode, no other users are able to log on to the repository until after the MDL import completes.
Run large MDL exports and imports on the database server where the Warehouse Builder repository resides in order to get the best performance.
This section describes the components of the Metadata Loader:
The Metadata Export Utility exports objects from a Warehouse Builder repository to an operating system file. The utility can export an entire project, a set of modules, or a set of objects defined by a module. It can also export the global-shared transformation library.
The export file is a delimited character file which can be read with a text editor. Each record within the file begins with a keyword followed by one or more variable-length fields separated by a pipe (|) or caret (^). The separator character is specified when the file is exported. The pipe (|) is the default separator character.
#Project data <PhysicalName> <LogicalName> <UniversalID> <Version Label> PROJECT|WarehouseName|Warehouse Name|A86184D5336911D58E9000B0D02A59E4|null #Dimension <PhysicalName> <LogicalName> <UniversalID> <Prefix> <UsageType> <Imported> <Generated> DIMENSION|Channels|Channels Dimension Data Mart|7E727655029911D58DC900C04F48E9ED|ch|null|N|N
You can export all of the objects in a project or a subset of those objects. For example, you can export the entire warehouse project to a file, a few selected modules, or selected objects within a source or warehouse module.
When you export a subset of objects, the utility exports definitions for each object selected and all of that object's ancestors. For example, if you export only the dimension, the export file contains definitions for:
Table 11-1 lists the repository objects that can be exported and imported by the Metadata Loader.
| Projects | Modules | Module Objects |
|---|---|---|
|
Warehouse Builder Project |
Source |
Views |
|
|
Warehouse |
Views |
By default, the Metadata Export Utility exports values for configuration parameters. To override this, run the Metadata Export from the command line and include the statement CONFIGPARAM=N in the Metadata Export Utility's parameter file. For more information, see "Metadata Loader Command Line Utility".
To export a project:
To export a module or set of modules from a project:
To export a subset of objects in a module:
The Metadata Export Utility window displays. This window displays the name of the objects you are exporting
The Metadata Export Progress dialog displays the progress. When the export completes, the Metadata Export Results dialog displays.
The Metadata Export Dialog displays the object types and the number of each type that were exported. For a detailed view of the export process, click View Log File. This displays the entire log file.
The Metadata Import Utility imports objects from an export file into a Warehouse Builder repository. The Metadata Import Utility operates in one of four modes: add, replace, add and replace, and add and merge.
When you use the Metadata Import Utility, it searches the repository for any repository objects that exist in the repository and in the file you are importing. There are three methods to search for objects: Universal Identifiers, Logical Names, and Physical Names.
For example, if the search is by the logical name of a repository object in the export file, the Metadata Import Utility searches the repository for the object's logical name. If the an object with the corresponding logical name is not found, the resulting actions are based on the import mode you select. Table 11-2 lists and describes the available import modes.
If the Metadata Import Utility finds the logical name, the following actions result based on the import mode. Table 11-3 lists and describes the available import modes.
When importing using the add and replace, and replace mode, the import completely replaces the existing object's children so that the final object is exactly the same as the source object. Any children of a repository object that are not replaced or added are deleted. This occurs regardless of whether a child occurs in a mapping or is a foreign, primary, or unique key column.
For example, in the MDL export file, the CUST table contains three columns with the physical names: Last_Name, First_Name, and Middle_Init. In the Warehouse Builder repository, the same table already exists, and contains four columns with the physical names: Last_Name, First_Name, Status, and license_ID. During a replace operation, the columns Last_Name and First_Name are replaced, column Middle_Init are added, and column Status and license_ID are deleted. The final result is that the CUST table in the Warehouse Builder repository contains the same metadata from the CUST table in the export file. Table 11-4 lists examples of repository objects and children.
To import objects from an export file:
The MetaData Import Utility window displays.
If you have made any changes before starting the import, the Metadata Import Confirmation dialog displays. Click Commit to save any changes or Rollback to ignore changes and revert to the previously saved version.
File Name: Type in or browse to the export file you want to import.
Log File: Create a log file for the import. Warehouse Builder reads and processes the exported metadata and writes status and diagnostic information in the log file.
Import Options:
Search by:
Character Set: Select the type of character set used to create the import file. The default character set is defined by the Warehouse Builder client machine. Use the drop-down list to change the output character set.
The Metadata Import Confirmation dialog displays only if the exported metadata data information has not been reviewed.
The Metadata Import Progress panel displays. When the import is complete, the Metadata Import Results dialog displays.
This dialog displays the object types and the number of each type that were imported or skipped. For a detailed view of the import process, click View Log File. This displays the entire log file.
Example 11-2 displays the contents of an import log file. The import statistics display the total number of objects that have been added, replaced, and skipped.
Import started at 04/25/2001 4:59:46 PM ******************************************************************************** * Import for OWB Release: 3.0.0.0.0 Version: 3.0.0.3.0 * User: user30_3i Connect String: epaglina-pc:1521:ora8i * Data File: d:\owb3000\sco_dim_time_phy_m_tgt.mdl * Log File: d:\owb3000\imp_dim_time_phy_m_tgt.log * Trace: B * Trace File: d:\owb3000\imp_dim_time_phy_m_tgt.trc * Physical Names: Y Mode: CREATE * Ignore Universal Identifier: Y Commit At End: Y ******************************************************************************** Informational at line 15: MDL-1207 PROJECT with physical name <PRJ_Dimension> not imported because it already exists. Informational at line 21: MDL-1207 DATAWAREHOUSE with physical name <WH> not imported because it already exists. Informational: MDL-1134 COMMIT issued at end of import data file. Counts for OWB Import Utility ----------------------------- Total Projects Processed by Import = 1 ------------------------------- Project = PRJ_Dimension Entity in Project Added Replaced Skipped ------------------------------- ----- -------- ------- DATAWAREHOUSE: 0 0 1 DIMENSION: 1 0 0 LEVEL: 3 0 0 HIERARCHY: 2 0 0 LEVELRELATIONSHIP: 4 0 0 COLUMN: 18 0 0 UNIQUEKEY: 8 0 0 PRIMARYKEY: 2 0 0 CONFIGPARAM: 60 0 60 CHILDCONFIG: 14 0 0 Import ended at 04/25/2001 4:59:52 PM
If you are running an MDL Import or Export and encounter an error, an error message displays.
You can click Detail to display a detailed error log that lists the repository object and the object line that the error occurred in. This is useful for troubleshooting. In the example below, the repository object is the CUST dimension and the object is the TOTAL level.
You can execute the Metadata Loader Utilities from an MS-DOS window. These two utilities allow you to disable the export of configuration values, vary the separator character within an export file, and maintain directive files for selected export operations. These operations automate the consolidation or synchronization of metadata in multiple repositories that have a similar project structure.
The scripts for executing both the export and import utilities reside in the $OWBHOME\owb\bin\win32 directory for the Windows platform and $OWBHOME/owb/bin/solaris for UNIX platforms. Both the export and import utilities are driven by a set of directives. You can specify the directives in the following ways:
The following examples describe how to export and import Warehouse Builder definitions using these utilities with a directive file.
The format for a directive is:
You can also form a directive by replacing the value with the wildcard character (*), which matches any string, or with a list of named objects:
For example, you can specify a set of tables to be exported as:
The directives can be written in a simple text file. Table 11-5 summaries the keywords used to form export directives. You can use the comment indicator (#) to document your directive scripts. Put the indication in the first column of a record and follow it with text.
This example describes how to export an entire warehouse project. The operation requires two steps: create the directives file and then execute the Metadata Export Utility.
The directives file is a simple text file that contains a set of directives for the export utility.
USERID=GCCWH/GCCWH@dwdoc11-pc:1521:ora816 PROJECT=GCCWarehouse FILE=e:\MDL\GCCWarehouse-exp-JUL01 FIELDSEPARATOR=| LOG=e:\MDL\GCCWarehouse-exp-JUL01-LOG CONFIGPARAM=N
The following command invokes the Metadata Export Utility and specifies the above directive file:
w:\owb\bin\win32>exp parfile=e:\MDL\EXP_Directives Processing ... Export successful.
The objects have now been exported to the file and can be imported into a repository using the Metadata Import Utility.
You can direct the import utility to import objects from a file by answering prompts or by creating a file with a set of directives. Table 11-6 summaries the keywords used to form a directive. The format for each directive is Keyword=value.
If a MODE directive is not included, then the default is CREATE.
This example shows you how to import two modules using the Import Utility. The operation requires two steps: create a directive file and then execute the Metadata Import Utility.
The directives file is a simple text file that contains a set of directives for the export utility.
USERID=GCCWH/GCCWH@dwdoc11-pc:1521:ora816 FILE=e:\MDL\gccstar-exp LOG=e:\MDL\gccstar-imp-LOG MODE=CREATE CONFIGPARAM=N
The following command invokes the Import Utility and specifies the above directive file:
w:\owb\bin\win32>imp parfile=e:\MDL\IMP_Directives.txt Processing ... Import successful.
When you import a set of definitions from exported metadata, the Import utility can update existing definitions in a Warehouse Builder Project.
A mapping definition can be updated in a repository that is not identical to the mapping in the exported metadata file if any objects (tables, facts, transformations) that the mapping references cannot be found in the target repository. A warning message is written to the log file when this occurs.
Before you generate scripts from imported definitions, first configure the definitions and then validate them. The validation identifies malformed definitions. For additional information on the configuration, validation and generation of scripts, refer to Chapter 9, "Configuring, Generating, and Deploying".
A foreign key definition can be updated in a repository that is not identical to the foreign key in the exported metadata file if its referenced unique or primary key does not exist in the target repository. A warning message is written to the log file that the foreign key does not contain a referenced key.
The Split utility provides a workaround for the memory limitations of the MDL import utility when you are importing a large number of mappings. This utility generates export and import scripts for migrating mappings in pieces as opposed to migrating them all at the same time. The generated scripts have matching MDL parameter files that utilize the CREATE mode. These files can be edited.
If the MDL import fails because of large data, the split utility can be used to re-export and import the mapping data in smaller pieces. All other object types must be exported and imported using the standard MDL utilities. Only mappings can be split into smaller pieces. To export all entities other than mappings, a parameter file containing the following can be used:
The split utility splits the mappings within a module in a Warehouse Builder project. The size of the pieces are determined by a parameter located in a file provided with this application.
The expsplit batch script accepts the following arguments:
The following example shows you how to start the split utility.
expsplit exampleparams.txt c:\temp\ora_apps
The following example uses a parameter file exampleparams.txt. This file contains the following parameters:
This file is similar to the export parameter file for Warehouse Builder Metadata Loader, with the changes listed in Table 11-7.
Table 11-7
If the mappings for a Warehouse Builder project are split, the generated parameter files are named as follows:
owb_apps1.txt
owb_apps2.txt
and so on.
A batch file is generated c:\temp\owb_apps.bat (given the parameter target file prefix) to export the data from the repository. An import batch file is created to import, using create mode, into the same repository. These files can be edited if different target databases are required.
To migrate data using the split utility:
To export all objects other than mappings in a command line, use a parameter file with the following keywords:
If Warehouse Builder is used to perform export, use multi-select to select and export objects other then mappings.
expsplit exampleparams.txt c:\temp\ora_apps
The utility connects to the source repository, splits mappings, and creates multiple parameter files according to exampleparams.txt. These parameter files are used during the export. The utility also creates an export batch file and an import batch file.
Table 11-8 lists the files that are created.
Use Batch Services to run bulk operations, such as the import and export of metadata directly from the command line. You can also use Batch Services to run Warehouse Builder operations such as validation, generation, and deployment of objects in a database. You can choose to run single operations, or you can use parameter and bulk parameter files to run multiple operations. When you run command line operations, log files are written and stored for each operation. Batch Services can also be scheduled to run independently from the Warehouse Builder client application.
The Batch Service command line scripts are in the following locations:
UNIX
Windows NT/2000
The types of Batch Service command line scripts include:
You can run these scripts as single operations, or you can create a parameter file and a parameter file script that runs multiple operations using a single script. For more information, see "Running Multiple Command Line Operations".
Table 11-9 provides descriptions for the notations used in the command line syntax for Batch Services.
The following tables contain the command line syntax to perform various operations. To perform these operations on transformations, you must use transformation-specific syntax.
You can run multiple command line operations using a single script by creating parameter files. The parameter file is a separate text file that contains a batch of command line operation scripts with the same syntax used in the single operations. When you type the parameter file command line script to run the parameter file, you only need to specify the parameter file. Parameter files enhance performance by providing a single connection for all of the operations listed in the file. You can also choose to use a bulk parameter file for very large groups of operations to improve performance. Bulk parameter files refer to outside files such as data files and error files.
Table 11-15 contains the command line syntax for the parameter and bulk parameter file scripts.
Example 11-3, Example 11-4, Example 11-5, and Example 11-6 display the contents of a sample parameter file, bulk parameter files, and data file. Use the comments in the example as guidelines for creating these files.
===================================================================== # Place a pound sign before your comments # validate "My_^Project" <application Name> <element Name> <element Type> <logdir> validate "My_^Project" IM1 TAB1 TABLE /tmp/.owbObjects # generate # generate "My_^Project" <application Name> <element Name> <element Type> <logdir> generate "My_^Project" IM1 TAB1 TABLE /tmp/.owbObjects #deploy # similarly we do for deploy in file system and the database. deploydb "My_^Project" IM1 TAB1 TABLE <logdir> import import.mdl importmdl.log CREATE true true WEMSWIN1251 =====================================================================
===================================================================== # pound sign is for comments # The bulkErrorFile is the location of the error report. bulkErrorFile = /somebulkErrorFile # The defaultprojectAppResultDir is the directory the results are stored in. # If you leave it empty then it takes the current directory. defaultProjectAppResultDir = ~/ # The project is where you enter the name of the Project. project = "My_^Project" # The application is the name of the module. application = IM1 # For projectAppFCOData, specify the data file for this module with the path. # The data file contains the actions to be performed on specified objects. projectAppFCOData = /tmpA/Object/data1.dat # projectAppFCODeployFileOK specifies whether to deploy to file or not. # The default is false. projectAppFCODeployFileOK = false # projectAppFCODeployFileDir specifies the directory to deploy the generated scripts to. projectAppFCODeployFileDir = /tmpA/Object/ # projectAppFCODeployFileType specifies the file type to deploy. # There are three options: DDL,ANALYZE, or ALL. The default is ALL. projectAppFCODeployFileType= DDL # projectAppFCODeployDataBaseOK specifies whether to deploy to database or not. # The default is false. projectAppFCODeployDataBaseOK = false # projectAppFCODeployDatabaseType is the type of file you are deploying. # There are two options: PLS and ALL. The default is ALL. projectAppFCODeployDatabaseType = PLS # The projectAppResultDir is the directory where Batch Services results are sent to. projectAppResultDir = /tmpA # end of Bulk Setup parameter file =====================================================================
===================================================================== # pound sign is for comments bulkErrorFile = /somebulkErrorFile defaultProjectAppResultDir = ~/ # Provide a full path for the import. The default is the current directory. importFullPath = # Specify the MDL files to import. You can import multiple MDL exported files. # See note following this example for more information. importFiles = A*.mdl # Specify whether or not to run import. The default is true. importMode = true # Specify whether or not to import by physical names. importUsePhysicalName = # Specify whether or not to ignore the UOID when importing. The default value is true. importIgnoreUOID = # Provide a character set string. importCharacterSet = =====================================================================
You can import files by:
===================================================================== # pound sign is for comment # OBJECT NAME, OBJECT TYPE ACTION SET # <elementname>,<elementype> {<service> <, >}+ TAB1,TABLE validate generate deploydb TAB2,TABLE validate generate deployfile MAPP1,MAPPING generate deploydb # end of FCO Data file =====================================================================
The output for validation, generation, and deployment are stored in their respective log files. The log file has the following typical structure. The messages are shown in italics. The action and the date of activity are highlighted in bold. Example 11-7 shows a validation log file.
BEGIN ----------------------------------------------- Input: owbusername owbpassword servername-pc ora817 1521 -validate My_^Project im1 tab1 TABLE Output: directory = /owb/owbhome/owb/bin/im1_tab1_validate.log Action = validate Date = Mon Apr 02 14:30:26 GMT-08:00 2001 project = im1, element = tab1, type = TABLE, "ERROR", "No Columns", "Add new Column" END -----------------------------------------------
The Warehouse Builder Transfer Wizard enables you to synchronize, integrate, and use metadata stored in multiple sources and formats.
The Warehouse Builder Transfer Wizard allows you to import metadata from and export metadata to data warehousing tools. The Transfer Wizard performs two major tasks:
The Transfer Wizard creates an intermediate XML file conforming to the XML Metadata Interchange (XMI) standard. This process is transparent when you use the Transfer Wizard. You provide the source and target parameters and the Transfer Wizard performs the exporting, conversion, and downloading tasks.
Figure 11-9 shows an example of the transfer process for exporting metadata from Warehouse Builder into Discoverer.
Before you transfer metadata between two data warehousing tools, you need to perform tasks within the source tool to ensure that the metadata transfers successfully and displays appropriately in the target tool.
For detailed information on transfer considerations for metadata import and export, refer to Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations".
After you have prepared your source tools to ensure a successful transfer, you can use the Warehouse Builder Transfer Wizard to import the metadata. For more information on transfer considerations, refer to Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations".
The Warehouse Builder Transfer Wizard enables you to import metadata from the following types of sources:
This section contains instructions for using the Warehouse Builder Transfer Wizard to import metadata into Warehouse Builder:
For more information on transfer considerations, refer to Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations".
To launch the Transfer Wizard for an import:
The Oracle Transfer Wizard Welcome window displays, identifying the steps you perform while using the Transfer Wizard.
If you want to display version information about the Transfer Wizard, click About Oracle WB Transfer Tool. For version information about the individual bridges, press the Bridge Versions button from the About Oracle WB Transfer Tool dialog.
The Data Source and Target Identification window displays.
This description displays in the progress bar during the transfer process and is useful when you are performing multiple transfers.
The Transfer Parameter Identification window displays.
The Transfer Parameters window displays a different list of parameters based upon the metadata source you selected.
If any are incorrect, click Back to return to the previous screen and make the necessary changes.
The transfer window displays with a status bar.
The title of the transfer window is the description you provided. If you did not provide a description, a title does not display.
If the transfer is successful, the Transfer Wizard creates an output file and stores it in the location you specified.
After you have prepared your target tools to ensure a successful transfer, you can use the Warehouse Builder Transfer Wizard to export the metadata. For more information on transfer considerations, refer to Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations".
The Warehouse Builder Transfer Wizard enables you to export metadata to the following types of targets:
This section contains instructions for using the Warehouse Builder Transfer Wizard to export metadata from a Warehouse Builder project to a target. For more information on transfer considerations, refer to Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations".
To export metadata using the Warehouse Builder Transfer Wizard:
The Oracle Transfer Wizard Welcome window displays, identifying the steps you perform while using the Transfer Wizard.
The Data Source and Target Identification window displays.
This description displays in the progress bar during the transfer process and is useful when you are performing multiple transfers.
The Transfer Parameters window table lists parameters that you must enter or select. This window displays a different set of parameters depending on the target you selected in the previous step.
The Confirmation of Warehouse Builder Transfer window displays.
If any are incorrect, click Back to return to the previous screen and make the necessary changes.
The transfer window displays with a status bar.
The title of the transfer window is the description you assigned to the transfer on the Choose Data Source and Target Types window. If you did not provide a description, a title does not display.
During a successful transfer, the Transfer Wizard creates the output file and stores it in the location you specified.
The Archive/Restore Utility allows you to save and load versions of your Warehouse Builder repository metadata. The Archive and Restore utilities initially write to a file system. You can then move files from this file system into a third-party version control tool such as Oracle Repository, ClearCase, or SourceSafe.
Use the following sections to assist you with your Archive/Restore:
There are two places in Warehouse Builder where you can set up the version label used in the archive/restore. The first is in the New Project Wizard. The New Project Wizard contains a step that allows you to define version properties. The version label that you set here is the version label that is used when that project is archived. After you have created a project, you can edit the version label by opening the Properties dialog for the project. Click the Version Properties tab to modify the project version label.
Archive and Restore are different from Import and Export. Table 11-16 and Table 11-17 describe the differences between these features.
You can set up most of the archive and restore specifications using the Preferences dialog from the Administration or Project views. Use this dialog to specify labeling options, archive/restore directory locations and log folders.
To set up Archive/Restore preferences:
The Preferences dialog displays.
The archive service creates additional folders.
This folder contains the archive log files as they are created. The log files contain detailed information about the archive including how the file was created and the contents of what was archived. The name of each log file is automatically derived from the logical project name that is being archived. All archive log files start with A and incorporate the label, date, and time to make them unique.
This folder contains the restore log files as they are created. The log files contain detailed information about the restore including how the file was created and the contents of what was restored. The name of each log file is automatically derived from the logical project name contained in the archive. All restore log files start with R and incorporate the label, date, and time to make them unique.
The preferences have been set. You can now proceed to archiving and restoring your project.
Archiving a project allows you to copy metadata stored within a Warehouse Builder repository to an external location for the purpose of securing that data at a fixed point in time. Warehouse Builder provides an Archive Wizard to assist you in this process.
To archive a project:
The Archive Wizard Welcome page displays.
The Summary page displays a summary of the archive settings prior to running the archive process. If you want to see the details of your archive after the archive process is complete, check the Show details dialog following a successful archive box.
This begins the archive process. A progress window appears. When the progress bar reaches 100%, the archive process is complete.
If you checked the Show details dialog following a successful archive box, the Archive Results dialog displays. This dialog displays the name of each object type and how many of each were archived.
For a more detailed look at the archive process, click View Log File. This displays the entire log file.
Restoring a project allows you to recreate metadata within a Warehouse Builder repository from an external location.
To restore a project:
The Restore Wizard Welcome page displays.
The Select Archive page displays. Browse to or type the Archive File you want to restore.
The Summary page displays a summary of the restore settings prior to running the restore process. If you want to see the details of your restore after the restore process is complete, check the Show details dialog following a successful restore box.
This begins the restore process. A progress window appears. When the progress bar reaches 100%, the restore process is complete.
If you checked the Show details dialog following a successful restore box, the Restore Results dialog displays. This dialog displays the name of each object type, how many of each were restored, and how many of each were skipped.
For a more detailed look at the archive process, click View Log File. This displays the entire log file.
Warehouse Builder enables you to run reports on its design time metadata and runtime audit data using Oracle Discoverer workbooks. To run these reports, you must first install and access the Discoverer Workbooks packaged with Warehouse Builder. Discoverer workbooks can be used to run three types of reports: Definition reports, QA reports, and Runtime Reports.
Warehouse Builder is packaged with a set of Discoverer files, including import End User Layer (EUL) templates (.eex) and Discoverer workbook files (.dis). The following sections describe how to use these files to install and set up the EULs in Discoverer and how to access the Discoverer workbooks.
To install and access the Discoverer workbooks:
Before you can install the EUL templates and access the Discoverer workbooks, you must locate the necessary files packaged with your Warehouse Builder installation. You must also provide the required database connection information to enable the Discoverer administrator to access the Warehouse Builder data.
The following files used to install the Definition and QA workbooks are located at: \<OracleHome>\owb\misc\DiscoMetaDataReports-Design
The following files used to install the Runtime workbook are located at: \<OracleHome>\owb\misc\DiscoMetaDataReports-Runtime
To report on Warehouse Builder metadata, the Discoverer EUL must point to the correct instance in that database. The Discoverer administrator has a user account on the database where it is installed. If the Warehouse Builder metadata resides in the same database, you must provide the administrator with the user account for that instance and provide select privileges on public views. If the Warehouse Builder metadata resides in a different database, you must create a database link to enable the Discoverer administrator to connect to that database. Refer to the SQL Reference Manual for information on how to create a database link.
Once you provide this connection information and the administrator is able to connect to the Warehouse Builder metadata, you can create the .eex files by running the following scripts.
eexfix owbdefqa_template.eex mydesign.eex <OWBuser> [<OWBlink>]
<OWBuser> is the user account and <OWBlink> is the database link required to access the Warehouse Builder metadata.
eexfix owbruntime_template.eex myruntime.eex <OWBuser> [<OWBlink>]
Once you run this script, a .eex file is created and stored in the same directories as the packaged files.
You are now ready to import these files into the Discoverer 4.0 and view and edit the EUL using Discoverer Administration Edition.
You can import the .eex files into Discoverer by using the Import Wizard in the Administration tool. Refer to the Discoverer Administration User's Guide for details on importing .eex files.
After you import the .eex files, you can access the EUL for Warehouse Builder metadata and runtime audit data using the Discoverer Administration tool. This EUL is built using public views and contains high-level objects defined within Warehouse Builder projects. This reporting structure also facilitates user additions and extensions in future.
In the Administration tool, you can view a list of all the public views (grayed out) contained in the EUL that are not visible to the end user. These public SQL views, published with the Warehouse Builder installation, can also be used to construct new customized EULs.
Table 11-19 lists all the folders within the EUL for Warehouse Builder Definitions and QA workbooks and describes their contents. For descriptions of all the public views (grayed out and prefixed by All), refer to Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations".
Table 11-20 lists all the folders within the EUL for Warehouse Builder Runtime workbook and describes their contents. For descriptions of all the public views (grayed out and prefixed by All), refer to Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations".
To open the workbooks and run reports on Warehouse Builder metadata, connect to the Discoverer Plus Edition and open the workbooks owbdef.dis (for the Definition workbook) and qa.dis (for the QA workbook) from the following location:
\OracleHome\owb\misc\DiscoMetaDataReports-Design
You can open the runtime.dis file (for the Runtime workbook) from the following location:
\OracleHome\owb\misc\DiscoMetaDataReports-Runtime
These workbooks connect to the EULs previously installed.
If the Workbook in Other Database Account dialog displays, choose the default Open the workbook in current database account.
If you receive a warning about missing objects, the workbook cannot connect to the EUL. Check for incorrectly specified .eex file generation parameters. To validate these parameters, cancel out of this workbook and follow the prompts to create a table query based on the Warehouse Builder definition metadata EUL. If you do not see any objects in the Warehouse Builder definition metadata EUL, then the user you entered does not exist or does not have access to the data. Check to see if you have used a wrong user or entered the user name in lower case when creating the .eex file.
To check the information entered in the .eex file, open Discoverer Administration, open the Business Area Warehouse Builder definition metadata, right-click on one of the grayed out folders, and check its properties for the user name.
If you need to correct an error, you must rerun the .eex file generator with the correct user name and database link, delete this Business Area, and re-import it into Discoverer. You must also reconnect the Discoverer Plus Edition.
A workbook can contain multiple worksheets that enable you to document and manage your Warehouse Builder data. For example, the QA workbook contains worksheets for facts without dimensions, source objects not used, and mismatched data types. Reports or query results displayed in a worksheet can be edited and printed.
You can make your reports easier to read and more informative by reformatting them. To reformat the width of the columns, position your cursor in the column header between columns, so that it displays arrows pointing in both directions. Drag the column to make it wider or narrower.
When viewing Warehouse Builder metadata, you can choose objects from projects and modules defined within the Warehouse Builder repository. A down-pointing triangle to the right of the Page Items header signifies that you can click on the arrow and choose the Project Name, the Module Name, or the Table Name (when applicable).
Worksheets enable you to perform drill-down analysis on hierarchical data. A right-pointing triangle to the left of a column header signifies that you can drill down on that object. To perform a drill down:
The report displays the properties and hierarchical relationships for that object.
The definition workbook enables you to run reports on the objects defined within the Warehouse Builder repository, for example, Project, Measures, Dimensions, Hierarchies, Business Area, Tables, and Mappings.
The Version worksheet within the Definitions Workbook displays all the worksheets available within this workbook.
The Project worksheet contains the projects and modules defined within a logical Warehouse Builder design. The worksheet displays both source (Flat File, SAP, Designer, Oracle Database) and target modules.
The Measures worksheet lists the Fact tables defined within the selected project and module, along with their measures. You can further drill down on the Measure Name column to view measure properties such as data type, length, precision, and scale.
The Hierarchy worksheet lists all the dimensions, hierarchies, and levels defined within the selected project and module. You can drill on level names to view the level attributes and further on to view the attribute properties.
The Dimensions worksheet lists all the fact tables within the selected project or module, and the dimensions they reference.
The Business Area worksheet lists all the Business Areas defined within the selected project. It also lists the objects defined within each business area.
In the Tables worksheet, you can choose a table from a specific project and module. The worksheet then displays the columns defined within the selected table. The columns can be drilled on to view the column properties.
In the mappings worksheet, you can choose a mapping from a specific project and module. The worksheet then displays the source and target objects defined within that mapping.
The QA Workbooks enable you to report on incomplete and invalid object definitions. These reports are useful in tracking down logical errors in your Warehouse Builder design. The QA workbook contains the following types of worksheets.
The Facts without Dimensions worksheet reports on erroneous fact tables, within the selected project, that do not reference any dimensions.
The Facts without Measures worksheet reports on fact tables, within the selected project, that do not contain defined measures.
The Dimensions without Levels worksheet reports on the dimensions, within the selected project, that do not contain any defined levels.
The Source Objects Not Used worksheet reports on the source objects that have not been used as source items within mappings in the selected project.
The Level Attributes Not Used as Targets worksheet reports on the level attributes that have not been used as a target within mappings in the selected project.
The Measures Not Used as Targets worksheet reports on the measures that have not been used as a target in the selected project.
The Mismatched Data Types worksheet reports on mismatches based on the source and target types in the selected project.
The Runtime workbooks enable you to report on runtime data and track down runtime errors. The following worksheets are available within the runtime workbooks.
The Target Job Information worksheet reports on the job runs for each job selected from the Page Items field. A job includes several Warehouse Builder mappings that can be run separately. This report lists information on the runs along with any errors that were encountered.
The Errors worksheet displays the error numbers and error messages for each target object within a Warehouse Builder mapping within the selected job.
You can use the Query prediction feature within Discoverer to measure the cost of running Discoverer Workbooks.
Since the Warehouse Builder design data resides in one physical table (CMPALLCLASSES) with numerous views, the default settings for the Discoverer Query prediction are not optimal for increasing performance. To increase performance, you can either disable query prediction by updating the registry, or ANALYZE the table to gather information as indicated in the Discoverer documentation.
Below is an extract from the Discoverer Administration documentation. For details refer to the Discoverer Administration User's Guide.
For Windows NT, Discoverer 4.1 registry settings are stored under:
\\HKEY_CURRENT_USER\Software\Oracle\Discoverer 4\Database
For Windows 2000, Discoverer 4.1 registry settings are stored under:
\\HKEY_CURRENT_USER\Software\Oracle\Webdisco 4\Default\Database
QPPEnable =1 (0 = false, 1 = true): This setting enables you to turn Query Prediction on or off. Uses query prediction/performance (QPP) if set to 1.
An alternative is to analyze the Warehouse Builder table to improve the efficiency of the query prediction.
SQL> analyze table <username.tablename> compute statistics for all columns;
SQL> analyze table <username.tablename> compute statistics;
For example, if the user name is Warehouse Builder, then the statement is:
analyze table OWB.CMPALLCLASSES compute statistics for all columns;
analyze table OWB.CMPALLCLASSES compute statistics;
This analyze statement only needs to be run occasionally.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|
