Connectivity & Security Setup in a Multi-Cloud Environment
Part 2 of 3 in the series How to Develop Analytics Leveraging Data in Multiple Cloud Platforms
In part 1 of this series about how to develop analytics leveraging data in multiple Cloud platforms I showed how to use Oracle Analytics Cloud (OAC) to connect to Amazon Redshift and Oracle Autonomous Data Warehouse (ADW) to create an analysis using fact tables from both Cloud platforms. If you missed it, you can check out part 1 here.
In this blog post, I will describe a very important step and a one-time task that is required to use OAC to create reports leveraging data in both Oracle ADW and Amazon Redshift. In this post, we detail the steps to establish a connection to Amazon Redshift from OAC and to set up security for accessing data in Amazon Redshift.
To establish the connectivity from Amazon Redshift to Oracle ADW we need to create a private accessible Amazon Elastic Compute Cloud (EC2) instance to the OAC Server. B
How to establish connectivity from Amazon Redshift to Oracle ADW:
1. Create Security groups to allow the Amazon Redshift Inbound rules and specify the VPN Host details to restrict public access.
a.(Navigation: Properties -> Network & Security Settings -> VPC Security Group -> Edit Inbound Rules)
2. After entering the VPN Details in the Security Inbound Rules, you should be able to access the Redshift database after connecting to the VPN in a local machine.
Note: The redshift Cluster you are trying to access should be in the same Security Group.
Steps to set up Oracle Analytics Cloud:
Once setup in Amazon Redshift is complete, we need to create connections in the OAC Repository (RPD) (i.e., semantic model/layer) with Amazon Redshift & Oracle ADW.
We can create the System DSN ODBC Connection which helps us to import metadata and create the Data Model. After setting up a connection for Amazon Redshift or Oracle ADW we need to load Java Data Sources.
Below I will describe how to load Java Data Sources to complete the OAC setup:
1. Set up the AWS Connection:
1. Download & Install Amazon Redshift JDBC driver.
2. Once drivers are installed, open ODBC 64-bit connection.
(Navigation: Control Panel->System & Security->Administrative Tool->System DSN->Add)
3. Provide the required details to create the connection.
4. Select the configured DSN and we can start creating the Data Model.
2. Set up the ADW Connection:
1. Open ODBC 64-bit connection and select DataDirect 8.0 Oracle Wire Protocol from the list of available drivers as shown in the below screenshot.
(Navigation: Control Panel->System & Security->Administrative Tool)
2. Fill in the required details in General and Security Tabs.
Below are the details you will be required to fill in the details.
• Encryption Method: SSL Version
• True store, Key store details are in .p12 File
Convert Truestore and Keystore .jks files to .p12 files following the below process.
a. Open Command Prompt and navigate to the Java Bin path.
b. Syntax
key tool -importkeystore -srckeystore filepath\truststore.jks -destkeystore filepath\truststore.p12 -srcstoretype JKS -deststoretype PKCS12 -deststorepass adw_practice2019
3. Test Connection and configure the connection pool in RPD.
4. Load Java Data Sources:
Steps:
1. Open Administrator Tool.
2. Go to File>Load Java Data Sources.
3. Provide the details like Host, Port & Credentials of the Data Gateway.
4. Click OK on Confirmation.
5. Configure Remote Data Gateway:
Go to Connection Properties on the General Tab.
1. Change the Call Interface to JDBC as in the below screenshot
2. Provide the Redshift connection details in JDBC format.
jdbc:oracle:thin:@//:/
3. In the Miscellaneous Tab provide the below details:
The connection and security setup is a one-time process that is essential if you want to use OAC to create reports leveraging data in both Oracle ADW and Amazon Redshift. This is essential to developing analytics in multiple cloud platforms.
Apps Associates has a dedicated practice to Data and Analytics helping customers realize their data reporting goals to get the most out of their technology. For more how-to and technical guidance information, be sure to subscribe to our blog and follow Apps Associates on LinkedIn.