I have a report which takes 5 hours to run...
This report has almost 20 columns among which four are calculated columns(i.e,. case statements almost 50 when.. then.. on each column) and has some filter applied on these calculated columns and on direct columns as well.
Some of these columns are fetching data from fact table which again has a very huge data...
How to handle these situation, Suggestions Pleaseee...
Obiee is best suited for aggreagate data display. Beeter sollution is to create aggreagete table in the database then query from the aggrgate table instead of hitting table with huge data set.
mark if helps,
Try to insert result set in new table using a procedure .. and build report using the new table. Update the result set table by scheduling the procedure as daily/hourly.
Please mark if it is useful!
Edited by: 856152 on Dec 18, 2012 11:43 PM
Try to implement cache mechanism.
2. Try to use case statement in pd it self.
3. Create Aggregation tables.
4. Use Hints in Physical Layer.
5. Try to enable cache in physical layer.
The above methods some what you will get some how performance.
Hope this help's
There are couple of things I can suggest.
1) Create materialized views for that face table in database and then try to use
2) First test if its actually the issue with calculated measures or some other reason. You can do this by running the report by removing those 4 calculated columns.
3) If possible try to split the report into two reports and do a union.
mark helpful/correct if it helps
Thanks for your valuable suggestions.. I found that, Calculated columns are not the issue, i have removed those calculated columns and checked but still the query runs long..
And I have some limitations..
Working on OLTP
Dont have Cache Concept
Tables(has lakhs of records for even a single month) used are normal Views.
So the option for me would be creating an MV ??? or any other options ??
If i create an MV then its gonna improve the query performance ??