Sunday, 14 April 2013

Oracle BI Application 7.9.6.3 – Track BI Application Usage at Supervisor & HR Organization Level


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
  1. Evaluate/ Improve the BI Apps usage patterns within Team or/and Organization   
  2. Promote BI Apps within Teams and/or Organization
  3. Management Reporting on BI Apps Usage over time
  4. To charge the user at organization or team level based on report/database usage
  5. To fulfill legal /regulatory requirement around usage tracking and data protection compliances.
  6. To report on compliance reporting for business processes on source systems
  7. To help to discourage the source application access for reporting purpose
  8. 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.

Wednesday, 3 April 2013

Oracle Business Intelligence, v. 11.1.1.7.0 is Available on OTN


Oracle Business Intelligence v. 11.1.1.7.0 is now available on OTN for download. This blog is an attempt to share the recent communication from Oracle Product Management Team.

What’s New ??

The new release of Oracle Business Intelligence 11g, Release 11.1.1.7, features more than two hundred enhancements, designed to add new functionality, improve performance, and deliver an enhanced end-user experience. Some of the major new features and enhancements are:
New visualizations and user-interactions, including Performance Tile, Waterfall, and 100 percent stacked bar graphs. Breadcrumbs provide contextual awareness and navigation. Users can now anchor the header for tables or pivot views using the “Freeze Header” feature, which is also available on the BI Mobile app via the scroll gesture.
New Visualization Suggestion Engine helps business users select the chart type best suited for the display of current data and the analysis task they wish to perform— “Comparing Values”, “Analyzing Trends”, “Comparing Percentages”, etc.  An algorithmic engine evaluates data types and the cardinality of the result set to recommend the most optimal visualization.

 New Oracle Hyperion Smart View for Office Integration. The Smart View 11.1.2.2 Add-in replaces the Oracle BI Office Add-in as the primary Office application for Oracle BI Foundation. Both are still available with OBI 11.1.1.7.  Users now can access the Oracle BI Foundation Catalog in Smart View, add dashboards and analyses, create new analyses from scratch, in Excel, and save them as either Excel worksheets or back in the Oracle BI Foundation Catalog.

BI Mobile Enhancements. All new 11.1.1.7 visualizations are touch enabled and support gestural interactions. Double-tap on a view renders it in full-screen mode. Enhancements to email settings now allow users to email attachments in the PDF format also.

Tighter OBI-BI Publisher Integration. Translate OBI Dashboards to BI Publisher layouts automatically. Use multiple templates to  allow pixel-perfect printing of dashboards.  BI Publisher can now directly access subject areas in the Oracle BI Foundation Catalog, removing the need to create separate BIP data models.

BI Publisher Enhancements. Report Wizard enhancements, Layout Editor Extensions for Interactive Viewer, improved Data Model Editor usability, and private JDBC connections are now supported. XML and CSV formats can now be configured as “local sources”.  Integration with enterprise applications has been enhanced with the ability to convert an E-Business Suite catalog to BI Publisher. E-Business Suite Flex Fields are now supported as BI Publisher Report parameters.

 Advanced and Predictive Analytics. OBI Dashboards and prompts can run Oracle R for Enterprise (ORE) scripts that perform complicated statistical analyses, and Oracle Data Mining algorithms and cluster analysis, and visualize the data set as interactive images embedded in Dashboards.

 Big Data integration to Apache Hadoop as a data source. The BI Server is now able to generate Hive query language commands to query federated Hadoop data sources.

Exalytics support

Oracle BI 11.1.1.7 is not certified for Oracle Exalytics In-Memory Machine.  OBI 11.1.1.7, Endeca 3.0, and updates to TimesTen In-Memory RDBMS, Hyperion EPM and other Exalytics software are planned to be included with Exalytics PS3 in June (Oracle Internal Only).


OTN Download Link

Client and Server Components
 

Server Components

Additional and Optional Software Required

Release Note
Documentation Library
Certification Information
I hope you find this information useful. What has been changed ? Please watch this place.