Downgrade OBIEE RPD Version

Some times we need to down grade the obiee rpd repository version. I came across this situation, while I'm trying to deploy the .rpd of higher version to lower version of OBIEE.

Before going to deploy the rpd, first check your "Oracle BI Administration Tool" version. Then down grade your .rpd file to that version.

open the administrator and click on help, then click on About Oracle BI Administration Tool.

here you will find your OBIEE version and repository version

Now your rpd file should be equal to this repository version(326), if not you have to downgrade or upgrade the rpd file version.

Open the command prompt as an administrator. use this below command.

Go to <home>oraclebi\orahome\bifoundation\server\bin> nqgenoldverrpd   -P Password -I 'present rpd loaction'  –O 'output rpd location' -V 'required rpd version'.

nqgenoldverrpd : Command for downgrade rpd version.

-P                         : password for rpd file

-I                          : Input file location, that is your current rpd file location.
-O                         : Output file location, that is where you want to place your downgraded rpd file

-V                         : Version number, which is required to downgrade.

Example:

 <home>oraclebi\orahome\bifoundation\server\bin> nqgenoldverrpd   -P Mypass123$

-I D:\rpd\achanta349.rpd  –O D:\rpd\achanta326.rpd -V 326

now you can find the update rpd with repository version 326 at D:\rpd\achanta326.rpd

If you have any question please leave a comment.

WHAT IS BI APPS/ BI APPS ARCHITECTURE

Yeah, before we are dig into BI APPS, you should have minimum idea about data warehouse/ data warehousing. If you are new to the data warehouse please go through the below article, which gives a over view of data warehouse otherwise you can skip this link.

What is Data warehouse

In Oracle words:

“Oracle Business Intelligence Applications are complete, pre-built BI solutions that deliver intuitive, role-based intelligence throughout an organization. These solutions enable organizations to gain more insight and greater value from a range of data sources and applications including Oracle E Business Suite, PeopleSoft, JD Edwards, Siebel, and third party systems.”

Oracle BI Applications are built on the Oracle BI Suite Enterprise Edition, a comprehensive and market leading BI platform. This enables organizations to realize the value of a packaged BI application, such as rapid deployment, lower TCO, and built-in best practices, or to build custom BI applications—all on one common BI foundation.”

Components of BI Apps:

As defined BI Apps is a complete analytical solution which has the following components.

1. Informatica Power centre Platform

2. DAC Platform.

3. Pre-built Informatica metadata content

4. Pre-built DAC metadata content.

5. Pre-built BI meta data.(.rpd)
6. Data Warehouse.

Informatica Power centre platform:

This informatica platform is a third party tool which is used to perform ETL operations, i.e. it will Extract the data from source then Transform into required format and Load the data into target warehouse.

Informatica Installation.

DAC Platform:

This platform is used to operate and schedule the Informatica workflows/Tasks and Data warehouse tables.

DAC 11G Installation

Pre-built Informatica metadata content:

Once you complete the BI Apps installation, will get a pre-built informatica metadata content 

(Oracle_BI_DW_Base.rep) which includes the pre-defined/Designed mappings,session and workflows. 

Once you restore this content into your informatica repository, then you can able to view or access the OOTB code. After informatica content restored you can see the different type of folders for each type source system and each type of version like Oracle Apps 11.10.5,R12.1.2,R12.1.3 etc... 

As shown below

It is suggested to copy the original folder and make it as custom one before going to change any modification to the give code. ( Look at the picture Custom_SDE_ORAR1213)

Here all the SDE prefixed folders are used to extract the data from different type of data sources like Oracle,JDE etc to staging area.

Pre-built DAC metadata content:

After completion of BI Apps installation, you will get the DAC metadata content as like informatica.
Which has the predefined subject areas,tasks,warehouse table etc..

After you import the predefined content into DAC repository, you are able to see the source container for each source and it version as like Infomatica folders.

It is better to copy the source container and make it as custom one, before going to modify the OOTB content. So OOTB content won't be disturbed.

Pre-built BI Metadata:

It is purely reporting content, which is contains reports and dashboards. You can deploy this predefined .rpd (OracleBIAnalyticsApps.rpd)

Data warehouse:

We can use Oracle, Teradata and Netezza etc.. databases as Data warehouse. Oracle provided the predefined Data warehouse table in BI Apps. From DAC we can create those data warehouse tables.

So all Staging, Fact and Aggregate table will reside on a single database/schema.

Convert Multiple Rows into Single Row.

Here we have scenario like this.


Source:

                                                         

Above is the my source table/data, now I want to convert that data into row wise. As shown in below target table.

Target:

Step1:

Import source and target tables into informatica designer.

Step2:

Create new mapping and drag this source and target into that mapping.

Step3:

Create expression transformation and configure that as shown in below.

And click on OK.

Step4:

Create an Aggregate transformation and configure as shown in below.

Click on OK.


Srep5:

Please find the below is the complete mapping screenshot.

Now you can create the session and run the workflow.

If you want mapping XML please mail to me/ do comment here.


You may interested in convert one row into multiple.
One row to multiple

Union Transformation with Flat file and Database Table as sources.

Hello Guys, today I am going to explain about Union Transformation and how it is working while both sources are different.

What is Union: Union is a set operator in oracle by using this we can combine the different queries.

Scenario: Here I'm going to combine the data from a Flat File and a Oracle Table which are having the same structure.

Step 1:  Here I consider the EMP table as Oracle table, so we need to create the Flat file(*.CSV) which having the same structure as oracle table.

