Best Practices to Improve Query performance In OBIEE

Hi all,

Got fed up of this question?? Yes, me too.. So penning down some of the recommended practices to make better your query performance.

1. Dashboard should be as interactive as possible: column selectors, drill-down, guided navigation… Interactivity is one of the best assets of Oracle BI. Use it.
2. Check if time is spent on BI Server or database (response time and physical query duration versus compilation time). Normally, time spent on BI Server should not exceed few seconds. Otherwise, analyze the steps done on BI Server to find the cause
3. Are all tables included in this query really necessary? Do we have tables that are joined but are not included in select clause and do not have filters applied (real filters, not join conditions)?
4. If optimizing the SQL is not enough, look with a DBA at execution plan and find out the root cause of performance issue. Globally there are mainly four ways to improve performance at this point:
• Reducing volume of IOs by improving data access path.
• Reducing volume of IOs by reducing the volume of data read.
• Review the filters applied.
• Increasing parallelism (number of thread used to read big tables)
• Improving IO speed (hardware improvement, in-memory...)
5. Tend to star data model in repository
6. Use hints in physical layer of the repository
7. Use fragmentation
8. Use aggregation
9. Set the no. of elements
10. Use the where clause in LTS if possible
11. Apply index on column from DB side only
12. Get long running physical queries, check explain plan and make change accordingly to DB tables or from OBIEE.
13. Try to use cache seeding wherever possible
14. Implement data level security to lower the data set pulled from DB
15. Reconfirm that aggregate/fragmented tables are getting used properly
16. Various configuration options can be used to limit the maximum number of rows that can be downloaded, processed, rendered, included in mail, etc.

That's it what I can think of. There can be many more, please feel free to add in comment section.

Happy blogging.. :-)

 

Bridge table in OBIEE 11g, with example

Generally, OBIEE only supports one-to-many relationships but in some rare cases we have to use many-to-many relationships in our data model. To achieve this, we use the bridge table.

Consider a scenario where there is a sales representative who is participating in many deals that pay commission. Additionally, each deal may include many sales representatives so that each sales representative receives a percentage of the commission. In such case, we must model this many-to-many relationship in the repository using a bridge table concept.

Suppose F_COMMISSION fact table with commissions paid per invoice. D_SALESREP dimension table will have data for sales representative. D_CUSTOMER contains the data for each customer.

Now we will design bridge table COMMISION_BRIDGE in such a way that it will create a many-to-many relationship between the F_COMMISSION fact table and the D_SALESREP dimension table. COMMISION_BRIDGE should have a weight factor to calculate the weighted distribution of commissions among sales teams.
A rough physical diagram for the above scenario will look like as below,

Hope this helps, happy blogging..!! :-)

Generate a metadata dictionary in OBIEE 11g (Manual Method)

Keep in mind, you can generate metadata only in offline mode of a repository. Also note that this is Windows-Only Utility.

• Open the repository using the OBIEE Administration tool in offline mode.
• Navigate to Tools > Utilities > Generate Metadata Dictionary > Execute 

• Specify a directory location where you want this metadata should get exported, here I have given C:\OBIEE_Things\Metadata

• You should see a below message on a completion of this task.

• Now copy WEB-INF folder from $INSTANCE_HOME\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes to C:\OBIEE_Things\Metadata
• This would create a directory named as your repository name under the specified location. Here, "ABC".
• Rename this directory to the name as it is on the server. This is a mandatory step.

Note that, you can find this name in NQConfig.INI configuration file or else go to EM > Business Intelligence > coreapplication > Deployment > Repository. Here, it is ABC_BI0010

• Now login to Console (with admin privillage) to deply this metadata as an application.
• Deployments > Lock and Edit > Install.

• Specify Path as C:\OBIEE_Things\Metadata and select the radio button for ABC.

Note that, without copying WEB-INF folder to the metadata directory, the ABC directory cannot be deployed as an application in Weblogic, an application in weblogic needs deployment descriptor file (WEB-INF/web.xml)

• Click Next > Select Install this deployment as an application (this is selected by default)

• Click Next > Select bi_server1 > Click Next.

• Select I will make the deployment accessible from the following location > Click Next.

• Select Yes, take me to the deployment configuration screen (this is selected by default) and click Finish.

• Click Activate Changes.
• Once the changes are activated, click on Control tab > Lock and Edit > select ABC > Select start > Servicing all requests. > Click Yes > Click Release Configuration.

• Edit instanceconfig.xml and add the following after first ServerInstance tag:
• Location of instanceconfig.xml is $INSTANCE_HOME\config\OracleBIPresentationServicesComponent\coreapplication_obips1

<SubjectAreaMetadata>

<DictionaryURLPrefix>http://obieenow.localdomain:9704/Metadata/</DictionaryURLPrefix> 

</SubjectAreaMetadata>

• Save the changes.

• Go to EM and restart the services.
• Log in to analytics as user with BIAdministrator privileges. 
• Click Administration > Manage Privileges > Access to Metadata Dictionary. Make sure the users have Access to Metadata Dictionary privilege.
• Log in as user who has Access to Metadata Dictionary. 
• Click New > Analysis > Select Subject Area > Click on Metadata Dictionary. 

• This would open a new window with Metadata Dictionary details. This is nothing but the presentation layer to the BMM layer mapping.

• Click on the Logical Column Mapping - Dollars. This will present you the BMM layer to the physical layer mappings.

Happy blogging.. :-)

Difference between OBIEE and BI Publisher?

If you have OBIEE Suite licence, BI Publisher comes along with the suite as a package. Whereas, you can purchase BI Publisher's licence alone with minimal cost than OBIEE Suite. To consider this, below are the basic differences between two of the Oracle's reporting tools. When your reports are more pixel oriented then BIP is preferable whereas if you are looking more for analytics, graphs, interactive reports, dashboards, etc. then OBIEE is preferable.
Some of more basic differences are as,

OBIEE	OBIEE
For canned and ad-hoc reports	For canned reports only
Fully interactive	Non-interactive unless integrated with OBIEE
Not meant for a real time reporting	Meant for real time reporting
More for trends, analytics, dashboards, etc.	Mainly for fixed format, pixel perfect reports
Drill down is possible	Drill down is not possible
Need DWH/OLAP	OLTP is also preferable

Happy bloggin.. :-)