BusinessObjects Board

Webi & Better Performance

building reports webintelligence
#1

Below is from my notes.

Couple Questions:

  1. what am I missing below?
  2. “…Allocating table weights…”: how do I do that, and what are the benefits?
  3. “.…Minimizing usage of derived tables….”: What are the benefits? I thought having derived tables has zero effect --apparently I’m wrong–
  4. for XIR2 web.xml location is fine, for XIR3 there are more than one web.xml files exist, for timeout purposes which one to modify?

thanks

MrH

===========================================

::
:: Faster WEBI Reports ::
::

<< 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 20 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
    ‘from’ 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 ‘from’ 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:
    • Browse to directory Business Objects\BusinessObjects Enterprise
    XI R2\dataAccess\RDBMS\connectionServer\oracle.
    • 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”>

Y
Y
<Parameter …
The SQL from clause of all Business Objects reports is now reverse weighted, largest to smallest rows (refer ‘After’ 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.

MrH :us: (BOB member since 2007-08-17)

Any methods to speed up a report load time ??
Open Doc to Report Instance Passing Report Filters
Experiencing Slow Performance in WebI
Webi report takes ever to run
Universe optimization
how we improve the performance of report and universe?
Report Level Performence
Performence Tunning
BO report taking long time to fetch data in Infoview
troubleshooting steps
performance tuning
WIS 30951
Webi Report Performance
Performance issues while pulling the data from DB2
Reg webi report
Report takes longer to display the output
Document can't be retrieved
webi report performance issue
Tuning Report Performance
#2

See also Why is my report so slow, an oldie but goldie 8)

Andreas :de: (BOB member since 2002-06-20)

#3

If you are using the rule based optimizer then the best performance tweak you could make is almost certainly to use the cost based optimiser, not to modify the order of the tables in the from clause. I’d expect that the number of systems running under the RBO is vanishingly small nowadays.

slimdave :uk: (BOB member since 2002-09-10)

#4

Is there any parameter or setting in BO that makes it use the Rule based optimizer by default in BO?

We would like to use the cost based optimizer and disable any such parameter if it exists.

vips4life (BOB member since 2010-08-11)

#5

A hint could be specified in the connection definition – that would be worth checking.

slimdave :uk: (BOB member since 2002-09-10)

#6

Thank you MrH for the Info. Could you please give some hints for Netezza.PRM file changes for optimized performance or show me some directions where I can find it.

I also tried loking at XIR2 docs but did not find in the serever docs. I am concerned about over all BO query performance and we use DeskI a lot.

Ronika (BOB member since 2009-06-03)

#7

Stumbled upom this thread while searching for somthing that was out of context…very informative

Request the moderators to move this thread to the FAQ’s…

BTW just one query

What’s a 6sigma approache?

nitin_gons :india: (BOB member since 2009-05-26)

#8

Six Sigma:
DMAIC approach (Define, Measure, Analyze, Improve, and Control) for existing product and services.
DMADV approach (Define, Measure, Analyze, Design, and Verify/Validate) for designing new products and solutions.

This helps in improving the performance, profit etc.
Please let me know in case you need any more information.

deepak21.s :qatar: (BOB member since 2010-09-06)

#9

I have one doubt in one of the above mentioned point:

Not associating the List of Values (LOV) Objects which you don’t want to use as a prompt… How does this improve performance of report?

akl007 (BOB member since 2010-03-23)

#10

Hi

LOV should be displayed to business user for identifying the data quickly so no need to disable this property…
by the way which version of BO are you using?

Regards,
Venkat
www.rrtechsol.com

venkat vegi :india: (BOB member since 2014-02-03)

#11

It depends what the object is. Typically you don’t expose LOVs for measures.

#12

Hi,
I’m a long time BI Superuser, not a BI admin. I have a scheduled XI R3.1 report which was running fine but recently has issues, both when scheduled and when refreshed manually. The data is a running 12 months worth so is fairly fixed in terms of the number of records - combinations of ~ 4800 unique products, 40 unique customers and 30 invoice types sumnmarised at monthly level in the universe. !2 Dimensions and two Measures only.

It runs fine without filters, but when I add filters on a couple of dimensions of products, it takes a serious amount of resource to complete:

  • No filters the instance ran this morning in 455 seconds
  • With two (prompted) filters on Dimensions, one and one the instance eventually completed in 14,916 seconds

There are several similar reports across our universes which use similar filters which were also affected but to a lesser degree, so this seems unlikely to be an issue with the report itself. Indeed I completely re-created it and got near idential run times.

The BI Admin team seem to be at a loss. They have deleted and rebuilt the indexes on the dimensions being filtered and there was no difference. Any suggestions as to where to look next?

Thanks for your help!
John

Uncle John (BOB member since 2010-12-21)

#13

Have you got access to the CMC?
I would run the SQL using an appropriate SQL tool to take BO out of the equation. That way you can start to understand if the time it takes the schedule to complete is the same time as it takes the SQL to execute.

If there are a large number of rows returned or even a large number of row/column intersections or the resulting output is large you can sometimes see the query part of schedule take, for example, 30 minutes and the save as excel a further 15 minutes.

Having read your post again, it sounds like pure WebI refresh anyway. Get back to us and let us know.

Nick Daniels :uk: (BOB member since 2002-08-15)