Oracle Tools and Products

Exclusive offer: get 50% off this eBook here
Oracle Information Integration, Migration, and Consolidation

Oracle Information Integration, Migration, and Consolidation — Save 50%

The definitive book and eBook guide to Oracle Information Integration and Migration in a heterogeneous world

$32.99    $16.50
by Tom Laszewski | August 2011 | Enterprise Articles Oracle

In early 2010, when you went to the Oracle Technology Network (OTN) website, it had just one link called Information Integration. This link led you to a simple web page that had information on Oracle-to-Oracle database-centric migration and integration tools such as SQL Loader, Data Pump, Oracle Streams, and Oracle Data Guard. The OTN website has been updated, but still lacks comprehensive coverage of the Oracle Information Integration stack. In this article by Tom Laszewski, co-author of Oracle Information Integration, Migration, and Consolidation, we will provide you with the relevant information, so that you can use the right Oracle Enterprise Information Integration (EII) or data migration product for your data migration, consolidation, and information integration projects.

This article covers the Oracle integration, migration, and consolidation products, tools and technologies in terms of the following taxonomies: data migration, physical federation, virtual federation. The focus of this article is to cover the great breadth and depth of Oracle products, not all the finer details.

 

Oracle Information Integration, Migration, and Consolidation: RAW

Oracle Information Integration, Migration, and Consolidation: RAW

The definitive book and eBook guide to Oracle Information Integration and Migration in a heterogeneous world

        Read more about this book      

(For more resources on Oracle, see here.)

Readers in a DBA or database development role will most likely be familiar with SQL Loader, Oracle database external tables, Oracle GoldenGate, and Oracle Warehouse Builder. Application developers and architects will mostly likely be familiar with Oracle BPEL and the Oracle Service Bus.

Database migration products and tools

Data migration is the first step when moving your mission critical data to an Oracle database. The initial data loading is traditionally done using Oracle SQL Loader. As data volumes have increased and data quality has become an issue, Oracle Data Warehouse and Oracle Data Integrator have become more important, because of their capabilities to connect directly to source data stores, provide data cleansing and profiling support, and graphical drag and drop development. Now, the base addition of Oracle Data Warehouse Builder is a free, built-in feature of the Oracle 11g database, and price is no longer an issue.

Oracle Warehouse Builder and Oracle Data Integrator have gained adoption as they are repository based, have built-in transformation functions, are multi-user, and avoid a proliferation of scripts throughout the enterprise that do the same or simpler data movement activity. These platforms provide a more repeatable, scalable, reusable, and model-based enterprise data migration architecture.

SQL Loader

SQL Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. The tool is invoked, when you specify the sqlldr command or use the Oracle Enterprise Manager interface.

SQL Loader has been around as long as the Oracle Database logon "scott/tiger" and is an integral feature of the Oracle database. It works the same on any hardware or software platform that Oracle supports. Therefore, it has become the de facto data migration and information integration tool for most Oracle partners and customers. This also makes it an Oracle legacy data migration and integration solution with all the issues associated with legacy tools, such as:

  • difficult to move away from as the solution is embedded in the enterprise.
  • The current solution has a lot of duplicated code, because it was written by many different developers before the use of structured programming and shared modules.
  • The current solution is not built to support object-orientated development, Service Orientated Architecture products, or other new technologies such as web services and XML.
  • The current solution is difficult and costly to maintain because the code is not structured, the application is not well documented, the original developers are no longer with the company, and any changes to the code cause other pieces of the application to either stop working or fail.

SQL Loader is typically used in 'fat file' mode. This means the data is exported into a command-delimited fat file from the source database or arrives in an ASCII fat file. With the growth of data volumes, using SQL Loader with named pipes has become common practice. Named pipes eliminate the need to have temporary data storage mechanisms—instead data is moved in memory.

It is interesting that Oracle does not have an SQL unload facility, as Sybase and SQL Server have the Bulk Copy Program (BCP). There are C, Perl, PL/SQL, and other SQL-based scripts to do this, but nothing official from Oracle. The SQL Loader source and target data sources along with development languages and tools supported are as follows:

Data source

Data target

Development languages and tools

Any data source that can produce flat files. XML files can also be loaded using the Oracle XMLtype data type

Oracle

Proprietary SQL Loader control files and SQL Loader Command Line Interface (CLI)

The most likely instances or use cases when Oracle SQL Loader would be the Oracle product or tool selected are:

  • Bulk loading data into Oracle from any data source from mainframe to distributed systems.
  • Quick, easy, one-time data migration using a free tool.

Oracle external tables

The external tables feature is a complement to the existing SQL Loader functionality. It enables you to access data in external sources as if it were in a table in the database. Therefore, standard SQL or Oracle PL/SQL can be used to load the external file (defined as an external table) into an Oracle database table.

Customer benchmarks and performance tests have determined that in some cases the external tables are faster than the SQL Loader direct path load. In addition, if you know SQL well, then it is easier to code the external table load SQL than SQL Loader control files and load scripts. The external table source and target data sources along with development languages and tools supported are:

Data source

Data target

Development languages and tools

Any data source that can produce flat files

Oracle

SQL, PL/SQL, Command Line Interface (CLI)

The most likely instances or use cases when Oracle external tables would be the Oracle product or tool selected are:

  • Migration of data from non-Oracle databases to the Oracle database.
  • Fast loading of data into Oracle using SQL.

Oracle Warehouse Builder

Oracle Warehouse Builder (OWB) allows users to extract data from both Oracle and non-Oracle data sources and transform/load into a Data Warehouse, Operational Data Store (ODS) or simply to be used to migrate data to an Oracle database. It is part of the Oracle Business Intelligence suite and is the embedded Oracle Extract- Load-Transform (ELT) tool in this BI suite. With the usage of platform/product specific adapters it can extract data from mainframe/legacy data sources as well.

Starting with Oracle Database 11g, the core OWB product is a free feature of the database. In a way, this is an attempt to address the free Microsoft entry level ELT tools like Microsoft Data Transformation Services (DTS) and SQL Server Integration Services (SSIS) from becoming de facto ELT standards, because they are easy to use and are cheap (free). The Oracle Warehouse Builder source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Any data source that can produce flat files

Any databases from mainframe to open systems that has ODBC or JDBC connectivity

Can be used with the Oracle Gateways, so any data source that the Gateway supports

Oracle, ODBC compliant data stores, and any data source accessible through Oracle Gateways, flat files, XML

OWB GUI development tool, PL/SQL, SQL, CLI

The most likely instances or use cases when OWB would be the Oracle product or tool selected are:

  • Bulk loading data on a continuous, daily, monthly or yearly basis.
  • Direct connection to ODBC compliant databases for data migration, consolidation and physical federation, including data warehouses and operational data stores.
  • Low cost (free) data migration that offers a graphical interface, scheduled data movement, data quality, and cleansing.

SQL Developer Migration Workbench

Oracle SQL Developer Migration Workbench is a tool that enables you to migrate a database, including the schema objects, data, triggers, and stored procedures, to an Oracle Database 11g using a simple point-and-click process. It also generates scripts necessary to perform the migration in batch mode. Its tight integration into SQL Developer (an Oracle database development tool) provides the user with a single- stop tool to explore third-party databases, carry out migrations, and to manipulate the generated schema objects and migrated data. Oracle SQL Developer is provided free of charge and is the first tool used by Oracle employees to migrate Sybase, DB2, MySQL and SQL Server databases to Oracle.

SQL Developer Migration Workbench 3.0 was released 2011 and includes support for C application code migration from Sybase and SQL Server DB-Library and CT- Library, a Command Line Interface (CLI), a host of reports that can be used for fixing items that did not migrate, estimating and scoping, and database analysis, and a pluggable framework to support identification and changes to SQL in Java, Powerbuilder, Visual Basic, Perl, or any programming language.

SQL Developer Migration Workbench actually started off as a set of Unix scripts and a crude database procedural language parser based on SED and AWK. This solution was first made an official Oracle product in 1996. Since then, the parser has been totally rewritten in Java and the user interface integrated with SQL Developer. SQL Developer Migration Workbench source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

DB2 LUW, MySQL, Informix, SQL Server, Sybase

 

Oracle

SQL Developer GUI development tool, Command Line Interface (CLI)

