Fusion HCM BI Data Model Guide

Share

Build Fusion HCM BI Report Data Model (Step-by-Step Guide)

When working with reporting in Oracle Corporation Fusion Cloud HCM, one of the most critical and often misunderstood components is the BI Report Data Model. Many consultants jump directly into layouts (RTF/Excel), but in real implementations, the quality of your data model determines report performance, accuracy, and scalability.

In this guide, we’ll walk through how to build a Fusion HCM BI Report Data Model from a consultant’s perspective, including real project scenarios, configurations, and troubleshooting tips.

What is a BI Report Data Model in Fusion HCM?

A BI Report Data Model in Oracle Fusion HCM is the data layer behind a BI Publisher report. It defines:

  • What data is fetched
  • From which source (SQL, HCM Extract, Fast Formula, etc.)
  • How data is structured (groups, hierarchies)
  • Parameters used for filtering

Think of it as the engine of your report — while layouts only display data, the data model actually retrieves and organizes it.

Why Data Models Are Critical in HCM Reporting

In real projects, most reporting issues come from poorly designed data models, not layouts.

Typical issues:

  • Slow reports (bad joins, missing filters)
  • Incorrect employee data (wrong effective date logic)
  • Missing records (incorrect security or joins)

A well-built data model solves:

  • Performance optimization
  • Data accuracy
  • Reusability across multiple reports

Key Features of BI Data Models in Fusion HCM

1. Multiple Data Sources

You can combine:

  • SQL Queries
  • HCM Extracts
  • Web Services
  • Excel uploads

2. Parameterized Filtering

Allows dynamic filtering like:

  • Business Unit
  • Department
  • Employee Number

3. Data Grouping

Supports hierarchical structures:

  • Worker → Assignment → Salary

4. Bursting Support

Used for distributing reports:

  • Email payslips
  • Department-wise reports

5. Event Triggers

Can execute logic before or after report runs.

Real-World Implementation Use Cases

Use Case 1: Employee Master Report

A client wants a report showing:

  • Employee Name
  • Department
  • Job
  • Salary

Data model combines:

  • PER_ALL_PEOPLE_F
  • PER_ALL_ASSIGNMENTS_M
  • PER_JOBS

Use Case 2: Payroll Register Report

Requirement:

  • Group employees by payroll
  • Show earnings and deductions

Uses:

  • PAY_RUN_RESULTS
  • PAY_ELEMENT_ENTRIES

Use Case 3: Department Headcount Dashboard

Requirement:

  • Count employees per department

Uses aggregation logic:

 
COUNT(person_id)
GROUP BY department_id
 

Architecture / Technical Flow

A typical BI reporting flow:

  1. Data Model fetches data (SQL / Extract)
  2. Data grouped into XML structure
  3. Layout (RTF/Excel) renders output
  4. Output delivered via:
    • Online preview
    • Scheduled job
    • Email bursting

Prerequisites for Building Data Model

Before starting, ensure:

  • BI Publisher access (BI Administrator role)
  • SQL knowledge (mandatory in real projects)
  • Understanding of HCM tables:
    • PER tables (person)
    • HR tables (assignments)
    • PAY tables (payroll)
  • Access to:
    Navigator → Tools → Reports and Analytics

Step-by-Step: Build Fusion HCM BI Report Data Model

Step 1 – Navigate to BI Catalog

Navigation:
Navigator → Tools → Reports and Analytics

Click:
Browse Catalog → Shared Folders → Custom

Step 2 – Create Data Model

Click:
New → Data Model

Step 3 – Define Data Source

Choose:

  • Default Data Source (Oracle BI EE)

Step 4 – Create SQL Data Set

Click:
Data Sets → New Data Set → SQL Query

Example Query:

 
SELECT
papf.person_number,
papf.full_name,
paam.assignment_number,
paam.department_id,
paam.job_id
FROM
per_all_people_f papf,
per_all_assignments_m paam
WHERE
papf.person_id = paam.person_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date
 

Step 5 – Add Parameters

Click:
Parameters → Create Parameter

Example:

  • Name: P_DEPARTMENT_ID
  • Data Type: Number

Modify SQL:

 
AND paam.department_id = :P_DEPARTMENT_ID
 

Step 6 – Define Data Structure

After running query:

  • Click “View Data”
  • System auto-generates XML structure

Group example:

  • G_1 (Employee Group)

Step 7 – Add List of Values (LOV)

Create LOV for parameter:

 
SELECT department_id, department_name
FROM hr_all_organization_units
 

Attach LOV to parameter.

Step 8 – Save Data Model

Click:
Save → Provide Name

Example:
Employee_Master_DM

Testing the Data Model

Step 1 – Run Data Model

Click:
View Data

Step 2 – Enter Parameter Value

Example:
Department ID = 300

Step 3 – Validate Output

Check:

  • Correct employee count
  • Proper department filtering
  • No duplicate records

Common Implementation Challenges

1. Duplicate Records

Cause:

  • Incorrect joins between tables

Fix:

  • Use DISTINCT or proper join conditions

2. Missing Employees

Cause:

  • Wrong effective date logic

Fix:
Always include:

 
SYSDATE BETWEEN effective_start_date AND effective_end_date
 

3. Slow Performance

Cause:

  • Large tables without filters

Fix:

  • Add parameters
  • Use indexed columns

4. Security Issues

Cause:

  • Data role restrictions

Fix:

  • Validate user roles
  • Use secured views

Best Practices from Real Projects

1. Always Use Effective Dating Logic

HCM is date-driven — missing this leads to incorrect results.

2. Avoid Hardcoding Values

Use parameters instead of:

 
department_id = 300
 

3. Optimize SQL Joins

Use:

  • ANSI joins instead of old joins
  • Proper indexing columns

4. Limit Data Volume

Never fetch full employee data without filters.

5. Reuse Data Models

Design generic models for multiple reports.

6. Use Meaningful Naming

Example:

  • EMPLOYEE_PAYROLL_DM
  • HEADCOUNT_BY_DEPT_DM

Advanced Tips (Consultant Level)

Use WITH Clause for Performance

 
WITH emp_data AS (
SELECT person_id, person_number FROM per_all_people_f
)
SELECT * FROM emp_data;
 

Use Bind Variables Properly

Instead of:

 
department_id = 300
 

Use:

 
department_id = :P_DEPARTMENT_ID
 

Use Aggregation Smartly

 
SUM(salary)
COUNT(person_id)
 

Use Bursting Keys

For report distribution:

  • Employee Email
  • Manager Email

Frequently Asked Questions (FAQs)

1. What is the difference between Data Model and Layout?

  • Data Model → Fetches data
  • Layout → Displays data

2. Can we use HCM Extract instead of SQL?

Yes, but:

  • SQL is faster for real-time reports
  • Extract is better for large structured data

3. How to improve BI report performance?

  • Use filters
  • Optimize joins
  • Avoid unnecessary columns

Summary

Building a Fusion HCM BI Report Data Model is not just about writing SQL — it’s about understanding:

  • HCM data structure
  • Effective dating
  • Performance optimization
  • Business requirements

In real implementations, a well-designed data model ensures:

  • Accurate reporting
  • Faster execution
  • Reusability

If you master data models, report development becomes significantly easier.

For deeper understanding, refer to official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html


Share

Leave a Reply

Your email address will not be published. Required fields are marked *