Let me provide a comprehensive optimization strategy for your headcount dashboard based on the three key performance factors:
OTBI Dashboard Filter Optimization:
The primary issue is likely how your filters interact with the underlying queries. In 24A, Oracle changed the filter execution order for OTBI dashboards. Filters now apply sequentially rather than in parallel, which can cause slowdowns with multiple dimensions. To optimize:
Review your filter configuration and implement filter hierarchy. Set Department as your primary filter, then Location as secondary, then Job Family, and finally Employment Status. This order matters because Department typically has the highest cardinality and will narrow the result set most effectively first. In your dashboard prompt settings, configure filters to use “Limited by Other Prompts” so each subsequent filter only shows relevant values based on prior selections.
Also, replace text-based filters with ID-based filters where possible. Instead of filtering on Department Name (varchar), filter on Department ID (number). Numeric filters execute much faster, especially with indexes. Update your visualizations to display the descriptive names while filtering on IDs behind the scenes.
Subject Area Join Efficiency:
With 12 visualizations on a single dashboard, you’re likely creating redundant joins to dimension tables. Open each analysis in your dashboard and review the Criteria tab. I suspect you have unnecessary table joins that were automatically added. For headcount dashboards, you typically only need the Workforce Management fact table and directly related dimension tables.
Remove any indirect joins - for example, if you’re joining through Assignment to Position to Job to Job Family, simplify by using the direct Assignment to Job Family relationship if available in the subject area. Each extra join adds query complexity. In the Advanced tab of your analyses, check the SQL query being generated. Look for multiple LEFT OUTER JOINs to the same dimension table, which indicates inefficient join paths.
Consider creating separate analyses for different visualization groups. Instead of one large analysis feeding 12 visualizations, create 3-4 focused analyses. One analysis for headcount trends over time, another for current distribution by dimensions, and a third for vacancy metrics. This reduces the join complexity in each individual query.
Indexed Field Usage:
This is critical and often overlooked. The Workforce Management subject area has indexes on key fields, but not all filter fields are indexed. Review your filter columns:
- Department ID: Indexed (use this)
- Department Name: Not indexed (avoid filtering on this)
- Location ID: Indexed (use this)
- Location Name: Not indexed (avoid)
- Employment Status Code: Indexed (use this)
- Job Family ID: Indexed (use this)
Update all your dashboard prompts and filters to use the indexed ID fields. In your visualizations, you can still display the descriptive names by including both the ID (for filtering) and Name (for display) columns, but ensure filters only operate on the indexed ID fields.
Additionally, implement these performance best practices:
- Enable dashboard caching: Set cache to refresh every 6 hours since workforce data doesn’t change minute-to-minute
- Add a date range filter defaulting to current quarter - don’t load historical data unless needed
- Limit your initial dashboard view to show summary data, with drill-down options for detailed views
- Use dashboard-level filters rather than individual visualization filters to ensure filter values apply once across all queries
- Set row limits on your analyses (e.g., top 100 departments by headcount) to prevent massive result sets
After implementing these changes, test with your largest department filter first. You should see load times drop to under 45 seconds for most scenarios. If performance is still poor, consider requesting Oracle Support to review your subject area configuration and verify that all standard indexes are properly maintained in your environment.