Getting Started with Autonomous Data Warehouse

1 Getting Started with Autonomous Data Warehouse

Provides an overview of the service and describes how to get started with Autonomous Data Warehouse.

Topics

About Autonomous Data Warehouse

Autonomous Data Warehouse is designed to support all standard SQL and business intelligence (BI) tools and deliver scalable analytic query performance.

Autonomous Data Warehouse provides all of the performance of the market-leading Oracle Database in an environment that is tuned and optimized for data warehouse workloads.

As a service Autonomous Data Warehouse does not require database administration. With Autonomous Data Warehouse you do not need to configure or manage any hardware, or install any software. Autonomous Data Warehouse handles creating the data warehouse, backing up the database, patching and upgrading the database, and growing or shrinking the database.

Additionally, Autonomous Data Warehouse does not require any tuning. Autonomous Data Warehouse is designed as a ”'load and go” service: you start the service, define tables, load data, and then run queries. When you use Autonomous Data Warehouse, no tuning is necessary. You do not need to consider any details about parallelism, partitioning, indexing, or compression. The service automatically configures the database for high-performance queries.

Autonomous Data Warehouse is built upon the Oracle Database, so that all applications and tools that support that support Oracle Database also support Autonomous Data Warehouse. These tools and applications connect to Autonomous Data Warehouse using standard SQL*Net connections. The tools and applications can either be in your data center or in a public cloud. Oracle Analytics Cloud and other Oracle Cloud services are preconfigured for Autonomous Data Warehouse.

Autonomous Data Warehouse is a completely elastic service. When you get started with Autonomous Data Warehouse simply specify the number of OCPUs and the storage capacity in TB's for thedata warehouse. At any time, you can scale, increase or decrease, either the OCPUs or the storage capacity. When you make resource changes for your Autonomous Data Warehouse, the data warehouse resources automatically shrink or grow, without requiring any downtime or service interruptions.

Autonomous Data Warehouse includes a cloud-based service console for managing the service (for tasks such as creating or scaling the service), and monitoring the service (for tasks such as viewing the recent levels of activity on the data warehouse). Autonomous Data Warehouse also includes a cloud-based notebook application which provides simple querying, data-visualization, and collaboration capabilities. The notebook is designed to be used alongside other business intelligence applications.

You can use Autonomous Data Warehouse with Oracle Data Visualization Desktop to easily create visualizations and projects that reveal trends in your company’s data and help you answer questions and discover important insights about your business.

The following figure shows the Autonomous Data Warehouse architecture.

Description of the illustration adwc_ap_001.eps

Before You Begin with Autonomous Data Warehouse

Before you begin using Oracle Autonomous Data Warehouse, you should be familiar with Oracle Cloud.

See Getting Started with Oracle Cloud.

Before you create an Autonomous Data Warehouse instance:

On Oracle Cloud, sign up for a free trial subscription or purchase a subscription. You cannot create an Autonomous Data Warehouse deployment until you do so.
(Optional) if you want to leverage an object store for data loading you need your object store credentials to use with Oracle Autonomous Data Warehouse, including a username and a password. For details on the required credentials, depending on the object store you want to use, see the following:
- Oracle Cloud Infrastructure Object Storage, the username is your Oracle Cloud Infrastructure user name. The password is your auth token. See Working with Auth Tokens.
- Oracle Cloud Infrastructure Object Storage Classic, the username is your Oracle Cloud Infrastructure Classic user name and the password is your Oracle Cloud Infrastructure Classic password.
- Amazon S3, the username is your AWS access key ID and the password is your AWS secret access key. See AWS Identity and Access Management.
- Azure Blob Storage, the username is your Azure storage account name and the password is an Azure storage account access key. See About Azure storage accounts.
(Optional) Create a bucket for cloud storage manual backups:

If you want to manually back up Autonomous Data Warehouse to cloud storage you must associate an instance with a cloud storage backup location. For information on defining your Oracle Cloud Infrastructure Object Storage and creating the bucket for manual backups, see Manual Backups on Autonomous Data Warehouse.

After you create an Autonomous Data Warehouse instance:

(Optional) Reset the administrator password:

When you create an Autonomous Data Warehouse instance you are required to set the administrator password. If you want to change the administrator password or if you need to unlock the administrator account, see Managing the Administrator Account on Autonomous Data Warehouse.
(Optional) Add Oracle Machine Learning users:

