In old days of OBIEE 10g/11g there was only one option to limits output of the measure column based on one or more dimension i.e. using filters. One can use constants, repository variables, and session variables, presentation variables to drive filter conditions based on parameter selected by the users or predefined by system. The whole purpose of applying pre-defined filter is to restrict data and allows user to select parameters and drive report based on selected values of dimensions and attributes.
With OBIEE 11g, introduction of hierarchical column and selection steps makes the data limitation altogether a different affair.
Recently I have been at customer site; the customer was using Financial Analytics based on OBIEE 10g, the foundation has been migrated to OBIEE 11g. One of the requirements from user was to restrict all financial data reports based on associated cost centre to the user to start with, and then user should able to drive report for selected cost centre.
In OBIEE 10g this is been implemented by using global filter and session variable combination. When user logs in to system, the initialization block execution populates a cost centre specific session variable which is been used to restrict the global filter value and hence data report. User can choose desired cost centre value from global filter which in turn override the session variable value and hence data report.
Now customer decided to use the hierarchical column instead of simple attribute column in global filter and in data report introduced in OBIEE 11g after successful upgrade from OBIEE 10g.
The issue is then can we implement session variable based data restriction with hierarchical columns in OBIEE 11g rather than normal column. Customer tried different options but could not able to figure out how to achieve the desired result.
This blog is an attempt to show that hierarchical columns can be restricted by session variable value and can be overwritten based on global filter based on hierarchical column.
Custom Dashboard Page
The dashboard page has a report with Time Hierarchy Column and Revenue as shown below.
The Selection Step has been set as Start with all members and option of Override with prompt has been chosen as shown. This is to make sure that the global filter values can override the hierarchical column values in the report.
A global filter on Time Hierarchy column is included in the dashboard page, the dashboard page with time hierarchy and report works fine, the results are as expected based on user selections.
Session Variable and Hierarchical Column
I have a session variable ‘ENTERPRISE_YEAR’ defined in repository, for purpose of this exercise I have set as 2011 by changing the init block. Now what I want to do is to restrict data of the report based on session variable value. I have started looking into options on how we can do so.
The first thing which I have tried is to set default value of the Time Hierarchy Column based on session variable ‘ENTERPRISE_YEAR’. Unfortunately it is not supported. By choosing Default selection > Specific Column Values we can restrict the default to set value e.g. 2009, but one cannot make it dynamic by choosing session variable as a specific value.
The second option which I have tried is set default value of time hierarchy column based on session variable. Select Members option allows me choose the specific value but does not allow me to set specific value as session variable value. So there is no direct option to use session variable to restrict report output.
After investing couple of hours I come up with the work around. The option is to choose
Selection Steps > Apply a Condition > Choose Condition Type > Match and then define new condition and choose option of override with session variable and write the session variable ‘ENTERPRISE_YEAR’ as shown below.
Create a new selection step based on selection step defined earlier.
The selection steps of the report should be same as below.
Report Dashboard Page – Before and After Prompt Values
The following report shows calculated group (Selection Step) is applied, hence the Session Variable value i.e. 2010.
Once prompt values are selected as 2010, 2011, 2012, the output is been overridden by prompt value selections as shown below.
The workaround of applying session variable value to hierarchical column as default value worked as per expectation. In short session variable works with hierarchical columns with some extra efforts.