This discussion is archived
6 Replies Latest reply: Nov 28, 2012 8:36 PM by 929282 RSS

Usage Tracking in OBIEE 11g - S_NQ_DB_ACCT and S_NQ_ACCT

929282 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points