How to Develop Analytics Leveraging Data in Multiple Cloud Platforms
Part – 1: Data Model with Sample Analysis
Multi-cloud is a strategy where an organization leverages two or more cloud computing platforms to perform various tasks. A multi-cloud strategy allows stakeholders to pick and choose the specific solutions that work best for their organization. Organizations that do not want to depend on a single cloud vendor may choose to use resources from several different providers to get the best benefits from each unique service. A multi-cloud approach is preferred by organizations to enable flexible distribution of workloads, increase security, improve reliability, and reduce costs.
Apps Associates has a dedicated practice to Data and Analytics helping customers realize their data reporting goals to get the most out of their technology. Recently we came across a requirement where we needed to develop analytics across two different Cloud data warehouses – Amazon Redshift and Oracle Autonomous Data Warehouse (ADW).
To satisfy this requirement we have designed a solution leveraging multiple data modeling concepts, and one of the most important is Conformed Dimensionality. If a dimension is shared by more than one fact table with the same definition then we consider the dimension as conformed.
The Scenario: Sales Channels + Revenue
We have two different cloud environments: Oracle ADW and Amazon Redshift. The customer started selling their product using a new sales channel, and the sales that are processed from this new channel are captured in Amazon Relational Database Service (RDS) The sales detail captured in Amazon RDS contains the end user and the products that the user is buying. These sales are then loaded into Amazon Redshift using AWS Glue ETL (Extract, transform, load).
The customer counts revenue at the product level; revenue from all other sales channels is calculated in an Oracle database, and the sales from this new sales channel are processed in Amazon RDS and loaded into the Oracle database. The revenue of these sales from the new sales channel is calculated along with all other sales, and the revenue data is loaded into Oracle ADW using ODI (Oracle Data Integrator) ETL.
Revenue Reporting Requirements
The customer’s reporting requirement is to create reports for the revenue generated in Oracle ADW and drill into the data to analyze and understand usage and sales processed in Amazon Redshift with respect to the revenue generated using this new sales channel.
To accomplish this, we proposed a data model solution where we leverage the concept of conformed dimensionality between cross-cloud to navigate the reports populated from Oracle ADW to Amazon Redshift using Oracle Cloud Analytics (OAC). To move forward we need to establish a connection from OAC to Amazon Redshift and Oracle ADW which is discussed in the 2nd part of the article.
In the above-described scenario, product-related attributes are conformed for both sales and revenue, but the product attributes are available only in Oracle ADW. We use this dimension to join the facts from Amazon Redshift and Oracle ADW which enables us to create the reports with both revenue from Oracle ADW and Sales data from Amazon Redshift in one report. We can also create a detailed sales report and drill down the data from revenue to understand and analyze sales for a product based on the revenue generated for a particular year, period etc.
The diagram below describes at a high level what we are trying to achieve.
- We have Amazon Redshift and Oracle ADW in the same Virtual Private Cloud (VPC) which means there will be no additional cost involved when generating reports from OBIEE (Oracle Business Intelligence Suite Enterprise Edition).
- The raw sales data processed from the new sales channel is loaded to Amazon Redshift using the AWS Glue ETL Tool.
- Since the sales data from this new sales channel is not at the product level, we calculate the sales aggregate them at the product level and push these sales into the Oracle database. These sales are then loaded to Oracle ADW using the ODI ETL Tool.
- To generate reports from OBIEE we connect to both Amazon Redshift and Oracle ADW and generate reports.
To explain this solution in detail, we have separated this how-to article into three sections. In this publication, we’ll cover the Data Model and include a sample analysis. Below, you will see the other topics and links to those articles as they become available.
- Data Model with Sample Analysis
- Connectivity & Security Setup
- Connectivity with Other Oracle Tools
Data Model:
Creating reports and analytics using multiple on-premises databases is a common practice in data warehousing. With many companies moving to Cloud platforms, creating reports and analytics from multiple cloud instances will become a common practice.
Creating sales and revenue sample data sets is a complex task; to simplify the process we have considered the standard sample sales data model from Oracle data warehouse and split the fact table into two different data sets as described below:
- Sales Order Amount along with Product, Customer, and Time dimensions are created in Oracle ADW.
- Sales Order Quantity along with Customer dimension is created in Amazon Redshift.
We have considered the Product dimension as the conformed dimension between these two datasets and built the data model as below.
OAC Data Model:
Sample Analysis:
The Apps team created the below analysis using both Oracle ADW and Amazon Redshift. Based on the above data model we have created analysis using the below columns.
- Product ID – Value populated from the conformed dimension from Oracle ADW
- Sales Amount ADW – Sales Amount from Oracle ADW
- Ordered Quantity RDS = Ordered Quantity from Amazon Redshift
- Unit Price = Calculated column from both Oracle ADW and Amazon Redshift (Sales Amount / Ordered Quantity)
We also created multiple other analyses demonstrating drill across from ADW to Redshift and vice versa based on the product details.
I hope that this how-to article discussing a multi-cloud approach to developing analytics was useful to you. Subscribe to this blog to stay up-to-date on publications from Apps Associates, including more information on this topic.
If you are looking for a partner to help you get the most out of your data, Apps Associates is a premier enterprise applications partner that can help you achieve your goals. We partner with Oracle Amazon, Salesforce, and more to help you get the most out of your tech stack.
Contact Apps today.