Oracle Cloud Hybrid Data Warehouse Architectural Decisions

The Requirement

Whilst working with a global retailer on an Oracle Fusion implementation I was presented with an interesting challenge of designing an analytics solution for the Procurement team with the objective of being able to report globally on their goods and services not for re-sale spend. There was a solution already in place which was extremely manual and heavily reliant on excel, with the end product being presented in OBIEE 11g.

The driver for a new solution was to automate, reduce cost and align with the strategic analytics tools being implemented as part of the Oracle Fusion programme.

The Strategic Analytics Solution

The analytics landscape being implemented with Oracle Fusion was a combination of OBIA in the Oracle Cloud and Business Intelligence Cloud Service (BICS) extracting data from Oracle Fusion. More detailed data however was not available in the Oracle Cloud and therefore being able to access on premise data from Hadoop and Teradata Data Warehouses was a pre-requisite. The TO BE strategic analytics solution Landscape looked a little like this:

Combining Oracle Cloud data with data on premise required the use of Oracle’s Remote Data Connector (RDC), which essentially leaves data in place on premise and queries it from the Oracle Cloud. This comes with its own challenges which I will likely blog about at a later date but when used properly can be a powerful tool to have in your locker.

The OBIA solution included Procurement and Spend Analytics therefore if all the data required existed in Fusion the requirement would be easy to meet as we would be able to leverage the out of the box pre built data model within OBIA.

The Challenge

This was a global Oracle Fusion implementation split into many phases across multiple countries. Remembering the requirement to report globally we would not be able to do this using the strategic solution until Fusion had been implemented for every country, which could be a number of years. The requirement for the analytics solution was now.

So, we had a situation whereby the UK and Republic of Ireland were undergoing their Fusion Implementation and countries in central Europe and the far east would follow over a number of years. All countries currently operated with their own on-premise Oracle EBS 11i ERP instance.

So how best to integrate procurement data across four on premise Oracle EBS 11i instances with procurement data from one Oracle Fusion instance and model it in such a way that when the user consumes their analytics it appears as though it all comes from the same place?

This was to be a tactical solution that gradually converted into the strategic solution as each country went live with Oracle Fusion.

The Options

I decided at an early stage that due to the tactical nature of the solution moving on premise data to the cloud was not going to be an option. Yes, I could have considered Oracle Data Sync, but I didn’t think this was an appropriate choice for this solution. I also decided that as we were targeting a strategic solution, I wanted to use the out of the box OBIA Procurement and Spend Analytics data models as a target design. This made sense as it meant when a new country went live, we could just switch off the tactical solution and the solution would continue to work via the strategic route.

The first option I considered was making use of the strategic on-premise data warehouse components Teradata and Hadoop. Ingesting the relevant procurement data from each countries EBS 11i instance into Hadoop and Teradata and leveraging the RDC already in place as part of the TO BE strategic solution.

I could then connect to the data from the BICS semantic layer and present it to users via the BICS presentation layer alongside the data being ingested from Oracle Fusion. However, challenges presented here were that Hadoop and Teradata were still deemed ‘strategic’ and would therefore not accept the consumption of any tactical data solutions, as well as the ingestion mechanism choices available here being somewhat limited. So, this route was quickly removed from consideration.

Option two adhered to the same principles as option one, but the technology differed. We had at our disposal an Oracle Exadata machine and licenses for Oracle Data Integrator (ODI). Exadata was not strategic and therefore fit perfectly with the plans that eventually this solution would be replaced by a full Oracle Fusion and OBIA solution.

The plan centred around using the OBIA Procurement and Spend data models as a blue print. We would use ODI to extract data from source om premise EBS 11i environments and load into an Oracle Database housed on the Exadata machine. The data would be modelled in star schemas closely aligned with their OBIA equivalent designs.

Rather than loading the on-premise data into the OBIA Business Analytics Warehouse (BAW) I decided to federate the on-premise models from Exadata with the OBIA models in the BICS Semantic Layer. This prevented the levels of customisation required in the BAW and would allow for a more seamless transition from the tactical to strategic solution. The RDC would require configuration to work against the Oracle Database

The Data Model

The reporting requirement was to be able to report on spend by supplier, time, invoice and country (entity). Therefore, a target star schema was required with a spend fact and dimensions for supplier, time, invoice and entity:

At a dashboard level the vast majority of analytics would be at the time, entity and supplier level therefore I decided to also target an aggregate fact table which removed the invoice detail and would help greatly with performance. However, the ability to drill down to invoice still existed via the above star schema.

Rather than creating a star schema for each EBS 11i instance it made more sense to consolidate the procurement data for each into one star schema that could be split by entity. As a country went live on Fusion we would as discussed earlier stop loading on premise data into the tactical model, however we would retain its historical data so that historical reporting and trending requirements could still be met.

The RDC would allow the BICS semantic layer to both horizontally and vertically federate the out of the box OBIA Procurement and Spend model with the tactical model on Exadata via the RDC. This would then allow the user to interact with all Procurement goods and services not for re-sale data as though it were all being sourced from the same transactional system. Which of course eventually it would be.

Infrastructure and Data Flow

The design of this solution was complex owing to the number of components involved as well as the obvious crossover from the cloud to on premise. The below diagram illustrates the components required in order to make this work, as well as describing the data flow directions.

Conclusion

Migrating to the cloud as many organisations are of course doing is not always as straight forward as just “moving to the cloud” in a big bang approach. It is far more usual that the transition to the cloud will be completed iteratively over a number of years in phases that make both architectural and strategic sense to the business. That does not mean however that solutions cannot be put in place that allow the business to make the most of a cloud and on premise hybrid combination. Bold technology and architectural decisions are required to implement a solution such as that described in this blog and of course the engineering capability and business buy in play a huge role in its success.

For more information on how we can help you transition to a cloud data warehouse or perhaps discuss the contents of this blog in more detail please do get in touch at info@seriosgroup.com. We’re experienced across the Azure, AWS and Oracle Cloud as well as being able to fully test your solution end to end from an Assurance perspective.