Oracle Fusion HCM OTBI - Chapter 11: OTBI Performance Optimization Techniques
📖 Content
Why Optimize OTBI Performance?
OTBI reports, especially with large datasets, can suffer from long load times and slow performance. Optimizing performance ensures users get timely insights and reduces system strain, especially during peak times.
⚙️ Key Performance Factors in OTBI
-
Report Complexity: More complex reports (with many columns, filters, and aggregations) take longer to execute.
-
Data Volume: Larger datasets (e.g., 100,000+ rows) naturally take longer to process.
-
Server Resources: OTBI runs on the Oracle BI server, which may have limited memory, CPU, and disk space during heavy usage.
-
Filters and Prompts: Excessive use of filters and prompts can slow down performance if not properly indexed.
🛠️ Top Optimization Techniques
✅ 1. Limit Data in the Query
-
Reduce the number of columns in your report to only those that are necessary.
-
Avoid pulling all records in large datasets; instead, narrow down data with filters.
-
Limit date ranges or other dynamic criteria to reduce the total number of records fetched.
Tip: Narrow data ranges to 3 months or less for reports that track metrics like hires, terminations, or compensation.
✅ 2. Use Aggregations
-
Use aggregated measures like
SUM,AVG, orCOUNTinstead of fetching all transactional data. -
Avoid using detail-level data if only summary results are required.
Example: Instead of pulling individual payroll records, aggregate by department or position to reduce rows.
✅ 3. Efficient Filtering
-
Use indexed columns for filters (e.g., employee ID, date ranges, and departments).
-
Avoid using complex expressions in filters; stick to simple equality, range, or "IN" conditions.
Example: Use a department filter like Department Name = 'HR' instead of filtering with expressions.
✅ 4. Leverage Presentation Variables
-
Use presentation variables to dynamically control filters or columns based on user input. This allows users to control the data displayed, reducing the amount of unnecessary data being fetched.
Tip: Create default values for presentation variables so that users don’t have to input frequently used criteria (e.g., current fiscal year).
✅ 5. Optimize Joins
-
Avoid unnecessary joins across different folders or subject areas. Keep your queries simple by only joining the necessary columns.
-
If joining across subject areas is necessary, try to limit the number of rows involved by applying filters early in the process.
🧠 Real-Time Scenario Example:
You need to create a report showing employees’ current salary details by department.
Use the Compensation – Salary Details Real Time subject area.
Apply filters like Current Salary Only, and Department Name.
Use SUM aggregation for total salary per department.
Avoid bringing in old salary history or unnecessary columns.
📊 Advanced Techniques
✅ 6. Schedule Reports for Off-Peak Hours
-
If your reports are used infrequently but require large data sets, consider scheduling them to run during off-peak hours.
-
Use delivery options to send reports via email or FTP.
✅ 7. Partitioning Large Datasets
-
In environments with very large datasets (e.g., payroll or time tracking), partitioning the data by time (e.g., monthly partitions) can make queries much faster.
⚡ Additional Tips
| Tip | Reason |
|---|---|
| Use simple formulas for calculations | Avoid complex formulas in real-time reports, as they slow down performance |
| Always test performance before deployment | Test reports with different data volumes and complexity |
| Avoid using unnecessary formatting | Extra formatting elements (fonts, colors, etc.) add load time |
No comments:
Post a Comment