Navigating Oracle HCM Cloud Analytics: Choosing Between Oracle Cloud Reporting and Snowflake with Tableau
Are you looking at building an analytics platform for Oracle Cloud Human Capital Management (HCM)?
The Landscape: Oracle HCM Cloud Reporting Options
Oracle HCM Cloud offers 3 reporting options —
Option #1: Oracle Transactional Business Intelligence (OTBI)
Option #2: BI Publisher
Option #3: Oracle Analytics Cloud (OAC)
However, each of these options comes with its own set of challenges and limitations.
Option #1 Oracle Transactional Business Intelligence OTBI
Poor performance, only report level customizations available, cannot do complex reports that require joins across datasets.
Option #2 BI Publisher
No self-service, expensive to develop (takes a lot of time for formatting alone)
Option #3 Oracle Analytics Cloud OAC
Poor performance & execution issues.
OBIEE is not an option!
While these options have served some organizations well, they also present challenges, especially with the recent end-of-life status of Oracle Business Intelligence Enterprise Edition (OBIEE).
Embracing Change: Migration from OBIEE to Snowflake & Tableau
With OBIEE reaching its end-of-life, organizations are exploring alternative solutions besides the above that offer greater scalability, agility, and modern analytics capabilities, so did we. I’ve brought Snowflake into my enterprise already as a cloud data warehouse platform, and we’ve been utilizing Tableau as a data visualization tool for this business group; the combination seemed like a compelling option for consideration as there was low friction entry barrier so we’ve done exactly that for Oracle HCM analytics. During our assessment, we realized the following benefits:
- Enhanced Scalability: Snowflake’s architecture allows for easy scaling up or down based on data needs, providing faster data loading and query processing, leading to improved performance
- Improved Data Sharing: Snowflake enables seamless data sharing among users, allowing organizations to securely share data in real-time with internal and external stakeholders
- Cost Efficiency: Cloud-based solutions like Snowflake and Tableau typically offer cost savings compared to maintaining on-premises hardware infrastructure, as you only pay for the resources you use
- Advanced Analytics Capabilities: Tableau Cloud provides self-service analytics, empowering users to make data-driven decisions efficiently and effectively, enhancing overall business insights
- Data Security: Snowflake’s distributed architecture ensures high availability and security, making it an ideal solution for organizations handling sensitive data
The Migration Process: A High-Level Overview
Let’s delve into the high-level process of migrating from OBIEE to Snowflake and Tableau:
- Setting Up Data Warehouse: Establishing a robust data warehouse environment is crucial for efficient data management. (useful resource: https://www.snowflake.com/oracle-to-snowflake-technical-migration-guide/ )
- Migrating the OBIEE Repository to Snowflake: Transitioning your existing OBIEE repository to Snowflake involved a lot of analysis and planning to decide what subject areas were needed for the new cloud-based analytics platform.
- Reporting with Tableau: Leveraging Tableau for visualization unlocked powerful insights from our data stored in Snowflake, but we’ve needed to spend a lot of time designing and combining OBIEE RPDs for each module to enable reusability with the Tableau Data Sources (a few are mentioned in the detailed architecture below)
How We’ve Implemented It
We’ve adopted a strategic approach to migration, utilizing Oracle Data Integrator as the ETL layer for seamless data extraction from Oracle Cloud as shown below —
Extracts were generated as flat files from Oracle Cloud.
Used a few pre-built accelerator packages that we’ve purchased from a couple of different professional services companies for bringing in data from Oracle HCM Cloud into Oracle data warehouse, we’ve wrapped those in Oracle Data Integrator to migrate into Snowflake.
Migration Scope: What We’ve Accomplished
Below I cover the high-level process of how we set up a data warehouse, migrating your OBIEE repository to Snowflake, and getting reporting with Tableau.
Our migration project has covered the following key aspects:
- Replicating 307 Oracle HCM base tables into Snowflake some on a nightly basis, and some hourly.
- HCM Modules covered: Core HR, Learning Management, Performance Management, Benefits, Talent Review, and Talent Pools.
- Adapting ETL code for compatibility with Snowflake connections.
- Transitioning over 700 database objects from Oracle Database to Snowflake.
- Creating 8 new Tableau Snowflake Data Sources to replicate existing data sources.
Ensuring Security
Security is not just a consideration. Rather, it is a top priority throughout the implementation process of Oracle HCM Cloud analytics in Snowflake and Tableau.
Let’s delve into the approach my team orchestrated to secure the HCM Data Warehouse while ensuring sensitive HR data remains protected throughout the migration process.
Our Security Rules
Our primary goal is to restrict access to sensitive HR data — ensuring it can only be visible for select HR users while they view reports through Tableau. Here are the rules we’ve implemented:
- Staging Data Securely: HCM application data is staged to files in a secure cloud location accessible by Oracle Data Integrator (ODI).
- Limiting Access: HR users access Data Warehouse data solely via Tableau reports, with no direct SQL access.
- Restricting Developer Access: Data Warehouse developers and DBAs have SQL access only in non-prod environments where data is masked.
- Data Masking in Development: HCM application data files in non-prod environments are pre-masked for use in development.
Leveraging Oracle Tools for Security
We utilized & assessed several Oracle tools to enhance security throughout the migration:
- Transparent Data Encryption (TDE): Encrypts data at rest, safeguarding physical database files stored on disk.
- Data Vault (DV): Manages security realms to control user access to specific database objects, preventing unauthorized access.
- Data Masking and Subsetting: Alters table data to mask and hide sensitive PII data, ensuring data security in non-prod environments.
The goal of securing the HCM Data Warehouse is to restrict access to sensitive HR data. Data should be visible only to select HR users viewing reports through Tableau. Developers and DBAs should not have access to sensitive Personally Identifiable Information(PII) in any production system.
Oracle Security Implementation Details
Transparent Data Encryption (TDE): TDE encrypts sensitive data at rest (on disk storage), securing it from unauthorized access even if database files are compromised. Encryption keys are stored in an external keystore for added protection.
Data Vault (DV): DV restricts access to specific schemas in the Oracle database, preventing unauthorized access even by privileged users like DBAs. Realms, command rules, factors, rule sets, and secure application roles are utilized to control access.
Data Masking: Data masking alters sensitive data by modifying or randomizing specific columns, ensuring that non-production environments do not contain PII data. This is crucial for development and testing purposes.
For example, in our development environment, our Analytics Developers will need SQL access to build and test ODI mappings and Tableau reports. These users need data that look and act like real data. They should not see actual information like social security numbers or salaries. Data masking can add randomized values into these fields in the non-prod environment to build the Data Warehouse and Reporting system.
Masking is executed during export. In-Export mode masks and subsets the data in near real-time while extracting the data from a database. The extracted masked and subsetted data is written to data pump export files that are further imported into test, development, or QA databases.
Data Masking Methodology:
Managing Production and Non-Prod Environments
Production Environment:
- Production needs to be completely secure with only applications accessing the data and users accessing the applications. No direct SQL access should be allowed. Security is applied at multiple levels, first by the user’s credentials into Tableau and then Tableau’s credentials into the data warehouse. Tableau will implement row-level security to further restrict the data that each individual can view. Oracle Data Integrator will have its own account for modifying data in the HCM Data Warehouse. Individual Developers may be granted access to non-PII tables, to be determined on an as-needed basis. All IDs accessing Production data must be granted specific entry to the HR Data Warehouse Schema via Data Vault realms and rule sets.
- DBAs will be responsible for the data masking as part of the cloning process to replicate masked data to lower instances. Data will be masked during the generation of export files.
Development/Non-Prod Environments:
- Development and Non-Prod Environments will always contain masked data. No PII data will be contained in these environments. Ideally, we would set up a Data Vault and Transparent Data Encryption like Production, in order to test these features during development and UAT. However it is ultimately not necessary as the data will be masked. Even without DV and TDE, the data in Non-Prod will still be secured by database user accounts. Only those accounts with grants to the HCM DW schema will be allowed to query data.
- Tableau data sources will use Row-Level security as it will be in Production, to test this functionality. Some users may be set up to access all rows of data. We can utilize a similar setup as was used for AP/GL row-level security.
Oracle SSO: we’ve also utilized Oracle SSO cookie authentication session variables for applying column-level security controls based on the custom profile option assigned in our ERP (Oracle eBusiness Suite). We have also implemented row-level security based on user access as we did in the legacy systems as shown below.
Conclusion: Unlocking the Full Potential of Your HCM Data
As your organization embarks on its Oracle HCM Cloud analytics journey, consider integrating Snowflake and Tableau into your toolkit. It could be a visionary and strategic step — proven as to my former enterprise. This migration not only provided enhanced scalability, agility, and modern analytics capabilities but also paved the way to unlock the full potential of HCM data, alongside other enterprise data stored in our Cloud data warehouse.
Through the process of collating, aggregating, and modeling data from diverse sources, we’ve gained profound insights while empowering informed decision-making and strategic planning.
I’m keen to hear your perspectives and address any queries you may have regarding Oracle HCM Cloud analytics implementation. Please don’t hesitate to share your comments below.
Thank you for reading!
