7 Connect to Databases Deployed on a Public IP Address

You can use Oracle Analytics Cloud to connect to databases with a public IP address so that end users can analyze that data in visualizations, analyses, and pixel-perfect reports.

For example, you might want to analyze data in a database deployed on Oracle Cloud Infrastructure.

Topics:

• Connect to a Database Deployed on Oracle Cloud Infrastructure with a Public IP Address
• Connect to Oracle Autonomous Data Warehouse with a Public IP Address

Connect to a Database Deployed on Oracle Cloud Infrastructure with a Public IP Address

Configure Oracle Analytics Cloud to connect to a database deployed on Oracle Cloud Infrastructure with a public IP address, so that end users can analyze that data in visualizations, analyses, and pixel-perfect reports.

Topics

• Typical Workflow to Connect to a Database Deployed on Oracle Cloud Infrastructure

• Prerequisites

• Record Database Information

• Enable Database Access Through Port 1521

• Connect to Your Database from Oracle Analytics Cloud

Typical Workflow to Connect to a Database Deployed on Oracle Cloud Infrastructure

If you’re connecting to an database deployment on Oracle Cloud Infrastructure for the first time, follow these tasks as a guide.

Task	Description	More Information
Verify the prerequisites	Verify that your environment satisfies the prerequisites required for this configuration.	Prerequisites
Record database information	Record connection information for database.	Record Database Information
Enable database access	Add an ingress rule to give Oracle Analytics Cloud access to the database.	Enable Database Access Through Port 1521
Connect to the database	Create and test your connections.	Connect to Your Database from Oracle Analytics Cloud

Prerequisites

Before you start, make sure you have the required environment.

Step	Description	Important Information to Note
Set up Oracle Analytics Cloud	Deploy Oracle Analytics Cloud.	Region Availability Domain
Set up a Virtual Cloud Network (VCN) on Oracle Cloud Infrastructure	Set up a VCN for the database deployment on Oracle Cloud Infrastructure. Note: The VCN must be in the same Region and Availability Domain as Oracle Analytics Cloud.	Virtual Cloud Network Subnet Same: Region Availability Domain
Deploy a database: Deploy the database on the VCN in Oracle Cloud Infrastructure Populate the database with data Set up a database user with permissions to read database tables	Deploy a database on the VCN in Oracle Cloud Infrastructure. Note: The database must be in the same Region and Availability Domain as the VCN.	Public IP Database Unique Name Host Domain Name Database User/Password Same: Region Availability Domain Virtual Cloud Network Client Subnet

Record Database Information

All the information you need to connect to a database is available in the Oracle Cloud Infrastructure Console. Record the information now, so you have the required details when you set up the connection in Oracle Analytics Cloud.

1. In Oracle Cloud Infrastructure Console, click in the top left corner.
2. Click Databases. Under MySQL, click DB Systems.
3. Locate the database you want to connect to and record the Public IP address.
   
   Description of the illustration uc1_databases.jpg
   
4. Click the name of the database you want to connect to and write down the values in these fields: Database Unique Name, Host Domain Name, Virtual Cloud Network, Client Subnet, and Port.
   
   Description of the illustration uc1_database.jpg
   
5. Find out the user name and password of a database user with permissions to read from this database, and write them down as you need these later. For example, the user SYSTEM.

Enable Database Access Through Port 1521

Add an ingress rule that enables Oracle Analytics Cloud to access the database through port 1521.

1. Make a note of the Oracle Analytics Cloud IP addresses that you want to allow access to.
2. In Oracle Cloud Infrastructure Console, click in the top left corner, and click Databases. Under MySQL, click DB Systems.
3. Click the database that you want to connect to.
4. Click the Virtual Cloud Network link.
   
   Description of the illustration uc1_database_vcn.jpg
   
5. Navigate to the appropriate subnet, and under Security Lists, click Default Security List For <VCN>.
   
   Description of the illustration uc1_vnc.jpg
   
6. Click Add Ingress Rules.
   
   Description of the illustration uc1_securitylist.jpg
   
