OBIEE Report/Dashboard Performance Tuning

You come Morning to the office And all the users are complaining about the OBIEE reporting system is very slow (dashboards). What is your approach to resolve the issue?

Ans: 

1. First check NQServer.log 

2. Admin Tool -> click Session Manager. Check if there is any bottleneck and accordingly resolve.

      Refresh the sessions.........

3. Analytics or Answers -> Administration-> Manage Sessions

 Check the physical query and execute from the back-end(BE).

Performance Tuning at real time

Performance Tuning is the huge topic to answer;

let’s take step by step approach as follows...
       Check whether the same query is running or anyone update the query.If the same query is running then follow the steps given below,

• When you open the dashboard page, first figure out whether Prompt or Report is taking more time
• If it is prompt, check for any multi select prompts which are taking time to load all values.. (best practice is set default values).
• If Report is taking longer time, set some default filters and query for just couple of records and check the report performance.
• Take physical SQL from the session log (if SQL is not generated check for log level whether it is less than 2).
• Run this Physical SQL in the TOAD or any SQL Editor.
• Check Explain plan for the cost of the query.
• Hash Joins/Cartesian Joins are always kills performance.
• Some-times, Force Inner-join also helps to force the tables to have inner-join between two tables.
• Check are there any "Full Table Scans" happening instead of Index Scan.
• Consult your DBA for the Stats.

OBIEE real time questions

Interview questions on OBIEE

Best Practices of Presentation Layer in OBIEE

1. Presentation catalog should map to one Business model & mapping layer(BMM) layer objects only.
2. Use parent folders and sub-folders to group facts and similar dimensions together.
3. Avoid the use of aliases when a new presentation column is created.
4. The presentation columns in a table should be sorted alphabetically if no specific order is asked by the customer.
5. Make proper use of the permissions in this layer like authentication and authorization.
6. Don’t use special symbols (“) in column name, though its permitted.
7. Presentation columns should not have the same name as presentation table.
8. Eliminate unneeded objects to reduce user confusion.
9. Limit number of objects in folder to 7-12 (for performance tuning only).
10. Use object description field to convey information to users when they hover the mouse in answers on a presentation column.
11. Keep names short to have space on reports and give the meaningful table names and columns names to identify easily on subject areas. Remove primary key columns and other unnecessary columns that does not going to use in the creation of the reports.
12. Try to create separate folder for each data mart(HR, operation, SCM, sales) if it is coming from same logical layer.
13. Get costumer sign off of the presentation layer structure before building reports. This will avoid later replacements of columns which affects the reports constructed.

BMM Layer Best practices in OBIEE

Physical Layer Best practices in OBIEE

Best Practices of BMM layer in OBIEE

1. Always run Global Consistency Check before releasing a repository:

• Whenever we make changes to a repository, always be sure to run Global Consistency Check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI server from loading the repository. Use the Consistency chec k manager to identify and debug check messages.

Note: Whenever you do Consistency check , Right Click the Changed Business Model Object and go for Check consistency rather than using the Global Consistency Check. This will minimize the time needed for Consistency Check.

2. Minimize the use of Snow-flakes. Always go for Star Schema’s:

• Every logical fact table must join to atleast one logical dimension table.
• Note that when the source is a fully de-normalized table or flat file, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables.

3. Create dimension hierarchies for every Dimension in the Business Model:

• Even if a meaningful hierarchy definition cannot be thought of, just create one with the Grand Total Level and Detail Level. For Dimension Hierarchies the ‘Number of Elements at this level’ should increase from 1 at grand Total to the corresponding distinct values at each level. This can be approximate values; need not be the exact ones. Define keys at each level of the Hierarchy.

4. Don’t keep unwanted physical columns in the Logical Layer:

5. Give Meaning ful Names to the Logical columns. Avoid assigning a logical column the same name as a logical table or Business Model Object:

6. Logical Fact & Dimension table columns:

• Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users.
• Bring only required columns into BMM layer for reporting.
• Do not assign logical primary key for logical fact columns.
• Create “dummy” measures to group facts.

7. Use Multi User Development Environment:

• Use the Multi-User Development facility if there are multiple developers. Multiple developers to connect “online” to the same repository file and Making changes is not recommended.
• Multi User Development allows user to define a series of projects with in the repository file, where each project is a subset of the entire repository. If developers want to make changes, they can check out a project to a local machine make and test the changes, and then check the modifications back into the master repository file.

8. Level-based Metrics:

• When creating level-based measures, make sure that all appropriate fact sources map to the appropriate level in the hierarchy using aggregation content. You set up aggregation content in the levels tab of the Logical Column dialog for the measure. 
• Note that this is different from the content tab of the Logical dialog for the measure. Note that this is different from the content tab of the logical table source dialog, which is used to specify the grain of the source tables to which it maps. You only need to set up aggregation content in the levels tab of the logical column dialog for level-based measures. For measures that are not level based, leave the logical level field bank.

9. Separate business model:

• Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality.

10. Arrange the logical columns alphabetically if customer does not require any. This will save time when you revisit:

11. Fix the warnings if any, don’t ignore it:

12. Performance Tuning:

• Minimize the use of conditional checks and ‘CASE WHEN’ usage in the formula of Logical columns. This will affect performance. Instead make proper use of the where clause content filter of the LTS if the condition applies to all the columns/ measures in the logical table.
• Make proper distinction between count and count distinct. If you are counting on a unique value column don’t use count distinct. This will affect performance.
• Avoid dimensions in Fact tables and avoid measures in dimension tables.

Best practices of Physical layer in OBIEE
Best Practices of Presentation Layer in OBIEE