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