Oracle BIEE 11g 11.1.1.7.1 – Sub Totals and Pivot Calculations

February 8, 2015 Manish Chaturvedi

    This post is a reappraisal of another a while back by Venkat from RittmanMead, which can be read here. The original post had laid out behind the scene handling of Summary and Grand Totals in Pivot tables.

The main take away is that as of OBI 10.1.3 Sub Totals and Pivot Table calculations are frequently handled by the BI Server – with only the basic Select pushed to the database. Intermediate results used to derive the calculations and totals are found in tmp files on the BI Server, raising questions about both performance and scalability.

So has anything changed how OBIEE generates SQL (both Logical and Physical) as far as Summary Reports and Sub Totals are concerned?

OBIEE 11g: Implement Act As/Proxy

What is "Act As" Functionality?

One user can be enabled to act for another user in Oracle BI Presentation Services. When a user (called the Proxy User) acts as another (called the Target user), the proxy user can access the objects in the catalog for which target user has permission.

This functionality is useful for IT Support to troubleshoot object access problems reported by OBI users.

Levels of "Act As" Functionality

    Each Proxy User is assigned an Authority level, also called Proxy Level. This determines privileges and permissions granted to the proxy user when accessing target user's objects.

Restricted Proxy Level

• Proxy user only has Read-Only permissions to target user's objects.
• Proxy user are restricted by their own Privileges, and do not inherit Target users privileges.

Full Proxy Level

• Permissions and Privileges are inherited from Target user's account.

Cache Management in OBIEE 11g

   BI Server Query Cache

     Query Cache is a feature of the BI Server to save query results on the disk, to be later reused when      a similar query request arrives.

     

     As such Query Cache is performance enhancement feature which is transparent to the user,   

     It falls on the OBIEE administrator to ensure it is setup and managed in an optimal fashion.

   

   Keeping the Cache Fresh

   Problem:

If cache entries are NOT purged when the ETL has updated the underlying database tables, then the users see incorrect or old data in their Reports and Dashboards.

   Solution:

A well understood method of Cache Management is to configure Oracle BI Server Event Polling Tables. These tables track the updates to database, with a row inserted for each table which has been updated by the ETL process. The Oracle BI Server polls the table(s) at predetermined interval, removing the stale cache entries.

Hiding Subject Area Objects in OBIEE 11g

I plan to post entries which are along the lines of a Cookbook, this might evolve in a separate grouping within the blog. 

The tool of choice is Oracle BI.

How

·         Provide Hide Object If Condition in the General Tab of Presentation Column Properties.

Happy New Year 2015!

This Blog attempts to cover my thoughts on things Data Warehouse with particular slant towards Oracle BI/DW tools and techniques. 

It is an attempt to collate Data WareHouse  information, and hopefully learn and collaborate  with fellow professionals.

Here is to a New Year and New Endeavors!!!