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.

Oracle BI Publisher reports not showing up data unless we change the report output format

We have an Oracle Retail Sales Audit 14.1 (ReSA) application which has some pre-built operational insights reports.  These are OBIEE/Oracle BI Publisher reports for Sales Auditors or Financial managers.

While validating the ReSA application one of the user comes up with a question asking "When I try to view a report from the ReSA application it shows a blank BI Publisher report and if only I change the report format to PDF or html it shows up the report data.

After doing some R&D, I found the reason - it is because of the report level property "Auto Run" marked unchecked

 Auto Run - This feature will not allow the report to execute immediately when user opens the report.  This will prompt the user to select parameter values before it actually runs the report

Oracle BI Publisher Plug-in not visible

Here is my first blog on BI Publisher.  After working for years in Oracle BIEE of different versions, I got an opportunity to work in BI Publisher 11g now.

To start with I install Oracle BI Publisher 11g Trial Edition (v11.1.1.7.1) on my 64 bit windows machine – All good.  Then I went on installing the Oracle BI Publisher Desktop 11.1.1.7.0 for 64 bit Office on Windows, unfortunately, the installation asked to install a 32 bit version of the BI Publisher Desktop.  I suspect it could be due to the version of the Microsoft office tools.

I then downloaded the below desktop version and tried installing - Oracle BI Publisher Desktop 11.1.1.7.0 for 32 bit Office on Windows.  The installation successfully completed in less than 2 minutes, but I could not see the BI Publisher plug-in on the windows document.  I tried installing different versions of Oracle BI Publisher Desktop, but no luck

I finally find a way !!

All we need to do is to navigate to the below path and run the setup.exe

C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\DotNetInstallFiles  (This path could be different for your installation)

In few seconds you will notice the BI Publisher add-in available on your MS Word or MS Excel office tools.

Top N analysis

I had a requirement to display the number of orders each state is making in a specific time period. This report should show only Top 5 states plus one record for all orders from rest of states. 

Though there are several ways of doing this, but I did using combined with similar request.

1) Create a request which is having states and orders count. Apply filter on "Orders count" where rank(orders) is greater than 5.  Save the request under shared folders in some name.

2) Create another request with necessary columns needed and apply filter on "Orders count" such that it will display only the Top 5 states. Add one dummy column to the report and hard code value ‘1’.  I will use this dummy column for sorting purpose later

3) Now select combined with similar request option, create the request like as earlier mentioned and apply filter on state, Using Advanced -> "filter based on result of another request" and point to the first request (item 1) using the Browse. Add one blank column to the report and hard code value ‘2’ for sorting purpose and edit column formula of reporting state as follows;

case when CUSTOMER.STATE = 'X' then 'Y' Else 'OTH' end 

(Make sure you really don't have a valid state_cde as 'X' now or in future, otherwise results may go wrong)

4) Sort the report using the dummy column

5) Finally hide the dummy column using column properties -> Column format -> Hide

 

Avoid Null in OBIEE dashboard prompts

It is always possible that NULL or white spaces appear in the dimensional attributes.  If we create a prompt with control type "multi-select" for that dimension, we will see blanks appearing in the list of values.  My customer said for any prompt they don't want to see NULL appearing, instead they want to see some text, something like, say "NULL VALUE" .

How to do this?

Add the below in Show "SQL Results" of the prompt

SELECT CASE WHEN TRIM(TABLE."COL NAME") IS NULL THEN 'NULL VALUE' ELSE TABLE."COL NAME" END FROM SUBJECT AREA

TRIM function in the above SQL will convert white spaces to NULL which is then decoded to 'NULL VALUE' using the CASE statement