[OBIEE 11g] Prioritizing Logical Table Sources

Prioritizing Logical Table Sources

This is one of the new feature got introduced in OBIEE 11g which helps to dynamically choose the logical table source for a particular dimension or fact based on the user preference.

Its very important for a reporting tool to have option to dynamically choose the underlying physical sources. For example some times user may prefer to get data from the transactional data(Current Data) or from warehouse(Past Data) based on the requirement.

OBIEE 11g provides priority setting option on logical table sources which helps the BI server to choose the appropriate logical table sources at run time. Usually lowering priority number for a particular logical table source increases the chances to get queried. Typically ’0′ or ’1′ will be used to indicate ’0′ as current data and ’1′ indicates past data.

We can use this priority setting in following three ways

1.Standard usage of Priority
In this way we can simply prioritize the logical table sources by setting the priority number for each LTS.

Steps:

1.In RPD for each LTS set the priority group number

2.Permanently Reversing the Priority of Logical Table Sources
In this way we can reverse the priority setting in RPD for only certain subject areas. Its like we can build subject areas like one for current data and other one for historical data based on the same logical table.

Steps:

1.Create table in database which holds subject area name and reversal number (1 for reverse and 0 for normal) for which we have to permanently reverse the priority

2.Create a session initialization block and vector session variable like below.

3.Reversing the Priority based on the User Query
In this way we can reverse the priority setting in RPD by passing value like ’0′ (keeping the priority defined in RPD)or ’1′(reversing the priority defined in RPD) through a request variable.

Steps:

1.Create table in database which holds subject area name and reversal number (1 for reverse and 0 for normal) for which we have to permanently reverse the priority

2.Create a session initialization block and vector session variable like below.

3.While running query from answers set a request variable (0 for reversing or 1 for normal priority) which will dynamically shift the priority

You can try reversing the priority by passing the value to the request variable mentioned and try getting the past data in the “Sales Current” subject area.