How to find database fields for an OBIEE report?

Often it is required to investigate what is the database source (table field) for an OBIEE report field(s) or to find out if in BI data model (repository) you can find a specific information(field).

To speed up the investigation process I have decide to export the BI repository documentation and in this way I'm able to quickly progress with my analysis and also to respond immediately to any kind of questions.

On one hand you have the OBIEE report with details about Subject area, Presentation Table and Presentation column and on the other hand BI repository documentation with complete details about Business Model, Logical Table, Logical Column, Logical Table Source, Database, Physical Catalog, Physical Schema, Physical Table and Physical Column.





If you have Oracle BIAPPS version 7.9.6.4 and you need to find what database fields not included into Physical layer you may take a look to Oracle Business Analytics Warehouse Data Model Reference . This is made up of three distinct pieces:
• The Oracle Business Intelligence Applications Naming Conventions and Domain Values Guide
• The star schema diagrams
• The table and column descriptions

This video will present below steps:
- Create a simple OBIEE report for required fields
- Connect to Admin tool and export repository documentation
- Open repository documentation and find database table and fields
- Query database table and fields using SQL developer

If you liked this article feel free to share it with your fellows. Thank you! 

For some other helpful videos check my YouTube Channel. 

To receive notifications for new articles follow me

Or subscribe by email

Email Address

How to Add eBS DFFs (Descriptive Flex Fields) to BI Apps (OBIA)?

BI Apps (OBIA) vanilla solution is not including "out-of-the-box" any Descriptive Flex Fields (DFF) available and extensively used in eBusiness Suite (eBS) modules for different objects (AP invoice, AR transactions, GL journal lines, etc...).

Almost all eBS implementations are storing supplementary information in DFF and most of the time this information is extensively used for reporting both in eBS custom reports or any other external reporting tools.

When you plan to implement BI Apps (OBIA) you have to include as an additional step/requirement to extract eBS DFFs by customizing out of the box solution and make them visible to end user (BI answer).

High level steps

For clients with BI Apps (OBIA) using Informatica:

1. Add new field(s) to database table
2. Customize Informatica
3. Customize repository

For clients with BI Apps (OBIA) using ODI:

1. Add new field(s) to database table
2. Customize ODI
3. Customize repository

This post will not give details about above scenarios, but also will explain a second option I've successfully implemented. This solution may be implemented quicker without any knowledge about Informatica or ODI as is based on BI repository customization.

I recommend this solution when you are running against time and you don't have closed to you any Informatica/ODI expert but you have around a BI admin expert.

Solution steps

Physical layer

1. Open repository on-line using BI Admin tool
2. Create a new physical database and create only one connection pool to point to your eBS database
3. Import table and desired DFF field(s) using metadata import functionality
4. Set cache persistence time for one day
5. Create joins of new added table to existing dimension

Business Model Mapping layer

• Add DFF field(s) to dimension and rename it with a business wise name

Presentation layer

• Add field(s) to each Subject Area where dimension is used 

Live Demo

Here is a step by step video.

If you liked this article feel free to share it with your fellows. 

For some other helpful videos check my YouTube Channel. 

To receive notifications for new articles subscribe to this blog.

Email Address