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.
Customer Problem
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.
First Attempt
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.
Second Attempt
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.
Work Around
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.
Conclusion
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.
No comments:
Post a Comment