Some Good OBIEE Architecture Framework

Lets explore some variants of OBIEE Architecture document prepared as below . Credit to the Authors !

OBIEE Architecture 1


OBIEE Architecture 2


OBIEE Architecture 3


OBIEE Architecture 5

Some more will be in its way …..Keep watching 🙂

OBIEE Modeling Best Practices

Some more points are going to be added to my good book of Best Practices as below : This could be considered as amendment on existing Best practice that I already had posted few weeks back .See here

Physical Layer
• Eliminate all Circular Joins that either cross dimensions or are contained within a single dimension. Use Physical Layer table Aliases to duplicate certain tables and modify joins so that the Circular Join is removed. (Should have 1 less join than there are tables in physical SQL generated)
• Do not include filters in the Physical layer when creating joins – instead build them into the Business Model on the Logical Tables Sources
• Never model Fact-to-Fact Joins. The proper modeling technique is to let analytics choose more than one fact table in a sub query, and let the Analytics Server or the Database join the result set. (Drill Across scenarios)
• Cross database joins should not exist. (Performance Issues)
• Make all tables “cacheable.” Set cache persistence time as “infinite” and utilize polling tables for refreshes

Business Model Layer
• Only use 1:M complex joins between Logical Dimension Tables and the Facts.FK Joins limits the flexibility and power of the SQL generation engine.
• Identify the base granularity of a logical table, both Dimension and Fact. Only add physical tables to the logical table source (LTS) that are at the same or higher levels of granularity
• Ensure that all levels of a hierarchy have an appropriate value for the Number of elements field. This will enable the Analytics server to select the most optimal pre-aggregated fact tables to use
• Avoid outer joins within LTS as they are always included in the query, even if it is not used. If necessary, create one LTS without the Outer Join and another with the Outer Join. Order the Outer Join LTS after the Non Outer Join so that it will be used only when necessary.
• Always verify the SQL generated is correct.
Presentation Layer
• Ensure that aliases for Presentation layer columns and tables are not used.Aliases are useful when making changes to a production system, but for a first time deployment should be removed.
• Structure Presentation Catalog around the Facts. Make sure that only the dimensions supported by the Facts are part of the Presentation Catalog
• Avoid technical labels in the presentation Layer.

• Minimize number of Initialization blocks. Combine where possible
• Only use Logging level > 0 during development and debugging
• Move as much of the query logic to the ETL as possible to improve system response time. Pre-calculation of additive metrics and attributes will reduce query complexity and therefore response time
• Denormalize dimensions and Fact table FKs as much as possible to reduce joins
• Try to eliminate all outer joins in a Data Warehouse by doing ETL lookups and replacing with default (commonly ‘Unknown’) parents. This will simplify the Business Layer, ensure a consistent record set, and improve performance
• Design Business Model Layer to support ad-hoc reporting instead of catering to specific reports. This will enable end users to perform their own analysis and create their own reports as needed

OBIEE Performance Improvement using Database Tuning

There are couple of way to improve the performance of OBIEE request based on the underlying database servers and its object configurations :

Reorganizing Indexes : Multiple Upsert (Update/Insert) and Desert (Delete / Insert) operations on database object invalidate the existing index and index need to be rebuild after successive load to ensure that the query hitting the index should return optimal performance .

Drop Index : Since Index occupies the database storages ,so any unnecessary index will slow down the query performance and degrade update operations .

Creating B-Tree Index : Create indexes to speed-up access times and improve join operations . Create B-tree indexes on dimension table columns that have a large number of distinct values. This will speed-up access time .Typically this kind of index needed on top of which OBIEE prompt has been built . However join operations have been speeded up by creating multicolumn B-tree indexes on foreign key reference columns of fact tables, and multicolumn B-tree indexes including the primary key and selected columns from the dimension tables. Indexes should be designed sensibly and ideally should not contain more than 5 columns.

Creating Bitmap Index : Creating Bitmap indexes on dimension columns that have few distinct values will speed up the access times . These are most effective when queries constrain multiple columns which have bitmap indexes . However the join operations could be accelerated by Creating bitmap indexes on foreign key reference columns where the index value has a low degree of selectivity (more than 3 – 5% of the table values on average per value) .

Degree of Parallelism : If there is sufficient computing resources, database server need to be configured such that optimal degree of parallel processing could be achieved .Given sufficient resources, this can greatly improve query response time.

Disk I/O Traffic : Considering the network traffic and bandwidth the optimal performance could be achieved if the Disk has been put into RAID , LVM and the database also put into Logical Cluster like Real Application Clustering (RAC) from Oracle front .

OBIEE Save Current Selection

This OBIEE feature is known for User friendliness . Using save selection allow users to view the dashboard page with their most frequently use choices like what we have done while Bookmarks any URL in browser for ease of navigation and facilitate quick search .It applies typically for the prompts and then press Go to let the Request/Report filtered out based on the selected Prompts and then Save the Selection using any conveninent name for future access .See below :

Save Selection 0

Let  say I have saved this as ‘Germany’ .

Save Selection 1

You can see the “Saved selection” entry has been created under         “root\users\administrator\_selections”  as below :

Save Selection 2

Save Selection 3


