Learn About Creating a Data Mart for Line-of-Business Analytics

The complexities of data warehouses and the need for IT involvement make it difficult for lines-of-business to access and use real-time data and advanced analytics to speed up the generation of actionable insights.

The typical solution to get to the needed answer gets data extracts out of isolated applications or systems, brings those data sets into a spreadsheet, then manually blends and analyzes the data to create the desired report or analysis.

This process is labor intensive, complex, and prone to human error, which raises questions about data accuracy and data security.

Refer to this LOB Analytics with Autonomous Data Warehouse architecture to understand the components and complexity in implementing the solution: Description of lob-analytics-adw.png follows
Description of the illustration lob-analytics-adw.png

Architecture for Creating 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 data warehouse location so line-of-business teams can perform planning and budgeting analytics on specific data sets independently from other teams.

The architecture includes the following general components:

Oracle Cloud Infrastructure: Data is staged and warehoused using services provisioned in Oracle Cloud Infrastructure.
Oracle Autonomous Data Warehouse: Standard scripts provided with Oracle SQL Developer copy the data from temporary storage into a pre-defined schema in the data warehouse. The data warehouse provides scalable, dynamic data warehousing to support modern analysis and data management.
Oracle Analytics Desktop: This freely available analytics software provide data storytelling with visualization, what-if analysis and collaboration from your desktop.

Description of architecture-flat-file-analytics.png follows

Description of the illustration architecture-flat-file-analytics.png

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.
Communication between temporary object storage and the data warehouse uses an Auth token and user name and password for authentication.
SQL access uses user name and password and Oracle Wallet credentials for authentication.
Data transfer from a local machine hosting the Oracle Analytics Desktop uses an SSL-encrypted connection.
You can optionally use network access control list (ACL) rules to allow access to certain hosts or to a range of hosts based on IP address.

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

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

Oracle Autonomous Data Warehouse
Oracle Cloud Infrastructure Object Storage

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 SQL Developer Web: Use Oracle SQL Developer Web to connect to Oracle Autonomous Data Warehouse using an Oracle Wallet. 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:

https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
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:

https://www.oracle.com/middleware/technologies/oracle-data-visualization-desktop.html

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 create a wallet with client access credentials for Oracle Autonomous Data Warehouse, you must have an administrator account for Oracle Autonomous Data Warehouse.