Configuring the Remote Data Connector (RDC) for BI Cloud Service (BICS)

For other A-Team articles about BICS and Data Sync, click here

Introduction

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.

 

Main Article

Install RDC Application

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 this link on OTN and is called ‘BICS Remote Data Connector’

2. Log in to Weblogic. Navigate to “Deployments” > “Install“.

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

3. Enter the Path where the WAR file is located, then hit ‘Next’ to continue

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

4. Make sure ‘Install this deployment as an application’ is selected, hit ‘Next’ to continue:

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

5. Select the Server(s) to install the application into.  In this case the AdminServer is selected.  Hit ‘Next’ to continue.

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

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.

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

7. If successful, a ‘successfully installed’ notification will be received:

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

and you will see the application listed and active:

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

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:

For Linux:

export DISABLE_RDC_METADATA_SECURITY=1

For Windows:

set DISABLE_RDC_METADATA_SECURITY=1

Then re-start Weblogic.

9. To confirm the Remote Desktop Connector was installed correctly, navigate to this path

http://<weblogic-server>:<weblogic-port>/obiee/javads?status”

If the steps above have been correctly followed, then the following XML file will appear:

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

Configure Data Source

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.

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

2. Select ‘Generic Data Source’ in the options:

14

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.

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

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:

  • Oracle’s Driver  (Thin) for Instance Connections;
  • Oracle’s Driver  (Thin) for RAC Service-Instance Connections;
  • Oracle’s Driver  (Thin) for Service Connections;

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.

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

5. Keep the default options for the ‘Supports Global Transactions’ and ‘One-Phase Commit’ and hit ‘Next’

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

6. Enter the appropriate values for the On-Premise Oracle Database, and then ‘Next’

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

7. Make sure the configuration is correct, then ‘Test Connection’.  If the connection is successful, click ‘Next’

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

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.

Oracle_WebCenter_Portal_11g_R1_PS7__Running_

Download and Deploy Public Key

Within the Service Console for BICS, Select the ‘Database Connections’ tab, and then ‘Get Public Key’

Oracle_BI_Cloud_Service_Console_png_705×245_pixels

Save the key on the weblogic server in the following path: $DOMAIN_HOME/rdc_keys/<deployment_name>

The <deployment_name> is ‘obi-remotedataconnector’ by default.

Using the prebuilt Webcenter VM available on OTN – the path would be:

/oracle/domains/webcenter/rdc_keys/obi-remotedataconnector

If the path doesn’t exist, create it – and then save the Public Key there.

 

 

Set up RPD Connection and Publish RPD to BICS

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.

Windows7_x64

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.

Windows7_x64

3. A ‘Success’ notification should be received.  If it’s not, check the previous steps.

Windows7_x64

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:

<WebLogicServer>:port/obiee/javads/<datasourcename>

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.

Windows7_x64

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.

Windows7_x64

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’

Windows7_x64

 

9. Select the ‘Replace Data Model’ option

Windows7_x64

10. Browse to the RPD and enter the Repository password.  Then click ‘OK’.

Windows7_x64

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.

 

Summary

This article walked through the steps to download, configure, and the Remote Data Connector for BICS.

 

Further Reading

For other A-Team articles about BICS and Data Sync, click here

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

 

