Hi there, good day! Here’s our scenario and problem:
The processing time of our report in WebI is so slow (30mins to 1hr++). It involves user prompts which serve as filters and 2 tables with millions of data (9million and 2 million),among others. These 2 tables in particular have the many-to-many cardinality. During UAT, it was realized that the processing time of the report retrieval depends on the volume of the actual data being returned. In this sense, there are cases when processing time is less than 1 minute. given that the data entered by the user encompasses only a small number of records but when a different data is entered, which is expected to encompass a large volume of data, processing takes more than even an hour. By the way, ETL side of this is thru Oracle Warehouse Builder (OWB).
I’ve tried creating an index on the tables concerned but still, processing time takes 1hr++ for some user prompts. I test this thru SQLDBX wherein direct DB access is performed (relatively, this is faster than thru Business Objects – Webi).
As we are looking for optimization/resolution on this matter, we came to the possibility of using Views. I’ve scratched out the idea of Derived tables because when I tried them, processing time worsened. About Views, is it possible to pull data from OWB to Designer Universe ? If so, kindly advise its effects, improvement (if any) or advantages/disadvantages.
If Views are not recommended in this scenario, are there any other ways we can do to improve this?
Any comment/suggestion will be greatly appreciated.
Thank you.
chinky 
(BOB member since 2010-10-12)