We have come across in a situation where we have to generate the crystal report from more then 2 queries using crystal reports designer. All queries contains different-2 transaction tables with relevant joins from dimension tables. So to make the report, we have to create different-2 connections from the universe and it eventually gave performance problem while dealing with larger sets of records.

 

In this case, we have to link each query with other queries on the basis of common dimension and while fetching the report for long range of data, it takes a lot of time to complete.

 

For small amount of data, linking of the query with each other will work fine but when larger amount of data going to be processed eventually crystal reports takes a lot of time.

The reason for taking a lot of time for report is, because there are multiple connections we have created for every query to connect the universe, so at the time of running the report each connection, accessing the database with its own time and at the time of linking each query on the basis of common dimensions, the report took a lot of time to complete. As each individual query work like a single table and without index. Also each individual query contains a fact table with dimensions from different-2 dimension tables. so at the time of linking each query with another query using link option, eventually it will give performance issue when trying to refresh the report.

 

Even if we set the parameter "JOIN_BY_SQL = Yes" in universe, then in that case also, there will be multiple fact tables joined in a single query and the query will be more costly, as the query will consist more then 1 fact table in its definition. The explain plan of the query will show huge cost to complete the query.

 

We can directly surpass the universe and connect the reports with database directly but in this case also we have to join multiple fact tables in a single query and that will also take more time.

 

The best solution to improve the performance, create aggregate tables in database which will consists all the requisite object's related columns and so eliminating the need for more then one query in the reports. Definitely for making the aggregate tables in database requires one more step to organize all the columns from different-2 tables and also it require a procedure which populates the aggregate tables based on the frequency of data.

 

The same thing in web-intelligence is working fine. In webi, we don't require to link each and every query on the basis of common dimensions. So each and every query run independently and only 1 database connection solves the purpose. After all queries fetch the data, the result set automatically combined on the basis of common dimensions.

 

For the smaller amount of data, the linking of queries with each other work fine but for the larger amount of data, all the data should be come from single aggregate table.