| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
In Oracle Warehouse Builder you can access data from a variety of sources. As a precursor to extracting any data set, you first import its metadata.
This chapter includes the following topics:
The source systems for a data warehouse are typically transaction processing applications. For example, a sales analysis data warehouse typically extracts data from an order entry system that records current order activities.Designing the extraction process can be problematic. If the source system is complex and poorly documented, then determining which data to extract can be difficult. Moreover, the source system typically cannot be modified, nor can its performance or availability be adjusted. To address these problems, first import the metadata.
Metadata is the data that describes the contents of a given object in a data set. For example, the metadata for a table would indicate the data type for each column. After you import the metadata into Warehouse Builder, you can annotate the metadata and design an extraction strategy independently from the transaction processing application.
Review the list of Supported Sources and Targets to determine if the source from which you want to extract data is supported in Warehouse Builder.
If you have not already done so, create a location and module for the source as described in "Creating an Oracle Data Warehouse".
Right-click the module and select Import.
Follow the prompts in the Metadata Import Wizard.
The wizard prompts you for information based on the type of source you selected.
Subsequent Steps
After successfully importing the metadata, you can design ETL logic to extract the data from the source, transform the data, and load it into a target schema.
Over the course of time, the source metadata may change. If this occurs, you can use Warehouse Builder to first identify the ETL logic that would be impacted and potentially made invalid due to a change in metadata as described in Chapter 31, "Managing Metadata Dependencies".
To introduce the changed metadata into Warehouse Builder, right-click the desired module and select Import. As described in "Re-Importing Definitions from an Oracle Database", Warehouse Builder recognizes when you are re-importing metadata.
Whether you want to import metadata from a table, file, or application, the general process is the same and you always import metadata into a module.
In the Project Explorer, right-click the module and select Import. Follow the prompts in the Import Metadata Wizard.
Example: Importing Data from Flat Files
Assume that there are numerous flat files stored across three different drives and directories. In the Connection Explorer, you created 3 locations. Now in the Project Explorer, right-click the Files node and select New to create a new module. Repeat this for each of the three directories. For each of the three modules, select Import. A wizard directs you on how to import one or more files into each module.
Table 5-1 lists the data storage systems and applications that Warehouse Builder 10.2 can access.The table lists the supported sources and targets in each Location node as displayed in the Connection Explorer.
Table 5-1 Sources and Targets Supported in Warehouse Builder 10.2
| Location Node in the Connection Explorer | Supported Sources | Supported Targets |
|---|---|---|
|
Databases/Oracle |
Oracle db 8.1, 9.0, 9.2, 10.1, 10.2 |
Oracle db 9.2, 10.1, 10.2 |
|
Databases/Non-Oracle |
Any database accessible through Oracle Heterogeneous Services, including but not limited to DB2, DRDA, Informix, SQL Server, Sybase, and Teradata.Any data store accessible through the ODBC Data Source Administrator, including but not limited to Excel and MS Access. |
To load data into spreadsheets or third-party databases, first deploy to a comma-delimited or XML format flat file. |
|
Files |
Delimited and fixed-length flat files. |
Comma-delimited and XML format flat files. |
|
Applications |
SAP R/3 3.x, 4.x Oracle E-Business Suite PeopleSoft 8, 9 |
none |
|
Process Flows and Schedules/OEM |
None |
OEM Agent 9.0, 9.2 |
|
Process Flows and Schedules/Oracle Workflow |
None |
Oracle Workflow 2.6.2, 2.6.3, 2.6.4, 11i |
|
Process Flows and Schedules/Concurrent Manager |
None |
Concurrent Manager 11i |
|
Business Intelligence/BI Beans |
None |
BI Beans 10.1 |
|
Business Intelligence/Discoverer |
None |
Discoverer 10.1 |
|
Databases/Transportable Module Source |
N/A |
|
|
Databases/Transportable Module Target |
N/A |
Warehouse Builder communicates with non-Oracle systems using Oracle Database Heterogeneous Services and a complementary agent. Heterogeneous Services make a non-Oracle system appear as a remote Oracle Database server. The agent can be an Oracle Transparent Gateway or the generic connectivity agent included with Oracle Database.
A transparent gateway agent is a system-specific source. For example, for a Sybase data source, the agent is a Sybase-specific transparent gateway. You must install and configure this agent to support the communication between the two systems.
Generic connectivity is intended for low-end data integration solutions and the transfer of data is subject to the rules of specific ODBC or OLE DB drivers installed on the client machine. In this case, you do not need to purchase a separate transparent gateway; you can use the generic connectivity agent included with the Oracle Database server. You must still create and customize an initialization file for your generic connectivity agent.
For additional information on distributed processing systems, see Oracle Database Distributed Database Systems.
Warehouse Builder provides an end-to-end business intelligence solution by enabling you to integrate metadata from different data sources, designing and deploying it to a data warehouse, and making that information available to analytical tools for decision making and business reporting.
Warehouse Builder introduces Business Intelligence (BI) objects that enable you to integrate with Oracle Business Intelligence tools such as Discoverer and Business Intelligence (BI) Beans. You can define BI objects in Warehouse Builder that enable you to store definitions of business views and presentation templates. You can then deploy these definitions to the Oracle Business Intelligence tools and extend the life-cycle of your data warehouse.
This section contains the following topics:
Warehouse Builder enables you to derive and define Business Intelligence (BI) objects that integrate with analytical business intelligence tools, such as Oracle Discoverer and BI Beans. By deploying these BI definitions to your analytical tools, you can perform ad hoc queries on top of the relational data warehouse or define a dashboard on top of multidimensional data marts.
The BI objects you derive or define in Warehouse Builder represent equivalent objects in Oracle Discoverer and BI Beans. These definitions are stored under the Business Intelligence node on the Warehouse Builder Project Explorer.
The Business Intelligence node contains two additional nodes called Business Definitions and Business Presentations. You start by first creating a Business Definition module to store the definitions to be deployed to Discoverer. For details, see "About Business Definitions". You can also create a Business Presentation module to store the presentation templates you want to deploy to BI Beans. For details, see "About Business Presentations".
Business intelligence is the ability to analyze data to answer business questions and predict future trends. Oracle Discoverer is a BI tool that enables users to analyze data and retrieve information necessary to take business decisions. Discoverer also enables users to share the results of their data analysis in different formats (including charts and Excel spreadsheets).
Discoverer uses the End User Layer (EUL) metadata view to insulate its end users from the complexity and physical structure of the database. You can tailor the EUL to suit your analytical and business requirements and produce queries by generating SQL. The EUL provides a rich set of default settings to aid report building.
Through BI objects, Warehouse Builder enables you to design a data structure that facilitates this data analysis. Business Intelligence objects in Warehouse Builder provide the following benefits:
Complete and seamless integration with Oracle Discoverer and BI Beans.
Advanced deployment control of metadata objects using the Warehouse Builder Control Center.
Complete, end-to-end lineage and impact analysis of Discoverer objects based on information in the Warehouse Builder repository.
Ability to utilize Warehouse Builder metadata management features such as snap shots, multi language support, and command line interaction.
You can integrate with Discoverer by deriving business definitions directly from your warehouse design metadata. Alternatively, you can also create your own customized business definitions in Warehouse Builder.
The business definition objects in Warehouse Builder are equivalent to the Discoverer EUL objects. When you derive business definitions from your existing design metadata, Warehouse Builder organizes the definitions in Item Folders that correspond to Folders in Discoverer. You can define joins and conditions for the Items Folders and select the Items they contain using the Warehouse Builder wizards and editors. Additionally, you can define Drill Paths, Alternative Sort Orders, Drills to Detail, and Lists of Values for the Items within the Item Folders.
Warehouse Builder also enables you to define any functions registered with Discoverer. You can also sort your definitions by subject area by defining Business Areas that reference multiple Item Folders. You can then deploy these Business Areas along with the business definitions to a Discoverer EUL using the Control Center. For more information about deploying business definitions, see "Deploying Business Definitions".
For information on creating, deriving, and deploying business definitions in Warehouse Builder, see Chapter 15, "Defining Business Intelligence Objects".
You can integrate with Oracle BI Beans by building and deploying the structures for Business Presentations or BI Reports directly from your warehouse design metadata.
Presentation Templates are stored within Business Presentation modules created under the Business Intelligence node on the Warehouse Builder Project Explorer. In Warehouse Builder, you can define first cut cross-tab and graphical Presentation Templates that can be deployed multiple times.
For information about creating business presentation, see Chapter 15, "Defining Business Intelligence Objects".
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
