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 !

Hail ! Microsoft Windows 7 – Official Release

Vast ! Yes , Gospel about good Internet world …. It is on 3rd day after Microsoft announces its official release pack for Windows 7 . Previously I was using Windows 7 build 7100 Ultimate edition . Now 7600 full and final OEM pack come in its way .I am not too familiar about the lingo in Microsoft world like … OEM , Beta , RC , RTM,Final Retail etc .But being a fond of typically testing any new versions of OS as and when come hence googling around if I could get any copy for 7600 build version (AFAIK , this one is full RTM release) ! Strange … So big the Open world ! Couple of pirated versions are flowing around for desktop home users with lots of lingo’s not known .Yes , definitely all those are at torrent , share , direct access vault,warez etc . I am not giving the link due to obvious reason of litigation .Bit confusing at first shot which one to download !!! But need to familiarize with the terminology to strap thorugh correct and latest one….

Caution !  It has been encouraged by Microsoft to purchase a genuine copy rather using a pirated version because pirating is exploiting the softwares and it is an identity theft  . Indeed !

After couple of experiment gather below info which could be helpful for someone and lemme put in gist :

Microsoft official Windows 7  launched on October 22nd , 2009 .

Windows 7 RC ( Release Candidate ) is the current officially available version although a lot of internal builds have leaked after that. Windows 7 RC is still available for download from Microsoft.

RTM stands for Release to Manufacturers. RTM means that the code for Windows will be finalized and partners will receive it and start preparations to launch new PCs. The version of a software product that is given to manufacturers to bundle into future versions of their hardware products. RTM versions are typically released to manufacturers before they are released to the general public so that the manufacturers can work out any bugs the software may encounter with hardware devices. The release of an RTM version does not necessarily mean that the creators have worked out all the problems with the software; there still may be more versions of the product before it is released to the general public.Windows 7 will hit RTM this August 2009 .

OEMs will receive Windows 7 RTM software images beginning approximately 2 days after official RTM release, as a little time is required to release and distribute these images. After that the Multilingual copy of OEM is build .

OEM stands for “original equipment manufacturer” and OEM software is a phrase that refers to software that is sold to computer builders and hardware manufacturers (OEMs) in large quantities, for the purpose of bundling with computer hardware. The third-party software that comes with your digital camera, graphics tablet, printer or scanner is an example of OEM software.The end-user license agreement (EULA) for fully functional OEM software may state that it is not allowed to be sold without the accompanying hardware.

The AIO copy is All in one copy where Windows 7 Home, Professional , Ultimate all editions ISO image will be available .(both pre-compiled for 32bit-x86 and for 64 bit i.e x64 hardware configurations ) .

Latest RTM build is : Windows 7 Ultimate Build 7600.20510 .However couple of RTM come with build 7600.16385 and it could be upgraded tp 20510 after applying the online patch available from Microsoft .Both of these are known to be Ultimate Retail(Final) x86 and x64 version which is actual sold copy from Microsoft .

Windows 7 Ultimate RTM product that is meant to be pre-installed by the OEM on new PCs to be shipped later this year(2009) .

Windows 7 E will ship in Europe without a browser due to EU legalities. A browser will be provided by the PC manufacturer on a CD or DVD or pre-installed by them on the PC.

Windows 7 N is similar to E but it doesn’t include Windows Media Player – again due to EU legalities.

Hardware requirements for Windows 7?
1 GHz processor (32- or 64-bit)
1 GB of RAM (32-bit); 2 GB of RAM (64-bit)
16 GB of available disk space (32-bit); 20 GB of available disk space (64-bit)
DirectX 9 graphics device with WDDM 1.0 or higher driver

It’s enough info right ? Now lets jump into it and grab the new features ! Thanks to Microsoft ….. and Enjoy your Windows 7 ! Cheers ..