Hi,
I have universe and few reports are on ODS. Reports performance is very poor so I would like to know tips and tricks to improve performance on reports and universe as well. Please share your thoughts and expertise.
Thanks!
MJR (BOB member since 2003-03-31)
Hi,
This can give you some ideas:
Marek Chladny 
(BOB member since 2003-11-27)
There isn’t much focus in that link on ODS-specific data.
What you may want to consider is a set of summary tables for the commonly run queries. If you’re not allowed to create tables, see about materialised views in Oracle - don’t use derived tables because they won’t improve performance, they’ll just change where the SQL is written (in designer rather than in reporter).
An option is to see if you can get an index or two added to the database but you need to understand that the DBA may push back on this if the table is heavily used for input and the index would have a detrimental effect upon the performance of the system from the user’s viewpoint.
Another option is replication (using the appropriate toolset depending upon the RDBMS).