OBIEE : Interesting analysis on Usage Tracking

Below is some interesting analysis you can do Usage tracking data

Total No of users used the system at least once :-

Select distinct user_name from s_nq_acct;

Maximum concurrent queries fired at a time in the selected duration :-

select
 max(distinct_users) as max_concorent_users
from
(
SELECT TO_CHAR(start_ts, 'YYYY-MM-DD HH24:MI') AS start_ts_min,
       COUNT(DISTINCT user_name)               AS distinct_users
FROM   s_nq_acct
where start_dt Between date1 and date 2
GROUP  BY TO_CHAR(start_ts, 'YYYY-MM-DD HH24:MI')
) 


Maximum concurrent sessions fired at a time in the selected duration :-

select
 max(distinct_users)    as max_concorent_users,
max(distinct_reports)  as max_concorent_reports,
max(distinct_sas)      as max_concorent_sas
from
(
SELECT TO_CHAR(start_ts, 'YYYY-MM-DD HH24:MI') AS start_ts_min,
       COUNT(DISTINCT user_name)               AS distinct_users,
       COUNT(DISTINCT saw_src_path)            AS distinct_reports,
       COUNT(DISTINCT subject_area_name)       AS distinct_sas
FROM   s_nq_acct
where start_dt
GROUP  BY TO_CHAR(start_ts, 'YYYY-MM-DD HH24:MI')
) 

Maximum concurrent reports and subject areas :-

select
 max(distinct_users)    as max_concorent_users,
max(distinct_reports)  as max_concorent_reports,
max(distinct_sas)      as max_concorent_sas
from
(
SELECT TO_CHAR(start_ts, 'YYYY-MM-DD HH24:MI') AS start_ts_min,
       COUNT(DISTINCT user_name)               AS distinct_users,
       COUNT(DISTINCT saw_src_path)            AS distinct_reports,
       COUNT(DISTINCT subject_area_name)       AS distinct_sas
FROM   s_nq_acct
where start_dt 
GROUP  BY TO_CHAR(start_ts, 'YYYY-MM-DD HH24:MI')
)


Comments

Post a Comment

Popular posts from this blog

Image
Variables in OBIEE Part 2 Session Variable: Session variable is similar to the dynamic repository variable in that they obtain values from initialization block. The difference is session variable scope is limited to the user session.  Session initialization block will run every time if a new user logged in There are two types of session variables available System Session variable Non system session variable System session variable : System variables are OBIEE reserved variable. Example for system session variable is USER.  USER variable holds username always. Non System Session Variable:   Non system session variable can be initialized using initialization block. Like system session variable the scope of non system session variable is limited to session.  Non system variables are commonly used for security implementation. For eg:  if we want the region of a user, we can create initialization block to get regi...
Read more
Image
Variables in OBIEE Part 3 Presentation Variable : Presentation variable is the variable available in presentation service. Presentation variable can be initialized from prompts. Presentation can be initialized from Prompt by either creating Presentation prompt or can set variable from column prompt set varaiable option Presentation variable can call in report using below syntax  ('@{Variable_name}{variable value}') Below is the syntax to pass multi-select characters to column filter @{varaiable_name} ['@'] {'varaiable value'}
Read more

Difference between Global Filter and Dashboard Prompt

Many of beginners still don't know what is the difference between filter and prompt.Here is a simple solution for them filter only filter a particular report but prompt is used filter a page or dashboard which contain one or more report. Now the Difference between global filter and dashboard prompt. global filters are also called saved filters,It can apply to multiple reports.Dashboard prompt is a special kind of filter that filters request that are embedded with the dashboard. Building Dashboard Prompt : Step 1: In Oracle BI Answers select new dashboard button in that select Subject area. Step 2:Select the scope of the dashboard,if we select scope as page then prompt will filter only reports in a particular page.scope is dashboard then it will filter all the reports that are present in that dashboard. step 3:select column and operator step 4:select control and constraints step 5:set defaults and set presentation variables if needed. step 6: create label step 7:save...
Read more