3 Replies Latest reply on Feb 4, 2013 5:03 AM by Vineeth. K.M

    Using Global Temporary Table in OBIEE 11

      THe requirement is to run a database function before the report runs. This Function accepts 5 parameters (Presentation Variable) and populates a GTT Global Temporary Table.
      This GTT is configured in presentation Layer.

      1. Created a dummy report which returns only one row and called the fucntion using "Evaluate" in this.
      2. Created 2nd report using GTT Columns.

      All these 2 reports placed on dashboard and Presentation variable are set in the prompt. The first one runs correctly and populate the "Regular Table". Means when I create a regular table instead of GTT I can see the correct record. But if I change it to GTT then the data does not get populated in this table.

      Any Inputs?

        • 1. Re: Using Global Temporary Table in OBIEE 11
          but seems it has to do something with GTT data hodling capability to the session level.

          Try creating GTT with PRESERVE ROWS or ON ROLLBACK DELETE ROWS.

          seems the session which populates GTT is different from the session which selects data from it so data gets deleted even before the table is accessed.

          Please mark if it helped.

          • 2. Re: Using Global Temporary Table in OBIEE 11
            Thanks user2534719 for the response. Yes I think that may be the issue. THe requirement is to "NOT TO" run that report until all 5 parameters are selected in the prompt and all 5 presentation variables have the data.

            Any other suggestions.
            • 3. Re: Using Global Temporary Table in OBIEE 11
              Vineeth. K.M

              If you are using a GTT, you need to run the function which populates the GTT before OBI server executes the select query on that table.
              In your case, the OBI server is executing the select query from the table before the data is populated in the table.
              In order to force the BI server to run the query after the GTT is populated, you need to specify the condition in the connection pool settings under the connection scripts tab.

              In the execute before query, enter your physical query which runs the function to populate the GTT.

              Hope this helps !