Skip to main content

Data & Intelligence

Consolidating on-premise and public cloud data on Oracle BI Apps

With an increase in the adoption of cloud applications by large corporations, most organizations today are in some form of hybrid state i.e. they are using a combination of both on-premise as well as cloud applications to run their business.

Regardless of where the applications maintain their data, organizations need the ability to see a complete view of the company spanning across different parts of the business, which in this case would be combining insightful data across on-premise as well as public cloud instances.


To take some examples:

  • Combining HR and Financials data to analyze Revenue per employee
  • Combining Sales and Financials data to create customer Scorecards
  • Combining Sales and Order Management data to improve your demand planning system
  • Combining your Sales and Financials Data for Forecasting


In this article, I would like to present multiple design approaches that other organizations have successfully used to consolidate data from multiple cloud and on-premise applications and to perform seamless analytics across these varied data sources.

If you are attending Collaborate15 (#C15LV), please join me to discuss this topic and case studies of what other organizations are doing to address this challenge.

What is Cross Functional analysis?

Cross functional analysis is the ability to gather valuable business insights by efficiently joining both CRM and ERP data that are used to run front-office and back-office applications for an organization.

Shown in Figure 1 below is an example of cross functional analysis used to build a “Quarterly Revenue Forecast Report” for a Services Company that has three Business Units and Runs Oracle Cloud to manage its CRM and Oracle EBS to manage its Financial Processes.


Figure 1

Data Consolidation Options

 There are multiple options you could consider to fulfill cross functional analysis by pulling information from a variety of source systems. Here, I would like to discuss the following two options:

  1. Consolidating all your insightful data on Oracle Business Analytics warehouse and using on-premise OBIEE to perform analysis
  2. Consolidating your data on Oracle Cloud Database schema and using Oracle BI Cloud Service (BICS) to perform analysis

The top half of Figure 2 below shows multiple cloud applications. The top lefts shows various cloud applications where data is generated and hosted. The top right shows the current Oracle cloud analytics platform that can be used to perform reporting.

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

Likewise, the bottom left shows multiple on-premise applications that create data on an on-premise instance. The bottom right shows OBI Application being used to perform reporting.


Figure 2

Option 1 – Consolidating all your insightful data on Oracle Business Analytics warehouse and using on-premise OBIEE to perform analysis

 If you’re using all on-premise applications to run your business, you can use your existing Oracle BI Applications ETL process to extend into non-oracle data sources.

If you’re in a Hybrid state using some cloud applications to run certain parts of your business, you can use cloud ETL (Extract, Transform and Load) tools to move your data onto on-premise staging tables, followed by Oracle’s prebuilt adaptor to push the data onto corresponding Star Schemas.

You might want to choose option 1 to move your public cloud data (Salesforce, NetSuite, Oracle Applications cloud) on to Oracle Business Analytics warehouse (OBAW) if you have already deployed Oracle BI Application and are using its Data Model to consolidate to non-Oracle data sources.

Data Movement Technology

There are number of technologies you can use to move your public cloud data to an on-premise Data Warehouse staging table. Oracle has recently released a number of technologies and approaches that can be used for this purpose. The technology to move data between Cloud and On-prem instances is constantly evolving.

As shown in Figure 3 below, in order to consolidate the data into an on-premise Oracle Business Analytics Warehouse,  we would continue to use Oracle’s pre built adaptor to go on to oracle ERP sources, and use one of the newly released Data Movement technologies to move data from cloud to a common staging table.


 Figure 3

Design Considerations 

  1. Use Staging tables to consolidate data from multiple applications(on-premise /public cloud)
  2. Leverage BI Apps Data Model
  3. Leverage Common dimensions for analysis across applications
  4. Assign Unique Data Source Number for each distinct applications

Option 2 – Consolidating your data on Oracle Cloud Database schema and using Oracle BI Cloud Service (BICS) to perform analysis

If you are considering cloud to build your enterprise analytics platform, there are number of Data Movement technologies that can be used to consolidate your data on Oracle’s cloud based database schema service.

The approach here would be similar to the one discussed in Option 1, the difference being, we will consolidate the data on Oracle Cloud based Data Base schema Service.



 Figure 4

Cloud technology is the next critical evolutionary step in software. It’s a trend that has momentum to become the dominant platform of the future. If you are interested in learning more about why you should consider cloud to build your enterprise analytics platform, you can view my earlier post here  on modernizing your analytics platform on Oracle BI Cloud Service.



Shiv Bharti is the Practice Director of Perficient’s (NASDAQ:PRFT) National Oracle Business Intelligence Practice. Shiv has solid experience building and deploying Oracle Business Intelligence products. He has successfully led the implementation of more than 75 Business Analytics and custom data warehouse projects.

Shiv is a thought leader in the Business Analytics space and a frequent speaker at large conferences like – Oracle Open World, Collaborate, KScope as well as Oracle User Group, sharing his perspectives and thoughts on industry trends,strategies, best practices and case studies.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shiv Bharti

Shiv is the Practice Director of Perficient’s National Oracle Business Intelligence Practice. Shiv has solid experience Building and Deploying Oracle Business Intelligence Products. He has successfully led implementation of over 75+ Oracle Business Intelligence and Custom Data Warehouse Projects. Shiv has worked in multiple industries and with clients that include fortune 500 companies . He has Expertise leading large global teams, as well as in-depth knowledge across multiple verticals and technologies. Prior to 2008, Shiv was a member of the Oracle and Siebel Core Engineering Teams and responsible for the Design and Development of numerous Business Intelligence Applications.

More from this Author

Follow Us