The most likely instances or use cases when SQL Developer Migration Workbench would be the Oracle product or tool selected are:

  • Data migration from popular LUW RDBMS systems to Oracle using fat files or JDBC connectivity.
  • RDBMS object (stored procedures, triggers, views) translation from popular LUW RDBMS to Oracle.
Oracle Information Integration, Migration, and Consolidation The definitive book and eBook guide to Oracle Information Integration and Migration in a heterogeneous world
Published: September 2011
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

 

        Read more about this book      

(For more resources on Oracle, see here.)

Oracle Data Integrator

Oracle Data Integrator (ODI) is a product that Oracle acquired from Sunopsis SA in 2006. This acquisition may have seemed unnecessary at the time as Oracle already had OWB for data migration and integration. However, the complaint from customers was that OWB required an Oracle database and the OWB engine was based on PL/SQL. Therefore, it was a very database-centric product, and at the time did not support target databases other then Oracle.

ODI is a data migration and integration software product, providing a declarative design approach to defining data transformation and integration processes, resulting in faster and simpler development and maintenance. Based on an Extract-Load- Transform (E-L-T) architecture, Oracle Data Integrator, unlike traditional Extract- Transform-Load (E-T-L) products, loads the data into the target database and then does the transformation processing. The ELT approach is important for a number of reasons:

  • The underlying target database SQL and database languages (such as PL/ SQL, T-SQL, and DB2 SQL PL) are used to perform the transformations and not a proprietary ETL language.
  • The RASP (Reliability, Availability, Scalability, Performance) of the underlying database can be used instead of a proprietary engine that is external to where the data resides.
  • In most cases, getting the data into the database and then transforming it is faster than the way most IT developers and DBAs use ETL products.

By combining data, event-based, and service-based integration, ODI is able to address varying needs from legacy data migration, data warehousing, and business intelligence to Master Data Management, Service Oriented Architecture, and others (such as cloud computing). Oracle Data Integrator provides a unified infrastructure to streamline data migration and integration projects. A white paper on ODI and legacy modernization using ODI can be found at http://www.oracle.com/us/ products/middleware/059481. The ODI source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Flat files, JDBC, ODBC, web services, XML

Any ODBC or JDBC compliant database, JMS, flat files, XML, web services

 

ODI GUI development tool, SQL, PL/SQL, T-SQL, DB2 SQL PL, Java, CLI

The most likely instances or use cases when ODI would be the Oracle product or tool selected are:

  • Bulk loading data on a continuous, daily, monthly, or yearly basis.
  • Direct connection to ODBC and JDBC compliant databases for data modernization, migration, consolidation, and physical federation (data mart, data hub, and data warehouses).
  • Web services-based data migration.
  • Data migration or Change Data Capture (CDC) that offers a graphical interface, scheduled data movement, data quality and cleansing, and data profiling.

Oracle Enterprise Manager tuning and diagnostic packs

Oracle Enterprise Manager (OEM) 11g provides a single, integrated solution for testing, deploying, operating, monitoring, diagnosing, and resolving performance problems. It delivers manageability and automation for your grid across both Oracle and non-Oracle technologies. This means that Oracle Enterprise Manager (OEM) can be used to diagnose data migration issues across Oracle databases, non-Oracle databases, and the application tiers.

OEM was at one point a thick client-based solution that had both a slow response time and limited graphical capabilities. OEM also lacked support for other databases or even the Oracle Application Server. In the last few years, OEM has been upgraded to support other databases (Sybase, SQL Server, DB2, and others), and includes support for the Oracle Application Server and other application servers such as IBM Websphere. OEM now has browser-to-database support; support for the web-to- application server tier to database level tracing and diagnostics.

The OEM tuning and diagnostic packs can be used in place of Oracle UTLBSTAT/ UTLESTAT or the Oracle Stats Pack. The Oracle Stats Pack was introduced in Oracle 8i and replaced UTLBSTAT/UTLESTAT – which are still in use today. Oracle Stats Pack has been the typical method of diagnosing Oracle performance concerns. It is a great tool, but a lot like coding in Unix VI, and really requires an expert to determine the root cause. OEM tuning and diagnostic packs make the process more of a science than an art. OEM tuning and diagnostic packs, unlike the Oracle Stats Pack, are an OEM option which means that there are license fees associated with their usage. The source and target data sources of OEM tuning and diagnostic packs along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

