ODI 12c Using custom SQL to over ride default Source query.

In this post i will give you a brief idea about a new feature introduced in ODI 12c.

As in informatica now ODI also support SQL query over ride, we can use a custom sql query to over ride the source query.

This feature is available in 12c in Extract option under physical diagram

After creating the mapping navigate to the Physical tab and in Extract Option we can see a feature CUSTOM_TEMPLATE.

In this area we can write a custom select statement and that will be used as a source query for populating the target.

Please remember to map all the fields that in the over ride query from source.

IKM Used: IKM Oracle insert global.

Thank you.

upload RPD Failed: Metadata Lock could Not be acquired. In OBIEE 12c

Issue: Not able to upload rpd in OBIEE 12c, throws an error upload RPD Failed: Metadata Lock could Not be acquired
in

OBIEE version : 12.2.1.0.160719
OBIEE Client tool : 12.2.1.1.0

Cause: Not included some class files.

Solution:

To work around this issue, you can replace the binaries in the 12.2.1.1.0 client from the 12.2.1.0.0 client to make it work successfully.

Steps:

Take a backup of the below files

1) Navigate to the location

 C:\Oracle\Middleware\Oracle_Home\bi\modules\oracle.bi.commandline.tools\scripts

Rename datamodel to datamodel_12.2.1.1.0

2) Navigate to the location

 C:\Oracle\Middleware\Oracle_Home\bi\common\templates\wls

Rename: oracle.bi-commandline-tools-template to oracle.bi-commandline-tools-template_12.2.1.1.0

3) Navigate to the location

C:\Oracle\Middleware\Oracle_Home\bi\lib

Rename: bi-commandline-tools to bi-commandline-tools_12.2.1.1.0

Then copy the files  from the 12.2.1.0.0 client. To the above location

4) Navigate to the location

C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi1\bitools\bin

Take a backup of the file datamodel.cmd then edit datamodel.cmd file

Change the call statement as shown below

call "C:\Oracle\Middleware\Oracle_Home\bi\modules\oracle.bi.commandline.tools\scripts\data-model-cmd.cmd" %*

After this try to upload the RPD again 

After this step upload rpd successfully worked for me.

Than you .

Steps to import BISAMPLE Schema for OBIEE12c training.

1: Navigate to the lactation 

C:\app\OracleHomeUser1\product\12.1.0\dbhome_1\NETWORK\ADMIN

Open tnsnames.ora file and add the below tns entry and save the file

PDBORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdborcl)

    )

  )

2: Open your SQL Developer and run the below query as SYS user

ALTER PLUGGABLE DATABASE ALL OPEN;

3: Creating BISAMPLE user and gave privileges.

Connect as pdborcl and run the query attached (BISAMPLE_USER.SQL)  

For creating user and giving privileges

4: Unzip the dump files (Link) that you downloaded into the below location

C:\app\OracleHomeUser1\oradata\ForOBE

5: Query to create a directory that pointing to the dump files

Run the query from pdborcl

CREATE OR REPLACE DIRECTORY datapumpdir as 'C:\app\OracleHomeUser1\oradata\ForOBE';

To verify the directory is created use the below command

SELECT * from ALL_DIRECTORIES.

6: Command to import the schema.

Open command prompt and run the command.

impdp system/<Password>@pdborcl directory=datapumpdir dumpfile=BISAMPLE.dmp logfile=BISAMPLE.log

Now all the tables will be imported to the schema BISAMPLE. 

java.sql.SQLRecoverableException: ORA-01033: while starting OBIEE 12c

Issue: java.sql.SQLRecoverableException: ORA-01033: while starting obiee12c

Unable to start OBIEE 12c. When starting admin server the server failed.

Reason: The issue is related to DB, In OBIEE 12c we are installing our metadata in portable database that is PDBORCL. So while start up it will not start automatically we have to manually open the database.

Soultion:

Command to open the portable database.

ALTER PLUGGABLE DATABASE ALL OPEN;

after opening the database please start OBIEE 12c.

Error RCU-6080 while installing RCU for OBIEE 12c

Error: RCU prerequisite check failed with error code RCU- 6080 while configuring RCU for OBIEE 12c.

Cause: The reason for the failure is, from OBIEE 12c onwards oracle prefer to install the obiee metadata schema in pluggable database rather than container database. While installing database 12c we will get a prompt to select install pluggable database with the current installation. So by default in 12c we have a pluggable database with object name 'PBDORCL'. So instead of using ORCL  use PDBORCL as service name and try to create the schema.

But before using pluggable database please make sure that the DB is in open state. Because when starting DB oracle only open the default DB ORCL, So it will be better to use a trigger to open the pluggable DB PDBORCL.

Command to check the MODE of database.

"SELECT NAME ,OPEN_MODE FROM V$PDBS"

Then you can see the OPEN_MODE as mounted so please below command to open the portable database.

ALTER PLUGGABLE DATABASE ALL OPEN;

After executing the command the database will open and that will be available for read and write.

you can use the below trigger for opening pluggable database.

CREATE OR REPLACE TRIGGER OPENPBD AFTER STARTUP ON DATABASE

BEGIN

     EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END OPENPBD;

Then replace the service name with pdborcl and run the RCU prerequisite one more time.

now you can see the step passed and you can continue with the installation.

Thank you .

INST-07545: Unexpected error while installing OBIEE 12c Client.

Error:INST-07545: Unexpected error the distribution setup_bi_client 12.2.1.2.0 incompatible features with the following.....

Cause: This issue is because we are trying to install the obiee12c client in the same oracle_home where we installed our obiee12c.

Action: Change the oracle installation directory to a fresh directory and continue with the installation.

As shown below i changed the directory into C:\OBIEE_ClientHome

Then the issue is resolved and able to proceed with the installation.

Thank you :).

Connecting to ORACLE database from SSIS

Issue: I have to connect to ORACLE database from my SSIS tool.

Solution: Download and install

32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio 

Using the following link:http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

Then install the driver and it ask for data base details before the final step.So provide the DB Name(Just a name),Host name,Port,Service name

After competing installation restart your machine.

Then open your SSIS and select OLEDB Source and create a new connection.

From the Provider select Oracle Provider for OLEDB.

Then it will ask for the server or file name, give the name of your DB that you provided during installing the client.(Entry in your tnsnames.ora) Then give username and password and test the connection.


You can see connection successful and you can connect to the database and select the tables.