Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Data Security setup using Session Variable Initialization Block fails with nQSError:23006 OBIEE 12.2

Received Response
56
Views
9
Comments
3822729
3822729 Rank 4 - Community Specialist
edited Aug 13, 2024 5:29PM in Oracle Analytics Cloud and Server

Hello Experts,

We have OBIEE 12.2.1.4.0 on Linux server. Trying to implement data security using session variable Initialization Block. Performed these steps -

1. Created Inti Block - "Authorization"

pastedImage_2.png

2.  Non-System session variable 

pastedImage_3.png

3.  Defined a Data Filter for BIConsumer Application Role using the session variable.

pastedImage_4.png

4. Also, I tested the Init Block by passing a valid username and was able to retrieve data from the SQL

pastedImage_5.png

When I login to analytics and run the query , I receive an error [nQSError: 23006] The session variable, NQ_SESSION.Reg_Cd, has no value definition. 

pastedImage_9.png

Can you please tell me where I went wrong in this setup. Any help is much appreciated

Regards

Rakesh

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Since you enabled caching for that variable - did you cache the variable without any value? Like when it was empty?

  • 3822729
    3822729 Rank 4 - Community Specialist

    Hi Christian,

    I am afraid I did . When I checked Row-Wise Initialization I think the cache got checked as well & I never gave a default value to the variable. How can I correct this ?

    Thank You.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Purge the cache. And gove the variable a default value so you never risk that error.

  • DPal
    DPal Rank 1 - Community Starter

    As per my understanding, you are trying to initialize multi-valued session variable. In this case, you need to only check the box "Row wise initialization" and "use caching" that u already did and importantly remove the target variable "Reg_Cd" that you defined.  Then, replace your initialization block query with the following query:

    SELECT 'Reg_Cd', R_CD from OIS_USER_INFO where username = ':USER' 

    This query will initialize the Reg_Cd session variable containing multiple values since you have checked row wise initialization.

    Now, I think it will work. Please let me know.

    Regards,

    DPal

  • 3822729
    3822729 Rank 4 - Community Specialist

    Thanks Christian. So, I purged the cache, unchecked the cache and gave a default value(04) to the variable "Reg_Cd" and  ran the query. I am only getting the default value back. Doesn't look like the Init Block SQL Query is executed. Please help me figure out what's missing. Regards.

    pastedImage_1.png

    pastedImage_0.png

    pastedImage_2.png

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Drop the whole init block and variable and recreate.

  • 3822729
    3822729 Rank 4 - Community Specialist

    Hi Christian,

    I deleted the entire Init block & variable. recreated again, but I could not get it to work. It would only show me the default value of the variable. Not sure if this is a bug . If I remove the default I get the error - [nQSError: 23006] The session variable, NQ_SESSION.Reg_Cd, has no value definition.

    I tried the other suggestion given by DPal & it seems to be working in my case. In the Admin tool context based help, it says we can use Row-Wise initialization to define dynamic session variables . I re-wrote init block query to get the variable name & the values from the sql table. and ran the test query in analytics successfully.

    Thank You.

    pastedImage_1.png

    pastedImage_2.png

    pastedImage_3.png

  • 3822729
    3822729 Rank 4 - Community Specialist

    Hi DPal

    Thank You for your suggestion. It seems to work for my need. Please see my previous update.

    Regards

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    My bad. I hadn't seen that you forced a variable name rather than getting it from the SQL.