Departmental Data Warehousing - an EBS Integration Example

Analysts need an efficient way to consolidate data from multiple financial systems, spreadsheets and other data sources into a trusted, maintainable, and query-optimized source.

With Oracle Autonomous Data Warehouse and Oracle Analytics Cloud, you can load and optimize data from Oracle E-Business Suite and other sources into a centralized data warehouse location for analysis so departments can gain actionable insights.

This reference architecture positions the technology solution within the overall business context:

Lines of business typically don't have timely or efficient access to data and information. Analysts gather the data manually, work with it on an individual basis, and then share copies of files through email or file servers. The data is not centralized, so ensuring the accuracy and the security of the data is difficult. Analysis can take a long time and the results are not easily repeatable.

A data mart is a simple form of a data warehouse that is focused on a single subject or functional area, such as sales, marketing, or finance and are often built and controlled by a single department within an organization. Given their single-subject focus, data marts usually draw data from only a few sources. The sources could include internal systems, a central data warehouse, or external data.

Governed data warehouses and data marts can provide rich information to business users and more effectively deliver the organization's key performance indicators without relying heavily on IT resources and availability.

At a conceptual level, the technology solution addresses the problem as follows:


This architecture uses Oracle Data Integrator to load and optimize data from multiple sources into a centralized Oracle Autonomous Data Warehouse and then uses Oracle Analytics Cloud to analyze the data to provide actionable insights.

Description of analysis-ebs.png follows
Description of the illustration analysis-ebs.png

The architecture focuses on the following logical divisions:

  • Data refinery

    Ingests and refines the data for use in each of the data layers in the architecture. The shape is intended to illustrate the differences in processing costs for storing and refining data at each level and for moving data between them.

  • Data persistence platform (curated information layer)

    Facilitates access and navigation of the data to show the current business view. For relational technologies, data may be logical or physically structured in simple relational, longitudinal, dimensional or OLAP forms. For non-relational data, this layer contains one or more pools of data, either output from an analytical process or data optimized for a specific analytical task.

  • Access and interpretation

    Abstracts the logical business view of the data for the consumers. This abstraction facilitates agile approaches to development, migration to the target architecture, and the provision of a single reporting layer from multiple federated sources.

The architecture has the following components:

  • Data integration

    Oracle Data Integrator is a comprehensive data integration platform that covers all data integration requirements: from high-volume, high-performance batch loads, to event-driven, trickle-feed integration processes, to SOA-enabled data services. You can download Oracle Data Integrator from Oracle Cloud Marketplace.

  • Autonomous Data Warehouse

    Oracle Autonomous Data Warehouse is a self-driving, self-securing, self-repairing database service that is optimized for data warehousing workloads. You do not need to configure or manage any hardware, or install any software. Oracle Cloud Infrastructure handles creating the database, as well as backing up, patching, upgrading, and tuning the database.

  • Analytics

    Oracle Analytics Cloud is a scalable and secure public cloud service that provides a full set of capabilities to explore and perform collaborative analytics for you, your workgroup, and your enterprise.

    With Oracle Analytics Cloud you also get flexible service management capabilities, including fast setup, easy scaling and patching, and automated lifecycle management.


Use the following recommendations as a starting point to load and optimize data from Oracle E-Business Suite and other sources into a centralized data warehouse location for analysis.

Your requirements might differ from the architecture described here.

  • Data Refinery

    With Oracle Data Integrator (OCI), you can create mappings between your data sources and targets to refine and cleanse the data using both both both ETL and E-LT methods.

  • Data Integration to support EBS

    Knowledge modules in Oracle E-Business Suite help you extract data from business applications and integrate it into the data warehouse with regular mappings from an Oracle Database.

  • Oracle Analytics Cloud

    Before you connect Oracle Analytics Cloud to Oracle Autonomous Data Warehouse, have a database administrator whitelist the IP address (or address range) for your Oracle Analytics Cloud instance. The database administrator must add a security rule that allows TCP/IP traffic from Oracle Analytics Cloud to the database.


When loading and optimizing data from Oracle E-Business Suite and other sources into a centralized data warehouse location for analysis, consider the following implementation options.

Guidance Data Refinery Data Persistence Platform Access & Interpretation
Recommended Oracle Data Integrator Oracle Autonomous Data Warehouse Oracle Analytics Cloud
Other Options   Oracle Database Exadata Cloud Service  
Rationale Oracle Data Integrator provides knowledge modules (KM) that provide bidirectional connectivity with all Oracle E-Business Suite modules, including object tables/views and interface tables, so you can extract and load a comprehensive selection of data. Oracle Autonomous Data Warehouse is an easy-to- use, fully autonomous database that scales elastically, delivers fast query performance and requires no database administration. It also offers direct access to the data from object storage via external tables. Oracle Analytics Cloud is a fully managed and tightly integrated with the Curated Data Layer (Oracle Autonomous Data Warehouse).


The Terraform code for this reference architecture is available in GitHub. You can pull the code into Oracle Cloud Infrastructure Resource Manager with a single click, create the stack, and deploy it. Alternatively, you can download the code from GitHub to your computer, customize the code, and deploy the architecture by using the Terraform CLI.

  • Deploy by using Oracle Cloud Infrastructure Resource Manager:
    1. Click Deploy to Oracle Cloud

      If you aren't already signed in, enter the tenancy and user credentials.

    2. Review and accept the terms and conditions.
    3. Select the region where you want to deploy the stack.
    4. Follow the on-screen prompts and instructions to create the stack.
    5. After creating the stack, click Terraform Actions, and select Plan.
    6. Wait for the job to complete, then review the plan.

      To make any changes, return to the Stack Details page, click Edit Stack, and make the required changes. Then, run the Plan action again.

    7. If no further changes are necessary, return to the Stack Details page, click Terraform Actions, and select Apply.
  • Deploy by using the Terraform CLI:
    1. Go to GitHub.
    2. Follow the instructions in the README document.

Explore More

Learn more about related architectures and about the features of this architecture.

Change Log

This log lists only the significant changes: