Introduction
Oracle Cloud HCM offers rich set of tools for reporting & bulk data extract. In this article we will try to compare various tools, their intended usage and general recommendations so you can select the right tool for the task at hand. This article is not geared towards the detail deep dive but more like a high-level guidance to help you make informed decisions.
Brief introduction to the reporting & extract tools available in Oracle Cloud HCM
- OTBI (Oracle Transactional Business Intelligence) is used for analysis and ad-hoc reporting of transactional data. Business attributes and metrics are organized into subject areas providing a user friendly interface to build reports. Remember that OTBI is a reporting tool and not an extract tool.
- BIP (BI Publisher) – Oracle BI Publisher is an enterprise reporting product that provides the ability to create and manage highly formatted pixel perfect reports from a wide range of data sources. We recommend you use HCM extract as the data extract engine\data source and BIP on top of it to format reports. Try to stay away from using custom SQLs as the data source while developing BI reports for HCM requirements.
- HCM Extracts is primarily an outbound integration tool for high volume data, that lets you choose the HCM Data, gathers it from database, processes & archives. It can then convert this archived data to a format of your choice either by using inbuilt formatting options or by using BI Publisher and deliver to recipients. For e.g. Bulk employee data download, payroll interface, benefits interfaces, etc.
- BICC (BI Cloud Connector) comes preconfigured with your Oracle Cloud HCM Application (no separate installation or license needed). You can use the BI Cloud Connector Console to schedule a once-only or regular data load, known as a Cloud Extract. Extracted data can be used to feed Oracle HCM Analytics or Oracle BI Applications
This blog doesn’t cover other reporting tools available for HCM Cloud (but require separate license) such as Oracle Analytics Cloud – Data Visualization (OAC DV) and Fusion Analytics Warehouse (FAW). OAC DV and FAW could be used when you need to report across multiple data sources (e.g. HCM, ERP, Other On-Prem Applications). FAW can be used when you need a snapshot or incremental snapshot.
Given below is a comparison of each tool based on key parameters. This would help you select a tool based on your reporting requirement.
| Tool |
OTBI |
BIP |
HCM Extract |
BICC |
| Primary Usage |
User friendly, ad-hoc reporting. Primary option for HCM reporting, adhoc reports, dashboards, Infolets |
Highly formatted pixel perfect reports. Use BIP only for report formatting, stay away from creating SQL based data models as much as possible |
Outbound Integrations, bulk data export, built-in knowledge for HCM objects. Primary option for bulk data export, outbound integrations e.g. employee data download, benefits interfaces, payroll interface, reporting on payroll balances, etc. |
Oracle Analytics feed, multi-pillar (HCM, ERP, CRM, SCM) implementations. Primary option for data feed to Oracle Analytics, can also be used in case of multi-pillar implementation where you need download data from ERP\CRM\SCM pillars. |
| tool? |
|
|
|
|
| Full vs Incremental mode |
Not a data extraction tool |
Not supported |
Supports full as well as incremental run (provides advanced options for delta\changes only export) |
Supports full as well as incremental run |
| Security |
Data security is inherited from HCM Cloud, subject areas and catalog folders are also secured |
Data security is not implemented out of the box, you can use secured views in the data model to implement security. You can implement catalog\folder security. You can use OTBI as a data source and then you benefit from OTBI data security. |
Interface\export runs as an elevated\admin user |
Export runs as an elevated\admin user, you also need access to Webcenter content\UCM to review the output package |
| Part of HCM Cloud? (not a separate install) |
Yes |
Yes |
Yes |
Yes |
| Upgrade Safe |
Yes |
No (Data model changes in an Upgrade might require retrofitting custom SQLs) If you use OTBI as a source, it is upgrade safe |
Yes |
Yes **Schema changes (if any) are documented as part of the release\ upgrade |
| HCM Object Coverage |
Extensive |
Complete |
Extensive |
Extensive |
| Cross Oracle Cloud Apps? |
No (HCM Only) |
Yes |
No (HCM Only) |
Yes |
| Multithreading supported? |
No |
No |
Yes |
Yes |
| Coding Effort/Technical know-how required |
Low – User friendly UI. Wide range of delivered reports available |
High – Requires knowledge of underlying data model, development effort to build custom queries |
Medium – Delivered extracts available but requires some level of development/ customization to meet requirements. |
Low |
| Ability to customize |
Low. Limited to attributes/metrics available in delivered subject areas. Flex field attributes can be pulled into OTBI. |
High. Access to all tables and views |
Medium. Ability to write custom Fast formulas to extend data capture apart from the delivered user entities. No ability to customize extract base query |
Low. No ability to customize extract base query but can add custom filter |
| Ability to query across modules/objects |
Medium. Cross subject area reporting is possible. |
High. Can join tables/views from any module |
High. Complex relationships, hierarchical and non-hierarchical |
Low. No ability to join objects |
| Good idea to use in case of reporting Payroll balances? |
No |
No |
Yes |
No |
| Encryption |
N/A |
PGP-based encryption support |
PGP-based encryption support |
PGP-based encryption support |
| Delivery options\storage |
UI – OTBI can also be embedded into HCM Cloud either as an analysis or a complete dashboard, Email |
Email, Printer, Fax, FTP, UCM, WebDav, HTTP |
FTP, UCM, Email, Printer, Fax, File System, Documents of Record |
UCM or Oracle Cloud Storage Service |
| Scheduling |
Available, scheduling using BI Agent object |
Available, custom scheduling using Trigger/Data model |
Available, custom scheduling using Fast formula |
Available, No custom scheduling |
| API\Webservices for automation |
Yes, /analytics/saw.dll/wsdl/v10 |
Yes, ScheduleReportWSSService/wsdl |
Yes, FlowActionsService or PayrollProcessingActionService |
Yes, /bi/ess/esswebservice/wsdl |
| Performance |
Inbuilt performant queries |
Onus on customer to take care of performance |
Inbuilt performant queries, plus onus on customer to write suitable filter conditions |
Inbuilt performant queries |
| Clob/Blob data type support |
Supported but could lead to performance issues and limited to field size restrictions |
Supported |
Not supported |
Supported |
| Language Translation |
Supported |
Supported through translation tables |
Supported |
Supported |
| Audit Report Usage\Objects |
Supported (20D) |
Supported (19A) |
Not supported |
Not supported |
To summarize the tools selection for your Oracle Cloud HCM implementation, please use OTBI as your primary reporting tool — HCM Extract as the primary tool for outbound integrations or bulk data download — and stay away from custom SQL based BIP reports as much as possible.
Good luck with your implementation…