7. For each IP address that you want to give access to, add an ingress rule to allow any incoming traffic from the public internet to reach port 1521 on this database node, with the following settings:

   • SOURCE CIDR: Enter the IP address that you wrote down in Step 1.

   • IP PROTOCOL: TCP

   • SOURCE PORT RANGE: All

   • DESTINATION PORT RANGE: 1521

   • Allows: TCP traffic for ports: 1521

   
   
   Description of the illustration uc1_ingressrules.jpg
   

Connect to Your Database from Oracle Analytics Cloud

After enabling access to the database, use the database connection information you wrote down earlier to connect Oracle Analytics Cloud to the database. The way you connect to the database depends on what you want to do with the data.

• Visualize the data.

• Model the data using Semantic Modeler, then generate analyses and dashboards.

• Model the data with Oracle Analytics Cloud Model Administration Tool, then generate analyses and dashboards.

• Publish the data in pixel-perfect reports.

Connect to Your Database for Data Visualization or Semantic Modeler

In Oracle Analytics Cloud, create an Oracle Database connection for data visualizations in the usual way. See Create Database Connections.

Description of the illustration uc1_conns.png

Use the database details you recorded earlier to fill in the Create Connection dialog.

Description of the illustration uc1_cconnection.png

Specify these values:

• New Connection Name: A name for the database you want to connect to.

• Host: The Public IP address for the database instance. For example, 123.213.85.123.

• Port: The port number that enables access to the database. For example, 1521.

• Username: The name of a user with read access to the database.

• Password: The password for the specified database user.

• Service Name: A concatenated name comprising Database Unique Name and Host Domain Name, separated with a period. For example, CustDB_iad1vm.sub05031027070.customervcnwith.oraclevcn.com.

Connect to Your Database in Model Administration Tool

In Model Administration Tool for Oracle Analytics Cloud, click File, then Open, then In the Cloud to open your semantic model. See Edit a Semantic Model in the Cloud.

When you sign in, use connection information for your Oracle Analytics Cloud to fill in the Open in the Cloud dialog.

Create a connection pool for your database. In the Physical pane, expand the DBaaS node, right-click the database icon, and click Properties to display the Connection Pool dialog. Use the database details you recorded earlier to specify Call Interface, Data Source Name, User Name, and Password.

Description of the illustration connpool-gif.gif

Specify these values:

• Call interface: Select Default (Oracle Call Interface (OCI)).

• Data Source Name: Specify the connection details. For example:

  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=129.213.85.177)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=CustDB_iad1vm.sub05031027070.customervcnwith.oraclevcn.com))

  For SERVICE_NAME, specify the concatenated Database Unique Name and Host Domain Name separated by a period, for example, db1_phx1tv.mycompany.com. To find both these names in Oracle Cloud Infrastructure Console, click Databases, under MySQL click DB Systems, and then click the name of your database.

Connect to Oracle Autonomous Data Warehouse with a Public IP Address

Configure Oracle Analytics Cloud to connect to Autonomous Data Warehouse over a public IP address so that end users can analyze that data in visualizations, analyses, dashboards, and pixel-perfect reports.

Topics

• Typical Workflow to Connect to Oracle Autonomous Data Warehouse with a Public IP Address

• Prerequisites

• Enable Access to Oracle Autonomous Data Warehouse

• Connect to Oracle Autonomous Data Warehouse

Typical Workflow to Connect to Oracle Autonomous Data Warehouse with a Public IP Address

If you’re connecting Oracle Analytics Cloud to Autonomous Data Warehouse over a public IP address for the first time, follow these tasks as a guide.

Task	Description	More Information
Verify the prerequisites	Verify that your environment satisfies the prerequisites required for this configuration.	Prerequisites
Enable access to Autonomous Data Warehouse	Upload your Autonomous Data Warehouse Client Credentials file (wallet file) to Oracle Analytics Cloud.	Enable Access to Oracle Autonomous Data Warehouse
Connect to Autonomous Data Warehouse	Create and test your connections.	Connect to Oracle Autonomous Data Warehouse

Prerequisites

Before you start, make sure you have the required environment.

Step	Description	Important Information to Note
Set up Oracle Analytics Cloud	Deploy Oracle Analytics Cloud.	Region Availability Domain
Set up Oracle Autonomous Data Warehouse	Deploy Autonomous Data Warehouse. Deploy Autonomous Data Warehouse on Oracle Cloud Infrastructure. Populate Autonomous Data Warehouse with data. Set up a database user with permissions to read database tables on Autonomous Data Warehouse	Host Name Port Number Service Name (Obtain these details from tnsnames.ora in the Autonomous Data Warehouse Client Credentials file.)

