Once the project is submited to the Client. Client says
that report is taking long time to run? Where u need to
check?
- 2 Answers
- 6671 Views
- Wipro, I also Faced
- E-Mail Answers
Answer Posted / bharat
1. How would you improve the performance of the reports.
Performance of the reports starts with analyzing the problem.
The problem could be with database, Universe or the report
itself
Analyzing the database:
-Run the SQL from the report on an oracle client like SQL
Navigator or toad after passing in all the parameters.
-Identify if the SQL takes considerable less time than the
report. If yes then the problem is with the Universe or with
the report if no then
-Run an explain plan on the SQL
-Look to see if all the statistics are computed, indexes are
built and the indexes are used
-Check to see if aggregate tables can be used ( Aggregate
tables are useful if the data can be concised to 1/10th of
fact data)
-Check to see if data has increased and usage of
materialized views could help.
oCreating materialized views enable to pre -run the complex
joins and store the data.
oMost of the DW environment has a day old data hence they
don’t have lot of overhead.
oRunning a report against a single materialized table is
always faster then running against multiple tables with
complex joins.
oIndexes can be created on this materialized view to further
increase the performance.
-Check to see if the performance of the SQL can be increased
by using hints ,if yes then add a hint to the report SQL and
freeze the SQL, this might have an additional overhead of
maintaining the report
Analyzing the Universe:
- Check is all the outer joins are properly created and
remove unnecessary outer join
- Business Objects as such do not use Multi Pass SQL , Multi
pass SQL is a technique a software use to break down a
complex SQL into multiple smaller SQLs. Hence a query using
one fact table and three dimension tables can be broken down
into two, one between the dimension tables and the second
between the first result and the fact table. This can be
achieved in BO by creating Derived Tables. The derived table
would be based on three dimension tables and the reports
hence can use one derived table and one fact table instead
of four tables.
- The Keys tab allows you to define index awareness for an
object. Index awareness is the ability to take advantage of
the indexes on key columns to speed data retrieval.
o In a typical data warehousing environment surrogate keys
are used as primary keys instead of natural keys , this
primary key may not be meaningful to the end user but
Designer can take advantage of the indexes on key columns to
speed data retrieval.
o The only disadvantage is it would not return duplicate
data unless the duplicate data has separate keys
- Check to see if the size of the universe has increased
recently
oTry to create a different universe for new requirements
oUnder extreme conditions the AUTOPARSE parameter in the
param file can be turned off, this could be too risky if not
handled properly.
Analyzing the Report:
-Check to see if there are any conditions which could be
pushed into universe as Filters
-Check to see if a formula has multiple usage ,turn this to
a variable
-Check if there are any variables which are not used, remove
them.
-Remove any additional filters or alerters on the report.
| Is This Answer Correct ? | 5 Yes | 0 No |
Post New Answer View All Answers
Can we configure two instances of CMS pointing to two different CMS system databases?
What are the general problems that are faced while importing the .biar files and what is the solution to overcome it in business objects?
If I have three predefined prompts. In a report it will come randomly. How they will come in a specified format?
CAN ANY BODY PROVIDE DESCRIPTION OF ONE COMPLEX UNIVERSE AND ONE COMPLEX REPORT IN BOS 6.5 TO SRINIVASBO@YAHOO.CO.IN
What Is Snow Flake Schema?
I have 2 doubts regarding the "Input Control" functionality in BI4: 1) Can the Input Controls in BI4 be manipulated? I mean, can we change the IC display as BOLD or change the values from Italics to regular? Or change the colors? 2) How to get hierarchy type control in IC? Let's say my first IC is for Country and second is for State. If I select a particulat country from Ist IC,second one should show me states belonging to selected country. Is that possible? Thanks in advance!!!
Hi I'm using data federator.I would like to know if it allows select statment in where clause.For example: SELECT Employee, company, orgunit FROM TAB_BIG WHERE employee IN (Select TAB_SMALL.employee FROM TAB_SMALL WHERE flag = X ) bye
Explain are data mart and dwh normalized or denormalized? Do both of them exist in the same tier?
Regarding Hyperlinking in Business Objects. We have a few reports in QA environment where a number of hyperlinks are employed using OpenDoc function.In some of the reports,hyperlink is used to move to another tab within the same report.The formula used include "DocID" and ReportName="tab name".This works fine but now,for the PROD move,this will not work as the Doc Ids are subject to change. Is there a way to overcome this?Without DocID,how will move to a particular tab? Thanks in advance
How were the reports scheduled and how to resolve unexecuted documents?
Explain what are the measure objects?
1. 101 vinay hyderabad 123 naresh usa 152 singh delhi 101 vinay usa 123 naresh mumbai 156 raju chennai From the above data we need to retrive current data in a report ? how and explain in detail ?
Explain the data types present in bo and what happens if we implement view in the designer and report?
Explain is data mart?
Hi friends,
I am trying to create OLAP connection in IDT. I am getting
error "error code:405 METHOD NOT ALLOWED"
I am using "http:
