1. Create a data mart from multiple on-premises Oracle databases for continuous analytics
  2. Learn About Creating a Data Mart from Multiple Databases for Continuous Analytics

Learn About Creating a Data Mart from Multiple Databases for Continuous Analytics

Your organization uses data heavily for driving business decisions across functions, but your data is distributed across multiple systems and applications both on- and off-premises. Aggregating and managing your data to gather business insight is a manual and resource-intensive process that impedes innovation.

A centralized, scalable, and secure data warehouse solution that includes easy data migration, data management, and continuous data analytics provides the agility in gaining business insights that you are looking for.

Architecture for Creating a Data Mart from Multiple Databases for Continuous Analytics

Using a relatively simple architectural pattern, you can load and optimize data from multiple database sources into a centralized data warehouse location so you can perform continuous data analysis to gain business insights.

The architecture includes the following general components:

  • Oracle Cloud Infrastructure: Data is transformed and warehoused using services provisioned in Oracle Cloud Infrastructure.

  • Oracle GoldenGate: Transforms the data from one or more Oracle databases and stores the resulting data in Oracle Autonomous Data Warehouse. The data warehouse provides secure, scalable, dynamic data warehousing to support modern analysis and data management.

  • Oracle Analytics Cloud: Explore data through interactive visualizations, load and model data, and analyze data by creating reports and dashboards.

Description of architecture-database-analytics.png follows
Description of the illustration architecture-database-analytics.png

Before You Begin

To capture data from one or more on-premises databases, you must meet the following prerequisites:

  • You must have an on-premises Oracle Database version Oracle 11g, Oracle 12c, Oracle 18c or Oracle 19c.

  • Prepare your database to be accessed by Oracle GoldenGate. This solution provides the necessary basic steps.

  • Identify a range of IP addresses that are permitted to access Oracle Autonomous Data Warehouse.

Data Security

To make sure that data is secure when it moves between systems, the architecture uses the following strategies.

  • Data transfer from a customer data center into Oracle Cloud uses a VPN or Oracle Cloud Infrastructure FastConnect.

  • SQL access uses user name and password and Oracle Wallet credentials for authentication.

  • Oracle Autonomous Data Warehouse can control access using a network access control list (ACL). Using an access control list, a specific data warehouse only accepts connections from addresses specified in the access control list and rejects all other client connections. By default, when there are no network access control lists specified, the database is accessible from any IP address.

Database Connectivity

There are three different ways to connect to Oracle Autonomous Data Warehouse.

  1. Most organizations protect networks and devices on a network using a firewall. A firewall controls incoming and outgoing network traffic using rules which allow the use of certain ports and access to certain computers (or, more specifically IP addresses or host names). A firewall separates internal networks and the public internet.

    Oracle Autonomous Data Warehouse can be accessed using the public internet. To access Oracle Autonomous Data Warehouse from behind a firewall, the firewall must permit the use of the port specified in the database connection when connecting to the servers in the connection. The default port number for Oracle Autonomous Data Warehouse is 1522.

  2. Oracle Cloud Infrastructure FastConnect provides an easy way for you to connect your on-premises network to Oracle Autonomous Data Warehouse using FastConnect Public Peering. Oracle Cloud Infrastructure FastConnect provides higher-bandwidth options, and a more reliable and consistent networking experience than internet-based connections. Without Oracle Cloud Infrastructure FastConnect, the traffic destined for public IP addresses is routed over the internet.

  3. Oracle Autonomous Data Warehouse supports private access from Oracle Cloud Infrastructure resources in a virtual cloud network (VCN) using one of two methods:

    • Private endpoint

    • Service gateway

    With either private access option, the traffic stays within the Oracle Cloud Infrastructure network and does not traverse the internet. However, if you use a service gateway, requests to the service use a public endpoint for the service.

    If you do not want to use a public endpoint, you can use a private endpoint in your VCN. A private endpoint is represented as a private IP address within a subnet in your VCN. Private endpoint is available only with Oracle Autonomous Data Warehouse on shared Exadata infrastructure.

    Both the service gateway and private endpoint allow connectivity to Oracle Autonomous Data Warehouse from private IP addresses in private subnets without requiring an internet gateway in your VCN. After you configure a service gateway or private endpoint, there are no special steps required to connect to Oracle Autonomous Data Warehouse.

About Required Services for Creating a Data Mart from Multiple Databases for Continuous Analytics

You should be familiar with provisioning and using the recommended services and technologies. In an Oracle Cloud environment, creating a data mart from one or more databases for continuous analytics uses the following services and features:

Data is captured from one or more on-premises Oracle Databases using Oracle GoldenGate from Oracle Cloud Marketplace and stored in Oracle Autonomous Data Warehouse. Oracle Analytics Cloud provides data analytics visualization.

To create a data mart from one or more databases for continuous analytics, you must have the following services running on Oracle Cloud Infrastructure:

  • Oracle GoldenGate microservice from Oracle Cloud Marketplace

  • Oracle Autonomous Data Warehouse

  • Oracle Analytics Cloud

See Learn how to get Oracle Cloud services for Oracle Solutions to get the cloud services you need.

In addition, you will use the following technologies:

  • SQL*Plus: Use the command line interface to connect to Oracle Autonomous Data Warehouse using an Oracle Wallet.

  • Remote Data Gateway: Download and install the remote data gateway to allow Oracle Analytics Cloud to access data stored in a private subnet. Instructions and a link for downloading the installer are provided under the Download Code section. Instructions for installing the gateway are provided in later sections.

About Roles and Privileges

To integrate the services needed to load and analyze data, you need certain user roles and privileges. The steps for creating the roles and privileges are provided in later sections.

To download a wallet with client access credentials for Oracle Autonomous Data Warehouse, you must have an administrator account for Oracle Autonomous Data Warehouse.