The current version of the remote data connector available on OTN requires Java 1.8. This may impact the version of the web-server you are able to run. For instance, weblogic 10.3.6 is not certified for java 1.8, but weblogic 12c is. This is a temporary issue, an updated version should be available shortly, and then this section will be removed.
This original article was written in March 2016, and at the time was intended for the BI Cloud Service (BICS). The same steps apply for the Oracle Analytics Cloud.
Since this article was written, new functionality to allow a direct connection from Data Visualization (DV) to on-premise databases has been added. That is detailed in this article. Additionally, DV can now connect directly to on-premise Essbase sources. That is covered in this article.
The BICS Remote Data Connector (RDC) was released in March 2016. It allows reports and analyses in BI Cloud Service (BICS) to directly connect to an On-Premise Oracle Database. When a report is run, a SQL request is generated by BICS and sent to the on-premise Weblogic server. Weblogic sends that request to the on-premise database, and then compresses the results before returning those to BICS where it is displayed. This gives customers with large on-premise data sets the ability to use BI Cloud Service without having to push all of that data to the cloud.
There are several pre-requisits:
- The on-premise data source must be an Oracle DB. Future versions of the RDC tool will expand that to other database vendors.
- The BI Admin tool used to create the RPD must be 12c. Prior versions do not offer the JDBC (JNDI) Data Source option that is required for this process. Download and install the 12c BI Admin Tool from OTN selecting the 'Oracle Business Intelligence Developer Client Tool' option.
- A Weblogic server running in the On-Premise environment. The latest version of weblogic is available through this link, although prior versions will likely work as well.
- A knowledge of networking, security and firewalls. The On-Premise weblogic server needs to be accessible externally, and the port defined in the RPD connection needs to correctly route to the weblogic server port.
This article will not go into detail of security, load-balancers, DMZs, firewalls etc. The assumption is that the knowledge exists to make sure the connection from BICS can be correctly routed to the on-premise Weblogic server. Some links to help can be found in the 'Further Reading' section at the end of the article.
Please note that while this approach can be used with the 'Schema Service' version of BICS, once the RPD is uploaded, data stored in the schema service database will not be accessible. The RPD model will replace the Schema Service Model, and will not be able to connect to schema service data.
Multiple connections and subject areas can be defined in the RPD, so if a customer has both On-Premise connections, and DBaaS connections - those CAN be modeled in the RPD and will be available in BICS once the RPD has been uploaded to BICS. For more information on defining a connection to DBaaS, see this article.
1. Download the War file application to be installed into Weblogic and save to a file location that is accessible to the server where Weblogic is running. This War file is available through OTN through this link and is called 'Oracle BICS Remote Data Connector'.
2. Log in to Weblogic. Navigate to "Deployments" > "Install".
3. Enter the Path where the WAR file is located, then hit 'Next' to continue
4. Make sure 'Install this deployment as an application' is selected, hit 'Next' to continue:
5. Select the Server(s) to install the application into. In this case the AdminServer is selected. Hit 'Next' to continue.
6. Make sure 'DD Only: Use only roles and policies that are defined in the deployment descriptors', and 'Use the defaults defined by the deployment's targets' are selected in the relevant sections (see below), then hit 'Finish' to install the application.
7. If successful, a 'successfully installed' notification will be received:
and you will see the application listed and active:
8. The Remote Desktop Connector has metadata security built in. To fully verify the application is working, and to connect to it through the BI Admin tool, this security will need to be temporarily disabled. Shutdown the Weblogic Server, and then in the same command shell or shell script used to start weblogic - set this variable:
Then re-start Weblogic.
9. To confirm the Remote Desktop Connector was installed correctly, navigate to this path
If the steps above have been correctly followed, then the following XML file will appear:
Multiple data sources can be setup. Use the following process for each and use a unique name. The connection to each data source would then be defined in the RPD connection.
1. Within the Weblogic Administration Console, expand 'Services' and 'Data Sources' and select 'New' to create a new data source.
2. Select 'Generic Data Source' in the options:
3. Enter a Name for the Data Source and a JNDI Name, and the database type. In the initial release of the RDC tool, 'Oracle' is the only supported Database type. In future releases this will be expanded to other vendors. Note - the JDNI Name forms part of the URL used to access the data source, so try to avoid spaces and other characters that may cause problems with the URL.
4. For the Database Driver select the appropriate driver for the On-Premise Oracle Database. In the initial release, the following options are currently supported:
Depending on the version of weblogic, the listed version may be slightly different. In this case the 'Oracle's Driver (Thin) for Service Connections' is selected.
5. Keep the default options for the 'Supports Global Transactions' and 'One-Phase Commit' and hit 'Next'
6. Enter the appropriate values for the On-Premise Oracle Database, and then 'Next'
7. Make sure the configuration is correct, then 'Test Connection'. If the connection is successful, click 'Next'
8. On the final configuration screen, select the Target to deploy the JDBC data source. In this case we use the AdminServer. Hit 'Finish' to complete the configuration.
Within OAC, go to Service Administration’ and then ‘Manage Connections’
Using the drop down menu in the far upper right of the screen, select ‘Get Public Key’ and download the file
Save the key on the weblogic server in the following path: $DOMAIN_HOME/rdc_domains/rdc_keys/obi-remotedataconnector- weblogic-<version>
as an example, it might be:
If the path doesn't exist, create it - and then save the Public Key there.
The name of the 'pem' file is not important. The tool will read all 'pem' files in the directory. If a single on-premise weblogic server is being used for multiple BICS instances (for instance a 'Dev' and 'Production'), then both of the 'pem' keys should be saved here. As long as the file type is left as '.pem', then they will both be read and used, no matter what the file is renamed to.
This process works best when an RPD is firstly created against the local On-Premise Database and is tested On-Premise to confirm joins / calculations / subject areas are working as desired.
1. Open the 12c Admin Tool but Do NOT Open the Existing On-Premise RPD. Under the 'File' menu, select 'Load Java Datasources'. If this option is not available, the Admin Tool is not the correct version. Download from OTN.
2. Enter the Host Name / IP address, port, and user that can connect to the weblogic server where the RDC was installed. NOTE - this hostname or IP does not need to be available externally. This is just used to load the Java Datasource in the RPD for this step.
3. A 'Success' notification should be received. If it's not, check the previous steps.
4. Open the RPD, and right click on the Connection Pool and select 'Properties'. In the 'General' tab of the resulting properties screen, update the Data source name to have the syntax:
The value used for the Weblogic Server needs to be able to resolve from the BICS server in the Oracle Cloud, to the on-premise Weblogic host. If the customer has a resolvable name (www.oracle.com for instance) and the correct firewall rules have been put in place for a request to be routed to the internal weblogic server, then that would suffice. Otherwise, use an IP address that will be routed to the weblogic server. The hosts file on the BICS server is not accessible to have an entry added.
In recent versions of the Admin tool, you may also see a username / password field. This is not used in RDC, so you can enter whatever you like in those fields. The database connection is defined in the on the weblogic server.
5. Go to the 'Miscellaneous' tab and make sure 'Use SQL Over HTTP' is set to 'True'. The value in 'Required Cartridge Version' may be missing. Do not change that.
6. Note - you will not be able to import tables using this connection. That should be done while the standard oracle RPD connection is in place to the On-Premise Oracle Database.
7. Save the RPD
8. In the BI Cloud Service Console, select 'Snapshots and Models'
9. Select the 'Replace Data Model' option
10. Browse to the RPD and enter the Repository password. Then click 'OK'.
11. Reports can now be written in BICS to connect to the On-Premise data source.
PLEASE NOTE. The Remote Desktop Tool will not work with customer SSL certificates that include a wildcard character '*' in the Common Name (CN). If HTTPS is being used, and errors are found, then check this is not the cause.
Use this URL: https://www.sslshopper.com/ssl-checker.html and in the 'Server Hostname' enter the full https URL being called in RDC, and then check it. If wildcard characters are being used, then a new certificate without these need to be generated.
There is a fix in the works for RDC, but currently this is not in place.
This article walked through the steps to download, configure, and the Remote Data Connector for BICS.
Quick Start Guide for Remote Data Connector: BICS Remote Data Connector getting started guide
Configure Weblogic to use SSL: http://docs.oracle.com/cd/E13222_01/wls/docs81/secmanage/ssl.html
Configure Plug-in for proxying requests from Oracle HTTP Server to Weblogic: http://docs.oracle.com/cd/E28280_01/web.1111/e37889/oracle.htm#PLGWL551