Analyzing usage

Now that we are getting data into the tables and have set up the BI repository to access the data, we can start creating analyses to see what is going on. Typical analyses that I would create are:

  • Top 10 slowest dashboards
  • Top 10 slowest analyses
  • User activity over time
  • Non-users
  • Dashboard usage analysis
  • Error reports

Usage measures

Before using some of the measures, you need to understand what each one does and how they are relevant. The following describes each field in the main usage tracking table:

  • TOTAL_TIME_SEC: The time (in seconds) that the Oracle BI Server spent working on the query while the client waited for responses to its query requests. This setting is the same as the response time in the NQQuery.log file. Usually it is the difference between the start time and the end time. The same results are returned from the following function:
        ROUND((CAST(END_TS as DATE)-CAST(START_TS as DATE))*86400)
  • COMPILE_TIME_SEC: The time (in seconds) required to compile the query.
  • ROW_COUNT: The number of rows returned to the querying client.
  • CUM_DB_TIME_SEC: The total amount of time (in seconds) that the Oracle BI Server waited for backend physical databases on behalf of a logical query.
  • CUM_NUM_DB_ROW: The total number of rows returned by the backend databases.
  • NUM_DB_QUERY: The number of queries submitted to backend databases in order to satisfy the logical query request. For successful queries (SuccessFlag = 0), this number will be 1 or greater.
  • NUM_CACHE_HITS: Indicates the number of times the existing cache was returned.
  • NUM_CACHE_INSERTED: Indicates the number of times the query-generated cache was returned.
  • QUERY_TEXT: The SQL submitted for the query.
  • QUERY_BLOB: The data type is ntext when using SQL Server, and CLOB when using Oracle, DB2, or Terradata databases. It contains the entire logical SQL statement without any truncation.
  • QUERY_KEY: An MD5 hash key that is generated by Oracle Business Intelligence from the logical SQL statement.
  • SUBJECT_AREA_NAME: The name of the business model being accessed.
  • REPOSITORY_NAME: The name of the repository the query accesses.
  • IMPERSONATOR_USER_NAME: User ID of an impersonated user. If the request is not run as an impersonated user, the value will be NULL.
  • USER_NAME: The name of the user who submitted the query, from the USER variable.
  • PRESENTATION_NAME: The name of the Presentation Catalog in Oracle BI Presentation Services.
  • QUERY_SRC_CD: The source of the request; for example, Drill or Report.
  • SAW_DASHBOARD The path of the dashboard. If the query was not submitted through an Interactive Dashboard, the value will be NULL.
  • SAW_DASHBOARD_PG: The page's name in the Interactive Dashboard. If the request is not a dashboard request, the value will be NULL.
  • SAW_SRC_PATH: The path name in the Oracle BI Presentation Catalog for the request.
  • START_DT: The date the logical query was submitted.
  • START_HOUR_MIN: The hour and minute the logical query was submitted.
  • START_TS: The date and time the logical query was submitted.
  • END_DT: The date the logical query was completed.
  • END_HOUR_MIN: The hour and minute the logical query was completed.
  • END_TS: The date and time the logical query finished. The start and end timestamps also reflect the time that the query spent waiting for resources to become available.
  • ERROR_TEXT: The error message from the backend database. This column is only applicable if SUCCESS_FLG is set to a value other than 0 (zero). Multiple messages will concatenate and will not be parsed by Oracle BI Server.
  • CACHE_IND_FLG: The default value is N. Y indicates a cache hit for the query and N indicates a cache miss.
  • SUCCESS_FLG: This indicates the completion status of the query:
    • 0: The query completed successfully with no errors
    • 1: The query timed out
    • 2: The query failed because row limits were exceeded
    • 3: The query failed due to some other reason

The following describes each field in the extra usage tracking table used to store physical SQL sent to the database:

  • QUERY_TEXT: The first 1024 characters of the SQL query
  • QUERY_BLOB: The whole SQL query sent to the database
  • TIME_SEC: The time taken to return data from the database
  • ROW_COUNT: The number of rows returned from the database
  • START_TS: Start time
  • START_DT: Start date
  • START_HOUR_MIN: The time (in hours and minutes) that the query was sent to the database
  • END_TS: The time the results were returned to the BI Server
  • END_DT: The date the results were returned to the BI Server
  • END_HOUR_MIN: The hours and minutes in the HHMM format that the data returned
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.14.145.82