Hi Ayala01,

Potential Causes of Data Variation:

1. Query Execution Plan Changes

Optimizing execution time may have altered the query execution plan, causing different indexing or join strategies to be used.
This can lead to variations in how data is fetched, especially if the query involves aggregations or joins.

2. Parallel Processing Effects

Some optimization properties enable parallel execution, which can cause slight differences in how data is retrieved.
If the query is executed in multiple threads, the order of data retrieval might change, affecting certain calculations.

3. Data Model Property Impact

Some BI Publisher data model properties (like fetch size, caching, or query rewrite) can influence how data is processed.
If caching is enabled, it might return slightly different results based on previous executions.

4. Implicit Sorting Differences

If the optimization removes an implicit ORDER BY, the data might be retrieved in a different sequence.
This can cause variations in reports that rely on default sorting behavior.

5. Oracle BI Publisher Performance Tuning Considerations

Oracle provides best practices for optimizing BI Publisher reports, including avoiding excessive filtering and ensuring proper indexing.

Oracle Business Intelligence Publisher Performance Recommendations for Fusion Cloud Applications (Doc ID 2800118.1)
Oracle BI Publisher Best Practices for SaaS Environments (Doc ID 2145444.1)
https://docs.oracle.com/middleware/bi12214/bip/BIPDM/GUID-478A2B1D-BC89-421E-9702-5749BA49EFD2.htm

=======Please review the following best practices to be followed to create a data model =======

Data model creation best practices:

-> Edit data model -> click on Validate. Check if you see any 'Warnings' or 'Errors'. Note the warnings and errors, modify the SQL to avoid them.
-> Analyze the explain plan and identify high impact SQL statements.
-> Add required filter conditions and remove unwanted joins.
-> Avoid and remove FTS (full table scans) on large tables. Note that in some cases, full table scans are faster and improve query fetch.
-> Use SQL hints to force use of proper indexes.
-> Avoid complex sub-queries and use Global Temporary Tables where necessary.
-> Use Oracle SQL Analytical functions for multiple aggregation.
-> Avoid too many sub-queries in where clauses if possible. Instead rewrite queries w
-> Avoid group functions like HAVING and IN / NOT IN where clause conditions.
-> Use CASE statements and DECODE functions for complex aggregate functions. Please refer below document for best practices.

========OTHER CONSIDERATIONS========

• Only Return the Data You Need
• Use Column Aliases to Shorten XML File Length
• Avoid Using Group Filters in Data Model. Instead, enhance Your Query
• Avoid PL/SQL function Calls in WHERE Clauses
• Avoid Use of the System Dual Table
• Avoid PL/SQL Calls at the Element Level
• Avoid Including Multiple Data Sets
• Avoid Nested Data Sets
• Avoid In-Line Queries (as summary columns)
• Avoid Excessive Parameter Bind Values
• The number of columns selected in SQL should not exceed 100
• Sorting should be done in the SQL, not during report formatting
• Avoid having WHERE clause on large tables with only NOT IN or <>