Database
Different DBMS’s require different solutions. Know the DBMS and DBA’s to find out what is best.
Database design – How the data is stored in the database will have a huge impact on performance. Data Warehouses, designed for data retrieval, perform much better than transaction based production systems, designed for data input.
Indexes – Make sure the database is well indexed. Fields used in table joins should be indexed. Fields commonly used in reports may need to have additional indexing.
Maintenance - Make sure the database is well maintained. Indexes and db statistics need to be refreshed regularly.
Views – Complex joins or calculations may be faster in a view. Views can offer ways around bad database design or DBMS limitations. Views can also simplify universe design. It may be possible to influence indexing or to use DBMS specific functions that are not generally compatible with Business Objects with a view.
Connection drivers – Use native DBMS drivers whenever possible. ODBC is usually much slower.
Dummy rows – Adding blank rows to tables can help avoid outer joins. Add a row that can be linked in where no data exists. The row should contain nulls, blanks or 0’s to avoid altering any query results.
Add fields - Doing common string manipulations and calculations on the database load saves report time.
Delete trailing blanks – Padded fields in the database mean extra time in the report to trim the blanks off again.
Universe
PRM files – PRM files set options on how Business Objects reacts with specific DBMS’s. Properly setting these options can affect performance.
Array fetch size – The array fetch size is the number of rows brought back in a single network packet. The default is 50. SQL Server OLE DB requires a 1. The effects of changing this can vary from report to report on the same universe. Experiment with various values (suggest 1, 50, 100, 500, 1000).
Aggregation of measures – Adding aggregation functions (Sum, Count, Min, Max, etc) to measures greatly reduce the number of rows returned by queries. The data cube can be delivered much faster across the network and the reports will spend much less time calculating variables and aggregating the data.
Select best table. Field for an object – Some fields in the database is effectively interchangeable. Creating objects off of the best one will improve performance. Prime candidates are primary keys used in equi-joins between tables, especially codes in lookup tables. A data table may contain millions of rows compared to tens of rows in a lookup table.
Predefined conditions – Predefined conditions may allow the use of functionality built into the DBMS. More efficient design of complex condition sets may be possible.
Trim – Trim trailing blanks off of character strings at the universe level instead of dealing with them in the report.
Mark “fast” objects – Tell the users what objects are indexed.
Avoid outer joins – Outer joins severely degrade performance. Investigate any outer joins completely from a usage point of view. What seems to be required technically may not make business sense.
Custom LOV’s – Conditions can improve the retrieval of values. Sorting can improve the “apparent” performance; it takes longer but the user finds values faster. Custom LOV’s combined with predefined conditions allow the use of codes in the condition while displaying descriptions for the user. The LOV could come from another object, list or SQL statement.
Format stings – String manipulation is usually much faster at the universe level than at the report level.
Short-cut joins – Add short-cut joins where applicable. This may reduce the number of tables in a query by not joining in unnecessary tables.
Aggregate aware – Use aggregate awareness when possible. This can avoid the use of multiple data providers and/or reduce the number of rows returned to the data provider.
Dimension/Detail/Measure - Correctly identifying the object type can avoid reporting issues. BusinessObjects automatically tries to summarize measures, which may not be necessary if the object will always be used as a dimension or detail. When using multiple data providers in a report, dimensions are required for linking, but details and measures can be used in blocks without additional linking or variables.
Create report specific objects* – Tailored objects and predefined conditions can improve performance for specific reports, especially objects referencing joined table and fields. After creating and using report specific objects in a report, hide them so they will not confuse the users and use them inappropriately.
Allocating table weights – Table weight is a measure of how many rows there are in a table. Lighter tables have fewer rows than heavier tables. By default BusinessObjects (from version 4.1.3 onwards) sorts tables from the lighter to the heavier tables (those with the least amount of rows to those with the most). This determines the table order in the FROM clause of the SQL statement.
The order in which tables are sorted at the database level depends on your database. For example, Sybase uses the same order as BusinessObjects, but Oracle uses the opposite order. The SQL will be optimized for most databases, but not for Oracle where the smallest table is put first in the sort order’s, if you are using Business Objects version 4.1.3 onwards, and are using an Oracle database, you can optimize the SQL by reversing the order that Business Objects sorts the tables.
To do this you must change a parameter in the relevant PRM file of the database. You modify this parameter as follows:
1. Open the relevant PRM file in a text editor. For example, the file ORA7EN.PRM in the
Oracle folder under the Data Access folder.
2. Find the REVERSE_TABLE_WEIGHT parameter in the Default section of the file (normally
at the beginning of the file).
3. Change the Y to an N. For example the parameter appears as
REVERSE_TABLE_WEIGHT=N. If the line is not in the file, the default is Y
This forces Business Objects to sort the tables from those with the most rows to those with the least rows.
Note: You can also manually change the number of rows for any table in Designer.
To view the number of rows in any table, Select View > Number of rows in tables. The number of
rows appears at the bottom left of each table symbol.
To change this number, do the following:
1. Open your universe in Designer.
2. Right-click the relevant table
3. Select Number of Rows in Table from the contextual menu.
The Table Row Count Dialog box appears.
4. Select the Modify manually tables row count radio button.
A text box appears at the left of the dialog box.
5. Type a number in the text box. This is the number of rows that you want to use for the table.
6. Click OK, and then save the universe.
Using shortcut joins
A shortcut join links two tables that are already joined in a common path. You can use a shortcut join to reduce the number of tables that are used in a query. You create a shortcut join as follows:
1. Right-click a join. This the one that you want to be a shortcut join.
2. Select Join properties from the contextual menu.
3. Select the Shortcut join checkbox.
4. Click OK.
Note: Shortcut joins will not create loops
Query
Conditions – Use the faster operands when creating conditions. The operands are approximately listed from fastest to slowest (=, <, >, <=, >=, between, In List, Matches Pattern, <>, Not). AND conditions together are faster than OR conditions together. Eliminate unnecessary rows with conditions instead of filtering them out in the report. Build conditions on codes whenever possible because codes are probably from lookup tables and, therefore, have smaller result sets and are better indexed. In some DBMS’s, the order of conditions can effect performance.
Eliminate objects – Extra objects are more overhead, may cause additional table joins, and may decrease aggregation, causing additional rows of data. If it is not used in the report, get rid of it.
Add objects – The inclusion of indexed objects may improve data retrieval time.
Do not pre-sort – Do not add sorts in the Query Panel. Sorting adds time to data retrieval. Business Objects will usually re-sort the data anyway.
Allow duplicate rows - The option in Query Panel to remove duplicate rows is a tremendous performance hit. BusinessObjects will suppress duplicate rows in the report.
Delete trailing blanks – If the data is not trimmed at the database level, check this option in Query Panel. It is much faster that trimming the data in a variable or formula.
Union data – When possible use Union instead of adding additional data providers. Union makes the db server do the synchronization instead of the BusinessObjects.
Report
Clean up - Delete unused variable, formulas and constants. Unused variables and formulas may recalculate when the report is opened, refreshed or changed. Constants are just extra overhead.
Variables – Replace formulas with variables. Variables are more efficient and do not leave a copy of each revision in the report the way formulas do. Make sure variables are coded efficiently. Remove variables that are equivalent.
Avoid grouping variables – Grouping variable values to create a new variable is a slow process. Create a table in the db or an object in the universe to perform this task.
Do not concatenate* – Create separate cells to hold data or literals and resize the cells for correct spacing. (Hold this off for a last resort. Effect is usually minimal and it makes report creation more complex and less portable.)
Eliminate extra data providers - BusinessObjects must synchronize all data providers. Eliminating data providers reduces the amount of time to recomputed report values.
Add data providers – Breaking complex queries up may simplify them enough to be worth the synchronization time. It can reduce the amount of time required to return data by eliminating table joins, avoiding outer joins, or reducing the number of rows retrieved.
Crosstabs – Use crosstabs instead of tables with a lot of columns defined by variables.
Table with multiple data providers – When building a table with more than one data provider, start with data from the provider with the most non-linked dimensions. Linked dimensions, details and measures can all be placed in the same table. Non-linked dimensions must first be converted to details of a linked dimension by adding a variable.
Make sure that all the join columns are indexed (Bitmap wherever appropriate)
2. Avoid using string operators in the join.
3. Avoid 'snow-flaking'.
4. Use shortcut joins whenever appropriate.
5. Increase the array fetch size to maximum for the universe connection.
6. Make your LOV’s (list of values) go against the dimension tables and not against the fact.
Proper indexing in the DBMS, for example all columns used in joins should be indexed. Columns, which are often used in conditions, should be indexed.
- Using Aggregate/summary tables for larger fact tables
0 comments: