|
Thursday 29 November 2012
Wednesday 28 November 2012
Setup Oracle BIA 7963 – Data Security
Setup Oracle BIA 7963 – Data Security
Customer wants to
secure their data based on things like Ledger, Company, Operating Unit, etc.
All users who login via Oracle eBS should inherit (based on the current
responsibility) the same security settings in Oracle BI. The following is a
possible solution.
Environment
§ Oracle eBS R12.1.1
§ Oracle BI EE 11.1.1.6.0
§ Oracle BIA 7.9.6.3
Setup
The setup consists of
a few different parts
§ Data Security
Data
Security
The Data Security is
based on different Roles and a Profile Option assigned to the responsibilities
in Oracle eBS
Oracle
eBS
§ Create ‘BI Type User’-profile option
§ Assign ‘BI Type User’-profile option to
Responsibility
§ Assign Responsibility to User
Each Responsibility has either a specific ‘BI Type User’-profile
option or a ‘BI Type User’-profile option on Site level. A view (xx_obia_user_groups_v) in Oracle eBS ‘holds’ the profile option information.
|
01
|
select fpov.level_value
responsibility_id
|
|
|
02
|
,
fpov.level_value_application_id application_id
|
|
|
03
|
, 'OBIA '
|
|
|
04
|
||
fpov.profile_option_value autorisatierol_code
|
|
|
05
|
, fl.meaning
autorisatierol
|
|
|
06
|
from applsys.fnd_profile_option_values
fpov
|
|
|
07
|
,
apps.fnd_profile_options_vl fpo
|
|
|
08
|
,
apps.fnd_lookups fl
|
|
|
09
|
where fpo.profile_option_id
= fpov.profile_option_id
|
|
|
10
|
and fpo.application_id
= fpov.application_id
|
|
|
11
|
and fpov.profile_option_value
= fl.lookup_code
|
|
|
12
|
and fl.lookup_type
= 'BI_TYPE_GEBRUIKER'
|
|
|
13
|
and fpo.profile_option_name
= 'XXBI_TYPE_GEBRUIKER'
|
|
|
14
|
and fpov.level_id
= 10003
|
|
Oracle
BI
In Oracle BI, there is a Initialization Block which populates
the; ROLES Session Variable
|
01
|
select ( select sector
|
|
|
02
|
from apps.xx_obia_user_groups_v
|
|
|
03
|
where responsibility_key
= 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
|
|
|
04
|
and responsibility_id
= valueof ( nq_session.oltp_ebs_resp_id ) )
|
|
|
05
|
|| ';'
|
|
|
06
|
|| ( select autorisatierol_code
"ROLES"
|
|
|
07
|
from apps.xx_obia_user_groups_v
|
|
|
08
|
where responsibility_key
= 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
|
|
|
09
|
and responsibility_id
= valueof ( nq_session.oltp_ebs_resp_id ) )
|
|
|
10
|
|| ';'
|
|
|
11
|
|| ( select responsibility_key
"ROLES"
|
|
|
12
|
from apps.xx_obia_user_groups_v
|
|
|
13
|
where responsibility_key
= 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
|
|
|
14
|
and responsibility_id
= valueof ( nq_session.oltp_ebs_resp_id ) )
|
|
|
15
|
"ROLES"
|
|
|
16
|
from DUAL
|
|
Oracle
Enterprise Manager (EM)
In the EM ‘all’ the
different Application Roles, related to the Data Security are created.
There are a few
different Application Roles;
§ Out-of the box
§ Data Security
§ eBS Profile
Out-of-the-Box
§ BIAdministrators
§ Administration privileges.
§ BIAuthors
§ Create, use or consume content.
§ BIConsumers
§ Use / consume content,
§ Every authenticated user.
§ BISystem
§ Component connections between products.
Data
Security & eBS Profile
Oracle
BI Administrator (Identity Manager)
In the Identity
Manager, the Business Model Filters are applied to the Data Security
Application Roles
The Business Model
Filters are based on the Initialization Blocks. Some out-of-the-box, others
custom.
This setup should be
sufficient to apply Data Security to all queries, which query the Logical
Tables with the Business Model Filters applied to it.
Note: Application roles data filters won’t apply for
users with BI Administrator role.
By definition the
BIAdministrator application role is granted the
“oracle.bi.server.manageRepositories” permission, which is equivalent to the
10g “Administrator” user who also had unrestricted access. Hence, data filters
won’t affect users with BIAdministrator Role. (source: Oracle Support)
Measure prompt
Taking the reference of this OTN Post I thought of playing around prompts and measure columns.
The exercise is based on Sample Sales Lite SA ( XML Based SA )
Environment- OBIEE 11.1.1.6.0 ( Windows 7 -32 bit /Tested on IE 8 )
SA- Sample Sales Lite
Create an analysis of 3 columns namely
1> "Time"."Per Name Year"
2>"Base Facts"."Revenue"
3>"Base Facts"."Revenue"
I would rename the columns ( no. 2 & 3 ) as shown in the snapshot below.
Column formula of column no 2
Column formula of column no 3 ( You can have complex calculation - In the given example I have used a very simple one )
Save the report. [R1]
Go back to home page and once again navigate to Sample Sales Lite SA.Take 2 to 3 measures for example in the analysis and capture their names from the respective column formulae.We would need this while creating a dashboard prompt. Look at the below snapshot.
"Base Facts"."Revenue"
"Base Facts"."Billed Quantity"
"Base Facts"."Discount Amount"
Now go to dashboard prompt editor.Select the option Variable Prompt.Add the details as shown in the below snapshot.
Save the prompt and pull it on the dashboard with the report R1.
Select the first measure ( Revenue ) and hit apply.You will see the Revenue measure values getting populated in the second column while values of 3rd column would base upon the 2nd column that we select from the prompt.
You may find blogs on changing the column name dynamically.
I have to test this solution against an SA based on a relational database and perform multiple rounds of testing. Try it out - You may find some even more interesting facts. Good luck.
Subscribe to:
Posts (Atom)