6 Replies Latest reply: Nov 28, 2012 10:36 PM by 929282 RSS

    Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT

    929282
      Hi Experts,

      How to fetch the physical query using usage tracking in OBIEE 11g, i hope in OBIEE 11g there is a feature to capture the physical query using S_NQ_DB_ACCT.

      I have imported the table S_NQ_ACCT and S_NQ_DB_ACCT in to RPD.

      Please let me know how to join these two tables(Using which Key).


      Thanks.
        • 1. Re: Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT
          437473
          You could use the join:

          S_NQ_ACCT.ID = S_NQ_DB_ACCT.LOGICAL_QUERY_ID

          and will find the physical query in column S_NQ_DB_ACCT.QUERY_TEXT


          //Samuel
          • 2. Re: Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT
            929282
            Hi,

            Thanks for your prompt reply.

            Which ID in table S_NQ_ACCT? Do you mean NODE_ID... If so this is different.

            Thanks
            • 3. Re: Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT
              437473
              I mean S_NQ_ACCT.*ID*, which also is the Primary Key of S_NQ_ACCT.

              /Samuel
              • 4. Re: Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT
                929282
                Hi,

                There is no column 'ID' in S_NQ_ACCT. I could see the ID column in S_NQ_DB_ACCT table only.

                Please Suggest me.

                Thanks
                • 5. Re: Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT
                  user248025
                  Hi,

                  Which version are you using it?

                  If you in obiee11.1.1.6.0 and above version refer,


                  CREATE TABLE [dbo].[S_NQ_ACCT](
                       [USER_NAME] [varchar](128) NULL,
                       [REPOSITORY_NAME] [varchar](128) NULL,
                       [SUBJECT_AREA_NAME] [varchar](128) NULL,
                       [NODE_ID] [varchar](15) NULL,
                       [START_TS] [datetime] NULL,
                       [START_DT] [datetime] NULL,
                       [START_HOUR_MIN] [char](5) NULL,
                       [END_TS] [datetime] NULL,
                       [END_DT] [datetime] NULL,
                       [END_HOUR_MIN] [char](5) NULL,
                       [QUERY_TEXT] [varchar](4000) NULL,
                       [QUERY_BLOB] [ntext] NULL,
                       [QUERY_KEY] [varchar](128) NULL,
                       [SUCCESS_FLG] [numeric](10, 0) NULL,
                       [ROW_COUNT] [numeric](10, 0) NULL,
                       [TOTAL_TIME_SEC] [numeric](10, 0) NULL,
                       [COMPILE_TIME_SEC] [numeric](10, 0) NULL,
                       [NUM_DB_QUERY] [numeric](10, 0) NULL,
                       [CUM_DB_TIME_SEC] [numeric](10, 0) NULL,
                       [CUM_NUM_DB_ROW] [numeric](10, 0) NULL,
                       [CACHE_IND_FLG] [char](1) NOT NULL,
                       [QUERY_SRC_CD] [varchar](30) NOT NULL,
                       [SAW_SRC_PATH] [varchar](250) NOT NULL,
                       [SAW_DASHBOARD] [varchar](150) NOT NULL,
                       [SAW_DASHBOARD_PG] [varchar](150) NOT NULL,
                       [PRESENTATION_NAME] [varchar](128) NOT NULL,
                       [ERROR_TEXT] [varchar](250) NOT NULL,
                       [IMPERSONATOR_USER_NAME] [varchar](128) NOT NULL,
                       [NUM_CACHE_INSERTED] [numeric](10, 0) NULL,
                       [NUM_CACHE_HITS] [numeric](10, 0) NULL
                  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


                  this one is working fine and no issue.

                  Thanks
                  Deva
                  • 6. Re: Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT
                    929282
                    Hi Deva,

                    Thanks for your reply. Usage Tracking is working fine, The issue is how to join these two tables S_NQ_ACCT and S_NQ_DB_ACCT. (i.e using which keys)

                    I am looking for the physical query from S_NQ_DB_ACCT table.


                    Thanks.