How to handle multiple queries simultaneously in Crystal reports connecting from Business object Universe.
page 2 of 3
by Umesh Bansal
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19396/ 25
Article Contents:

Explanation

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.

 


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-07-23 2:00:36 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search