For information on adding Oracle Machine Learning users see: Creating User Accounts for Oracle Machine Learning.

For information on accessing Oracle Machine Learning see Accessing Oracle Machine Learning User Management Page.
(Optional) Download Oracle Data Visualization Desktop and add a connection to Autonomous Data Warehouse. See Working with Analytics and Visualization.

Key Features of Autonomous Data Warehouse

Describes key features of Autonomous Data Warehouse, an affordable, feature-rich service in the cloud.

Key Features

Managed: Oracle simplifies end-to-end management of the data warehouse:
- Provisioning new databases
- Growing or shrinking storage and compute resources
- Patching and upgrades
- Backup and recovery
Fully Tuned: “Load and go”:
- Define tables, load data, run queries
- Provides good performance out of the box
- Run your queries using any business analytics tool or cloud service
- Built-in SQL worksheet and notebook also included
Fully Elastic Scaling: Scale compute and storage independently to fit your data warehouse workload with no downtime:
- Size the Autonomous Data Warehouse to the exact compute and storage required
- Scale the Autonomous Data Warehouse on demand: Independently scale compute or storage
- Shut off idle compute to save money
Autonomous Data Warehouse Supports:
- Existing applications, running in the cloud or on-premises
- Connectivity via SQL*Net, JDBC, ODBC
- Third-party data-integration tools
- Oracle cloud services: Analytics Cloud Service, Golden Gate Cloud Service, Integration Cloud Service, and others
High-performance queries and concurrent workloads: Optimized query performance with pre-configured resource profiles for different types of users.
Oracle SQL : Autonomous Data Warehouse is compatible with existing applications that support Oracle Database.
Built-in web-based data analysis tool: Web-based notebook tool for designing and sharing SQL based data-driven, interactive documents.
Database migration utility: Easily migrate from Amazon AWS Redshift, SQL Server, and other databases.

Simple Cloud-based Data Loading

Autonomous Data Warehouse provides:

Fast, scalable data-loading from Oracle Object Store, Azure Blob Storage, AWS S3, or on-premises data sources.

SQL Developer Autonomous Data Warehouse Support

Using Autonomous Data Warehouse with SQL Developer you can do the following:

Connect to Autonomous Data Warehouse
Create tables in Autonomous Data Warehouse
Load data into Autonomous Data Warehouse
Copy tables to Autonomous Data Warehouse
Transfer a schema to Autonomous Data Warehouse

Business Intelligence Tools Support

Autonomous Data Warehouse is compatible with a number of business intelligence and data visualization tools from Oracle and from trusted third parties.

Oracle Analytics Cloud
Oracle Data Visualization Desktop
Third-party Business Intelligence tools

Typical Workflow for Using Autonomous Data Warehouse

Describes the steps to start using Autonomous Data Warehouse.

Task	Description	More Information
Create and log in to your cloud account	Provide your information and sign up for an Oracle Cloud Service.	Getting Started with Oracle Cloud
Provision Autonomous Data Warehouse	Create an Autonomous Data Warehouse instance.	Quickstart Tutorial: Provisioning Autonomous Data Warehouse
Start the data warehouse	Start and verify that an Autonomous Data Warehouse instance is up and running.	Starting and Stopping Autonomous Data Warehouse
Create database users and obtain security credentials	Create accounts for your users and assign them appropriate privileges. Assign the necessary Autonomous Data Warehouse roles.	Creating Users with Autonomous Data Warehouse
Connect to your data warehouse using SQL Developer	Download SQL Developer or another database client tool. Obtain security credentials and connect to your Autonomous Data Warehouse.	Connecting to Autonomous Data Warehouse Using Oracle Database Tools
Connect to your data warehouse using a database client	Download a database client. Obtain security credentials and connect to your Autonomous Data Warehouse instance.	Connecting to Autonomous Data Warehouse
Scale the data warehouse	Use the Service Console to scale an instance to add or remove CPU or storage resources.	Starting, Stopping, and Scaling Autonomous Data Warehouse
Load data into the data warehouse	Use the available tools to load data into the data warehouse.	Loading Data with Autonomous Data Warehouse
Migrate data from Amazon Redshift	Migrate data from an existing Amazon Redshift data warehouse.	Migrating Amazon Redshift to Autonomous Data Warehouse
Monitor the data warehouse	Check on the health and performance of the data warehouse.	Managing and Monitoring Performance of Autonomous Data Warehouse
Manage the data warehouse	Check on the day-to-day operation of your service, monitor performance, and review important notifications.	All aspects of the database lifecycle are simplified by the service, including patching and upgrading the database. You can obtain service information including the service and database version from the service console. Backing Up and Restoring Autonomous Data Warehouse