DB2 LUW, MySQL, Informix, SQL Server, Sybase

Any databases from mainframe to open systems that have ODBC or JDBC connectivity

Oracle

Oracle Enterprise Manager web UI, SQL, PL/SQL, CLI

The most likely instances or use cases when the OEM tuning and diagnostic packs would be the Oracle product or tool selected are:

  • Database migration, consolidation, performance, and tuning.
  • Information integration, data consolidation, and data grid deployments.

You get what you pay for. Using free tools like SQL Loader, SQL Developer Migration Workbench and, to a lesser degree, Oracle Warehouse Builder all seem like the logical choice because of their perceived cost advantage. This is only a perceived cost as the actual cost to build high speed data migration solutions, sophisticated data mapping, or data quality functionality on top of these free tools far outweigh the initial no cost solution. Certainly, for quick, one-time data migration involving less than 500 tables and 100 gigabytes of data, the 'free' solutions will work just fine. In these cases, SQL Loader or SQL Developer Migration Workbench may be 'enough tool'.

Physical federation products

Physical federation, as the name implies, involves physically moving data from its target data source into an Oracle Online Transaction Processing (OLTP) system, Data Warehouse (DW) or Operational Data Store (ODS). The data movement may take place using bulk file transport, data streaming through ODBC or JDBC, a messaging system, reading of database log files or any information integration mechanism.

Traditionally, physical federation has been associated with enterprise data warehousing. Master Data Management and data hubs have moved physical federation from being a read-only database (such as a data warehouse or data mart) to a component of a company's OLTP infrastructure. This complicates the situation as solutions like Change Data Capture (CDC), bi-directional replication, and data write back to the source may be required.

Since we are pulling data in many cases from legacy environments, non-Oracle databases, or consolidating information that may have duplicate data, solutions such as data quality, cleaning, and profiling are often a component of physical federation architectures. Oracle offers products that not only move the data, read the changes from the source (CDC), and replicate the data in a bidirectional fashion, but also cleanse the data and provide detailed analysis and a profile of your various data sources.

Oracle Data Integrator

Oracle Data Integrator (ODI) can be used for initial data migration and can be used for physical federation. This is the case as ODI offers both bulk load and CDC capabilities. Therefore, ODI could be used to do the initial load of your federated database and then keep the federated database in sync with the online source databases. ODI CDC capabilities are provided through Knowledge Modules. ODI comes with a large set of out of the box Knowledge Models (KM) or you can develop your own.

Knowledge Modules are code templates. Each KM is dedicated to a specialized task or set of tasks in the overall data integration process. The code in the KMs appears in the form that it will be executed except that it includes ODI substitution methods, enabling it to be used generically by many different integration jobs.

The code that is generated and executed is derived from the declarative rules and metadata defined in the ODI Designer module. The ODI physical federation source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Flat files, JDBC, ODBC, XML, web services, custom developed KDM to support any data source or source application system

Any ODBC or JDBC compliant database, JMS, flat files, XML, web services, custom developed KDM to support a target data source or application

ODI GUI development tool, SQL, PL/SQL, T-SQL, DB2 SQL PL, Java, CLI

The most likely instances or use cases when ODI would be the Oracle product or tool selected are:

  • Physical federation where sophisticated data transformations, data cleansing, and data profiling is required
  • Data sources or target not supported by Oracle GoldenGate

Oracle GoldenGate

Oracle GoldenGate software was acquired from the software company GoldenGate in 2009. Prior to GoldenGate, Oracle did not offer a heterogeneous bidirectional data replication solution. Oracle did offer Oracle Streams at the time of the acquisition, but this product only supported Oracle-to-Oracle replication; replication to non- Oracle databases could be done but required custom development. GoldenGate was clearly the leader in the marketplace for heterogeneous replication; so the acquisition jumped Oracle to the forefront in the software space of log-based heterogeneous data replication solutions.

GoldenGate's zero-downtime solution allows new systems to be continuously updated with the changes committed in source databases, so that applications and/or users can continue to use the source and target databases without any downtime. To support this approach, GoldenGate's core software platform captures, transforms, routes, and delivers transactional data in real-time across heterogeneous databases and operating systems. The platform consists of decoupled modules that can be combined across the enterprise to provide maximum flexibility, availability, and performance.

