Deploy an ETL Platform for Oracle Utilities Data on Oracle Cloud and Azure
After running Oracle Utilities Customer Cloud Service applications to automate customer care, billing, and payment operations, Gas South deployed a data extraction, transformation, and loading (ETL) platform on Oracle Cloud Infrastructure (OCI).
Founded in 2006, Atlanta-based Gas South provides natural gas to more than 425,000 residential, commercial, and governmental customers in 14 states, including Florida, New Jersey, Ohio, Texas, and Tennessee. In addition to modernizing its customer care and billing operations in a multicloud deployment on Oracle Cloud and Microsoft Azure, Gas South has recently revitalized its mission to Be A Fuel For Good by giving back 5% of its annual profits to help children in need, prioritizing basic needs, education and health. The company has donated more than $12 million to charities since 2006, including $3.2 million in 2022, its largest year ever for corporate giving.
In its deployment on OCI, Gas South's customer care and billing data is captured from the utility's SaaS applications, held in an Oracle Cloud Infrastructure Object Storage bucket, parsed into JSON files, moved through Oracle SOA Cloud Service, and stored in a generalized data extraction (GDE) database. The utility application data is then structured for further analysis and reporting before it is manually triggered to pass through a secure shell (SSH) tunnel to an Attunity Qlik integration engine which connects to a Microsoft Power BI application running on an SQL Server on Azure.
Highlights of this deployment include:
- Users are authenticated by using an SSH tunnel, bastion host, and public/private keys before connecting to the generalized data extraction (GDE) database in a private virtual cloud network (VCN).
- The GDE service is currently used to take customer care data from the Oracle Utilities Customer Cloud Service SaaS application and pass it through to an Attunity Qlik integration engine, which connects to an Azure BI application for analysis and reporting.
- Archive logs are enabled in the Oracle database on OCI to capture file updates which are detected by Attunity Qlik and then integrated into Microsoft BI on Azure.
Architecture
Gas South has built and deployed Oracle SOA Cloud Service (SOACS) on Oracle Cloud Infrastructure (OCI), allowing the utility company to integrate its Oracle Utilities Customer Cloud Service (CCS) with its self-service portal, interactive voice response (IVR) system, and mobile applications that need to access Gas South’s payment and financial systems.
To analyze the data generated by CCS, Gas South has deployed a generalized data export (GDE) system to extract data from CCS and import it into Microsoft Business Intelligence, which is hosted in Microsoft Azure.
The diagram below shows the data flow for the SOA integration system.
gas-south-data-flow-oracle.zip
The following describes the data flow of the SOA integration:
- A user accesses Gas South's portal.
- Billing data from Oracle Utilities CCS (CCS) is generated and sent to Oracle SOA Suite (SOA).
- SOA sends billing data to the financial system.
- SOA sends the billing to the self service portal for user review.
- The user pays the bill.
- The payment is processed.
- SOA is updated with the payment to show that the bill is paid.
- SOA updates CCS to show that the bill is paid.
The diagram below shows the data flow for the data export system.
gas-south-data-flow-ccs-oracle.zip
The following describes the CCS data flow for the data export and import:
- CCS initiates a generalized data export (GDE) every two hours to export data to object storage.
- A command line interface (CLI) script on the Oracle Base Database Service VM is initiated to copy, split, and load the files into the database.
- Attunity Qlik is configured to read the Oracle Database for log changes.
- If changes have occurred, Attunity Qlik pulls the changes from the Oracle Database
- The data is pushed to Azure SQL.
- The data is access by Microsoft Business Intelligence.
The following diagram illustrates the reference architecture diagram for Gas South.
gas-south-architecture-oracle.zip
The Gas South Portal, IVR, and mobile app are hosted on Microsoft Azure. Microsoft Azure and the Gas South financial systems connect to the OCI virtual cloud network (VCN) by using an internet gateway. SOA services provide the middleware and CCS, Gas South Systems, and third-party financial systems serve as the source and target systems.
An admin server, two managed servers, and a managed file transfer (MFT) server are deployed as part of SOA. Both managed servers are load balanced by an Oracle load balancer. The database is deployed using Oracle Base Database Service.
For the data export, object storage is used to store the files exported from CCS. A script on the Oracle Base Database Service (staging database) is initiated to copy, split, and load the files into the database. Microsoft Attunity is used to read and pull data from the staging database which is then accessed by Microsoft Business Intelligence using an SSH tunnel. Oracle Cloud Infrastructure Vault is used to manage keys.
Oracle has provided the following future state recommendations to Gas South:
- Explore upgrading to SOACS Marketplace. Upgrading to SOACS Marketplace will assist Gas South in reducing IT maintenance and administrative costs.
- Explore cloud native and Platform as a Service (PaaS) options for the transform and loading process of the data from CCS to Azure SQL, taking advantage of Oracle Cloud Infrastructure Functions, Oracle Cloud Infrastructure Events, Oracle Cloud Infrastructure Data Integration, and Oracle Integration Cloud Service.
The following diagram illustrates this future reference architecture.
gas-south-architecture-future-oracle.zip
A new data flow pattern will result from the architectural changes:
gas-south-data-flow-future-oracle.zip
The following describes the CCS data flow for the data export and import:
- CCS initiates a generalized data export (GDE) every two hours to export data to object storage.
- An event in Oracle Cloud Infrastructure Events detects the addition of exported data files in object storage.
- The event invokes a function.
- A function in Oracle Cloud Infrastructure Functions invokes Oracle Cloud Infrastructure Data Integration.
- Oracle Cloud Infrastructure Data Integration stores the processed files in object storage.
- Oracle Cloud Infrastructure Data Integration splits and stores the processed files in object storage.
- An event in Oracle Cloud Infrastructure Events detects the addition of split data files in object storage.
- The event invokes a function.
- A function reads the split files from object storage.
- A function invokes Oracle Integration Cloud Service.
- The data is pushed to Azure SQL and is accessed by Microsoft Business Intelligence.
The architecture has the following components:
- Tenancy
A tenancy is a secure and isolated partition that Oracle sets up within Oracle Cloud when you sign up for Oracle Cloud Infrastructure. You can create, organize, and administer your resources in Oracle Cloud within your tenancy. A tenancy is synonymous with a company or organization. Usually, a company will have a single tenancy and reflect its organizational structure within that tenancy. A single tenancy is usually associated with a single subscription, and a single subscription usually only has one tenancy.
- Region
An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).
- Availability domain
Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.
- Virtual cloud network (VCN) and subnets
A VCN is a customizable, software-defined network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. A VCN can have multiple non-overlapping CIDR blocks that you can change after you create the VCN. You can segment a VCN into subnets, which can be scoped to a region or to an availability domain. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VCN. You can change the size of a subnet after creation. A subnet can be public or private.
- Route table
Virtual route tables contain rules to route traffic from subnets to destinations outside a VCN, typically through gateways.
- Security list
For each subnet, you can create security rules that specify the source, destination, and type of traffic that must be allowed in and out of the subnet.
- Internet gateway
The internet gateway allows traffic between the public subnets in a VCN and the public internet.
- Service gateway
The service gateway provides access from a VCN to other services, such as Oracle Cloud Infrastructure Object Storage. The traffic from the VCN to the Oracle service travels over the Oracle network fabric and never traverses the internet.
- Load balancer
The Oracle Cloud Infrastructure Load Balancing service provides automated traffic distribution from a single entry point to multiple servers in the back end.
- Compute
The Oracle Cloud Infrastructure Compute service enables you to provision and manage compute hosts in the cloud. You can launch compute instances with shapes that meet your resource requirements for CPU, memory, network bandwidth, and storage. After creating a compute instance, you can access it securely, restart it, attach and detach volumes, and terminate it when you no longer need it.
- Object storage
Object storage provides quick access to large amounts of structured and unstructured data of any content type, including database backups, analytic data, and rich content such as images and videos. You can safely and securely store and then retrieve data directly from the internet or from within the cloud platform. You can seamlessly scale storage without experiencing any degradation in performance or service reliability. Use standard storage for "hot" storage that you need to access quickly, immediately, and frequently. Use archive storage for "cold" storage that you retain for long periods of time and seldom or rarely access.
- Vault
Oracle Cloud Infrastructure Vault enables you to centrally manage the encryption keys that protect your data and the secret credentials that you use to secure access to your resources in the cloud. You can use the Vault service to create and manage vaults, keys, and secrets.
- Monitoring
Oracle Cloud Infrastructure Monitoring service actively and passively monitors your cloud resources using metrics to monitor resources and alarms to notify you when these metrics meet alarm-specified triggers.
- Oracle Base Database Service
Oracle Base Database Service is an is an Oracle Cloud Infrastructure (OCI) database service that enables you to build, scale, and manage full-featured Oracle databases on virtual machines. A VM database system uses OCI Block Volumes storage instead of local storage and can run Oracle Real Application Clusters (Oracle RAC) to improve availability.
Get Featured in Built and Deployed
Want to show off what you built on Oracle Cloud Infrastructure? Care to share your lessons learned, best practices, and reference architectures with our global community of cloud architects? Let us help you get started.
- Download the template (PPTX)
Illustrate your own reference architecture by dragging and dropping the icons into the sample wireframe.
- Watch the architecture tutorial
Get step by step instructions on how to create a reference architecture.
- Submit your diagram
Send us an email with your diagram. Our cloud architects will review your diagram and contact you to discuss your architecture.
Acknowledgments
- Authors: Robert Huie, Sasha Banks-Louie
- Contributors: Brad Goodwin, Cory Quinn, Santosh Dixit, Pradyumna Kodgi, Joseph Dominski, Mckenzie Holiday, Robert LiesGas South Team: John Stiles, Jose Figueroa, Smitha Viswambharan, Marshall Sutherland, Arun Valliappan, Nadeem Fatmi, Michael Sallustio