Oracle BI Publisher Plug-in not visible - II

After installing the BI Publisher desktop, we sometimes don't see the BI Publisher Plug-in available in the MS Word Ribbon.  I have mentioned few steps that needs to be performed to get the plug-in Part - 1 of this post.

Sometimes despite we follow the steps explained in Part - 1, we don't find the plug-in.  Here are few more additional steps that I have followed to get it on the .doc file.

I have done this in MSWord 2016 and I believe this will work in other versions as well

1) Open the Word Doc, Click on File -> Options

2) On the Word Options window, select "Add-ins" and click "Go" next to Manage "COM Add-ins"

3) On the "COM Add-ins window, check the box against "BI Publisher Template Builder for Word", and click OK.

4) Now that you should see the BIPublisher plug-in on the menu

Unfortunately, this workaround to get the BI Publisher has to be performed each time you open the word document.  There could be a better way of doing this, still searching...

How to avoid ODI-1590 failure due to unexpected char: '\' @

I was trying to load an Oracle on-premise database table to an Oracle BICS target using ODI and end-up in the below error.  My ODI 12.2.1.2.6 installation is running on a Windows Server and the error may not Occurred if it is Linux or other OS installations.

ODI-1590: The execution of the script failed.

This is my simple mapping to illustrate the workaround.  As you can see in the logical view, it just loads the data from DEPT_SRC onpremise table into DEPT_TGT BICS DB table.

Here are the LKM and IKM that I have used

• LKM SQL Multi-ConnectGLOBAL
• IKM SQL to Oracle BI Cloud Service

Upon running the mapping it just failed with 

ODI-1590: The execution of the script failed.
Caused By: org.apache.bsf.BSFException: exception from Groovy: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Load_DEPT_TGT: 64: unexpected char: '\' @ line 64, column 23.
       def traceLoc = "C:\Users\KS\AppData\Local\Temp\bics_calls.log";

If you examine the above log information, it says unexpected "\" char found.  All you need to do is open the mapping and select the target in Physical tab

You will see multiple physical properties that can be modified.  Select the one for trace file to avoid the "\" char.

Now if you run the mapping you will see there is no error and the target would have loaded.

Migrate Data from Oracle BICS to Oracle DBaaS or to on-premise Database

We have recently moved our on-premises OBIEE deployment to Oracle Business Intelligence Cloud Service (Oracle BI Cloud Service).  As a part of this, data from our Oracle 11g DB is moved to the BICS - Schema as Service database.

Now that we have decided to move from BICS to Oracle Analytics Cloud (OAC) to get the additional benefits. Here I will be explaining the process involved in moving the data from the BICS schema to Oracle 12c Database as Service (DBaaS).  Moving the DB objects from BICS to DBaaS can be done either with data or without.  But the process remains the same;   It all depends on how we export the BICS data.

Here are the detailed Steps

1) Login to BICS using the Cloud Services URL.

2) Open the apex Database Schema where you will see the Service Details : apex(Oracle Database Cloud Service)

3) Click on the Exports on the left and then on "Export Data"

4) You will be prompted to confirm whether the export is with data or without

5) Once you confirm it will take few minutes (depending on the data volume) and you will see the export dump (Status will change to Available)

6) With the SFTP details that you see on the same screen you should be able to download the files to your local machine.  I use winSCP here for moving the .dmp to local machine

 Note :

1. If you don't know  the password for the SFTP user, you can reset it using the Navigation Menu on the same screen (Three Dashes on the top left) -> Users -> SFTP Users
2. Currently there is no option to specify the objects in the export, so it will be full export with or without data.
3. BICS will automatically specify a name for the dump and we cannot change it during the export
4. You will also notice a warning saying "Export files are available for 2 days and downloadable from your secure FTP download area in the outgoing directory."  But as of today, I can see the .dmp files more than 10 days on the SFTP server although it is not appearing in the export list 

7) Once the file is on local, transfer the export dump to DBaaS host via WinSCP or any similar tool. This may require the ssh keys for password less authentication

8) One important point to note here is the Tablespace and Schema of BICS database will be something different from what the tablespace and schema what we wanted to have in DBaaS.  This shouldn't be a problem as the data pump utility allows us to remap them as a part of import.

9) Login to the DBaaS host via. Putty (same ssh keys mentioned in point 7 is required here).

10) Create a table space in DBaaS to import the BICS objects.  Schema creation is not required as it will be a part of import.  Sample create statement is shown.

CREATE TABLESPACE TEST_TS
    DATAFILE 
        '/u01/app/oracle/oradata/ORCL/test_ts.dbf' SIZE 1240000 AUTOEXTEND ON NEXT 1  
    MAXSIZE 52457300 
    BLOCKSIZE 8192 
    NOLOGGING 
    DEFAULT NOCOMPRESS 
    ONLINE 
    SEGMENT SPACE MANAGEMENT AUTO 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;  -- Do not copy/paste as is


11) Create a directory called dpdump under '/u01/app/oracle/admin/ORCL or any other directory of your convenience.

12) Chmod to 755

13) Move the <export>.dmp transferred from the local to this directory.

connect to PDB (PDBORCL in my case) via SQLPLUS as SYSDBA.  You need to know the SYS or SYSTEM password.  A normal user (abc.xyz@company.com with which we login the Cloud console -> SQL workshop is not sufficient)

CREATE OR REPLACE DIRECTORY BICS_TO_DBAAS as '/u01/app/oracle/admin/ORCL/dpdump/';

GRANT READ, WRITE ON DIRECTORY BICS_TO_DBAAS to sys;

Exit SQLPLUS.

14) From the command prompt run the impdp

impdp sys/<SYSPASSWORD> SCHEMAS=BICS_SCHEMA remap_schema=BICS_SCHEMA:DBaaS_Schema remap_tablespace=BICS_TABLESPACE:DBaaS_TABLESPACE  directory=datapump dumpfile=<export>.dmp

Note : If you ignore the remap_schema then the name of the schema in DBaaS will be just the one in BICS (This may not be appropriate)

15) Login and verify the DB objects created in DBaaS database via SQL Developer or any similar tool.  Note, the process the moving the BICS to on-premise database is just the same.