7 Replies Latest reply on May 10, 2013 1:27 PM by barry.goodsell

    Dynamic physical table name vs. Cache

    Lucas B
      Hello, Experts!

      I'm facing quite an interesting problem. I have two physical tables with the same structure but with a different data. Requirement is to show same reports with one or another table. Idea is to have dynamically changed physical table name with session variable usage. Session variable can be change in UI so it was working until cache was turned on. When cache is turned on logical statements sent to OBI backend are the same even for different values of session variable that stores physical table name. Once cache is populated every users will get values from cache. This is possible source of discrepancy because some users might run reports with tableA values and some with tableB values.

      Are there any options to set OBI to use data related to proper physical table name (i.e. accordingly to session variable value)? Model clone is not an option because it will be way to hard and complex to maintain both, beside same reports need to work sometimes with one table name and sometimes with other...

      PS. Cache is set to be common for all users.

        • 1. Re: Dynamic physical table name vs. Cache

          If I'm not wrong try to give a chance to OBIEE to use its Intelligence ...

          Have you heard about Fragmentation or else the below at LTS properties->Content tab?
          Check for these
          Fragmentation content:
          Check the Check box for 'This source should be combined with other sources at this level'

          As per this you got 'Value-Based' Fragmentation.

          Try it out.

          Hope it make sense, if does mark and rest send email

          • 2. Re: Dynamic physical table name vs. Cache
            Rajesh Gurram - BI
            Hi Lucas,

            You can use fragmentation feature in LTS to let BI Server pick the required table based on the data that each table has.

            You can set the table name for each report by changing the session variable name using SET VARIABLE under Advanced tab of answers.

            Mark helpful/correct if this helps.

            Rajesh Gurram
            • 3. Re: Dynamic physical table name vs. Cache
              Lucas B
              thank you, I've found another way to make it running. In fact there are two ways of doing it: filter LTS and have all data filtered from single table with session variable or use fragmentation content also with session variable.
              Now tricky part is to set variable from UI, currently I'm using issue raw sql: call NQSSetSessionValue( 'String SV_SIGNOFF=aaa;' ) but I have to figure out how to change session non system variable value without need of administrator user rights.

              There is GoURL method, but it's not working...
              2. Add In ORACLE_HOME/bifoundation/web/display/authenticationschemas.xml

              <RequestVariable source="url" type="informational" nameInSource="lang"
              biVariableName="NQ_SESSION.LOCALE" />

              inside the top <AuthenticationSchemaGroup> </AuthenticationSchemaGroup> tag
              • 4. Re: Dynamic physical table name vs. Cache
                Rajesh Gurram - BI
                You can change the value of a session variable with the procedure that I mentioned above (SET VARIABLE).
                • 5. Re: Dynamic physical table name vs. Cache
                  Lucas B

                  I've tested SET VARIABLE statement already but it doesn't set variable for the whole session (old variable value is showed in repo sessions manager). Same report run in a different tab gives me an error:
                  Metadata of results did not match metadata found in cache. This can be resolved by having an administrator refresh the server metadata.

                  call NQSSetSessionValue is working fine, but there will be a problem with usage because users don't have rights to access Issue SQL.


                  Edited by: 998616 on Apr 29, 2013 4:52 AM
                  • 6. Re: Dynamic physical table name vs. Cache
                    Rajesh Gurram - BI
                    The scope of SET VARIABLE is only for that particular report where you are using. It will not impact at RPD/other reports.

                    You can check the session variable value that is getting changed for that particular report (you will be verifying that from the sql that is fired), then you will have to apply the same for all the reports that needs a different value for that session variable.
                    • 7. Re: Dynamic physical table name vs. Cache
                      My preference for this sort of thing is to push the complexity into the database. If Table A and Table B have identical structures, create a database view that unions all the data into one place:

                      CREATE OR REPLACE VIEW both_tables AS
                      SELECT 'A' source_table, A.*
                      FROM tableA A
                      UNION ALL
                      SELECT 'B' source_table, B.*
                      FROM tableB B

                      Then you can model new view this into your RPD and filter on the source_table column at runtime.

                      Of course, if you're not allowed to make changes to your data source, you could always include the above SQL as an Opaque view in your Physical layer. If you're going to do that, I'd recommend expanding out the "*"s to the full column list.