Thursday, July 23, 2020

OBIEE with ADW as a source

I needed to set on-prem OBIEE to work with Oracle ADW (Autonomous Data Warehouse), in the RPD. (Oracle Autonomous Data Warehouse provides an easy-to-use, fully autonomous database that scales elastically, delivers fast query performance and requires no database administration.)

First I followed the fine blog by Dayne Carley "Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse". It's very relevant for OBIEE as well. Please note that OBIEE is the minimal requirement to work with ADW. The Administration tool client should be (at least) as well.

  • From ADW console I downloaded the wallet file.
  • Copied it to my PC (in my case created ADW19 folder under the Admin client folder) and unzipped the file.
  • Opened CMD interface in Windows.
  • There updated the sqlnet.ora file, setting the proper location of my files: WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\Obiee12214_client\ADW19")))
  • Defined CRED_LOC variable: SET CRED_LOC=C:\Obiee12214_client\ADW19 
  • Defined OAC_HOME variable (yes I know it's not OAC): SET OAC_HOME=C:\Obiee12214_client
  • Copied the 2 ORA files to the network location of the Admin client: copy %CRED_LOC%\*.ora %OAC_HOME%\domains\bi\config\fmwconfig\bienv\core
  • Run the ADmin client from the CMD:

Next I created a new repository and run File/Import Metadata, chose OCI:

Used the TNS name (the low option is more fitting to BI) and user&Password

Next is the regular behavior of choosing tables and Views...

Please note that unlike regular data sources behavior, the database name in Physical Layer should remain the default one (db202006021216_low in my case).

Otherwise you will get general error NQSError: 10058 when trying to get data.

 Now we need to do some work from the server side:

We should update the tnsnames.ora file on the server (located in MW_HOME\user_projects\domains\bi\bidata\components\core\serviceinstances\ssi\oracledb) with the content of the tnsnames.ora from the wallet (copy and add it).

As you might see in the picture, In the same folder there is an additional folder /wallets/client. Copy the cwallet.sso file from your ADW instance to this directory.

Restart the OBIEE server.

That should do the trick.

The same should work for OAS as well.

No comments:

Post a Comment