Using Oracle Machine Learning with Autonomous Data Warehouse

Provides steps to access and create Oracle Machine Learning users. Oracle Machine Learning provides a notebook style application designed for advanced SQL users and provides interactive data analysis that lets you develop, document, share, and automate reports based on sophisticated analytics and data models.

Key features of Oracle Machine Learning:

Allows collaboration among data scientists, developers, business users
Leverages the scalability and performance of Oracle Platform and its Cloud Services

To use Oracle Machine Learning with Autonomous Data Warehouse you need to create users and access the system:

OML User Management lets the Admin (user with administrative privileges) create and modify Oracle Machine Learning user accounts.
OML Application – application users access Oracle Machine Learning to create, view, and share notebooks for data analytics, data visualization and other Oracle Machine Learning tasks.

To use Oracle Machine Learning, from Autonomous Data Warehouse Service console:

Click the Administration tab.
Select Manage Oracle ML Users.

This provides access to OML User Management. Access the User Management page as Admin and create and manage Oracle Machine Learning user accounts.

After you create user accounts, you or the user’s that you provide credentials to can use the user credentials and password to access and work with Oracle Machine Learning.

Building Reports and Dashboards with Analytics in Autonomous Data Warehouse

Working with Oracle Analytics Cloud

You can use Oracle Analytics Cloud with Autonomous Data Warehouse. Use Oracle Analytics Cloud to select interactive visualizations and automatically create advanced calculations to reveal the insights in your data.

For more information, see Using Oracle Analytics Cloud with Autonomous Data Warehouse.

Working with Oracle Data Visualization Desktop

You can use Oracle Data Visualization Desktop with Autonomous Data Warehouse. Just connect to Autonomous Data Warehouse, select the elements that you’re interested in, and let Data Visualization Desktop find the best way to visualize it. Choose from a variety of visualizations to look at data in a specific way.

For more information see Working with Data Visualization Desktop in Autonomous Data Warehouse.

Working with the Sample Data Sets in Autonomous Data Warehouse

For users who want to start using the service without creating their own tables, Autonomous Data Warehouse provides the read-only Sales History and Star Schema Benchmark data sets.

These data sets are provided as Oracle Database schemas SH and SSB respectively. Any user can query these data sets without any manual configuration.

Note:

Both SH and SSB are provided as schema-only users, so you cannot unlock those users or set a password.

Sales History (SH) Schema

The SH schema provides a small data set you can use to run the sample queries in the Oracle Database Data Warehousing Guide. Note that you need to prefix the table names with the schema name SH in your queries. For example, the following query shows you how the SQL function RANK() works:

SELECT channel_desc, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
   RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
   RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
FROM sh.sales, sh.products, sh.customers, sh.times, sh.channels, sh.countries
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
  AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-09', '2000-10')
  AND country_iso_code='US'
GROUP BY channel_desc;

For more information on the SH schema see Sample Schemas and Schema Diagrams.

Star Schema Benchmark (SSB) Schema

The SSB schema provides a well-known large sample data set. The SSB schema in the Autonomous Data Warehouse contains 1 TB of data. You can use this schema to test the performance of your service. You can run the sample queries on this schema with different database services, HIGH, MEDIUM, LOW and with different number of OCPUs to test the performance of different Autonomous Data Warehouse configurations.

The SSB schema contains the tables: lineorder, customer, supplier, part, and dwdate. See Sample Star Schema Benchmark (SSB) Queries and Analytic Views for a list of sample queries you can use against the SSB schema. Note that you need to prefix the table names with the schema name SSB in your queries.

For more information on database services, see Predefined Database Service Names for Autonomous Data Warehouse.

Securing Autonomous Data Warehouse

Autonomous Data Warehouse stores all data in encrypted format in the Oracle Database. Only authenticated users and applications can access the data when they connect to the database.

