9 Replies Latest reply on Apr 13, 2017 1:16 PM by Sherry George

    OBIEE 12c Usage tracking problem

    3068122

      Hi ,

       

      After enable usage tracking option in NQSCONFIG.INI using obiee 12c repository tables ,

      session logs have been inserting "S_NQ_ACCT" table but have not been inserting "S_NQ_DB_ACCT" table.

       

      In .rpd loglevel option value is 7.

      Also there is variable which name is 'LOGLEVEL'.LOGLEVEL variable's default value is 7.

       

      Is there anyone who have met this issue ?

       

      NOTE :  RCU schemas created in Microsoft SQL Server 2014

        • 1. Re: OBIEE 12c Usage tracking problem
          Gianni Ceresa

          Did you check your log files to see if there isn't at some point a reference to this table with a message giving you a reason?

           

          PS: when you have a question keep it flagged as a question

          Capture.PNG

          • 2. Re: OBIEE 12c Usage tracking problem
            Christian Berg

            1.) Thanks.

             

            2.) RPD and database must match. If your RPD objects don't match the physical schema then it won't work.

            • 3. Re: OBIEE 12c Usage tracking problem
              3068122

              Yes ,

               

              i checked obis1-diagnostic.log and there is no error about S_NQ_DB_ACCT table.

               

              However , I tried to create direct sql report using S_NQ_DB_ACCT.

              If i don't use S_NQ_DB_ACCT.QUERY_BLOB column in query ,

              there is no problem , query is executed without error. If i use S_NQ_DB_ACCT.QUERY_BLOB column in query , query gives me error

               

              I think , issue is related to QUERY_BLOB column in S_NQ_DB_ACCT table.

               

              === QUERY  ===

              SELECT QUERY_BLOB

              FROM S_NQ_DB_ACCT

               

              State: HY000. Code: 43093. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. (HY000)

              State: HY000. Code: 16001. [nQSError: 16001] ODBC error state: 01004 code: 0 message: [Microsoft][ODBC SQL Server Driver]String data, right truncation. (HY000)

              State: HY000. Code: 16011. [nQSError: 16011] ODBC error occurred while executing SQLExtendedFetch to retrieve the results of a SQL statement. (HY000)

               

              In obis1-diagnostic.log there is an error like below.

               

              [2017-02-20T18:10:18.24+03:00] [OBIS] [ERROR:1] [] [] [ecid: ] [sik: ssi] [tid: f98] [nQSError: 16011] ODBC error occurred while executing SQLExtendedFetch to retrieve the results of a SQL statement. [[

              file: server\Query\Execution\DbGateway\Odbc35\..\Odbc\Src\SQXDGOdbc.cpp; line: 740

              Properties: description=ExecutePhysical Exchange; producerID=0x5fae0aa8; requestID=0x59bd0008; sessionID=0x59bd0000; userName=weblogic;

              [nQSError: 16001] ODBC error state: 01004 code: 0 message: [Microsoft][ODBC SQL Server Driver]String data, right truncation.

               

              ********** Task: 1. Running for (mls): 31 **********

              Description: Producer Executing Query

              Repository Name:ssi;Subject Area Name:;User Name:weblogic

              Logical Hash of SQL: 0x5c28a494SQL:SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report';EXECUTE PHYSICAL CONNECTION POOL "Usage Tracking 12C" SELECT QUERY_BLOB

              FROM S_NQ_DB_ACCT

              • 4. Re: OBIEE 12c Usage tracking problem
                Christian Berg

                Errr is your BLOB a VARCHAR(4000) in the RPD or not?

                • 5. Re: OBIEE 12c Usage tracking problem
                  3068122

                  In rpd ,QUERY_BLOB column type is LONGVARCHAR(32768)

                  • 6. Re: OBIEE 12c Usage tracking problem
                    Sherry George

                    Are you using datadirect driver to connect to Microsoft SQL Server 2014? if so, what version?

                    • 7. Re: OBIEE 12c Usage tracking problem
                      3068122

                      No ,

                      we are using SQL Server driver.

                      ODBC to repository schemas like below.

                      • 8. Re: OBIEE 12c Usage tracking problem
                        Christian Berg

                        Had huge issues with SQL Server and the driver dlls.

                         

                        In the end I went for DB type "SQLS12" and ODBC2.0 in the RPD and the SQLS native dlls in the Win ODBC config

                        • 9. Re: OBIEE 12c Usage tracking problem
                          Sherry George

                          Hi,

                          This could be a driver problem and it is difficult to debug a buggy driver imo. If you have a test environment to replicate the issue, I would suggest changing the driver to datadirect or even a different version of SQL server driver like the one Christian mentioned and see if issue gets resolved.

                           

                          Edit: Also I believe you are using ODBC 3.5 in OBIEE RPD.