Don’t forget the Logical Layer

I often see OBIEE RPD’s which are developed via “Drag ‘n Drop”. If you know what you are doing this hasn’t necessarily have to be a problem. It becomes a problem if you do not know what you are doing. Especially dragging and dropping multiple tables together can give you additional ‘functionality’, which you do not need. Sometimes you are not even aware that you added additional links and keys, you don’t need. It becomes even worse when it’s ‘functionality’ you don’t want. I know from experience that it can cause incorrect results, depending on the columns you select in your analysis.

Normally I build my Logical Models manually from scratch instead of via “Drag ‘n Drop”. This way I know exactly what I am doing and I don’t get surprised by unintended functionality. This is by no means a ‘Best Practice’, but it works for me. Let’s consider it to be a ‘Right Practice‘.

There is another reason for avoiding a “Drag ‘n Drop and leave the Logical Layer like that”-Construction of the Logical Layer. The Logical Layer is an important part in the construction of the actual Physical Query(s). The Oracle BI Server does not have to be a Black Box. You just need to give the Oracle BI Server as much information as possible so you know / understand, what query(s) the Oracle BI Server will fire to the underlying database(s). Pay attention to the following recommendations:

  • Dimension Logical Tables must have a Logical Key assigned, if possible a business related key (order number, purchase number, employee number). Fact Logical Table do not have a Logical Key assigned
  • Fact Logical Tables contain Measure Columns with an Aggregation Rule (SUM, COUNT, MIN, MAX, etc.) applied to it.
  • Define a Dimension Hierarchy for every Dimension Logical Table
    • Each Level is unique
    • The Primary Key of the lowest Level matches the Primary Key of the Logical Table
    • Specify the number of element per Level
  • Specify the Content Level for each Logical Table (Column)
    • Each Content Level ‘belongs’ to a Level in the Dimension Hierarchie
  • Construction of the Logical Table
    • (Re-) Naming Conventions
    • Only those columns you really need

The above listing is by no means exhaustive. There are few interesting links you can check out, when it comes to modeling the Logical Layer;

One important note has been made by Christian Berg; “Think and Understand before you give ‘Advice’“. The same goes when you are trying to apply things yourself. Don’t just do it, because you can. Do it because you understand why you have to do it that way.

Feel free to comment.

Update to the FAQ-section of the OBIEE – Sample Application (V406)

For those of you who are using or are interested in the OBIEE – Sample Application (V406), the following might be of interest to you.

As you can see in this Comments Section, an issue was raised regarding the expiry of some Oracle Database Users. Development has updated the FAQ Section.



Searching for Doc Id 1428008.1 on MOS

If you are searching online for information about multiple Authentication Providers in Oracle BI 11g, you could find references (even on my own blog) to Doc Id; 1428008.1 on My Oracle Support (MOS). Via this note you were able to obtain following: TechNote_LDAP_Auth_DB_Groups_V3.pdf.

If you try to find this note now, you won’t be able to find it. It seems that the note has been closed. You should be able to find the information you are looking for here (Configuring LDAP as the Authentication Provider and Storing Groups In a Database)


Setup Oracle eBS 2 Oracle BI SSO

I made several blogposts in the past. That’s why it could be useful to collect these posts into one document. This document covers all the steps needed to integrate Oracle eBS (R12) and Oracle BI (11g) with each other. SSO might be a little bit confusing. In this case it’s meant that you can log into Oracle BI via Oracle eBS without having to log in again and while maintaining context.

Oracle Data Integrator 11g Cookbook – Review

Somewhere in the summer of 2010 the first 11g R1 release of Oracle Data Integrator (ODI) became GA. ODI is the leading Data Integration platform within the Oracle Product Family. It seemed like a logical step that a book on ODI 11g would be launched. The Oracle ODI Product Management team have shared their knowledge into a book called; Oracle Data Integrator 11g: Getting Started. This book presents the audience with a step-by-step tutorial. In the end the reader should be familiar, comfortable and successful with (the new features of) ODI 11g R1.

After the first ‘Getting Started’ book a more advanced book on ODI 11g seemed like a very logical next step.  The Oracle Data Integrator 11g Cookbook is a collection of recipes which cover the more advanced topics in ODI 11g.

Oracle Data Integrator 11g CookbookWhere the first book is more an introduction to ODI, the aim of the second book will uncover the more advanced features of ODI. Some basic knowledge of ODI is essential if you want to get the most out of the this Cookbook.

The Oracle Data Integrator 11g Cookbook is nicely structured. In a chronological order, the various components of ODI are covered. From Installation, Security, Variables, (inside) Knowledge Modules, Advanced Coding, Advanced ETL Processing to a Catch-all chapter which reveals the little know secrets and gives answers to several FAQ’s.

Each chapter consists of several (related) recipes. Each recipe is a step-by-step explanation of a specific task. Sometimes you have to perform some actions to get ready for the (next) recipe. Next to that it’s explained how things work. Last but not least, the recipes go beyond the steps necessary to complete a task in the; ‘There’s more’- and the ‘See also’-sections of the recipe. Within the tasks you will find notes which give some additional information to the recipe. Screenshots, code-(snippets) and links to external sites (Documentation, Oracle Support, Blogs, etc.) complete the recipes.

Consider the Oracle Data Integrator 11g Cookbook as a written MasterClass. Probably even for the more experienced ODI consultants, this book will reveal something they didn’t know before. I guess the authors succeeded in writing a book which differs from the first one and is still worth reading.

For me personally, I this book gives me more understanding of the capabilities of Oracle Data Integrator. I just recently (seriously) started exploring ODI. I would say that in this stage the Oracle Data Integrator 11g: Getting Started better fits my knowledge, but the  Oracle Data Integrator 11g Cookbook definitely is next on my reading list.

Enjoy reading this book.

Integrating Oracle eBS Responsibility Profiles and Oracle BI 11g Application Roles

I have been blogging earlier about the integration between Oracle eBS R12 and Oracle BI 11g as well as inheriting the Oracle eBS Security in Oracle BI. Instead of making a Oracle BI Application Role for each Oracle eBS Responsibility you would like to use in Oracle BI, make use of an Oracle eBS Profile Option. You could define an Oracle eBS Profile Option (e.g. XXBI_SECURITY_PROFILE – Oracle BI Security Profile) and apply this to an Oracle eBS Responsibility. This makes it easier to maintain the Security Inheritance. When you add a new Responsibility to Oracle eBS, you do not have add this Responsibility to Oracle BI. Just make sure the Oracle eBS Responsibility gets the Oracle eBS Profile Option applied or define the Oracle BI Security Profile on Site Level as a default.

The Oracle BI Security Configuration for Oracle BI basically looks like this;

Oracle eBS - Oracle BI 11g Security Configuration


So instead of matching the Oracle eBS Responsibility to a Oracle BI Application Role, match an Oracle eBS Profile Option (assigned to an Oracle eBS Responsibility or on Site Level) to a Oracle BI Application Role.

You have to follow a few steps in Oracle eBS.

Lookup Type

Navigate to Application Developer – Application, Lookups, Common to add a lookup type with the different values for the Oracle BI Security Profile.

You can check the values via the following query:

select t.lookup_type
 , t.meaning
 , a.application_name
 , t.description
 from applsys.fnd_lookup_types_tl t
 , applsys.fnd_lookup_types b
 , applsys.fnd_application_tl a
 where b.lookup_type = t.lookup_type
 and b.security_group_id = t.security_group_id
 and b.view_application_id = t.view_application_id
 and a.application_id = b.application_id
 and b.lookup_type = '&XXBI_SECURITY_PROFILE'
 and t.language = '&LANGUAGE'
 and a.language = '&LANGUAGE'
select flv.lookup_code
, flv.meaning
, flv.description
from applsys.fnd_lookup_values flv
where lookup_type = '&XXBI_SECURITY_PROFILE'
and flv.language = '&LANGUAGE'
and trunc(sysdate) >= trunc(flv.start_date_active)
and trunc(sysdate) < nvl(trunc(flv.end_date_active), trunc(sysdate) +1)

Oracle BI Security Profile

Navigate to Application Developer – Profile to add a Oracle BI Security Profile

You can check the values via the following query:

select fpo.profile_option_name
, a.application_name
, fpotl.user_profile_option_name
, fpotl.description
from applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 , applsys.fnd_application_tl a
where fpotl.profile_option_name = fpo.profile_option_name
 and a.application_id = fpo.application_id
 and a.language = fpotl.language
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'

Add the following code as SQL Validation


into :visible_option_value,
from applsys.fnd_lookup_values fl
where fl.lookup_type = ‘Enter Lookup Type here’
and fl.language = ‘Enter Language here’
and trunc(sysdate) >= trunc(fl.start_date_active)
and trunc(sysdate) <nvl(trunc(fl.end_date_active), trunc(sysdate) + 1)”
COLUMN=”\”BI Security Profile\”(10)”


Assign Oracle BI Security Profile to Oracle eBS Responsibility

Navigate to System Administrator – Profile, System to assign the Oracle BI Security Profile to the Oracle eBS Responsibility

You can check the values via the following query:

Site Level (Default)

select fpov.profile_option_value bi_type_gebruiker_site_level
 from apps.fnd_profile_options_vl fpovl
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.profile_option_id = fpovl.profile_option_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10001 -- Site
 and fpotl.user_profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'

Responsibility Level (Specific)

select fr.responsibility_id
 , fr.responsibility_name
 , fpov.profile_option_value bi_type_gebruiker_resp_level
 from apps.fnd_responsibility_vl fr
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.level_value = fr.responsibility_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10003 -- Responsibility
 and fpotl.user_profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'

The remainder of the setup in the Oracle Enterprise Manager and the actual match via an Initialization Block is described here. The following query could be used to retrieve the Oracle eBS Profile Option and assign it to the ROLES-session variable

Oracle BI Initialization Block: GetApplicationRoles

select NVL ( rl.bi_security_profile_resp_level
 , sl.bi_security_profile_site_level ) bi_type_gebruiker
 from ( select fpov.profile_option_id
 , fpotl.language
 , fpov.profile_option_value
 from applsys.fnd_responsibility fr
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.level_value = fr.responsibility_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10003
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'
 and fr.responsibility_id = fnd_global.resp_id
 and fr.application_id = fnd_global.resp_appl_id
 ) rl
 , ( select fpov.profile_option_id
 , fpotl.language
 , fpov.profile_option_value
 from applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10001
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE' ) sl
 where sl.language = rl.language(+)
 and sl.profile_option_id = rl.profile_option_id(+)

Feel free to comment.

OBIEE 11g – Error selecting ”Coreapplication” in Oracle Enterprise Manager (ADF_FACES-60097, ADF_FACES-60096)

When we logged into Oracle Enterprise Manager (EM) and select ‘Coreapplication’ in the left pane , we encouter the error; “ADF_FACES-60097 : For more information, please see the server’s error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1”

This seems like a known bug so we followed the steps in Doc Id 1363602.1. Unfortunately that didn’t solve the problem.

Oracle Support advised us to increase the logging for the EM as shown in Doc ID 1464333.1. This resulted in Error Logging in one of the AdminServer-diagnostic Log Files. A Fatal Error occurred reading this file: ‘FMWhome/user_projects/domains/lnbisfoundation_domain/sysman/mds/partition1/ai/bi/fragments/mdssys/cust/user/weblogic/deployment.jspx.xml’

Investigating the structure of this file turned out that for some reason this file was empty (zero bytes). Removing the file and restarting the environment did the trick for us. The file got recreated and ‘Coreapplication’  could be selected again.

[Update 09/14/2015 – FMWhome/user_projects/domains//sysman/mds/partition1/ai/bi/mdssys/cust/user//instance.jspx.xml
can also be zero bytes. A delete and a restart can fix the EM.]

– Daan