cancel
Showing results for 
Search instead for 
Did you mean: 

Query performance

Former Member
0 Kudos

Hi

How can i improve the query performance in boxir2 using webi report

.I check the following

Indexes,aggrgate functions.Other than this how can improve the performance of the query

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hai Madhu,

Wheather it improves query performance or not, we should take care of the following points to improve performance in all ways:

1)Using add queries instead of sub reports in WebI

2)More contexts should be used rather than aliasing

3)Measure function must have projection function set to database delegate

4)Never make alias of fact tables

In addition to what you said.

Hope this helps you,

Pujitha

Answers (7)

Answers (7)

Former Member
0 Kudos

Thanks

Former Member
0 Kudos

Thanks

Former Member
0 Kudos

thanks alot

Former Member
0 Kudos

Hi..

Check out this URL.

http://forums.sdn.sap.com/thread.jspa?threadID=1921910&tstart=0

I have provided the check points where the performance bottlenecks can be removed.

0 Kudos

If you are facing a specific problem, please provide more details. Performance optimization is more of an art than a science.

What kind of data source do you use in your universe? Did you try running the SQL statement directly against your data source?

Regards,

Stratos

Former Member
0 Kudos

Hi,

Performance Tuning in Universe:

1. In Universe design Un-necessary Tables, Classes, Objects and joins are to be remove.

2. Use Shortcut joins for query retriving fast.

3. Integrity Check.

4. Use Index awareness

5. Disable List of values for Measures and unncessary dimension Objects.

6. Partitioning the fact table can boost your query performance.

7. Keep the number of key figures low to increase the performance

Performance Tuning in Web I:

1. Un-necessary Objects remove from Query Panel.

2. Limit the no of Rows for report level analysis and usage.

3. Use Query filters and report filters.

4. Array fetch size will be use as max.

5. Limited use of complex queries and Prompts.

6. Decrease the Creation of variables.

All the Best,

Madhu...

Former Member
0 Kudos

Hi,

Query perfomence dependent this levels:

<< Universe Level >>

  • Modifying the Array Fetch parameter (advanced parameters window of connection. Just edit connection in Universe and click twice Next. or we can control this parameter in CS.CFG file. Bigger number = faster query but it should be set up according to capacity of memory)

  • Allocating table weights

  • Using shortcut joins

  • Using aggregate functions

  • Creating and using aggregate tables

  • Minimizing usage of derived tables

<< Report Level >>

  • If you insert the Page Number/TotalNumberOfPages cell or the TotalNumberOfPages cell into a large report with many pages, Web Intelligence takes longer to return the data to the report.

  • Opting for Refresh At-Will over Refresh-On-Open

  • Not associating the List of Values (LOV) Objects which you don't want to use as a prompt

  • Using universe Condition Objects, try to restrict/filter the data at universe end rather than at the report end.

  • Keeping Complex Calculations at universe side.

  • Minimizing usage of Report variables/formulas

  • Auto height and auto width on report cells sucks in resources. setting cell sizes to fixed increases performance.

<< CMC Level >>

  • Increasing Maximum Allowed Size of cache (CMC / servers / service categories / webi / Properties of webiprocessing server / Maximum Document Cache Size (KB) <<---- increase the default!!!)

  • Increasing Minutes Before an Idle Connection is closed

  • Increasing File Polling Interval in seconds

  • Adjust Connection timeout, keep it as small as possible (webi report server attribute)

<< Config File Level >>

  • freeing up unused system resources

keep the session time out as small as possible (default is 20 mins)

XIR2

for Java: web.xml file found here:

"..\Tomcat\webapps\businessobjects\enterprise115\desktoplaunch\WEB-INF\web.xml"

by changing the <session-timeout> 20 </session-timeout> entry.

XIR2

for .NET: web.config file found here:

"..\Business Objects\BusinessObjects Enterprise 11.5\Web Content\Enterprise11\InfoView\Web.config"

by changing the timeout=" 20 " entry.

  • Changing the SQL works for specific RDMBS.

By default, Business Objects (Ver 4.1.3 Onwards) sorts the tables in the

u2018fromu2019 clause in ascending order of the row count i.e., the table with

minimum number of rows is placed at the beginning. Sybase uses the same order as Business Objects and so there is no effect on performance. But Oracle sorts the tables in descending order of the row count. So the table with maximum number of records is placed at the beginning. Oracle's rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. For optimum SQL execution in Oracle, the u2018fromu2019 clause should be in order from largest to smallest which is the reverse order of that of BO and so affects the performance of the BO reports.Resolution: Table weight is a measure of number of rows in a table. To change the default Business Objects settings, the ORACLE PRM file must be modified as below:

u2022 Browse to directory Business Objects\BusinessObjects Enterprise

XI R2\dataAccess\RDBMS\connectionServer\oracle.

u2022 Open ORACLE.PRM file, change the REVERSE_TABLE_WEIGHT

value to N from Y.

Snippet from oracle.prm file:

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE DBParameters SYSTEM

"../dbparameters.dtd"><DBParameters>

<Configuration>

<Parameter Name="REVERSE_TABLE_WEIGHT">Y</Parameter>

<Parameter Name="BACK_QUOTE_SUPPORTED">Y</Parameter>

<Parameter u2026

The SQL from clause of all Business Objects reports is now reverse weighted, largest to smallest rows (refer u2018Afteru2019 in figure 2.2). Now both BO and Oracle uses the same sort order and so the BO Report takes less than 10 seconds to open.

<< Server Level >>

Horizontal and Vertical Clustering. Load Balancing and Failover.

<< Hardware Level >>

Better hardware. All BOE related servers & DB Servers within the same LAN if possible. Gigabit Connections between servers. DB and BOE Servers on physically separate machines.

<< User Requirement Analysis >>

Stick to 6Sigma if budget allows. Rest of the approaches are destined to fail.

<< DW Background >>

Learn Datawarehousing from Kimball's methodology, Inmon's methodology is very hypothetical, unpractical and unfortunately not well fit for proper reporting system design.

<< Other >>

for .NET in IIS minimize the idle time out. (Application pool with Infoview, Properties, Edit the Shutdown worker processes after being idle for (time in minutes): on the Performance tab). Restart IIS, Webi App server, and Webi Rep server.

All the best,

Praveen