Tag Archives: OBIEE tutorial

OBIEE Prototyping

Today I read an article by Jeff McQuigg (a leading Oracle Business Intelligence expert) who wrote about often overlooked topic – prototyping in OBIEE. I think it’s a great way to deliver OBIEE solution fast and effectively. There might be several organizational issues – such as users’ good will and availability, knowledge of the data, and scheduling meetings – but in general – I think it’s great way to proceed. And I think such a meeting would look like this:

Stay well!

OBIEE topics of interest

Good morning All,

I’m back from Florida vacation. What a change – to come from sunny Florida to cold and raining New York.

Trying to see what’s going on in the OBIEE world. Couple interesting topics on the OTN:

Write back – topic – very interesting and growing

Example on how not to ask questions about complex joins – the author should have provided all the details in the beggining

Not using AGO and TODATE functions – I don’t know why anyone would like to avoid using AGO and TODATE functions for time-series analysis. The only two options are: non-Oracle DB which doesn’t support complex PARTITION BY queries or someone doesn’t want to create time dimension.

Also, there’re some very good discussions on the OBIEE Enterprise Methodology Group.

Good luck and enjoy your week.

OBIEE expert forum is up and running

I’m delighted to announce that a new useful OBIEE forum / discussion board is open. Idea was up in the air for a long time – and it seems as multiple people planned this for awhile independently (rnm1978, Mark Rittman, Adrian Ward, and other guys and myself included). Part of it was frustration with moderation on official Oracle’s board, and part of it was a need for a resource where OBIEE and business intelligent professionals could share secrets of the trade. The difficult part is keeping balance between sharing knowledge as well as  providing a moderated environment.

The board is already active with several interesting topics going. If you’re interested in participating – please apply at Google OBIEE group

Thanks to Mark Rittman who organized the movement and to all those who agreed to become moderators.

OBIEE issues – you asked I answer (find answer)


I’m back after being busy with some really interesting issues at work.  I found a minute and I’d like to post few items.

First of all – the question “In OBI, How to move System Wide default settings from one environment to another?” comes up a lot – here’s an official response that I found on the knowledge base:

System wide default settings cannot be moved from one environment to another by design. The recommended way to move any other changes is to perform an Archive and then an unarchive. But, this method does not work for System wide default settings

The following Bug has been logged to make this feature available in the future releases:

So now you know what to tell your project manager.

Second item I found interesting is:

“How to reduce the space between multiple pivot tables placed side by side?”

1. Provided that s_oracle10 is in use; backup the following files before editing them
– C:\OracleBI\web\app\res\s_oracle10\b_mozilla_4\views.css
– C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\b_mozilla_4\views.css

2. Set the following section in both files above:


3. Restart the OC4j and the Presentation server
Third item,
“When defining multiple connection pools for a database in the physical layer of the rpd, is it possible to make the queries use the 2nd connection pool because current behavior seen is that the 1st connection pool is used by default for all queries?”

There is currently no way to make a query use a specific connection pool. The following bug has been raised to address this requirement- As you are already aware, current workaround is to have separate database defined in physical layer :


Customers requirement :

They have a new application that need connect to mutiple database, so under
physical layer root have two connection pool.
So under Database in physical layer 1st connection pool points to an oracle
instance and second connection pool points to a different oracle instance.
When we run report in answers, the connection information is always taken
from the 1st connection pool by default – hence we get table cannot be found
ora error. Reason being the table exists in the database instance referenced
in the 2nd connection pool.
When we do view data in RPD , the results return fine – this is because while
clicking on view data, we explicitly have to select the connection pool.
While creating the logical table/column, we only specify the database name
and the schema name and the table name- no place to provide connection pool
info – hence the server chooses the 1st one by default.
Need a way to fix this – current workaround suggested is to have multiple
database created in physical layer.

Thank you wonderful Oracle support representatives. You’re doing great job – often overlooked, but appreciated nevertheless.
Have a safe OBIEE day

Several new bugs

Happy New Year! It’s been a busy time, however, I’m back with some new posts. I wish the readers the best of the best of the best :-) I wish that your repository always stays consistent, that your physical layer is a true star, and your users never have problems with BI Answers.  I’ve digged up a few things that might be interesting:

Issue 1

Duplicate records are being eliminated from the Answers request results, when the desired results would be to include all rows, including duplicates.

The duplicate rows can have both the same case or different case.  Setting the case sensitivity parameter has no impact.


One row may be:

Another row may be:
dinner or Dinner

The second row is not included in the Answers results.  Running the same SQL directly on the server does return all rows, including duplicates.  Analytic server is removing the duplicates.


Analytic Server issues the distinct clause.


Include the table key column in the request.  Hide the column so it is not actually seen in the request results.  This will make each row unique and thus each row, including what would be considered duplicate, is included in the results.


Item 2

How to configure External Db Authentication that supports OBIS Impersonation (i.e. Delivers, BI Publisher)


To implement external DB authentication (i.e., authentication via a user/pw record in a database), you should NOT use AUTHENTICATION_TYPE = DATABASE. Instead, define it exactly like the Siebel Integrated setup in SiebelAnalytics.rpd’s/EnterpriseBusinessAnalytics.rpd’s Authentication init block. That is:
Use the default NQS Authentication type in NQSConfig.ini by commenting out all authentication types.
Define a separate physical database in the rpd which has a connection pool configured to hit the external database used which has the defined OBI users.
Enable Shared logon for this connection pool; set User name to :USER and Password to :PASSWORD.
Only the connection pool configuration is required for this physical database – no table/column objects are required.
Define an Authentication Session initialization block to set the session system security variable ‘USER’ to be used for OBIS External DB Authentication.
Point the initialization block to the connection pool defined in step 2.
Use an initialization string like “Select ‘:USER’ from external_table WHERE USERID=':USER’.
Define the ‘Edit Data Target’ to use system security variable ‘USER’ – this will make this initialization block the defacto OBIS authentication init block.
Define all connection pools used for backend data access to use ‘Shared logon’ with an explicit database user/password with at least read access to all backend tables.
This configuration supports OBIS Impersonation (i.e. Delivers). Database Authentication will be done via the connection pool defined in the Authentication init block. The user / password entered in an OBIPS client browser session will be passed to OBIS via the :USER / :PASSWORD system session variables. These values will then be passed to the database for user validation – if a database session is established using this user/password combo, the user will be considered authenticated to OBI.

Also, it does not require all OBI users to be defined in the rpd. In fact, remove all the OBI users from the rpd that will be authenticated via a database user entry. If you require OBIS Group Security assignments for users, you should use a separate external table (either existing or new) that stores the user-to-OBIS group associations, and make the associations via a session initialization block. Again, similar to the SiebelAnalytics.rpd’s/EnterpriseBusinessAnalytics.rpd’s Autherization initialization block.