Universe Optimization Techniques

[former_member4998]

Hi All..

• Universes are designed for the end users to generate the reports.These reports contain the data according to the business requirements.
• If the reports are giving exact data and taking much response time then it is not a preferable approach.

          The performance is also considered as a major role in the Business Objects Enterprise.

• In general, performance of the universe goes down then designers ask the DBA to improve the performance of the warehouse. The performance tuning of the reports can be done at various levels such as universe level, report level, database level, and server level.
• Analysis the report for which performance is low. Check the SQL and look any unnecessary joins and Avoid unnecessary joins in the report SQL. The performance at the universe level can be made optimum by the following actions.

• Use the aggregate functions like AVG, SUM, COUNT, in your queries.
  • Aggregate functions (sum, count, min, max) in measure objects at universe level. Aggregate functions will aggregate the data at database level rather than at report level which will save on processing time at report level and also reduce the number of rows returned back to report.
  • Every Measure Objects should use SQL Aggregation function so that while querying there will be less number of data set returned in report. Without this user has to handle many rows in the report side.
• Disable unnecessary List of values for Measures and dimension Objects
  • One temporary folder and one temporary .UNV file created for one universe which holds the LOVs temporarily for every instance of all reports running against one universe.
  • So make sure LOV is disabled for unnecessary Measures and Dimension Objects.
  • Right click on the object-> Object properties->In advanced tab, uncheck “Associate a List of Values”

          

• Use Universe Conditions instead of report filters
  • The reason is very obvious, as Universe conditions will restrict the data at database itself but if you use report filters data will fetched from database and then it will be restricted at report side which degrades the performance.
• Analyze report query for index awareness (work with  DBA for this)
  • Get the report SQL check the where clause, if indexes are used properly in SQL and also they exist in database.
  • Also check if statistics in database is updated as its not then DB may not generate optimized query.                        

• Develop universe for single business area
  • Design and develop the universe based on business areas (Sales, Finance, HR etc.. ), you need to develop the universe with multiple business ares model,  if your data warehouse is small.
• Modify “Array Fetch Parameters”
  • It is the number of records to be extracted from database at each fetch. E.g if your report query is supposed to fetch 5000 records and array fetch size is set to 100 then business object will connect to database 50 times and retrieve 100 records in each fetch. This will degrade the performance of the reports.
  • It is therefore preferred to modify the array fetch size as per the adequate memory on the client side.

        

• Use the aggregate awareness
  • Setting up aggregate awareness in a universe enables you to accelerate your queries by taking advantage of database tables that contain pre-aggregated data.
  • Once aggregate tables have been inserted into your data foundation, the next step to setting up aggregate awareness in a universe is to modify the SELECT statement of certain objects in the business layer to make them aggregate aware.
  • After you have defined the aggregate aware objects in your business layer, the next step to setting up aggregate awareness in a universe is to specify which objects in the business layer are incompatible with each aggregate table.
  • This has a two main advantages. It reduce storage space, and It improve performance
    • E.g.@Aggregate_Aware function
• Avoid unnecessary joins in the report SQL
  • Explore the universe properly and also Analysis the report for which performance is low. Check the SQL and look any unnecessary joins and take out.
• Use of shortcut joins
  • If you need to avoid unnecessary joins and don’t want to complicate your query, introduce a shortcut join. A short cut join is a join that joins tables by passing middle table that exits in the universe.
  • Shortcut joins provides the shorter path between 2 tables and thus improves the query efficiency.
  • Shortcut joins allow users to skip intermediate tables and allow alternative path between tables, which results in query performance from 1.5 minute to 30 seconds.
• Use Row and Time limit in Universe Parameter
  • Use the universe parameter “Limit size of result set to” and “Limit execution time to” efficiently and according to your need, because this feature can increase or decrease the universe performance based on how you use it.
• Minimum use of derived tables.
  • Some calculations or filtering which are done at universe or report level could be pushed down to database level (withoutthe need to actually modify the database). This has advantages, final users have less work to do.
  • But Derived tables degrade the performance as it does not store any data and hence run the SQL every time it is touched which in turns increase the run time of the report. Even if a single object is used from the derived table, the entire SQL is executed.
• Query Striping
  • As the report get reused, they become cluttered with unused object and queries.
  • This can cause extra work by the database.Which can cause the refresh to take longer time.
  • Most important items to remove unused queries unused measures
• Multi-sourcing with Data Federator for optimized
  • In some scenarios, users might need to retrieve data from multiple sources and use it into a report. Business Objects Web Intelligence provides the feature ‘Multi Data Provider Synchronization’ to merge multi-source data at the report level.
  • It is also possible to push the data federation down at the universe level using Business Objects Data Federator.
  • This provides a couple of benefits: the user does not have to deal with the two different data sources and the query performance is improved.
• Allocate weight to each table
  • Table weight is a measure of how many rows in the table. Lighter tables have less rows than a heavier table. By default BO sorts the tables from lighter to heavier. The order in which tables is sorted are based on the databases.
  • For example, Sybase uses the same order as BO, but Oracle uses opposite order. The SQL will be optimized for more databases, but not for Oracle where the smallest table is put first in the sort order. So, if you are using Oracle database, you can optimize the SQL by reversing the order that BO sorts the tables. Change the REVERSE_TABLE_WEIGHT value to N from Y
• Use Partitions for high volume fact tables
  • Partitioning the fact table can boost your query performance.
  • Work with DBA to get it done
• Universe parameter "BOUNDARY_WEIGHT_TABLE"
  • Large tables in the From clause have to be wholly parsed even if they are filtered by a condition afterward. By setting the Boundary_Weight_Table parameter, designers can define a limit (in number of rows) to avoid this full parse. If the table size in rows is greater than the entered value, the table is declared as a subquery:
  • FROM (SELECT col1, col2,...., coln FROM Table_Name WHERE simple condition)
  • In the statement above, only the columns necessary for the query are retrieved and the condition in the inner statement limits the number of records to parse in the outer one.

Hope this will be helpful

Thanks,

Sreeni