When refreshed first time in the morning, the report produced
blank results. When refreshed right after, it displays data.
Report has multiple queries (three) against three different universes
Xi3.1 any version
Reporting from Oracle DB
Reproducing the Issue
Create Webi report based on 3 diff universes.
Wait untill next morning and refresh report - this first refresh will take 10 minutes and then show empty report with no data
Refresh report immediately again and this time it takes 3 minutes to run and displays data correctly.
By reviewing complete Webi traces we determined that the problem was due to first Query, which run for 10 minutes and then was canceled at Oracle end without returning data.
During second refresh this same query would come back after 3 minutes and with data.
Looking at the universe we determined that the Universe this query was against had 10 minute execution limit.
Then we tried to run this same query in SQLPlus, by-passing BO product, and this test showed that the first refresh took more than 10 minutes, second - close to 3 minutes.
So we determined thet the issue was on Oracle side, where first query takes more than 10 minutes , then DB query is cached/optimized by Oracle DB and second run gives the data much faster.
There are 2 ways to address this issue :
1. Increase execution time for the universe in Universe parameters, this allows query to return data even during first refresh.
2. Engage DBA assistance to investigate and improve first query run on Oracle DB side (better partitionaing, change in joins definitions) to cut the first query time.
empty report; first refresh; results; different , KBA , BI-RA-WBI , Web Intelligence , Problem