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.