Step2:  Now import both flat file and oracle table as Source by using informatica source analyzer.

Step3: Create a new mapping by using mapping designer. And then please drag both sources from source folder.

Step4: Now create a union transformation, after that create 2 new groups as we are going to union two sources. As shown in below.

After this now you are able to see 2 input groups and 1 output group. Please find the below screenshot.

Step 5: Now we need to drag the Columns from Flat File source qualifier to Union Flat File group and then drag the columns from Oracle Table SQ to Union Database table group. Then Connect the Union transformation output ports to target.

Please create the session and run the workflow.

Still if you are getting any issues please let me know.
If you need Mapping XML, do comment here or please send a mail to me.


You may also interested in below SQL logic.
Convert one row into multiple rows using SQL transformation.

Convert one row into multiple rows in Informatica by using SQL transformation.

Scenario 1: convert one source row into multiple rows of the target.

For this scenario, I have seen lot of examples by using Java trns and Procedure Trns. But those are some what difficult to implement/understand.

In this example, I am using SQL Transformation to do the same.

Source:                                                                                               Target

  Val                                                                                                      Val

---------                                                                                           ---------------                        

   10                                                                                                         1

                                                                                                                2

                                                                                                                3

                                                                                                                4

                                                                                                                .

                                 

Step 1:

Import the source and target tables into informatica designer.

Step 2: 

Create the SQL Transformation then create input and output links after that configure the SQL query as shown in below.

SQL Query: SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10

Step 3:

Connect the links between SQL Trans and target.

Then create the session and run the workflow. 

Now check the Target table, you would see the desired result. :) :)

If you want to get the mapping XML please leave a comment or send your mail id to me.

Details About Informatica Stored Procedure Properties

Stored Procedure is nothing but collection of SQL/PLSQL statements which can be executed in database.

Once Stored procedure was created, then we can call it in informatica by using Stored Procedure transformation.

• Stored Procedure is a Passive Transformation.
• It can be used as both connected and unconnected mode.

 Stored Procedure Type:

we can set the stored type as below

• Normal
  If stored procedure type is normal means, it is a connected procedure and it will expect input from other transformations and return the out put to other transformations or will be called in other transformations.

• Source Pre Load
  If we set the stored procedure type as source pre load, integration service will execute the procedure in source database before ETL load starts. In other words we can say before executing Source Qualifier, procedure will be executed without returning any value.
   

• Source Post Load
  It is also work like as Source pre load but after Source qualifer query execution, informatica integration service will execute the procedure in source data base.
  

• Target Pre Load
  if we choose stored procedure type as sTarget pre load, integration service will execute procedure in target database before the target load is about to start. Work like as Target Pre SQL.
• Target Post Load
  If strored procedure type is Target post load, informatica integration service will execute the procedure in target database after data load completed into target.

Different Type of Lookup Caches in Informatica

Cache: Cache is nothing but collection of data stored in a place.

There are mainly 4 types of lookup caches in informatica.

• Static Lookup Cache
• Dynamic Lookup Cache
• Persistent Lookup Cache
• Shared lookup Cache 

Static Lookup Cache:

When session execution started, static lookup cache will be created by integration service. Once the static lookup cache was build, then integration service will query the data from lookup cache instead of database.If the lookup condition is true then static lookup cache will return the data otherwise it will return null.

The main advantage is performance, because if once the cache is build then informatica never try to hit the database to fetch the data from lookup table. So execution time will reduce.

As well as the main disadvantage is, after that cache build any changes happened in lookup table those changes will not be reflected in static cache.In this case we need to rebuild the cache.

Once the session execution completed static lookup cache will automatically destroyed.

Dynamic Lookup cache:    

When we choose dynamic lookup cache option in session level properties, the existing cache will be updated when the lookup table updated. That means any inserts or updates happened on target table, same changes will reflect in dynamic lookup cache.

In other words simply we can say, dynamic lookup cache will always be in synchronization with lookup table.

Dynamic cache will also be destroyed when the session execution completed.

Persistent Lookup Cache: 

If we select persistent cache in session level properties, infromatica integration server will create the cache file for the lookup table, so it can be used in next time. So this cache can be reused by providing the cache file name in session level properties. Here integration service first will check the cache file existence, if file is already existed then it will use the file otherwise it will create a new file.

It will be very helpful when lookup table data is static.

Integration service won't delete the cache file even after successful session execution.


Shared lookup Cache: 


We can share the persistent cache to other lookups by providing the cache name and cache location. 

How to kill the session in Oracle

As we know, while  performing any DDL and DML operations on oracle table, oracle database puts the lock on corresponding table, after successful query execution it will automatically release the lock on that table.

If we interrupt the query execution, the lock still remains on the table. So we can't perform any DML and DDL operation on the same table.

So before we are going to perform any DML or DDL operations, we should kill the session to unlock the table. Below is the procedure to kill session/unlock table.

first identify the object id for your table by using following query.   
SELECT * FROM dba_objects WHERE object_name='EMP';

in my case OBJECT_ID is 83603.

Now check the lock, if exist.

SELECT sid FROM v$lock WHERE id1=83603;

in my case sid value is 3341

if the above query didn't return any value means, there is no lock on that table.

SELECT sid, serial# FROM v$session WHERE sid=3341;

here sid=3341 and serial#=226371


Now we need to kill the session by using above values.

ALTER SYSTEM KILL SESSION '3341,226371' ;

once the session was killed, we can perform any type of valid operation on oracle table.