query to restrict Business unit and organization code in parameter prompt in OTBI as per the user

Mazher Shaikh

Hi

I want to know if there is any method where I can restrict Business unit and organization code in parameter prompt in OTBI as per the user roles assigned
for example :

E.g., Suppose 3 roles are assigned to user role A, role B and role C. A and B are assigned US MFG BU USD and C is assigned to US BU USD then in LOV both BUs should be show up 

The user access should be as per all the BU access given at Manage data access for users setup for all the roles 

similarly for organization code parameter:
E.g.,If US MFG BU USD is selected at BU parameter, and user has access to Org US001450 and US001730 at Manage data access for users setup; but PO is only created against org US001730 only; then Org US001730 will only be available in the LOV

Can you please let me know if there is any way to address this.

FYI:It is for OTBI report

Nathan CCC

Hi,

Assume this is for either 1) an analysis in OTBI 2) a report in OTBI with a data model with a data set type sql query with data source Oracle BI EE or data set type analysis that reference path to analysis in 1).

HCM does have data roles. But because you said Business Unit then I will assume this is a subject area in ERP or SCM. In ERP/SCM it is not normal to use multiple job roles for data security like you have described. Instead you just need 1 job role R for any business unit not one for each business unit (role A,B,C). Then in page manage data access for users for users with this role R you grant security context business unit X (US MFG USD) to user A and B, business unit Y (US BU USD) to user C etc.

Now if you query a measure column from your subject area, for which the designer of the subject area has implemented row level security by this dimension, as per the data security policies in the job role R, for example, Business Unit, the system will append a where clause at runtime to do data security. For example, user A and B will see transactions for BU X but not transactions for BU Y.

Unfortunately, the designer of the subject area may not have implemented data security on the dimension itself, ut only on the facts, so if the query for your list of values in your prompt is single dimension, it will not join to a fact, so user may get all values even if they cant see any transactions for some of those business unit values. A workaround for this is to edit the sql in the prompt to add to the existing dimension attribute column also a fact measure column in the list of values to get a select distinct of business units etc currently used by all transactions or perhaps only a sample of them as this is obviously not great for performance.

Remember if your parameter in a data model in a report in OTBI is mandatory the data source for your list of values query for a parameter in a data model can be obiee (implementing data security) even if your main data set for the results is FSCM/HCM/CRM.