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.
