Performance Tuning Techniques in OBIEE 11g

Steps to improve the performance.

1. implement caching mechanism
2. use aggregates
3. use aggregate navigation
4. limit the number of initialisation blocks
5. turn off logging
6. carry out calculations in database
7. use materialized views if possible
8. use database hints
9. alter the NQSONFIG.ini parameters

Note:calculate all the aggregates in the Repository it self and Create a Fast Refresh for MV(Materialized views).
and you can also do one thing you can schedule an IBOT to run the report every 1 hour or some thing so that the report data will be cached and when the user runs the report the BI Server extracts the data from Cache

Use of Database HINTS to OBIEE queries

Adding database HINTS to OBIEE queries

We had OBIEE queries that could be tuned by applying hints in physical layer tables. We can apply hint to use a specific index which helps in faster query performance. The syntax to apply this is :

index(table_name, index_name)

In oracle database the comma in hint syntax can be omitted however in BI this is required, although the explain plan with comma and without comma is exactly the same.

This approach works when we are tuning specific set of report, however if the subject area is used for multiple report types, the hint index gets applied to all queries and result in performance degradation in other queries. In those cases using a materialized view may be an better option.

OBIEE 11g: Deploy/Undeploy View in Physical Layer

OBIEE 11g: Deploy/Undeploy View in Physical Layer

Same like OBIEE 10G we can create opaque view in OBIEE 11G also. Here is screenshot and steps how to create opaque view in OBIEE 11G. If you want to know how to create view in OBIEE 10G click here Opaque view in OBIEE 10G

The steps are almost same like OBIEE 1OG. So giving the screenshot by stepwise.

Deploying View:

Here I want to create a view on employee table which need to be show only Indian Employee. So I am restricting the table here itself by creating view.

Right click on Employee Table and select Properties or Double click on the table to see the properties window.

From properties windows select  Table Type:Select and write the query in the 'Default Initialization String'. After that click 'OK'.

Now save the repository

If we are looking at the employee table now It will display with different icon. Right click on the table and click 'Deploy View(s)'. Click Check out if it asked for.

Click 'OK' with the below window

 Now the view is deployed in the physical layer. Click 'Close' with the below window

Now if we are updating the row count of the table it will be shown only the result of our SQL Query.

UnDeploying View:

If we are not in the need of view which we deployed, we can undeploy the view by the straight opposite process of deploying view.

Right click on view and click 'Undeploy View(s)'

In the next screen just click 'OK'. The view will be undeployed  and will get below window. Click 'Close' with the below screen.

Note:

After you undeployed the view check the table name whether the table name is matching with source table name. If it is not then rename the table with the same name of source table name.