Learn About Automated Loading of a Data Mart for Line-of-business Analytics

Creating a data mart to house and process flat-file data extracts from isolated applications or systems makes it easier for lines-of-business to access and use real-time data and advanced analytics to speed up the generation of actionable insights.

Using Oracle Functions and Oracle Cloud Infrastructure Events to automate the loading of the Oracle Autonomous JSON Database data mart makes the process efficient and repeatable.

Architecture for Automated Loading of a Data Mart for Line-of-Business Analytics

Using a relatively simple architectural pattern, you can load and optimize data from multiple flat-file sources into a centralized database location so line-of-business teams can perform planning and budgeting analytics on specific data sets independently from other teams.

Data exported from a database or application is stored in flat files as comma-separated values (CSV) and uploaded to a bucket in object storage. Event automation detects the new object and initiates an associated function to process the file, load the data into Oracle Autonomous JSON Database using REST services, and then move the file to another bucket for tracking purposes. The logging service provides insight into workflow execution and the database makes the data available for analysis.

The architecture includes the following general components:

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

• Oracle Functions: Oracle Functions is a fully managed, highly scalable, on-demand, functions-as-a-service (FaaS) platform, built on enterprise-grade Oracle Cloud Infrastructure, and powered by the Fn Project open source engine.

  With Oracle Functions, you can deploy your code, call it directly or trigger it in response to events, and get billed only for the resources consumed during the execution.

  Oracle Functions are container-native. Each function is a completely self-contained Docker image that is stored in your OCIR Docker Registry and pulled, deployed and invoked when you invoke your function.

• Oracle Event Services: Oracle Cloud Infrastructure Events is a fully managed service that eliminates the complexity of manually tracking changes across your cloud resources and responding to them in near real-time with functions, notifications, and streaming.

• Oracle Autonomous JSON Database: Oracle Autonomous JSON Database is a cloud document database service that makes it simple to develop JSON-centric applications.

• Oracle Analytics Desktop: This freely available analytics software provide data storytelling with visualization, what-if analysis and collaboration from your desktop.

Description of the illustration automate-loading-data-mart.png

Before You Begin

Before you begin, consider the following security and connectivity options.

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 for authentication.

• Oracle Autonomous JSON Database can control access using a network access control list (ACL). Using an access control list, a specific database 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 JSON Database.

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 JSON Database can be accessed using the public internet. To access Oracle Autonomous JSON Database 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 JSON Database is 1522.

2. Oracle Cloud Infrastructure FastConnect provides an easy way for you to connect your on-premises network to Oracle Autonomous JSON Database 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 JSON Database 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 JSON Database on shared Exadata infrastructure.

   Both the service gateway and private endpoint allow connectivity to Oracle Autonomous JSON Database 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 JSON Database.

About Required Services for Automated Loading of a Data Mart for Line-of-Business Analytics

You should be familiar with provisioning and using the recommended services and technologies. To automate loading a data mart for line-of-business analytics, you must have the following services running on Oracle Cloud Infrastructure:

• Oracle Autonomous JSON Database

• Oracle Cloud Infrastructure Object Storage

• Oracle Functions

• Oracle Cloud Infrastructure Events

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

In addition, it uses the following technologies:

• Oracle Functions: The function itself is written in Python.

  To develop functions locally or on a compute instance in Oracle Cloud Infrastructure, you will also need to install the Fn Project development platform from GitHub.

• Docker: Functions run as Docker containers on Oracle Cloud Infrastructure. This document assumes that you develop functions on a local Linux system that has Docker version 17.10.0 or later installed. For information about installing Docker on Linux, see Oracle Container Runtime for Docker User's Guide.

• Oracle SQL Developer Web: Oracle SQL Developer Web is a browser-based interface of Oracle SQL Developer, offering a subset of the features that are available in the desktop version. Oracle SQL Developer Web provides a development environment and a database administration interface for Oracle Database in Oracle Public Cloud.

  You can also download and install the full Oracle SQL Developer client from the following location by accepting the license agreement and clicking the Download link associated with your operating system:

  Oracle SQL Developer downloads

• Oracle Analytics Desktop: Provides powerful personal data exploration and visualization in a simple, desktop download. Oracle Analytics Desktop is the perfect tool for quick exploration of sample data from multiple sources or for rapid analysis and investigation of your own local data sets.

  Download and install Oracle Analytics Desktop by clicking the Download link from the following location:

  Oracle Analytics Desktop download

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.