Category Archives: Performance Tuning

Is it possible to use Dashboard Prompts to override Session Variables?

This is a two step process:

1) Tick “enable any user to set variable” checkbox in the RPD for the session variable
2) When you create a dashboard prompt, in the Set Variable list, choose to populate a variable for the dashboard prompt using a server request variable.

If you set a server variable it will override explicitly the value of this variable set via the initialization block.

The variable is changed in the dashboard that uses the prompt( where the variable is set).

Is there a way to specify an outer join in the physical layer

The dropdown box is disabled when I open the join dialog.

For the benefits of others readers, the customer wanted to know if it is possible to specify an outer join in the physical layer. Actually it is not possible explicitly specify an outer join in the physical layer. However, is possible to do this in the Business Model layer at either the “Business Model Diagram” level or within logical sources. Once done in either of those areas, the Analytics Engine will pass that join type into the generated physical SQL. The behavior of the two types of joins is:

– Full Outer Join defined in the “Business Model Diagram” between Logical Tables A & B – join will be performed between all foreign key relationships for the set of logical sources in logical tables A & B.

– Full Outer Join defined in a Logical Source – join will be performed only on the foreign key relationship chosen to define the join on.

The database features options for LEFT_OUTER_JOIN_SUPPORTED and RIGHT_OUTER_JOIN_SUPPORTED relate to whether the generated SQL will contain that syntax.

Users missing cache

If your users are missing cache, you might find the service request below helpful.

Description:
User1 one and User 2 have the same profile but User 2 is not hitting User 1’s cache even though the physical queries are exactly the same.

Cause
Customer had implemented data-level security. This resulted in different SQL for each user.

Solution
Cache hits are not based upon the physical queries.
They were using session variables in the connection pool login/password fields. Each different login has a different query, the issuer of the query is different, this results in the cache being specific to a user / session variable.
They modified the connection pool to use a shared login/password and this resulted in a cache hit.

Cache persistence

There’re a few well-written articles on about OBIEE caching configuration and strategies already. However, I’m posting a solution that I’ve found while investigating an unexpected behavior with tpl cache files disappearing for no reason (no updates in polling table, no manual purge, no BI services restart). Here’s the solution from Oracle:

The behavior was resolved when customer modified the refresh interval for the Repository variables that exist in their Repository.
When a dynamic repository variable is updated, cache is automatically purged. This is designed behavior. Cache will be invalidated (i.e. purged) whenever the initialization block that populates dynamic repository variable is refreshed. The reason that refreshing a variable purges cache is that if a variable was used in a calculation, and the variable changed, then cache would have invalid data. By purging cache when a variable changes, this problem is eliminated.

Since this is the designed functionality, Change Request 12-EOHPZ3 titled ‘Repository variable refresh purges cache’ exists on our database to address a product enhancement request. The workaround is to go through the dynamic repository variables and verify that the variables are being refreshed at the correct interval. If a variable needs to be refreshed daily, there may be a need to set up a cache seeding .bat file that runs after the dynamic variable has been updated. If the cache seeding .bat file runs prior to the refresh of the dynamic variable refresh, then the cache will be lost.

I think they should try to cover similar thing in OBIEE documentation.