Report refreshing for long time

[Former Member]

I have a record count of 45,000 . I followed the best practices to create the universe and report( Webi Report) .But still my report is taking 45 seconds. Please let me know how to optimize the time , the report runs.

All my users want report less than 7 seconds.

Even I changed the settings in the universe designer:

Limit the size of result set to 50.000.

Looking forward for your inputs.

Thanks,

Prathyusha.

[Former Member]

just try to run your SQL script generated by WEBI report on database.

After running compare the time taken to run query.

If there is no difference in time then there would be issue in query you have to optimize it.

Or else just provide more info about query to us so that we can help

[Former Member]

Actually I took the SQL Script generated by WEBI on database, its taking less than 2 seconds . I don't know if its with the report only.

Any more thoughts..

Thanks,

Prathyusha.

[Former Member]

can you please provide snapshot of query?

[Former Member]

Snap shot of database mean , like how long it ran or the whole table details?? This is simple report and have a 2 derived tables in the universe.

[Former Member]

Can I have snapshot of query generated by report?

[former_member191664]

1. "By default, BusinessObjects sorts from the lighter to the heavier tables (tables with the least amount of rows to those with the most). This affects the way that tables are ordered in the FROM clause of the SQL statement. The way that tables are sorted at the database level depends on your database."

To allocate Oracle table weight for better report based on Oracle db performance, for example,

D:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\jdbc\oracle.prm

D:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\jdbc\oracle9.prm

D:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\oracle\oracle.prm

D:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\oracle\oracle9.prm

- edit each oracle.prm file, change the REVERSE_TABLE_WEIGHT value to N from Y.

See http://studentworld4u.blogspot.com/2009/05/performance-tuning.html for detail.

Also see SAP KB 1181398 - Reverse_Table_Weight parameter not working for Oracle on http://service.sap.com/sap/support/notes/1181398 that "The Reverse_Table_Weight parameter works only when the Outerjoins_Generation parameter is set to ANSI_92."

2. check "Query Stripping" from the "Document Property" in your Webi report.

At last, check out Tips for Optimizing the Performance of Web Intelligence Documents on http://scn.sap.com/docs/DOC-58571.

Hope this helps,

Jin-Chong