Oracle Transactional Business Intelligence

Products Banner

Is there a way to limit OTBI Dashboard Prompt values based on security

Received Response


Is there a way to limit/restrict the OTBI Dashboard Prompt values based on security / data roles assigned to current user ? We are seeing that the analysis, when run separately, shows the data that is restricted according to the current user's access BUT we don't see the same behavior for the Dashboard prompts as they show 'all' the values.



  • You can try changing the Prompt to use a Logical SQL instead of direct column from subject area so that it adds a measure field to the sql. This will inject the data security on prompt.

  • A.JOSH
    A.JOSH ✭✭✭

    Thanks for the suggestion @User_ZTN0B

  • Have you tried using 'Choice List Values' as SQL Results? in the prompt itself.

    And have the SQL with restricted values ...Here is one sample example: instead of showing all year values from date dimension. I am showing only years that logged in user created an SR.

    SELECT "Time"."Year" FROM "Service - CRM Service Requests Real Time" 

    where ("Contact"."Email Address" = USER())

  • A.JOSH
    A.JOSH ✭✭✭

    Thanks for the suggestion, @Srinivas Malyala-Oracle I will try something on the same lines if I can get it working. The problem is that we don't want to use username explicitly to check/restrict the data as it may not be possible based on username as many data sets are controlled via roles.

  • A.JOSH
    A.JOSH ✭✭✭

    We got it working by doing something similar to this -

    SELECT  "Workforce Goals - Goal Status Overview Real Time"."Department"."Department Name" s_1,  "Workforce Goals - Goal Status Overview Real Time"."- Goal Management Process"."Count of Goals in Different Status within a Goal Plan" s_2 FROM "Workforce Goals - Goal Status Overview Real Time"

     The measure field will defer by subject areas/analysis but once you find it, you can use it in logical SQL like above and this will explicitly enforce the data security.

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭


    "Is there a way to limit OTBI Dashboard Prompt values based on security" Yes. DO NOTHING! This question assumes that a dashboard prompt has no data security. But a dashboard prompt DOES have data security. It does not bypass security in any way. It is just a logical query the same as any other analysis.

    Your basic dashboard prompts with a column prompt with "Prompt for Column" just a a presentation table column selected from a subject area with "Choice List Values" set "All Column Values" then this will be a logical query

    SELECT "presentationtable"."presentationcolumn" FROM "subject Area - Real Time" FETCH FIRST 65001 ROWS ONLY. 

    The metadata repository database (RPD) will add data security where clauses at runtime base on who you are with what roles and data access for users setup. But you do not have to take my word for it. You can see all this by go to page "Manage Sessions" /analytics/saw.dll?sessions then View Log to see the physical query(s) for the logical queries from the dashboard prompts people have used recently. In the information column will have Type=DashboardPrompt.

    Also you can set up a test case. For example create 2 users with same role which has a data security policy by business unit. In manage data access for users setup user A with access to business unit X and user B with access to business unit X. When the users use a dashboard prompt on business unit do they get the same list of values?

    As pointed out above data security is not always on the dimensions so a prompt may allow you to see in a list of values in a prompt a value for which none of the transaction facts you are allowed to see do have. So your question is not so much about data security but about how to I restrict my lists of values in dimensions to only the dimensions used by my facts and exclude dimensions that are not used by my facts - will return no data found?

    As ZTNOB change it to a query to which you "add a measure column" - to get only the distinct dimensions (but this will be less efficient in terms of seconds per query time taken to return results in the list of values)

    select distinct t.s FROM 
    (select all "presentationtable"."presentationcolumn" as s
    , "presentationtable"."presentationcolumn" as "measure_column - the one with the ruler icon"
    FROM "subject Area - Real Time" ) t

    Regards, Nathan

  • A.JOSH
    A.JOSH ✭✭✭

    Thanks for the insights @Nathan CCC. This helps.