Fine tuning Catalogs.

[Srik]

Hi ,

Can any advice me how to fine tune catalog for better query results and also to improve  Performance reports.

Thanks in advance.

Kanth

[cognostechie]

Basically, check the joins in the catalog and synchronise that with the indexes in your database, though this is not always possible as you may not have all the indexes required in the DB. Also check if there is any cross-join.

The performance also depends on which DB you are using because different databases interpret SQL differently. Some take more time to interpret and return the data to Impromptu.

[dwood09]

I don't know if this is applicable or not, but we moved from ODBC to OLE DB as the connectivity layer, which made SQL server queries run much faster.  It is true of all connections, if you can use native rather than generic, then you will experience performance improvments.

cheers,
DW

[TEVA_COGNOS]

To improve performance with impromptu:

Do the maximum of data transformation when loading your datawarehouse (preparation of date for instance)
Do the minimum data transformation in Cognos and it is better (according to my experience) to use database functions that cognos functions because cognos functions are applied in local i e after the results of the query...

Model as much as possible a perfect star scheme with (1,1) cardinality (even if you have to denormalize dimension) between fact table and dim table

Index the fields used to link your tables

Check the SQL generated by Impromptu in order to be sure that is the most optimized query. 
Copy the SQL of cognos in your database windows and analyse the SQL to be sure the index are used.

To be honest, in general the SQL generated by impromptu is not the best and sometimes without reason Impromptu generates a cross join when a join is possible.  I noticed in this case that the way to build the report was important to force impromptu to generate the better SQL.  Futhermore, create some hidden calculated fields enable sometimes to force impromptu to generate the best SQL.

Teva

[Sridark]

Optimizing Impromptu :-

You should be well known in this area.

1.   client/database-server balancing
2.   table weighting
3.   qualifications
4.   governors
5.   alternative data sources
6.   user-defined functions
7.   bulk fetch
8.   stored procedures
9.   auditing

If you can understand this concepts, try to implement it and notice the difference in performance.

Apart from this , to test the perfomance limit the number of rows,Use Snapshots and Thumnails.

Regards
Sridar.K