ORACLE BI(OBIEE) Federation

Modelling Federated Data using Oracle Business Intelligence (OBIEE)

This is one of the most powerful features of OBIEE.

Data exist in many different places. Instead of moving data to a single database, the BI Server can access multiple databases simultaneously to answer a single query, regardless of whether the databases are from different vendors.

When the BI Server accesses heterogeneous databases, it handles automatically, database operations such as (sort and join ) and required transformation.

The BI server can access a large of data sources(Relational Database Systems, Oracle, Peoplesoft Enterprise, JD Edwards Enterprise One, Oracle e-Business Suite, and SAP R/3 and mySAP, OLAP Sources like  Hyperion Essbase, and SAP BW Infocubesetc.,.) included XML.

This capability is also called Federation/Multi-Database Access/Cross-Database Join.

In OBIEE terms, Data Federation involves bringing in the metadata from multiple sources into the physical layer of the RPD (BI Repository) and integrating the metadata into a single business model and possibly a single subject area.  In OBIEE, the data sources can be relational (OLTP databases or star-schema data warehouses), multidimensional (OLAP), or files (such as Excel or flat files); and the data can be of varying levels of aggregation in these sources.

When an Analysis (report) is run from Answers that uses data from these sources, the BI Server creates the appropriate SQL (OLTP) or MDX (OLAP) statements to retrieve the data from the appropriate source and then integrates the data for display to the user.  The user does not need to know what the source of the data is, how many sources there are, or how the data is retrieved.

There are two types of data federation in OBIEE. 

They are

1.     Horizontal Federation
2.    Vertical Federation

Horizontal Federation

Horizontal Federation allows us to generate a result set that is retrieved from both multidimensional and relational data sources.

Horizontal Federation involves setting multiple data sources for a common single logical table object in the Business Model and Mapping (BMM) layer of the RPD, such that the granularity of the data from the various sources is at the same level, and some columns of the single logical table come from one source, and some from another source(s) – basically each source adding columns (not rows) to the logical dataset.

Example: A typical scenario for this pattern is where there is related data in multiple sources for a particular subject, but no single source holds the entire body of data for that subject – and this helps to bring all the descriptive data for the subject together into one.

Vertical Federation

Vertical Federation provides the ability to drill through aggregate multidimensional data into detail relational data.

Vertical Federation involves setting multiple data sources for a common single logical table object in the BMM layer of the RPD, such that the data for each columns could be coming from multiple sources, but at varying granularity levels – basically each set adding rows (not columns) to the logical dataset.

Example: A typical scenario for this pattern is where data at an aggregated granularity is sourced from an aggregated OLAP data source or an aggregated OLTP data source, while data at a detailed granularity is sourced from the transaction level (OLTP or detailed-level star-schema) data source.

In this Blog, will see an example on Horizontal Federation by taking a well know scenario Employee as a subject. Let us consider two relational data sources (MySQL which contains DEPT table and another source as Oracle which is having EMP table (both table are from SCOTT schema)).

Step1: First step is to import the EMP Table from the Oracle Database into the repository and DEPT table from MySQL Database…

So, our repository should basically have 2 tables in 2 different databases as shown below.

Step2: Next step is to create a join between these 2 tables in the physical layer. In order to achieve cross-database joins, the first step is to right click on the EMP table and then click on Physical Diagram -> All Objects and Joins.

In the same way (ensure that the physical Diagram window is open), right click and click on view physical diagram on the DEPT table source. That will automatically bring both the tables inside the physical layer.

Step3: Now, create a database join between both the tables using DEPTNO as the join column.

Save it.

Step4: Now, create a new Business Model Cross DB join. Drag and drop both the tables (DEPT and EMP) from the physical layer into the Cross DB join Business Model.

Step5: Right click on Cross DB Join and click on view the Business Model diagram.

Similar to what we did in the Physical layer, create a foreign key join between DEPT and the EMP table based on DEPTNO column.

Step6: Once this is done, drag and drop the Business Model into the Presentation layer and save the repository. Ensure that your repository is clear of any warnings or errors.

Click ok

Click Yes

Click Yes

Step7: Close RPD and open analysis Page

Try to create a report with Subject Area: Cross_DB

Drag dname  column from DEPT folder and SAL column from EMP table folder from Criteria into analysis and click onResults tab…

The output is:

You can observe the query is hitting two databases

Pros and Cons

Advantages:

• ·         You can join multiple sources at any level of detail (no need to have a key at the most detailed level)
• ·         Little impact on performance – you are just bringing together two summarised queries
• ·         Quick to model and deliver value (compared to building a DW)

Disadvantages:

• ·         Relies on good data quality – the hierarchies have to be identical across all sources
• ·         Tougher to implement when there are lots of transformation rules or data cleansing requirements
• ·         Works well for summarised queries, but joining a low “detail” level will result in a performance hit

Hope it is useful…

Thanks…

Mary.