The bidirectional capabilities of GoldenGate provide for multi-master database configurations in a federated databases environment. A GoldenGate Capture module resides on the source system, and a GoldenGate delivery module resides on the target system. In some cases, Oracle customers have used GoldenGate in addition to, or as an alternative to, Oracle DataGuard as an Oracle standby database solution. The Oracle GoldenGate source and target data sources along with development languages and tools that are supported are:

Data source

Data target

 

Development languages and tools

Oracle, DB2 z/OS, JMS-based systems, DB2 LUW, Sybase, SQL Server, Enscribe, SQL/ MX, MySQL

Oracle, SQL Server, Sybase, Enscribe, SQL/MX, Teradata, Oracle TimesTen, DB2 iSeries, DB2 LUW , Oracle E-Business Suite, Oracle PeopleSoft, Oracle JD Edwards

SQL, Unix Shell, DOS BAT

The most likely instances or use cases when Oracle GoldenGate would be the Oracle product or tool selected are:

  • Physical federation with limited data transformation, data quality, and data cleansing
  • Log-based CDC solution for performance and minimal impact to source database that may be incurred using trigger-based CDC
  • Near real-time data replication

Oracle CDC adapters

Oracle CDC offers log-based, non-intrusive database-based change data capture. The product offers change record filtering by operation, column, and content. The solution offers flexible delivery models including batch, micro-batch, and events. It also includes reliable delivery and recovery, and seamless integration with Service Orientated Architecture (SOA) Suite, and ODI. The Oracle CDC JCA-based adapters are configured using Oracle JDeveloper.

Development of applications that use the CDC adapters is done using a model- driven wizard approach in Oracle JDeveloper. The CDC adapters are JCA-based, there is a roadmap in place for JMS-based events support. The Oracle CDC adapters are Original Equipment Manufacturer (OEM) from Attunity Software. Except for the SQL Server adapter, there is no overlap with the GoldenGate solution. They are both log-based solutions, so the future merger of these two products does seem to make sense; but nothing has been announced. The Oracle CDC source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Mainframe DB2, IMS, VSAM, Adabas, and SQL Server

Oracle

Oracle Legacy Studio GIU, proprietary control files

The most likely instances or use cases when Oracle CDC would be the Oracle product or tool selected are:

  • Mainframe data sources are involved in the solution
  • CDC where tight integration with ODI is required

Oracle Master Data Management

Oracle's Master Data Management (MDM) solution is a set of applications (MDM data hubs) designed to consolidate, cleanse, govern, and share business data objects across the enterprise and across time. The business data objects are basically consolidated data that resides in different databases across the enterprise. It includes pre-defined extensible data models and access methods with applications to centrally manage the quality and lifecycle of master business data.

The first data hub solution Oracle offered was the customer data hub. This customer data hub was introduced in 2004 and it was a year later when the product data hub was introduced. One of the interesting aspects of all the data hub solutions is that there is no data integration or CDC solution embedded in the offerings. You are free to use any of the Oracle-based solutions or a third-party solution to collect the data from it's source database.

In the event that all customer systems can't be modernized and migrated at the same time, the Oracle MDM solution can be used to provide a single customer view across the organization. The Oracle Customer Hub is a Customer Data Integration (CDI) solution that enables organizations to centralize information from heterogeneous systems, creating a single view of customer information that can be leveraged across all functional departments and analytical systems.

The Oracle Customer Hub is the most mature of the Oracle data hub products. Oracle also offers product, supplier, site, and financial data hubs. I am sure the list of these data hubs will grow. These data hubs along with other Oracle solutions such as Oracle Portal, Oracle Gateways, SOA adapters and BI Suite make up a complete federation solution. A complete federation solution is an end-to-end solution that has a web portal frontend (Oracle Portal), Oracle Gateways for virtually federated data access, SOA adapters to access legacy data stores, and non-data centric sources like web services, and a BI Suite to provide data analysis and ad hoc reporting. Oracle MDM source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Any data source

Oracle

Java, SQL, web services

