How to import large text file (unstructured raw data) content into Oracle Table 's column using ODI 12c ( Oracle Data Integrator 12c )

In one of our client project, we came across a requirement to import large size text file content (unstructured raw data, more than ~10000 Characters ) into Oracle table's column. The files were API call responses in JSON format and had to be stored in Oracle tables using ODI (Oracle Data Integrator 12c) ELT tool.

Source Text File: The file has JSON data

Target Table: Stored the above JSON data as is in a Oracle Table's Column

Implemented the ODI mapping by following usual steps and it was all fine. But started facing issue the below issue when the content of the source file was more than 4000 characters.

 

Initial Implementation of Mapping in ODI - 

1.Defined the target table column type as CLOB 

2. Configured the File technology Physical schema and Logical schema in ODI topology  for File and     Oracle technology Physical schema and Logical schema for the target oracle database. then we created the datastore for File and Oracle table     in ODI model.  In File datastore of ODI model, we defined the File Format to Fixed and File column data type as Sting with 4000  and   defined the CLOB data type for target table column.  

3. Developed ODI mapping by considering the File datastore as source and oracle table as target. 

When we run the mapping we received the below error. 

 java.sql.SQLException: ORA-12899: value too large for column "DB NAME"."TABLE NAME"."Column Name" (actual: 5000, maximum: 4000)

We did some research over the internet and came to know that though we defined target table's column as CLOB, ODI would  not convert the source string type column  of File datastore to CLOB automatically. To resolve this we had to perform the below steps. 

Solution - 

1. Go to Topology  -> Technologies -> Expand the  "File" technology

2. Then expand "Data Types" option, right click and open "Fixed String". Here we proffered "Fixed String" data type because we  did not want to change the behavior of "Sting" data type. 

3. Click on "Convert To" option in the "Fixed String" properties window and change to data type to CLOB for Oracle technology and save it. 

4. Change the data type of  Source File Datastore column to "Fixed String" and define the maximum length. 

5. Run the mapping. 

With this configuration changes we were able to successfully load the file content CLOB type column of Oracle table. 

Join two tables from different schemas in RPD of OBIEE 12c/11g

Sometimes we may need to join the tables from different schemas in RPD. Lets go through with simple example. Assume there are two schemas in the same Oracle Database called Schema-1 and Schema-2 and we have imported the tables Sales-Daily from Schema-1 and Dim_Date from  Schema-2 into Physical layer as below. 

Now, to join the table Sales-Daily from Schema-1 and Dim_Date from Schema-2, we need to select the tables and right click for options, select "Physical Diagram" => "Selected Object(s) Only" from the options.

Next, define the join condition. 

Next, bring both the tables under on BMM Layer. 

How to use Presentation variable in RPD SELECT table type or RDP view or Opaque view

 Sometimes we may need to pass presentation variable value of  Dashboard Prompt to RPD opaque view. But is not possible directly via presentation variable. No worries, it is possible with Request Variable. 

Let us take a scenario, assume that you have created a opaque view in the physical layer of RPD and you need to pass the presentation variable value of Dashboard prompt to the opaque where condition. You can achieve this by following steps. 

1. First, create a session variable (non system session variable) in the RPD, let us say the variable name is From_Date

2. Next, create the dashboard presentation variable with the same name of session variable (From_Date).         (Here the session variable's and presentation variable's are same, when the dashboard user set a value         for  From_Date presentation variable, then that value overwrites the session variable From_Date.)

3. Then use the variable From_Date in the RPD Opaque view's where condition. 

Configure or enable Evaluate function in OBIEE (12c)

The EVALUATE function is disabled in OBIEE by default. You will get the below error for EVALUATE function in BI Analysis when EVALUATE function is disabled.

"error [nQSError: 43113] 

EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE"

If you encounter the above error, then you must set the value of the EVALUATE_SUPPORT_LEVEL parameter to 2 in the NQSConfig.INI file.

The default value of the EVALUATE_SUPPORT_LEVEL parameter is "0", which means that evaluate is not supported. 

Locate "EVALUATE_SUPPORT_LEVEL" in NQSConfig.INI file and set it to as below. 

# EVALUATE_SUPPORT_LEVEL:

# 1: evaluate is supported for users with manage Repositories permission

# 2: evaluate is supported for any user.

# other: evaluate is not supported if the value is anything else.

EVALUATE_SUPPORT_LEVEL = 2;

The NQSConfig.INI file is located at the following location:

<DOMAIN_HOME>/config/fmwconfig/biconfig/OBIS/NQSConfig.INI

Generally in most of the installations, the 
DOMAIN_HOME = /u01/Oracle/Middleware/Oracle_Home/user_projects/domains/bi