Performance Tuning
Today we are going to discuss about various options for improving performance of reports in OBIEE.
There are lots of options available in OBIEE to improve performance of reports.
Today will discuss about these…
1) Aggregate tables
3) Turning off logging
4) Constructing result using where clause
5) Limiting Number of Initialization block
6) Setting Query limits
7) Modeling Dimension Hierarchies Correctly
8) Setting NQSConfig Parameters
1) Aggregate Table (Aggregate Persistence Wizard)
Aggregate Table: Aggregate tables store precalculated measures that have been aggregate over a set of dimensional attributes.
This is very useful technique for speeding up query response time in decision support systems. This eliminates the need of run time calculations and delivers faster results to users.
The calculations are done ahead of time and the results are stored in the tables.
The key point is that the aggregate table should have fewer rows than the non aggregate table and therefore processing should be quicker.
Aggregate Persistence Wizard
Go to: OBIEE Admin > tool> Utilities > Aggregate Persistence Wizard
2) Caching
3) Turning off Logging
Logging can affect in performance of Oracle BI Server and can create large log files. Logging can be used in problem solving and trouble shooting problematic queries. So when it is necessary then only set logging level to 0 to 2. Set log level greater than 2 is not recommended by Oracle.
4) Constructing result using where clause
With the help of where clause in content tab in logical table source you can filter the data ( limits the rows ) returned from database.
Here , you can see the where clause and restrict the data using Expression Builder( marked in Blue).
5) Limiting Number of Initialization block
Initialize block are the only means to initialize dynamic repository , system session and non system session variables. We should be very careful about not to create too many init blocks.
As we know, In the case of system and non system session variables, the initialization blocks get executed every time a user logs in to the server. And In the case of Dynamic repository variables, the SQL in the Initialization blocks get executed every time the server is started or periodically if a schedule is set up to refresh the value of the variable.
6) Setting Query limits
We can enable oracle BI Server to track and cancel run way queries by placing various limits on the repository for a given user or group. For each user or group it is possible to limit queries by various condition :
1) maximum number of rows a query can retrieve from a database
2) maximum time a query can run on a database
3) restricting access to a database during particular time periods from Analytic server
7) Modeling Dimension Hierarchies Correctly
Dimension Hierarchy must be modeled accurately to ensure that Oracle BI optimizer chooses the most economical source. The Number of element for each level must be specified. The Number does not have to be exact,but ratio of number from one parent to child logical level should be accurate. See below screen shot,
8 ) Setting NQSConfig Parameters
NQSConfig.INI includes parameters that affect Oracle BI performance :
- Sort Memory size
- Sort buffer Increment size
- Virtual table page size
Posts