Comments

  1. Hi Richard,

    We followed the above mentioned process. While checking using the our url
    “http://:/obiee/javads?status”. we are getting below error:
    “Error 401–Unauthorized
    From RFC 2068 Hypertext Transfer Protocol — HTTP/1.1:
    10.4.2 401 Unauthorized”
    What could be the solution? Any suggestions
    And this we are doing in obiee12c.

  2. Hi
    Thanks for informative article regarding BICS. I have a question about on premise OBIEE 12 C
    Is there any other way for on premises OBIEE similar to BI cloud as below?

    Is it possible to fetched from OBIEE 12 C data to webcenter portal 12C using BISoapConnection created in Enterprise Manager?

    How to connect to OBIEE analytics port 9505/ 9502/ 9704 using http connect if the BI server only allows connections with ssl ports and sso ?

    In case of BISoapConnection I could not create BIImpersonator Role as described here
    http://www.oracle.com/technetwork/middleware/webcenter/portal/learnmore/wc-biappsconfig-396418.pdf

    Because I was not able to find below in Weblogic EM Application Policies after I searched through the entries for the Resource Name resourceType=oracle.bi.server.permission,resourceName=oracle.bi.server.impersonateUser

    Thanks

  3. Hi Richard,

    I have followed all the steps mentioned above. I deployed the on premise RPD into BIcloud following the steps mentioned above. The weblogic server is on my local machine.

    When I try to create any report from the uploaded RPD in BICS, I get the following error :

    [nQSError: 43119] Query Failed: [nQSError: 100031] Unknown Call interface type JDBC (JNDI).

    The connection details given in the RPD is as follows :

    http://XXX.XXX.XX.XXX:7001/obiee/javads/myjdbc

    I tried both from corporate network & non-corporate network, still get the same error.
    Could you please help.

    • It’s hard to say. I’m not part of support and don’t have access to the logs within the server which may help. I’ll provide a few known cases where that error may occur, but if this doesn’t help – you’ll need to contact support and see what they can find. Many people have gotten this working, so chances are there is something you missed somewhere, or incorrect software version being used, etc etc. Good luck.

      * Make sure you don’t have other Connection Pools that also use JDBC (JNDI), but pointing to incorrect URL. All JDBC (JNDI) connections should be through valid URLs.

      * Also, before uploading the RPD, user should switch to the Miscellaneous tab in each JDBC (JNDI) connection pool within Admintool and then save. The best way to check if everything is good is:open the RPD (if already open, close and reopen)right click on the connection pool “Copy” Paste in a notepad windowEach Connection Pool having JDBC (JNDI) type should have ‘use.sql.over.http’=’true’ in the PROPERTIES section within the text that was copied.

  4. Mohammad Jamal says:

    Could you please provide some insight into performance of BICS when the database is on premises ? vs a DbaaS.

    Regards
    M. Jamal

    • Hard to say. Much will depend on how much data you’re trying to move from on-premise back to BICS, the network, internet connection, etc etc

      I’ve not done – or seen anything on performance comparisons – but intuitively if you’re trying to pass a lot of data from on-premise back to BICS, and/or the network / connections are slower – then performance would be worse than BICS sitting in the same data center as a DBaaS DB.

  5. Excellent Article. I have few questions though:-

    Do we need to install complete BI (OBIEE) or just web logic sever installation should suffice on Windows machine?
    Do we need to create/ deploy managed server also? Or just admin sever is fine.
    In your steps, where you are loading java data sources, you have used host name as we center.mycompany.com is that the hostname of web logic ? Or it’s a domain name? If I install web logic sever with machine name as host ..per my understanding that should go as host name in that step.

    Thanks again for detaied steps.

    –Manu

    • Just weblogic and an admin server would suffice. No need for the BI server too. The host name was a virtual host name defined in the /etc/hosts – and was known throughout my network. When I set up the connection in the RPD – I had to use an external IP address that would route to my firewall, and then had firewall rules for that port, to be routed to the internal IP / hostname of the host running weblogic – so that way the IP:7001 connection would get through. The host name of the machine should be fine. The bigger thing is making sure the rules exist for the traffic to get routed to it from the BICS server through whatever firewall / DNS you have in place, so the connection succeeds.

      • Thanks . i have a single entry in host file as below
        127.0.0.1 localhost

        however, my weblogic is http:// :7001 does my host file needs to be changed?

        Also, can you provide more insights on your line “The bigger thing is making sure the rules exist for the traffic to get routed to it from the BICS server through whatever firewall / DNS you have in place, so the connection succeeds”

        Does this has to do anything with the loading java datasources OR its related to RPD connection details we put in?
        OR it is related to access rules in BICS to be routed to port 1521.

        -Manu

        • it wouldn’t hurt to add the machine name to /etc/host – although that shouldn’t really matter. The ‘bigger’ thing is related to the RPD connection string you use for the on-premise DB. You need to make sure the external IP address for your network / computer is used there. That’s what BICS will use to try and connect to your ‘on-premise’ database. Chances are you’re on a network with other computers – and you’ll be sharing an external IP. So you’ll use that external IP (or host name if it resolves externally) and port number in the RPD connection. You then need to make sure your firewall / router knows that when a request comes on port 7001 to that IP – then it needs to be routed to your computer on the internal network. Whether this is a home network with a router – or a corporate setup with firewall / DMZ / load-balancers etc – both will require rules to be set up to get the traffic to the computer with weblogic running. Every situation is different, that’s why it wasn’t covered in the blog. If you’re not sure – get with the networking folks at your company, or read up about your personal home router / network – and see how you can forward the traffic in there. That’s the key point. Making sure the traffic can get through your firewall and to the computer running weblogic so that the request can then be sent to the local DB, processed, and the result set sent back to BICS to be displayed.

  6. Going through the article again- can you explain the meaning of below line
    “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.”

    How do we check if the on premise weblogic server setup on local windows machine can be accesses externally? Also, by PORT defined in RPD , do you mean the port we are giving while loading Java data sources step?

    Also, what should be the hostname? If while configuring weblogic i used “localhost” as hostname and my port is 7001 for weblogic, should that is the port i need to use while loading java data sources?

    • How to check ? Try and connect to the JAVADS URL from ‘outside’ the network (:port/obiee/javads/) You could try using a phone browser, for instance – that’s not on wifi. That would be ‘outside’. Although make sure you use a DNS name, or IP address, that will resolve and get routed to the internal server. You can’t use ‘localhost’ outside the network. Use whatever the external IP/port combination is that will get redirected to that internal IP. You should talk with the network /firewall team. They’ll be able to help. Each company is a little different with their security / firewalls / DMZs etc.

      If you’re doing this in your ‘home network’ – then you’d need to use your external IP for your modem (google ‘what it my IP’ from a browser in the network), and then set up a rule on the modem / router that opens and forwards the traffic on that IP and port combination to the internal IP / port of the machine where weblogic is running. That way the connection will get through.

      And yes – the port is likely 7001 depending on your setup. That’s what mine was.

  7. Hello Richard,

    Thanks for the detailed article. I tried to follow the steps as per the article but I am getting into one issue and would like your help.

    I installed weblogic server on windows machine, mapped RDC as mentioned above BUT
    when I am trying to load Java Datasources, i get error, “failed to load javads metadata due to Error-500–Internal Server Errror” Kindly advise what could be the cause.

    kindly advise.

    –amit

    • Honestly – it’s hard to say. ‘Error-500-Internal Server’ error is a generic error that means something has gone wrong, but the server could not be more specific on what the exact problem is.

      One thing you have to make sure you do is disable the RDC Metadata security. That’s mentioned in the blog – but essentially in windows you need to set this variable in the same dos window that starts weblogic. If you’re starting from a command script – add this to the command script. If weblogic is already running – shut it down – then start it up with that.

      set DISABLE_RDC_METADATA_SECURITY=1

      You could check the weblogic logs and see if that gives you any more, and maybe double / triple check that all steps have been followed. This does work (lots of others have it going) – so it’s possible you missed something or there’s an issue with your environment. You can also enable logging for the obiee-javads tool These are generic instructions below – so you’ll need to edit for your environment.

      Good luck !

      ——
      To enable logging, create a file at $DOMAIN_HOME/config/fmwconfig/servers//logging.xml with the following contents:

      < ?xml version="1.0" encoding="UTF-8"?>

      Note: Default server_name is AdminServer

      • hmmm – the contents for the file got deleted by this site. I’ve uploaded as a text file here DOWNLOAD

        Remember to call this ‘logging.xml’ though when you create it

      • Hi Richard, Thanks for your reply. I checked all setups and found nothing missing. I have couple of doubts though-

        I installed “Generic Installer for Oracle WebLogic Server and Oracle Coherence” from the link above. I assume this is what is expected and not the complete FMW instllation. Request you to confirm once.

        Secondly, when going through Oracle Dcoument, I found addtional step that is missing from the steps above, they are-

        . Make BICS RDC available to BICS with the help of a network administrator.
        a. Configure the load balancer/reverse proxy for SSL communication and to route
        requests to the HTTP Server.
        b. Configure the HTTP server to direct requests to the WebLogic server.

        is this required, considering I am trying this from windows machine? If yes, what exactly needs to be done here..

        I am not able to figure out what could be missing as I tried all the steps from the beginning.

        –Amit

        • Unfortunately I’m not going to be able to help you trouble-shoot this too deeply – it’s probably best to open an SR, as support will have access to BICS logs etc.

          One thing you could do to test it – instead of installing your own weblogic server – is download one of the virtual box VMs from OTN. I tested it that way – downloading one that had an Oracle DB, and weblogic already installed. That way you know have everything set up correctly from that standpoint.

          It sounds like your error is before you’ve gotten to the point of uploading the RPD to BICS to test ? If yes – then the firewall / load-balancer piece shouldn’t matter at this point. As long as the browser you’re using to test JAVADS can connect to the machine with weblogic – then that step should work. If you’re past that point – and HAVE uploaded to BICS – then absolutely, you need to make sure you’ve set everything up so that the IP you’re using in your RPD resolves all the way to your weblogic server (make sure you’re not using an internal IP in your network – but an external IP). If you’re at home – you need to make sure your router etc is taking your external IP traffic for that port – and forwarding to your laptop with weblogic. If you’re in the office – then you need to make sure your firewall / networking rules are routing the traffic correctly. And you will need a self-signed certificate as well. See this instruction guide as well.

          http://www.oracle.com/technetwork/middleware/bicloud/downloads/bicsrdc-gs-v1-1-05042016-3007145.pdf

          Hope you figure it out.

          • Thanks for your inputs. I am not able to find prebult oracle VM with weblogic +sql developer+admin client on it.

            If you know one, can you share the OTN link?

            –Amit

          • I used this one (or the version from 10 months ago).

            You won’t find one with the BI Admin client – that’ll only run on windows. But you can assign the VM an IP on your network, and connect that way from your BI Admin tool on your laptop. SQL Developer – you could download and install the linux version to run on this VM, or just put on your laptop, then connect with: IP:1521 etc

            http://www.oracle.com/technetwork/middleware/webcenter/portal/downloads/owcp-vm-2251396.html

          • Thanks Richard. Do we need to install Node Manager for this to work? I have not installed / deployed mode manager in weblogic.

          • There’s a java property to control viewing of status page and loading java data sources from Admintool (unless this property is set to 1, both the aforementioned features will be protected by the key signature sent by BICS). This requires node manager.

            However there’s an environment variable that can be used, instead. In which case, no node manager is required.

          • can you let me know what that environment variable is and what should be its value?

          • it’s in the blog. See step 8.

    • Hello Amit,

      I got the same error Error-500–Internal Server Error” with the generic/basic Weblogic installation. Have reinstalled the complete BI 12.2.1.0.0 and didn’t get the issue.

      Also, RDC will support only 12.2.1.0.0 Admin tool only.

  8. Hi,

    I am using oracle VM and installed weblogic 12c in it and deployed war file after deployment I can see the status showing xml message but when I am creating the JNDI connection and calling this URL from browser I am getting 401 unauthorized error — URL used is http://weblogic server ip:port/obiee/javads/JNDIConnectionName.

    What should I do to resolve this.

    • I’d suggest opening an SR. There are so many variables – doing an SR will get you direct support and they can look at your specific use case.

      Off the top of my head – a couple of items that may cause 401 errors

      – make sure you’ve disabled metadata security (at least for testing) and are using the correct username / password
      – make sure you copy the public key to the correct directory on the weblogic server
      – make sure you have gone to the MISC tab in the Admin tool connection and set the ‘Use SQL over HTTP’ to be ‘true’

      Double check those. If that doesn’t help – open an SR, and they can get more specifics of your environment etc and help you dig in and figure it out. Good luck.

  9. Michael Rainey says:

    Hi Richard,

    I’m curious as to why a local WebLogic Server is required to create a connection between what’s essentially an RPD on another WLS instance (though hosted by Oracle in the cloud) and an on-premises database? It seems that the RPD connection could be a secure SSH string?

    Thanks for clarifying.

    Regards,
    Michael Rainey

    • Michael, I’m not part of the DEV team, but I did reach out to them. Their response

      – If we use a DB Protocol directly, it is inherently insecure. Also requires non-standard ports to be open, which is almost impossible to do on all customer environments.
      – If we use a secure protocol, like SSH, we give up performance. SSH is not designed to transfer substantial amounts of data.
      – Accessing multiple databases would require multiple ports or access points to be open, increasing difficulty in usage, maintaining security and setup.
      – We also need to make sure we’re maintaining security across BICS systems – customers have a ‘POD’ on a shared server – opening ports on the server for one customer may open up security holes for another. If the RPD/connection string is compromised, we would be unable to maintain security.

      Hope this helps. cheers

Add Your Comment