All connections to Autonomous Data Warehouse use certificate based authentication and Secure Sockets Layer (SSL). This ensures that there is no unauthorized access to the Autonomous Data Warehouse and that communications between the client and server are fully encrypted and cannot be intercepted or altered.

Certificate based authentication uses an encrypted key stored in a wallet on both the client (where the application is running) and the server (where your database service on the Autonomous Data Warehouse is running). The key on the client must match the key on the server to make a connection. A wallet contains a collection of files, including the key and other information needed to connect to your database service in the Autonomous Data Warehouse. For more information on connections to Autonomous Data Warehouse see About Connecting to Autonomous Data Warehouse.

You do not need to do any manual configuration to encrypt your data and the connections to your database. These are implemented by Autonomous Data Warehouse.

Autonomous Data Warehouse uses strong password complexity rules for all users based on Oracle Cloud security standards. For more information on the password complexity rules see Creating Users with Autonomous Data Warehouse.

Provisioning Autonomous Data Warehouse

Shows you the steps to provision a new Autonomous Data Warehouse instance using the Oracle Cloud Infrastructure Console.

Sign in to your Oracle Cloud Account at cloud.oracle.com.
From the Oracle Cloud Infrastructure page click in the top corner of the page and then click Autonomous Data Warehouse from the left navigation pane, and select a Compartment.

Choose your Compartment.
Click Create Autonomous Data Warehouse.
In the Create Autonomous Data Warehouse dialog, enter the following:
- Display Name: A user-friendly description or other information that helps you easily identify the resource. The display name does not have to be unique, and you can change it whenever you like.
- Database Name: The database name must consist of letters and numbers only. The maximum length is 14 characters.
  
  Note:
  
  Database names associated with a database terminated within the last 60 days cannot be used when creating a new Autonomous Data Warehouse database.
- CPU Core Count: Specify the number of cores for your Autonomous Data Warehouse database.
- Storage: Specify the storage you wish to make available to your Autonomous Data Warehouse database, in terabytes.
- Administrator Credentials: Set the password for the Autonomous Data Warehouse Admin user by entering a password that meets the following criteria:
  - Between 12 and 60 characters long
  - Contains at least one lowercase letter
  - Contains at least one uppercase letter
  - Contains at least one number
  - Does not contain the double quotation mark (")
  - Does not contain the string "admin"
- License Type: The type of license you want to use for the Autonomous Data Warehouse.
  - My organization already owns Oracle database software licenses. Bring my existing database software licenses to the database cloud service (details).
  - Subscribe to new database software licenses and the database cloud service.
If you want to use Tags, enter the TAG KEY and VALUE. Tagging is a metadata system that allows you to organize and track resources within your tenancy. Tags are composed of keys and values which can be attached to resources.
Click Create Autonomous Data Warehouse.

Your display shows Provisioning until the new Autonomous Data Warehouse database is available.

Quickstart Tutorials

Provides links to the Autonomous Data Warehouse quickstart tutorials.

Quickstart Tutorial: Provisioning Autonomous Data Warehouse

This tutorial shows you how to log in to the Autonomous Data Warehouse console, create an instance, and create a user.

Tutorial icon Quickstart Tutorial – Provisioning Autonomous Data Warehouse

Quickstart Tutorial: Connecting SQL Developer to Autonomous Data Warehouse

This tutorial shows you how to connect SQL Developer to a database in Autonomous Data Warehouse.

Tutorial icon Quickstart Tutorial – Connecting SQL Developer and Creating Tables

Quickstart Tutorial: Loading Your Data

This tutorial shows you how to load data from an Oracle Object Store into a database in Autonomous Data Warehouse.

Tutorial icon Quickstart Tutorial – Loading Your Data

Quickstart Tutorial: Running a Query on Sample Data

This tutorial shows you how to use SQL Developer to query tables in an Autonomous Data Warehouse database.

Tutorial icon Quickstart Tutorial – Running a Query on Sample Data

Quickstart Tutorials for Oracle Machine Learning with Autonomous Data Warehouse

Thse tutorials show you how to use Oracle Machine Learning with Autonomous Data Warehouse.

Tutorial icon Quickstart Tutorial –Creating Projects and Workspaces in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Creating and Running Notebooks in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Collaborating in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Creating a SQL Script in Oracle Machine Learning

Tutorial icon Quickstart Tutorial – Running SQL Statements in Oracle Machine Learning