Thus making the alteration in your filter criteria you can choose save selections for multiple items .

You can clear the existing saved selection from Page Options -) Clear My Selections .Note that clearing this doesn’t physically remove the saved selection .It is the way to logically clear the selection .I have clear the selection and after that to apply it once again we need not to save it once again as you can choose ‘Apply Saved Selection’ .

Save Selection 4

To physically delete a selection you need to click ‘Page Options’ -) Edit Saved Selections and Defaults and delete the already saved selection .

Save Selection 5

You could rename the already saved selection here .So whatever changes you do over here it will be reflected directly to the physically stored file at location “root\users\administrator\_selections” .

You could perform “Clear My Selections” functionality using a HTML based button added across all dashboard pages so that users need not to navigated across Page Options and the HTML is as below :

Save Selection 6

Hierarchy Aggregation – A Best Practice

Another topic could be added in the goodbook of Best Practice .In the OBIEE hierarchy, starting from the top and moving down. At each level below the Grand Total level, double-click the level. In the field Number of elements at this level: enter 10 and increase in increments of 10 through all levels in the hierarchy. This is necessary to avoid the below Hierarchy errors and let BI server optimizes the aggregated query :

[nQSError: 15001] Could not load navigation space for subject area Student Enrollment.

[nQSError: 15019] Table <Logical table name> is functionally dependent upon level <Logical level>, but a more detailed child level has associated columns from that same table or a more detailed table.

This message occurs when either a key is defined under the Total level or when the first child level below the Total level contains two keys. Right-click on the Total level and select Properties. The Keys tab should be grayed out. If it is selectable, check to see what is there and delete it. Go to the next level down, right-click, select the Keys tab. Only one Key should appear. Delete the key that does not belong.

OBIEE Protect Filter – A precautionary step

The feature was already there from Siebel Analytics 7.9 versions or probably from its earlier releases . We need to take some extra precautions before applying this in request filters which can be adopted as best practice in report development .

This ensures that the filter used in the request is not lost or overwritten by another filter or dashboard prompt that may supersede the request.
This option is only available if a value has been specified in the filter. If the filter item is set to “is prompted” then the Protect Filter option if not available.

To apply it on the filter in the Answers Request, select Filter Options button > Protect Filter.  See below :

BI Catalog Files ! How it works – The Security & Privileges

As we all know that comparing to its previous counterpart i.e Siebel where Catalog file stored into a single .webcat file ,Oracle put away the concept of single file rather all the catalog components including the privileges to them has now been stored in the disk(OS file system)  itself as a Files and Folders. Reason is simple maintainability , scalability and ease of migration .

File/Folder name is based on OBI display name of dashboards , pages etc .The URL has been encoded all in lower case i.e.

Object Name => object+name

Every file and folder of the catalog has now being associated “.atr” file
– object+name
– object+name.atr

For e.g :  Dashboard Ranks & Toppers become “ranks+&+toppers”  folder inside “_portal” and having associated “ranks+&+toppers.atr” file  to hold the permissions .

Even if the users , groups have associated permissions maintained under “/<root>/system/security/users” and “/<root>/system/security/groups” .

Apart from that,  other system related global privileges maintained under below files :

– /catalog
» /changepermissionsprivilege
» /changepermissionsprivilege.atr
» /maintenancemodeprivilege
» /maintenancemodeprivilege.atr
– /generalprivs
» /global+admin
» /global+admin.atr
» /global+answers
» /global+answers.atr
» /global+portal
» /global+portal.atr
– /security
» /administerprivs
» /administerprivs.atr
» /takeownershipprivs
» /takeownershipprivs.atr

Lets tamper how this privilege files have security byte assigned to it by using Linux command “xxd” of dumping binary file to Hex :

a) Privilege file :

– The number of accounts granted this privilege is located at byte 12.
– The account list starts at byte 13.
» Each account listed contains 13 bytes
» The first 2 bytes always seems to be 00 01
» The next 8 bytes are the HEX ID of the account
» The next 2 bytes determine if the privilege is granted or explicitly denied
◊ FF FF – Granted (for the first entry in the list)
◊ 01 00 – Granted (for other entries in the list)
◊ 00 00 – Explicitly denied
» The next byte always seems to be 00

b) privilege.atr file
– Byte 5 contains the length of the display name.
– Byte 9 is where the display name starts.

c)  object+name.atr file
– Byte 4 Contains the length of the object name that starts on Byte 8
– Byte 8 Start of the name of the object in nice form, including caps and spaces.
– Byte (11 + value of Byte 4) – Contains the HEX ID of the owner of this object – 8 Bytes
– Byte (19 + value of Byte 4) – Contains the number of permissions that have been assigned, in our case to groups.
– Next, each of the permission is represented in a 13 byte block.
» The first 2 bytes seems to always be 00 01
» The next 8 bytes of the 12 byte block contains the HEX ID of the user or group.
» The next 2 bytes of the 12 byte block contains the permission granted.
◊ FF FF – Full Control
◊ 0F 00 – Change/Modify
◊ 03 00 – Read
◊ 02 00 – Traverse
◊ 00 00 – No Access
» The last byte seems to always be 00

Thanks to Calpoly for these amazing info !