I needed to set on-prem OBIEE 12.2.1.4 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 12.2.1.4 as well. Please note that OBIEE 12.2.1.4 is the minimal requirement to work with ADW. The Administration tool client should be 12.2.1.4 (at least) as well.
Meaning:
- 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:
- CD %OAC_HOME%\BI\BITOOLS\BIN
- ADMINTOOL.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.