Enable Access to Oracle Autonomous Data Warehouse

To enable secure communication between Oracle Analytics Cloud and Autonomous Data Warehouse, you upload trusted SSL certificates to Oracle Analytics Cloud.

1. In Autonomous Data Warehouse Console, obtain the Client Credentials file.
   The Client Credentials file is a ZIP file containing the files cwallet.sso and tnsnames.ora. See Download Client Credentials (Wallets) in Using Oracle Autonomous Data Warehouse.
2. Extract the cwallet.sso file from the Client Credentials file.
3. Upload the cwallet.sso file to Oracle Analytics Cloud.
   1. Sign in to Oracle Analytics Cloud, open the Console and click Database Connections.
   2. Click Upload Wallet to upload a wallet for the first time or Replace Wallet to update an existing wallet.
   3. Click Browse and locate the wallet file (cwallet.sso) you downloaded from Autonomous Data Warehouse.
   4. Select the file and click Open.
   5. Click Update and OK to update the existing wallet file.

Connect to Oracle Autonomous Data Warehouse

After enabling access to Oracle Autonomous Data Warehouse, use the connection details you recorded earlier to connect Oracle Analytics Cloud to Autonomous Data Warehouse. The way you connect depends on what you want to do with the data.

• Visualize the data

• Model the data using Semantic Modeler, then generate analyses and dashboards.

• Model the data with Oracle Analytics Model Administration Tool, then generate analyses and dashboards.

• Publish the data in pixel-perfect reports.

Connect to Autonomous Data Warehouse for Data Visualization or Semantic Modeler

In Oracle Analytics Cloud, create an Autonomous Data Warehouse connection for data visualization. See Create Connections to Oracle Autonomous Data Warehouse.

Description of the illustration uc6_connectionadwc-gif.png

Now create a new workbook and dataset to visualize data from your Autonomous Data Warehouse.

Connect to Autonomous Data Warehouse in Model Administration Tool

You can use Model Administration Tool for Oracle Analytics Cloud to edit a semantic model connected to Autonomous Data Warehouse.

1. On the machine where you installed Oracle Analytics Cloud Client Tools, copy the cwallet.sso, sqlnet.ora, and tnsnames.ora from the zip file that you downloaded from Autonomous Data Warehouse to the folder:
   <Developer Client Tool installation folder>\domains\bi\config\fmwconfig\bienv\core
   
   Description of the illustration uc6_admintool-gif.gif
2. Edit sqlnet.ora so that the wallet location points to:
   <Developer Client Tool installation folder>\domains\bi\config\fmwconfig\bienv\core
   For example:
   WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\ade\admintoolOAC18.2.1\domains\bi\config\fmwconfig\bienv\core"))) SSL_SERVER_DN_MATCH=yes
3. In Model Administration Tool, click File, then Open, then In the Cloud to open your semantic model. See Edit a Semantic Model in the Cloud.
   When you log in, use the connection information for your Oracle Analytics Cloud instance to fill in the Open in the Cloud dialog.

   • For Port, specify 443.

   • For Host name, specify the host domain name of your Oracle Analytics Cloud instance.

   • Select SSL. For Trust Store and Password, point to a local JDK/JRE cacerts keystore that trusts certificates signed by well-known CAs.

4. Connect to Autonomous Data Warehouse.
   1. Click File, then Import Metadata to start the Import Metadata wizard, and follow the on-screen instructions.
      Description of the illustration uc6_imp_md-gif.gif
   2. On the Select Data Source page, for the Data Source Name value, specify a long TNS connection string from the downloaded tnsnames.ora file. Include the entire description, enclosed in brackets.

      For example:

      (description=(address=(protocol=tcps)(port=1522)(host=adwc.example.oraclecloud.com))(connect_data=(service_name=adwc1_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )

   3. For User Name and Password, enter the credentials for the ADMIN user or another suitable Autonomous Data Warehouse user.

You’re now ready to model the data in Model Administration Tool, publish the semantic model to Oracle Analytics Cloud, and create analyses and data visualizations using data from Autonomous Data Warehouse.