3 Replies Latest reply: May 15, 2012 9:21 AM by 935931 RSS

    OBIEE 11.1.1.5 : NQS temp files fill the disk space

    935931
      Hi guys,
      I've a big issue with muliti-database query.

      My RPD has two Connection Pools: Oracle's one for dimensions and Teradata's one for facts.

      The problem is in running queries that involve both Connection Pools: the temp files come to occupy more then 5GB, running out of disk space dedicated.

      The strange thing is that in development environment all is right... but in production environment, where I've a cluster of 3 bi_server, I've this issue.

      In development obiee querys Teradata tables otherwise in production it queries Teradata view. The amount of data is not very large, but for reasons unknown to me I've to put fact table into a teradata DB.

      Could I probably solve this increasing the disk space?
      Do I need to specify particular join between fact and dimensions or other implementations in repository?

      SOS!

      Thanks in advance!
      Regards,
      Tom
        • 1. Re: OBIEE 11.1.1.5 : NQS temp files fill the disk space
          Dhar
          Hi Tom,

          I understand that your star is spread across two different sources.... Fact in Teradata and dimensions in Oracle.

          Hhhmm, In this case you may want to use the driving tables for these inter database joins. In the BMM, on the logical join between the dimension and the fact, set the driving tables as dimension. The BI Server would then send to the Fact, the dimension values as parameters.

          Ex: select amount from fact where month in ('201201','201202') .

          So, this would take off the overhead on the BIServer to get the fact data and dimension data and do a join by itself.

          Hope this helps.

          Thank you,
          Dhar
          • 2. Re: OBIEE 11.1.1.5 : NQS temp files fill the disk space
            935931
            Hi Dhar,
            first of all thank you for your reply.

            Yes you centered my problem, but I tried your suggestion and I'm still having this problem...

            I saw in the nqquery.log file the physical query and I've noticed a massive use of the ORDER BY clause in logical query, also in the subqueries. I uncheked the voice in the DB function (in RPD) but nothing changed.

            Could be this the problem? How can I remove the order by clause in the logical query? could be usefull?

            I don't want to believe that OBIEE cannot manage multi-database simple inner join... and I've no time to develop other ETL procedure.

            Thank you,
            Tom
            • 3. Re: OBIEE 11.1.1.5 : NQS temp files fill the disk space
              935931
              In anyway I solved my issue implementing other ETL procedures to have dimensions and facts in the same DB (Teradata).

              Using the moving table in the logical join, gave me a problem exceeding the MAX_QUERIES_PER_DRIVE_JOIN parameter value and increasing this value the performance get worse.

              Greetings,
              Tom

              Edited by: ITom on 15-mag-2012 7.21