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 AI Lakehouse 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.	See Connect to Your Database From Oracle Analytics Cloud or Connect to Your Database in Model Administration Tool.

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.

Topics:

• Connect to Your Database for Data Visualization or Semantic Modeler
• Connect to Your Database in Model Administration Tool

Connect to Your Database for Data Visualization or Semantic Modeler

In Oracle Analytics Cloud, create connection to Oracle AI Database for data visualizations.

1. On your home page, click Create, then click Connection.
   
   
   Description of the illustration uc1-conns.png
   
   
2. Click Oracle Database, and enter the connection details that you recorded earlier, such as host, port, credentials, and so on.
   
   
   Description of the illustration uc1-cconnection.png
   
   
   • 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.
3. Click Save.

Connect to Your Database in Model Administration Tool

In Model Administration Tool installed on a local Windows machine, create connection to Oracle AI Database for data modelling.

1. In Model Administration Tool for Oracle Analytics Cloud, click File, then Open, then In the Cloud to open your semantic model.
2. Use the Open in the Cloud dialog to sign in using connection information for your Oracle Analytics Cloud.
3. In the Physical pane, expand the DBaaS node, right-click the database icon, and click Properties to display the Connection Pool dialog.
4. Use the database details you recorded earlier to specify Call Interface, Data Source Name, User Name, and Password.
   
   
   Description of the illustration uc1-connpool.png
   
   
   • 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))

   • Save the details.

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 AI Lakehouse with a Public IP Address

Configure Oracle Analytics Cloud to connect to Autonomous AI Lakehouse 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 AI Lakehouse with a Public IP Address

• Prerequisites

• Enable Access to Oracle Autonomous AI Lakehouse

• Connect to Oracle Autonomous AI Lakehouse

Typical Workflow to Connect to Oracle Autonomous AI Lakehouse with a Public IP Address

If you’re connecting Oracle Analytics Cloud to Autonomous AI Lakehouse 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 AI Lakehouse	Upload your Autonomous AI Lakehouse Client Credentials file (wallet file) to Oracle Analytics Cloud.	Enable Access to Oracle Autonomous AI Lakehouse
Connect to Autonomous AI Lakehouse	Create and test your connections.	Connect to Oracle Autonomous AI Lakehouse

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 AI Lakehouse	Deploy Autonomous AI Lakehouse. Deploy Autonomous AI Lakehouse on Oracle Cloud Infrastructure. Populate Autonomous AI Lakehouse with data. Set up a database user with permissions to read database tables on Autonomous AI Lakehouse	Host Name Port Number Service Name (Obtain these details from tnsnames.ora in the Autonomous AI Lakehouse Client Credentials file.)

Enable Access to Oracle Autonomous AI Lakehouse

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

1. In Autonomous AI Lakehouse 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 AI Lakehouse.
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 AI Lakehouse.
   4. Select the file and click Open.
   5. Click Update and OK to update the existing wallet file.

Connect to Oracle Autonomous AI Lakehouse

After enabling access to Oracle Autonomous AI Lakehouse, use the connection details you recorded earlier to connect Oracle Analytics Cloud to Autonomous AI Lakehouse. 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 AI Lakehouse for Data Visualization or Semantic Modeler

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

Description of the illustration uc6-connectionadwc-gif.png

Now create a new workbook and dataset to visualize data from your Autonomous AI Lakehouse.

Connect to Autonomous AI Lakehouse for Data Modeler

In Oracle Analytics Cloud Console, create a connection in the usual way. See Connect to Data in an Oracle Cloud Database.

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

Description of the illustration uc6-conn-console-gif.jpg

Specify these values:

• Name and Description: A short name and description to identify this connection in Oracle Analytics Cloud.

• Connect Using: Select Host, Port, and Service Name.

• Host: The host name of the Autonomous AI Lakehouse instance that you obtained from the downloaded tnsnames.ora file. For example, ALKc.example.oraclecloud.com.

• Port: The port number that you obtained from the downloaded tnsnames.ora file. For example, 1522.

• Service Name: The service name that you obtained from the downloaded tnsnames.ora file. For example, ALKc1_high.ALKc.oraclecloud.com.

• Connect as: The name of a user with read access to Autonomous AI Lakehouse. For example, ADMIN.

• Password: The password for the specified database user.

• Enable SSL: Select this option.

In Data Modeler, you can now model data from your Autonomous AI Lakehouse using this connection.

Connect to Autonomous AI Lakehouse in Model Administration Tool

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

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 AI Lakehouse 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 AI Lakehouse.
   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=ALKc.example.oraclecloud.com))(connect_data=(service_name=ALKc1_high.ALKc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=ALKc.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 AI Lakehouse 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 AI Lakehouse.