The most likely instances or use cases when Oracle MDM would be the Oracle product or tool selected are:

  • Out of the box customer, product, and other data hubs
  • An enterprise view of customers, products, suppliers, sites, and financial data

Oracle Data Quality and Profiling

The Oracle Data Quality and profiling solutions are OEM'd from Trillium. Trillium offers industry leading data quality and profiling solutions. Thus, it was an Oracle decision not to build a solution, but rather to use the best of the breed.

The Oracle Data Quality and profiling products are offered as part of ODI. The data quality and profiling product includes functionality for data rule derivation and data rule profiling, and provides support for custom and predefined data rules. This includes the ability to apply the data rules to data objects. The following is a list of the key features of Oracle Data profiling:

  • Automatically searches for relationships across sources; all the data can be collected and compared to data from other sources
  • Provides data standard definition rules that can validate user-specified patterns or data rules
  • Loads and analyzes the data in one single pass without impacting the source system
  • Profiling metadata and statistics are captured in the metadata repository to drive your data rules, ongoing data quality auditing, and automated data cleansing processes; you can also define data rules specific to your business or your problem domain, and test compliance with those rules during the data profiling process

The Oracle Data Quality and profiling source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Any data source

Oracle

GUI, CLI

The most likely instances or use cases when Oracle Data Quality and profiling would be the Oracle product or tool selected are:

  • Physical federation where source data requires significant data cleansing
  • To avoid DIY data quality and data profiling solutions

Physical federation provides a high performance environment where users can go to one place at any time to find the 'single data source of truth'. However, keeping all data sources in sync can prove to be a complicated problem to solve. Fortunately, Oracle provides many choices (Oracle GoldenGate, Oracle Data Integrator, and Oracle CDC) to keep the federated database and source data sources in sync.

Oracle Information Integration, Migration, and Consolidation The definitive book and eBook guide to Oracle Information Integration and Migration in a heterogeneous world
Published: September 2011
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on Oracle, see here.)

Virtual federation products

Virtual federation is about leaving the data where it is and providing the user community with a view to the data through the tool of their choice, including a web browser. Instead of moving data around like physical federation, an Oracle product is used to provide users with a single view of their data. Virtual federation using Oracle products can be done at the developer level using SQL, using Java development tools to service-enable any data source, drag and drop development using Oracle JDeveloper, and by end users using Oracle Business Intelligence tools.

Oracle Gateways and Heterogeneous Services

The core of Oracle's database virtual federation strategy is Heterogeneous Services (HS). Heterogeneous Services provides transparent and generic gateway technology to connect to non-Oracle systems. Heterogeneous Services is an integrated component of the database. Therefore, it can exploit all the capabilities of the Oracle database including PL/SQL and Oracle SQL extensions.

The two methods of connecting to non-Oracle databases through Oracle HS are:

  • Oracle Transparent Gateways are tailored specifically for the non-Oracle systems and provide an end-to-end (Oracle-to-non-Oracle) certified solution.
  • Oracle Generic Connectivity is based on ODBC and OLEDB standards so it allows connectivity to any non-Oracle system that is ODBC or OLEDB compliant. This solution is free and available with the database. It is recommended for non-Oracle systems for which Oracle does not provide a gateway solution.

Both Generic Connectivity and Transparent Gateways are based on Oracle Heterogeneous Services. These solutions are SQL-based federated integration solutions and have existed since Oracle version 8.0. They are some of the best kept secrets that Oracle has to offer. The Oracle Gateway source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

DB2 LUW, mainframe, iSeries, SQL Server, Sybase, or any ODBC data source

Oracle (HS runs in the Oracle database)

SQL, PL/SQL

The most likely instances or use cases when the Oracle Gateways would be the Oracle product or tool selected are:

  • Your IT department has strong SQL skills or the database organization will be running the virtual federation project
  • You would like to support any reporting, BI, reporting or portal tool, web mashup, or custom application to access the data

Oracle Business Intelligence Suite

Oracle has had many business intelligence solutions over the years including: Essbase, Oracle Reports, Business Intelligence Beans, Oracle Personalization, Orac Clickstream, Oracle XML Publisher (now Oracle BI Publisher), and Oracle Discove The database has built-in features such as OLAP/cube support, partitioning, data mining, star schema support, bitmap indexes, materialized views, and more. Oracle's solution had been lacking an end-to-end (database-to-end-user) solution set that provided a dashboard approach to business intelligence.

