I am working at the client site for deploying some of the WEBI reports that we have prepared at our development center.
Here when i run the reports on the production data, the reports take a lot of time to come. I think that there are some issues with the performance tunning of the reports.
I am using BOXIR2 with sp2 and the database is DB2 8.2.
Can anyone plz provide me with the tips or any good document for tuning the reports.
In all my reports we have filter conditions which we apply them on the report end not at the universe end.
HI Friendā¦
I was working on tuning of reports for past months and i would like to suggest the following things:
If your reports are runing slowly, the query might be taking more time to fetch the data from the database. just try runing the same query in database.
Reports might also run slow because of the images and formating of reports. Try to use simple formating for better performanceā¦
āI am working at the client site for deploying some of the WEBI reports that we have prepared at our development center.ā
How was the performance at the development stage?
Are the volumes similar?
If so, are the data schemas, indexes and activity levels similar. Something will be different if the performance is significantly different. You will know, or at least should know, how to investigate this in your organisation.
I am using BOXIR2 with sp2 and the database is DB2 8.2. Have a chat with your DBA. They may not be aware that tuning a reporting database is different to tuning an OLTP database. They should be but itās worth a try. Ask what the indexing strategy for the warehouse is.
Consider filtering the data at the query level - the result set will be much easier to manipulate and will also be returned more quickly.
Slapping aggregate awareness on without knowing what to aggregate is dangerous. By all means consider aggregation, but treat it as a mini project if there are no aggregates already built.
1 - Analyse your reports - what is commonly aggregated and which reports run slowly/donāt perform well
2 - Determine the appropriate aggregates - declare the grain of each one and design your tables
3 - Physical build - tables, indexes, ETL process, etc.
4 - Testing - verify that results from the aggregates are as expected - use raw SQL for this rather than clouding the issue
5 - Build your aggregates into your universe - context for each.
6 - Testing - verify that the aggregate navigation has been set up correctly and that the SQL generated flicks between detail and aggregate table correctly
7 - Deploy - put it live, monitor and review performance.
Itās a world away from adding a ānormalā fact table to an existing universe schema because it can be quite daunting if you havenāt done it before. Remember - better to put it in late and right than on time and wrong; a year from now, users wonāt remember that it went in two weeks late, but they will still be unhappy if you rushed it.
Are there any unused objects being returned in the queries?
Are there any complex queries in the report that could be moved to the universe or preferably the DB?
Also I agree with the suggestion to work with your DBA. Indexing can work wonders sometimes.
(BOB member since 2007-12-11)
(BOB member since 2008-02-05)
(BOB member since 2002-06-20)