7 Replies Latest reply on Jul 30, 2015 3:18 PM by Gianni Ceresa

    assigning system session variable to a dynamic repository variable obiee 11g

    asim cholas

      Hello all

      Is it possible to assign a system session variable (:USER) in initialization block of a dynamic repository variable?

       

       

      Something like below

       

       

      Select country from DM_users where user_name=':USER'

       

      Should we use NQ_SESSION.USER?

       

      It is possible with a non system session variable. But we have certain issues to create session variables in our environment. Any help will be highly appreciated

      Thanks

      Aj

        • 1. Re: assigning system session variable to a dynamic repository variable obiee 11g
          Christian Berg

          AJ,

           

          How can something with a user context be a repository variable? That's a session variable you want to create there.

          • 2. Re: assigning system session variable to a dynamic repository variable obiee 11g
            asim cholas

            We want to get the country name from a table which is in database. But we have restrictions to create a session variable in our environment. So we are trying to assign the value of session variable in the query. :USER will have value when a user logins to the system. So is there a way i could use it in the initialization block of the repository variable?

             

            thanks

            Aj

            • 3. Re: assigning system session variable to a dynamic repository variable obiee 11g
              Christian Berg

              You're not getting my point:

               

              REPOSITORY variables do not have user context! That's what's a SESSION variable does in OBI

              1 person found this helpful
              • 4. Re: assigning system session variable to a dynamic repository variable obiee 11g
                Gianni Ceresa

                To say it in other words: "do not have a user context" =  a REPOSITORY variable is shared between users, all the users share the exact same value. A repository variable will never act as a session variable just because of that point, it's shared....

                1 person found this helpful
                • 5. Re: assigning system session variable to a dynamic repository variable obiee 11g
                  asim cholas

                  Thanks a lot Christian and Gianni.

                  Christian, you are helping me for at least 5 years.. I will be really thankful to you always.

                  hmm. repository variable can hold same value irrespective of users. got it. But still have this confusion.

                   

                  for example

                   

                  when user 'A' login to the environment system session variable USER will hold the value of 'A'. in the initialization block of dynamic repository variable my query is like below.

                   

                  Select country from tabledummy where columnuser='A'

                   

                  in tabledummy we have data like below

                   

                  country  columnuser

                  USA       A

                  UK         B

                   

                   

                  so cant the variable hold USA in the variable. what could be the issue? ...

                   

                   

                  In our environment OBIEE administrator said they will not allow to create any more session variables as it is causing issues in user login due to the high number of initialization blocks already present. Is there any workaround for this issue?

                   

                  thanks

                  Aj

                  • 6. Re: assigning system session variable to a dynamic repository variable obiee 11g
                    Christian Berg

                    Once more: It isn't an init block for a repository variable. It's an init block for a session variable.

                     

                    If your admins have an issue with that then they better run a full review of all init blocks and variables and see which ones are garbage, which ones are badly configured, which ones are not marked with "deferred execution" even if they should be deferred...

                     

                    Just because a system is saturated with session variables doesn't mean that you can use repository variables instead of session ones. The scope of a repository variable is the same for ALL users. You can not change this behavior unless you force the BI server to cycle (shutdown and start up) every time a user logs on (that's a joke by the way).

                    • 7. Re: assigning system session variable to a dynamic repository variable obiee 11g
                      Gianni Ceresa
                      If your admins have an issue with that then they better run a full review of all init blocks and variables and see which ones are garbage, which ones are badly configured, which ones are not marked with "deferred execution" even if they should be deferred...

                      A huge, big +1 for this !!!!

                       

                      "OBIEE administrator said they will not allow to create any more session variables" is just not an acceptable answer, if you accept that tomorrow they tell you that you can't create any report anymore because of there are too many files (number, not size) or any other unacceptable answer.

                       

                      Christian gave the perfect recipe for your OBIEE admins, I would also add that as init blocks run SQL they can also ask the support of a DBA to look at the queries and see if they can't improve the performance of the queries in the DB... It's all about performance in the end, but stop using the tool isn't an answer (or for sure not one I would accept).

                      1 person found this helpful