The acquisition of Siebel provided Oracle with new products to offer an end-to- end solution called the Oracle BI Enterprise Edition (OBIEE). OBIEE includes a full set products from Siebel (Oracle Answers), Oracle BI Publisher, and ETL provided by OWB or ODI. The solution includes the ability to easily build BI dashboards without the need for a portal product. The suite includes Structure Query Reporter (SQR) which was a product offering from Hyperion. SQR has been around since t mid-1980s. Ironically, SQR was first developed by Sybase which is now owned by SAP. The Oracle BI Suite source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

ODBC, JDBC, XML, web services, URLs, COTS Applications: PeopleSoft Enterprise, JD Edwards EnterpriseOne, Oracle E-Business Suite, SAP R/3, and mySAP. OLAP sources including: Oracle Database OLAP Services, Microsoft Analysis Services Cubes, Hyperion Essbase, and SAP BW Infocubes

n/a

SOA/web services, Java, MS Office, SQR development language, eXtensible Business Reporting Language (XBR)

The most likely instances or use cases when Oracle BI Suite would be the Oracle product or tool selected are:

  • The focus is BI read-only data access
  • One dashboard is needed for all business intelligence information

Oracle SOA adapters

The Oracle SOA adapters provide both application and data level integration using standards based JCA adapters. When combined with the Oracle SOA Suite BPEL and Service Bus products, the SOA adapters form the foundation of an Enterprise Information System (EIS). Oracle SOA adapters support enterprise application solutions like Oracle E-Business Suite, Oracle PeopleSoft, Oracle Siebel, Oracle JD Edwards, as well as SAP, Microsoft CRM, Lawson, and other commonly deployed COTS solutions. These adapters are written by Oracle or third-party partners (Attunity and IWay) and are sold and supported by Oracle. Because they are JCA compliant, it is possible to use the adapters to perform two-phase commit across mainframe and Linux, Unix, and Windows (LUW)-based databases.

The adapters are grouped into application, technology, database, and legacy. Technology adapters support common EII technologies such as JMS, FTP, SMTP, SOAP, and REST. The database adapters include support for DB2, Sybase, SQL Server, and less mainstream databases such as MUMPS and Informix. The legacy adapters support both transactional (CICS and IMS-TM) and database (fat F\files, VSAM, IMS) sources.

Oracle SOA adapters are integrated into Oracle BPEL and the Oracle Server Bus (OSB). This allows you to do both process and messaging-based application integration. Using JDeveloper Oracle BPEL and Oracle Service Bus (OSB) modeling tools, you can easily integrate SOA adapters into your BPEL and OSB-based enterprise integration solutions. OSB source and target data sources along with the development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Specific drivers for almost all databases, message brokers, SAP applications, Oracle Applications, and more

n/a

Java, web services

The most likely instances or use cases when the Oracle SOA adapters would be the Oracle product or tool selected are:

  • Virtual federation at the Application Server/middle tier
  • Web services-based information integration strategy

Oracle Web Center and Portal

The first portal product Oracle offered in 1999 was database and PL/SQL-based, and was named Oracle WebDB. You are probably seeing a common theme in this article. This common theme is that all Oracle EII and data migration offerings introduced prior to 2000 were database-based. This is because Oracle was a database company. Oracle even first attempted to offer a Java application server in the Oracle database in 1999 as part of the Oracle Internet Computing Platform.

Oracle Portal provides an environment for creating a portal Web interface, publishing and managing information, accessing dynamic data, and customizing the portal experience, as well as an extensible framework for J2EE-based application access. The Oracle Portal solution is based upon BEA AquaLogic User Interaction (Plumtree) and the original Oracle Portal product. BEA had another portal-based offering called BEA WebLogic Portal. Features of this product are being merged into a combined Oracle Portal and BEA AquaLogic User Interaction.

Oracle WebCenter is both a Portal solution and a Java-based UI framework that can be used to develop a complete Web 2.0, social networking, and information (including content management) management experience. Oracle WebCenter is developed using Java from the ground up (unlike Oracle Portal which was developed using PL/SQL) and is the strategic Portal product from Oracle. The best of Oracle Portal will be merged with Oracle WebCenter to provide one Oracle Portal offering. The Oracle WebCenter source and target data sources along with development languages and tools supported are:

