Data Federation, Horizontal Federation and Vertical Federation in OBIEE

Data Federation is the process of and implementation of integrating metadata from multiple data sources into a single metadata database to allow for the integrated reporting of the data from those multiple sources. 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.  This is one of the most powerful features of OBIEE.

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 common patterns of how data can be federated in OBIEE for reporting – these patterns/methods are termed Horizontal Federation and Vertical Federation. I will explain them here.

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.  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 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.  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.


The steps for creating a BI Publisher Report

This is a brief post that outlines the steps for creating a BI Publisher (Published Reporting) report that may serve as a pre-introduction to the tool.

BI Publisher is a great tool for creating and publishing pixel-perfect reports – for documents such as statements, letters, etc. It has a lot of great features that will take some time to master but getting started is fairly straightforward.  In OBIEE 11g, BI Publisher can be accessed directly from the main web interface that you see when you log into OBIEE, and you can get started directly from there.

Here are the steps …

1. Create a Data Source (if not already created) – or in other words, create a connection to a Data Source (such as an Oracle database)

2. Create a Data Model or select an existing one – defines the data used by reports (data will come from the Data Source above). The Data Model will include …

  • 2b. Create one or more data sets in the data model – the actual data selected
  • 2c. Create one or more List of Values (LOVs)  –  primarily for use in drop down lists
  • 2d. Create one or more parameters – to add flexibility to your report
  • 2e. Save Sample Data to data model (optional but important – report previews will display better when creating layouts)

3. Design one or more layouts for the report data (template file and set of properties for rendering template file) Layout can be created using MS Word, Adobe Acrobat, MS Excel, Adobe Flashm and BI Publisher Layout Editor). BTW, a report can include multiple layouts.

4. Configure properties for the report – this includes properties that affect the formatting and output of the report.

5. Add translations (optional) – this allows you to add translations for any text that is translatable in your report.

6. View the report using the report viewer – to make sure it looks good and is formatted correctly.

7. Schedule the report and set its destination (basically creating a Report Job) – set the time and report output type.

If you would like to get more details, a tutorial on Getting Started with BI Publisher can be found here on OLL.