| Oracle® Warehouse Builder Scripting Reference 10g Release 1 (10.1) Part Number B12152-01 |
|
|
View PDF |
| Oracle® Warehouse Builder Scripting Reference 10g Release 1 (10.1) Part Number B12152-01 |
|
|
View PDF |
This preface includes the following topics:
Oracle Warehouse Builder is a comprehensive toolset for practitioners who move and transform data, develop and implement business intelligence systems, perform metadata management, or create and manage Oracle databases and metadata. This guide describes how to use Warehouse Builder Oracle MetaBase (OMB) Scripting Language to:
Create a definition of a data warehouse.
Configure the definitions for a physical instance of the data warehouse.
Validate the set of definitions and their configurations.
Generate a set of scripts to create and populate the data warehouse instance.
Generate data transformation scripts.
Deploy and initially load the data warehouse instance.
Maintain the physical instance by conditionally refreshing it with generated scripts.
Integrate Warehouse Builder metadata with other Business Intelligence products.
Populate Oracle Discoverer EULs and OLAP catalogs for analyzing the data warehouse.
This guide is intended for data warehouse practitioners who want to access Warehouse Builder functionality programmatically:
Business Intelligence application developers
Warehouse architects, designers, and developers—especially SQL and PL/SQL developers
Developers of large-scale products based on data warehouses
Warehouse administrators
System administrators
Other MIS professionals
In order to use the information in this guide, you need to be comfortable with the concepts of Relational Database Management Systems and Data Warehouse design. For information on data warehousing, refer to the Oracle Database Data Warehousing Guide. Also, you need to be familiar with Oracle's relational database software products such as Oracle Database, SQL*Plus, SQL*Loader, Oracle Enterprise Manager, and Oracle Workflow.
The Oracle Warehouse Builder Scripting Reference contains the following chapters and appendixes.
Chapter 1, " Introduction", provides introductory information about the Warehouse Builder scripting language, OMB Plus. It describes the types of commands and how to use them. It also explains how to read the syntax diagrams included in this reference.
Chapter 2, " OMB Plus Commands ", includes an alphabetical listing of the navigation, service, administrative, and shell commands you can use in OMB Plus. Commands for metadata manipulation are contained in separate chapters.
Chapter 3, " OMBALTER ", provides a reference for using the metadata manipulation command OMBALTER with Warehouse Builder objects.
Chapter 4, " OMBCREATE ", provides a reference for using the metadata manipulation command OMBCREATE with Warehouse Builder objects.
Chapter 5, " OMBDROP ", provides a reference for using the metadata manipulation command OMBDROP with Warehouse Builder objects.
Chapter 6, " OMBRETRIEVE ", provides a reference for using the metadata manipulation command OMBRETRIEVE with Warehouse Builder objects.
Appendix A, " Running OMB Plus in Oracle JDeveloper", gives instructions on how to use OMB Plus in JDeveloper.
Appendix B, " Default Groups and Parameters", lists the default groups and parameter for mappings.
Warehouse Builder now provides you with extensive debugging capabilities for your mappings from within the Mapping Editor. Use the Mapping Debugger to locate logical design errors in your mappings. The new features allow you to step through the data flow of a mapping using comprehensive debugging functions such as setting breakpoints and watches and interactively changing test data.
This release introduces a new commit strategy for mappings with multiple targets. In previous releases, Warehouse Builder performed independent commits. That is, Warehouse Builder committed and rolled back each target separately and independently of other targets. In addition to this option, Warehouse Builder now also performs correlated commits. Warehouse Builder considers all targets collectively and commits or rolls back data uniformly across all targets. Use the correlated commit when it is important to ensure that every row in the source impacts all affected targets uniformly.
In previous releases, Warehouse Builder by default created a temporary table for mappings that required additional processing of source data before exchanging partitions. This occurred when the mapping contained remote sources or multiple sources joined together. Beginning in this release, you can now by-pass the creation of a temporary table and directly swap a source into a target. Use Direct PEL in a mapping to instantaneously publish fact tables that you loaded in a previously executed mapping.
Multiple Name and Address Software Providers: Beginning in this release, Warehouse Builder is compatible with multiple certified Name and Address software providers. Third-party vendors can license Name and Address software directly to you for use with Warehouse Builder. This allows you to choose a name and address provider whose offering is the most appropriate for your project.
Name-Address Operator Wizard: In previous releases, you defined the Name-Address operator using the mapping canvas and the operator Configuration Properties sheet. For improved usability, Warehouse Builder now enables you to use a wizard and Operator editor to create and edit the Name-Address operator.
Match-Merge Operator: Warehouse Builder incorporates the data quality functionality formerly available in Oracle Pure Integrate. You can use the Match-Merge operator available in the Mapping Editor to define business rules for matching and merging records. The Match-Merge operator together with the Name-Address operator support householding, the process of identifying unique households in name and address data.
In a previous release, you could perform metadata change management using the OMB Plus scripting utility. Beginning in this release, you can also access these functions from the Warehouse Builder client user interface. Metadata change management enables you to take snapshots of metadata objects and use them for backup and history management. Snapshots are supported for any object on the navigation tree and can store information about an object alone (such as a table or module), or the objects within it as well (such as the tables within a module).
Security: Warehouse Builder now provides advanced repository security and auditing options that you can implement according to your security requirements. The advanced security options include the following:
Proactive Security: Warehouse Builder enables you to plug in a customized security PL/SQL implementation package in the Warehouse Builder repository to provide tailored access control to users according to the security rules defined by your organization.
Reactive Security: Warehouse Builder enables you to track audit information based on the metadata history and to determine security policies from such audit information.
Data Stewardship: Warehouse Builder enables an individual or a group of individuals to "own" portions of the metadata rather than the technical administrators. Metadata ownership thus becomes an important component of metadata security management.
RAC Support: With the 9.2 release, Warehouse Builder provides increased support for RAC features. Warehouse Builder now supports the use of net service names in the runtime. This enables you to plan maintenance of nodes in a cluster without having to reconfigure the runtime environment. Warehouse Builder also provides an increased availability in the runtime service. For example, if either the service instance or its associated node fails or is taken out of service, then the runtime service instance on a different node can take over. While the Warehouse Builder design repository can also be used in a RAC cluster, it will not take advantage of any failover features of RAC for this release.
ZONED Data Type Support: Warehouse Builder now allows you to load fixed format data files containing ZONED decimal data. In the Flat File Sample Wizard, specify the ZONED data type for a flat file you import. The format for ZONED data is a string of decimal digits, one per byte, with the sign included in the last byte. (In COBOL, this is a SIGN TRAILING field.) The length of this field is equal to the precision (number of digits) that you specify. You may also specify a scale, which is the number of digits to the right of the decimal point.
DECIMAL Data Type Support: DECIMAL data is in packed decimal format; two digits per byte, except for the last byte, which contains a digit and sign. The DECIMAL data type includes precision and scale and therefore can represent fractional values.
Warehouse Builder now enables you to create public database links that can be shared across a database. Public database links can be created by repository owners, as well as any user with the CREATE PUBLIC DATABASE LINK privilege.
Starting with this release, Warehouse Builder is available on HP-UX and AIX platforms. This new availability is an addition to the UNIX (Solaris and Linux), and Windows (NT, 2000, and XP) platforms, which have been available from previous releases. (Note that the MITI Bridges feature is only available on Windows platforms and the Name and Address Server is only available on Windows and Solaris platforms.)
Starting in this release, Warehouse Builder now includes a Public Application Programming Interface (API). To access the API, unzip and extract the following file to a folder on your local machine:
<owb home directory>\owb\lib\int\pubapi_javadoc.jar
Double click on the file index.html. Select the Help link for information on how to use the API.
The following new features were introduced in Oracle Warehouse Builder :
Enhanced Navigation Tree: The navigation tree that displays in the Warehouse Builder console has been enhanced to improve navigation between projects and facilitate direct access to metadata repository objects. All projects are now visible from the tree, whereas previously you could only see one project at a time. Now you can expand a project node to display the contents of the active project. The module tree no longer appears in a separate window.
Wizards, Editors, and Properties Sheets: All Warehouse Builder wizards, editors, and properties sheets are now launched from the navigation tree.
Business Areas Renamed to Collections: In previous releases, you could create business areas in warehouse modules to organize objects in Warehouse Builder and to export metadata to tools such as Oracle Discoverer. Starting in this release, collections replace business areas in all functions and introduce enhancements, such as the ability to import metadata into and export metadata from a collection.
Fact Tables Renamed to Cubes: The terms fact and fact table have been replaced with cube in this release to be in line with OLAP industry standards.
Logical Names Renamed to Business Names: All references to logical names of objects have changed to business names in this release.
Toolbars in the Warehouse Builder Console: The utility drawer has been removed and the side and top toolbars in the Warehouse Builder console have been merged at the top to consolidate the most important functionality in one place.
Addition of Deployment Management Objects: This release introduces three object types to assist in managing connections to deployment sources and targets: Locations, Connectors, and Runtime Repository Connections. Locations define the physical location of the deployment. Connectors define relationships between locations. Runtime Repository Connections provide information about Runtime Repositories. Using these objects, you can create multiple deployment targets for the same target design.
Single Deployment Management Interface: The Deployment Manager provides a single interface for managing deployments of all objects, and executions of deployed mappings, transformations, and process flows. It also provides immediate access to the history of previously deployed objects. Not only does the Deployment Manager enable you to perform all these tasks from one interface, but Warehouse Builder now keeps track of runtime metadata, providing you with the history of what has previously been deployed.
Design Metadata Browsing: The Warehouse Builder Design Browser has been enhanced to include all new exposed objects, such as external tables, locations and connectors. In addition, you can now launch the Design Browser as a standalone executable; it no longer requires Oracle DatabaseAS to be installed for a single-user usage.
Runtime Metadata Browsing: The Warehouse Builder Runtime Audit Viewer has been replaced by the Runtime Audit Browser, which provides web-based reporting. The Runtime Audit Browser provides a more extensive set of deployment and execution audit reports than was available in previous releases. This audit data comes from information stored in the Runtime Repository and includes both deployment and execution data.
Warehouse Builder Public APIs: Starting with this release, Warehouse Builder offers this alternative for programmatic access to Oracle Warehouse Builder features: a full set of Java public APIs for application programmers who want to embed Warehouse Builder features and services in their own applications.
Warehouse Builder Scripting Language: Oracle MetaBase (OMB) Scripting Language provides access to all Warehouse Builder functions without accessing the Warehouse Builder graphical user interface. Users can access Warehouse Builder metadata and functionality by using OMB Plus, Warehouse Builder's scripting utility. This gives developers the power of using Warehouse Builder programmatically and extending its functionality where required. For more information on OMB Scripting Language, refer to the Oracle Warehouse Builder Scripting Reference .
Security: Warehouse Builder now provides an optional repository security and auditing system that you can implement according to your security requirements. You can create a multiple user account system where multiple identifiable users can access the same Warehouse Builder repository. Warehouse Builder also enables you to plug in a customized security PL/SQL implementation package in the Warehouse Builder repository to provide tailored access control to users according to the security rules defined by your organization.
Metadata Change Management (Metadata Snapshots): Starting in this release, you can take snapshots of metadata objects and use them for backup and history management. Snapshots are supported for any object on the navigation tree and can store information about an object alone (such as a table or module), or the objects within it as well (such as the tables within a module).
Multiple Language Support (MLS): With this feature, you can store the displayed business names and descriptions in languages other than the base language of the repository. Your different translations of business names and descriptions can be used to deploy to an EUL in the language of the target user population.
Extensibility Through User-Defined Properties: Users can define additional properties for any Warehouse Builder objects using the Warehouse Builder OMB Plus scripting utility. After you define user-defined properties through scripting, you can access them in the user interface, the Oracle MetaBase (OMB) Scripting Language, Oracle Warehouse Builder Java API Reference s, and Warehouse Builder Design Browser. This enhances the extensibility of Warehouse Builder and makes it easier to integrate it with other Business Intelligence products.
Metadata Loader (Import and Export) Flexibility Enhancements: Two new features were added to enhance this area of the product. The first is the ability for you to export metadata directly from Collections. The second feature is available from the Metadata Loader command line utility. It provides you with flexibility to specify the type of actions you want to apply when you import a first-class object.
Starting in this release, you can use the Process Flow Editor in Warehouse Builder to create and define process flows. External process operators that you previously defined in mappings are upgraded to user-defined processes and are contained within a process flow module. Process flows now integrate in the same Warehouse Builder design environment and no longer require you to use Oracle Workflow design client to perform these functions. The Warehouse Builder process flow modeler natively understands the semantic of your mappings and enables you to model activities such as FTP, email, etc.
Mapping User Interface: A new pre-defined display set, named Mapping, was added in this release. Selecting this display set causes the Mapping to only display columns that effectively are mapped, or used.
Mapping Compression: This feature automatically detects unused connections between operators and attributes in any given mapping and eliminates them from the repository. This dramatically enhances the performance of loading and storing large mappings that represent significant data flows.
Metadata Loader (Import and Export): Import and export functionality now takes advantage of the new compression feature available for each mapping. This means that the Metadata Loader now exports and imports only those mapping objects that are actually used.
OLAP Integration: Warehouse Builder enables you to design, deploy, and load multidimensional OLAP objects as ROLAP or MOLAP models from different data sources. After the data is loaded, you can use BI tools and applications to run complex analytical queries that answer your business questions. Using Warehouse Builder, you can now create and manage both your relational and and multidimensional objects from the same cube and dimension designs.
Advanced Queue (AQ) Integration: Warehouse Builder enables you to import Advanced Queue definitions and to use AQs as data sources and targets while designing your data warehouse. Through Advanced Queue functionality coupled with the Messaging Gateways, Warehouse Builder enables you to support messaging applications on MQ Series and Tibco as Warehouse Builder data sources. AQs also enable you to propagate change data capture from your source system to your target. The ability to integrate AQs lays the foundation for providing real time data warehousing in the future.
External Tables: Starting in this release, you can use external tables to represent data from non-relational file sources in a relational, read-only format. You can import an existing external table from an Oracle Database database. Or you can create an external table in Warehouse Builder based on a flat file definition. Warehouse Builder will generate the right DDL for you to deploy you external table to an Oracle Database database.
Oracle Database Multiple Table Inserts: Warehouse Builder takes advantage of Oracle Database database functionality and generates a multiple-table insert statement when the target database is Oracle Database. This enables you to optimize mappings to insert data into multiple tables in one operation.
Oracle Database Table Functions: Warehouse Builder introduces the Table Function operator that enables you to improve performance when loading your target system. Use this operator to develop custom code that can manipulate a set of input rows and return another set of rows possibly of different cardinality. Unlike conventional functions, table functions output a set of rows that can be queried like a physical table.
Unbound Flat Files as Targets: In this release, you can create a new, unbound flat file object as you create your mapping. Warehouse Builder creates a new comma-separated, single-record-type flat file in the specified location. This feature makes it easier to load the contents of a relational object into a flat file.
Outbound Reconcile for Flat Files: Outbound reconciliation makes it possible to create a new repository object from a mapping flat file. This results in a new, comma-separated file to be created where specified, provided that the flat file is new to that repository. This feature makes it easier to "quickly dump" the contents of a relational object to a flat file.
Logical Records for Delimited Files: The Flat File Sample Wizard has also been enhanced to display an improved user interface that allows you to define logical records for delimited files.
Position-Based Master-Detail Loading: Position-based master-detail flat files are now easier to load with the use of additional mapping operators.
SQL Property Extensions: You can now specify SQL properties for flat files you import into Warehouse Builder. This enables you to pre-define SQL property values for each flat file field. Thus, if mapping a flat file source to a relational target, the target column will default to these pre-defined SQL property values. These values will be used when building a relational target column or when creating an external table column.
Mapping User Interface: A new set of property tabs is now available for you to quickly create and edit mapping operators and attribute properties.
Pivot and Unpivot Operators: Starting in this release, you can add a pivot operator or an unpivot operator to a mapping. The pivot operator allows you to transform a single row of attributes into multiple rows. The unpivot operator converts multiple input rows into one output row.
Name and Address Operator Enhancements: The Name and Address operator has been enhanced to include new input roles and output attributes. The United States Postal Service Code Accuracy Support System (CASS) reporting is also supported starting with this release.
Starting with this release, Warehouse Builder is available on UNIX (Solaris, and Linux), as well as Windows (NT, 2000, and XP) platforms. This applies to all the components of Warehouse Builder, with the exception of the Name and Address libraries, which are not available on Linux in this release. (Note that the OLAP Bridges feature is only available on Windows platforms and the Name and Address Server is only available on Windows and Solaris platforms.)
In this manual, Windows refers to the Windows NT, Windows 2000, and Windows XP operating systems. The SQL*Plus interface to Oracle Database may be referred to as SQL.
In the examples, an implied carriage return occurs at the end of each line, unless otherwise noted. You must press the Return key at the end of a line of input.
The following conventions are also used in this manual:
| Convention | Meaning |
|---|---|
| . . . | Vertical ellipsis points in an example mean that information not directly related to the example has been omitted. |
| ... | Horizontal ellipsis points in statements or commands mean that parts of the statement or command not directly related to the example have been omitted. |
| boldface text | Boldface type in text refers to interface buttons and links. Boldface type also serves as emphasis to set apart main ideas. |
| italicized text | Italicized text applies to new terms introduced for the first time. Italicized text also serves as an emphasis on key concepts. |
unicode text |
Unicode text denotes exact code, file directories and names, and literal commands. |
italicized unicode text |
Italicized unicode text refers to parameters whose value is specified by the user. |
| [] | Brackets enclose optional clauses from which you can choose one or none. |
The Warehouse Builder documentation set includes these manuals:
Oracle Warehouse Builder User's Guide
Oracle Warehouse Builder Installation and Configuration Guide
Oracle Warehouse Builder Transformation Guide
Oracle Warehouse Builder Scripting Reference
Oracle Warehouse Builder Release Notes
In addition to the Warehouse Builder documentation, you can refer to other documents listed below:
Oracle Database Data Warehousing Guide
Oracle provides additional information sources, including other documentation, training, and support services that can enhance your understanding and knowledge of Oracle Warehouse Builder .
For more information on Oracle Warehouse Builder technical support, contact Oracle World Wide Support services at:
http://www.oracle.com/support
For the latest information on, and downloads of, software and documentation updates to Oracle Warehouse Builder , visit MetaLink at:
http://metalink.oracle.com
You can order other Oracle documentation at:
http://oraclestore.oracle.com
Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at
http://www.oracle.com/accessibility/
JAWS, a Windows screen reader, may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, JAWS may not always read a line of text that consists solely of a bracket or brace.
Metalink is the Oracle support web site where you can find the latest product information, including documentation, patch information, BUG reports, and TAR entries. Once registered, you can access email, phone and web resources for all Oracle products. Metalink is located at:
http://metalink.oracle.com
Check Metalink regularly for Warehouse Builder information and updates.
You can order Oracle product documentation by phone or through the World Wide Web:
Phone: Call 800-252-0303 to order documentation or request a fax listing of available Oracle documentation.
Oracle Documentation Sales Web site: http://oraclestore.oracle.com
Oracle Customer Support Web site: http://www.oracle.com/support