Data source

Data target

 

Development languages and tools

Specific drivers for almost all databases, message brokers, SAP applications, Oracle Applications, and more

n/a

Java, PL/SQL, GUI

The most likely instances or use cases when Oracle WebCenter would be the Oracle product or tool selected are:

  • Presentation tier, browser-based integration
  • Mash-ups, centralization of all applications on one website

Oracle Business Activity Monitoring

Oracle Business Activity Monitoring (BAM) provides the ability to monitor business services and processes throughout the enterprise. Oracle BAM can be easily incorporated into any application or receive information automatically from other SOA components such as Oracle BPEL Process Manager. Oracle BAM empowers end users to react quickly to changing needs, based on application and system feedback, and to take corrective action as required. It allows business users real-time monitoring of business processes, web service interactions, data flows, and database activity.

Oracle BAM is a product that Oracle acquired along with a number of other products and technologies from PeopleSoft in 2005. PeopleSoft acquired the product right before the acquisition of PeopleSoft by Oracle. The product was a complete Microsoft solution, utilizing Microsoft .NET and SQL Server. Of course, one of the first major changes to the product was to port it to Java EE and an Oracle database. The Oracle BAM source and target data sources along with development languages and tools supported are:

Data source

Data target

Development languages and tools

Any data source, web services

Oracle (repository)

Java, web services, GUI

The most likely instances, or use cases when Oracle BAM would be the Oracle product or tool selected are:

  • Real-time dashboards for business users and executive decision makers
  • Gathering real-time data from databases, BPEL processes, and any web service-based source

Virtual federation can be achieved by many means using Oracle products. The great thing is that you have choices, but this can also lead to IT department and end user confusion. You need to know what your business goal is before deciding on what solution is best for you.

Summary

In this article we took a look at database migration products and tools, physical federation products, and virtual federation products.

In the next article we will take a look at some more oracle integration and consolidation products.


Further resources on this subject:


About the Author :


Tom Laszewski

Tom Laszewski has over 20 years of experience in databases, middleware, software development, management, and building strong technical partnerships. He is currently the Cloud Migration Director in Oracle's Server Technology organization. He established the initial business and technical relationships with Oracle's migration SIS and tools partners. His main responsibility is successful completion of migration projects initiated through the Oracle partner ecosystem and Oracle Sales. These migration projects involve mainframe service enablement and integration, mainframe re-host and re-architecture, and Sybase, DB2, SQL Server, Informix database migrations, and migrations to the Oracle Cloud product offerings. Tom works on a daily basis with TCS, Infosys, and niche migration systems integrators, customer technical architectures, CTOs and CIOs, and Oracle account managers to ensure the success of migration projects. Most recently, Tom is spending a significant amount of his time enabling Cloud computing service providers on the Oracle software and hardware stack. This involves architecting future-proof cloud infrastructure solutions utilizing Oracle Exadata, Oracle Exalogic, Oracle Virtual Server, Sun Blade Servers, Oracle Database, Oracle Fusion Middleware, and Oracle Enterprise Linux.

Before Oracle, Tom held technical and project management positions at Sybase and EDS. He has provided strategic and technical advice to several start-up companies in the Database, Blade, XML, and storage areas. Tom holds a Master of Science in Computer Information Systems from Boston University.

Books From Packt


Oracle Warehouse Builder 11g R2: Getting Started 2011
Oracle Warehouse Builder 11g R2: Getting Started 2011

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting
Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting

Oracle Database 11g – Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators

EJB 3.0 Database Persistence with Oracle Fusion Middleware 11g
EJB 3.0 Database Persistence with Oracle Fusion Middleware 11g

Oracle ADF Enterprise Application Development—Made Simple
Oracle ADF Enterprise Application Development
—Made Simple

Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation
Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation

Oracle Enterprise Manager Grid Control 11g R1: Business Service Management
Oracle Enterprise Manager Grid Control 11g R1: Business Service Management

Oracle GoldenGate 11g Implementer's guide
Oracle GoldenGate 11g Implementer's guide


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
e
A
P
x
w
f
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software