Abstract
The usage tacking feature of OBIEE 11g offers very useful stats around logical and physical queries. The logical query analysis is useful for optimization of report queries, to define caching strategies and also provide a backbone for summary advisor for suggesting proper aggregation tables to speed up query performance.
DBA and Support resources are been hugely benefited by ability to track the physical query stats which is helping to investigate the day to day users requests/issues and also for helping database tuning activities.
In short it is a very useful feature for report developer, solution designer, support personnel and DBA.
There are number of customers which may like to track reporting usage at user or supervisor or organization level. As supervisor and organization are natural choices for hierarchies within any organization it is natural inclination to provide the usage stats at supervisor, organization and time hierarchy levels.
For CRM Analytics Oracle do have a BI Apps for Usage Accelerator which reports against activities and access of users on Siebel CRM, but it does not provide any reporting on BI Apps access/usage.
The customers would like to use these stats for different purposes
- Evaluate/ Improve the BI Apps usage patterns within Team or/and Organization
- Promote BI Apps within Teams and/or Organization
- Management Reporting on BI Apps Usage over time
- To charge the user at organization or team level based on report/database usage
- To fulfill legal /regulatory requirement around usage tracking and data protection compliances.
- To report on compliance reporting for business processes on source systems
- To help to discourage the source application access for reporting purpose
- To help with change management process to focus on major impact users groups e.g. upgrade from 10g to 11g
The logical and physical query stats of usage tracking data can be enhanced with HR work profile data from BI Apps which would enable customers to achieve best of the two worlds.
This blog aim is to discuss the problem and solution for enhancement of usage query data by HR work profile data. The main features and limitations of the proposed solution are also discussed in subsequent sections.
Problem Description
The problem description is explained below in terms of what are the challenges and business requirement.
Challenges
- Companies cannot report on usage at supervisor and/or organization level e.g. what is usage of my team
- Supervisor cannot have access to his subordinate usage tracking and data protection compliances
- BI Apps project team cannot do management reports on application
- BI Apps owner could not evaluate BI Apps at team or/and organization
- Companies cannot fulfill legal/regulatory requirement around usage and data protection
- Shared Services don’t have any cost model to charge based on report usage at department level
- There is no help available for impact access for change management
Requirement
- Companies wanted a usage data/reports at supervisor and/or organization level for improving user adoption of BI Apps
- The BI Apps team should have ability to build/report management reports and should also help in managing chain management
- Application support would have measures in place to discourage the users from accessing source system for any reporting purpose
- The shared services should have ability to charge the user, team or organization against the actual usage of reports
- Companies should have ability to fulfill legal/regulatory requirement around usage and data protection
- Every user should know what he has done over last week on the BI Apps system, user should have access to dashboard reports with appropriate access control in place
Solution
A custom solution is recommended for enhancing usage tracking information with HR Work Profile data so reporting of usage stats at Team Level and Organization Level is possible.
The Usage Tracking should be enabled for logical and physical queries. The configuration for usage tracking for 11g, please refer my following blog.
The BI Apps HR is a prerequisite for this custom solution, but one can use the HR information if BI Apps HR is not implemented as part of BI Apps. As supervisor and organization are natural choices for hierarchies within any organization it is natural inclination to provide the usage stats at supervisor, organization and time hierarchy levels, this solution is an attempt to provide data at different level of hierarchies for supervisor and organization.
Idea is to link usage data for user for a day to HR profile data which is been populated by daily incremental load. Within source system a user always has an active assignment at the moment of time,. The assignment attributes such as employee number, effective start date and effective end date for assignment would help to link usage aggregated data for a day by using the employee user id and date of usage. Simple rule is to pick an assignment whose effective start date is less than usage date while its effective end date is greater than usage date.
The assignment record will provide the HR profile information such as employment, position, supervisor, organization, employee location etc associated with the assignment. This link to supervisor and organization would be useful to see the usage data at different hierarchy level data for supervisor and organization hierarchical levels.
In absence of HR analytics one can link the usage information with drivers such as employee and usage date to HR information to report on. The usage tracking enablement and HR information availability are must for implementing this solution.
Detail of Solution
Procedure to design a custom solution for reporting usage by supervisor and organization hierarchy level is follows
Step 1: Physical Structures for Custom Fact Table (e.g. WC_ACCT_HR_F) to hold the reporting data and should have foreign keys for employee, job, and position, pay grade, employee location, company, employment, supervisor and hr organization dimension tables. These dimension tables are confirmed dimension across HR BI Apps implementation.
The measures identified for custom fact are as follows: - # reports, # dashboards, # logical queries, Total time, Average Row Count, Complete Time Seconds, Sum Cumulative DB Time and # db queries Additional measures from HR profile such as #headcount, #active employees, # jobs etc would be useful to include.
Make sure that bitmap indexes on foreign keys (WID) would be useful for enhancing performance by using star transformation.
Step 2: PL/SQL or Informatica Task to populate custom fact, the procedure should be set to execute daily once incremental load is finished/on demand if required.
Sample from/where Clause is as follows
FROM
W_WRKFC_EVT_MONTH_F -- Workforce Event Month Fact
, DEV_BIPLATFORM.S_NQ_ACCT – Usage Tracking table
, W_EMPLOYEE_D -- Employee Dimension
WHERE
W_WRKFC_EVT_MONTH_F.SNAPSHOT_IND = 1 and -- Get Snapshot
W_WRKFC_EVT_MONTH_F.DELETE_FLG <> 'Y'
AND W_EMPLOYEE_D.ROW_WID = W_WRKFC_EVT_MONTH_F.EMPLOYEE_WID
AND DEV_BIPLATFORM.S_NQ_ACCT.USER_NAME=W_EMPLOYEE_D.LOGIN (+)
AND DEV_BIPLATFORM.S_NQ_ACCT.SUCCESS_FLG = 0.0 and DEV_BIPLATFORM.S_NQ_ACCT.QUERY_SRC_CD = 'Report' – Only Report
AND DEV_BIPLATFORM.S_NQ_ACCT.SAW_DASHBOARD NOT IN ( '/shared/Usage Tracking/_portal/Usage Tracking') – Not include usage tracking reports
AND DEV_BIPLATFORM.S_NQ_ACCT.SAW_DASHBOARD LIKE ( '/shared%')
AND (W_WRKFC_EVT_MONTH_F.SNAPSHOT_MONTH_END_IND in (1)
AND W_WRKFC_EVT_MONTH_F.EFFECTIVE_END_DATE >=DEV_BIPLATFORM.S_NQ_ACCT.START_DT AND
W_WRKFC_EVT_MONTH_F.EFFECTIVE_START_DATE <= DEV_BIPLATFORM.S_NQ_ACCT.START_DT) – Report Start Date should be between assignment Effective Start Date and Effective End Date
Step 3: Add PL/SQL script as a part of daily incremental load, by registering table, adding task within DAC. I would suggest including PL/SQL as a post script task
Step 4: Repository Design – Physical Layer – Logical Layer and Presentation Layer
Step 5: Report
The proposed exposed structures will open the number of possibilities to report on.
Products Involved
OBIEE 11g/DAC/PL-SQL
Business Benefits
Business Performance
- Supervisors and Budget Holders( Organization Head ) are better equipped with usage tracking reports and data protection compliances
- Companies can fulfill legal/regulatory requirement around usage and data protection, in case of shared services can have any cost model to charge based on report usage at department level
Cost
- Single system for reporting avoids the multiple silos of systems and their maintenance
- Usage reporting will enable decision-making and provides a value for money
Operation
- Team and Budget Holder Usage Reporting helps them to respond and manage solution more efficiently
My Inference
The solution suggested would be useful for team Holder usage tracking reports. I believe Oracle should offer some sort of out of box solution to support team and Budget Holder usage tracking reports. It is worth to try the solution to enhance overall reporting solution.
