Search

How to Create an Oracle Autonomous Data Warehouse Cloud Service with OAC Integration

This blog outlines how to configure an Oracle Autonomous Data Warehouse (ADW) service, create and load data tables, and connect the database to the Data Visualization service that comes as a part of Oracle Analytics Cloud (OAC).


About Oracle Autonomous Database and Related Solutions

Oracle’s launch of the world’s first Autonomous Database and related cloud services pioneers a next-generation data management platform. Oracle Autonomous Database Cloud uses machine learning to enable automation that eliminates human labor, human error, and manual tuning, to enable unprecedented availability, high performance, and security at a much lower cost over traditional data warehouses.

Key points to note about ADW are:

  • Provides a fully autonomous database that scales elastically

  • Self-tunes and optimizes for data warehouse workloads

  • Requires no database administration (always runs the latest security patches)

  • Based on Oracle Database 18c, Release 1, Enterprise Edition

  • Uses artificial intelligence (AI) to deliver high performance, and highly elastic, data management

  • Upgrades itself while the system is running

  • Optimizes and pre-configures to create the data warehouse cloud service quickly

  • Easily migrates on-premises data warehouses or data marts to ADW

  • Supports all standard SQL and business intelligence (BI) tools and delivers great query performance

How to Provision ADW

This requires a cloud compartment to be created prior to provisioning the ADW instance.

1. Provide a display and a database name.

2. Choose the workload type (in this example, “Datawarehouse”).

3. Choose deployment type (in this example, “Serverless”); if needed, choose a dedicated infrastructure that gets you Exadata with a different pricing option.

Figure 1: Visual representation of how to provision your ADW.


4. Configure the database (provide the CPU count and storage capacity) and choose “Auto Scaling” to support heavier workloads. This option lets the system to expand three times more than the provisioned number of CPUs depending on the workload on the database.


Important note on the “Always Free” option: This lets you create a limited number of “Always Free” Autonomous Databases that do not consume cloud credits. This supports both the “Data Warehouse” and “Transaction Processing” workload types. There are some restrictions, but remember, it’s FREE!


5. Provide the “ADMIN” user password with appropriate license type and, in less than ten minutes, you will create an ADW service.

Figure 2: Another visual representation of how to provision your ADW.


6. Once the service is created, download the client credentials zip file from the “DB Connection” tab and provide the password while downloading the file. This will be used to access the ADW from any client like SQL Developer (Version 18.2 or later, preferred is 19.1 or later), DV, OBIEE, etc.

Figure 3: Visual representation of the “DB Connection” tab location.


Other options like “Service Console”, “Scaling”, “Stopping ADW”, etc. are also available on the “Service” page.


7. Connect to ADW using SQL Developer and create a user with grant to “dwrole” (in some cases, you will need to grant the user tablespace access as well to do data loads).

  • Change “Connection Type” to “Cloud Wallet” and browse the downloaded “DB Connection” zip file with the required service names (high, low, medium)

  • Test and save the connection