OBIEE Presentation Layer Design Principles/Best Practices

www.obieefans.com

Principle 1.Subject Areas
Do not include all presentation layer objects in a single subject area (Presentation Catalog). While it is possible but still not recommended approach.
Principle 2. Development with End Users in Mind
Always develop the presentation layer so that end users are able to understand and use it.
Principle 3. Role Based Subject Area.
It is always good idea to create presentation catalog to suit the needs of individual users or user types. For ex. create a separate presentation catalog for sales managers because they need to see only an overview of an organization.
Principle 4. Presentation Tables

Presentation Tables should consistent across presentation catalog.

• List Dimension tables first.
• Do not mix dimension and fact columns in the same table.
• Apply consistent ordering and naming conventions for tables and columns across catalogs.
• Include the words “measure” or “fact” in the names of the fact presentation tables.

Principle 5. Rule of Seven
Keep presentation catalogs small and easy to understand by limiting the number of tables to seven.
Keep Presentation catalogs small and easy to understand by limiting the number of columns to seven.
Principle 6. Fact Tables
Try to limit the presentation catalogs so that there are no more than a couple of fact presentation tables in each catalog. It is important to avoid situation in which there are multiple join paths existing within one presentation catalog.
Principle 7. Implicit Fact Columns
If you have multiple fact presentation tables, it is always best practice to assign an implicit fact column. Implicit facts come into play when an Answers report contains columns from more than one logical dimension table and no explicit facts.
Principle 8. Canonical Time
Canonical time is a useful way for allowing users to report for a specific period of time across multiple star schema. If you use canonical time ,make sure that the corresponding time presentation table is given a very generic  name and that name is consistent across all the presentation catalogs.
It should be the first table in a catalog.
Principle 9. Secondary Time Dimension
This function enables users to build time reports for specific star schema.
Secondary time dimensions can be given their own presentation tables further down the list.We can place all secondary time dimension objects into a single presentation table.
Principle 10. Nesting Presentation Table
Prefix presentation table names with a hyphen to group common objects together into sub folders.
Principle 11. Presentation Object Names & Description

• Use the Alisa tab to keep track of prior names.

• Use the default option that synchronize the presentation column name with the underlying logical column name.

• Use only logical, business -oriented names ( rather than physical object names) in the presentation Layer.

OBIEE BMM Layer Design Principles/Best Practices

www.obieefans.com 


OBIEE BMM Layer Design Principles / Best Practices
There are certain design principles or best practices oracle suggest in designing the OBIEE repository. If you open any standard/sample repository provided by oracle you will understand what exactly I am talking about
Today we will discuss the design principles for BMM Layer.
Principle 1. 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 within 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.
Principle 2.  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 check manager to identify and debug check messages.
Principle 3.  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.
Principle 4. Logical Tables
When building logical tables, do not merge multiple dimension tables into a single logical dimension table,and do not merge multiple fact tables into a single logical fact table.
Having multiple logical fact tables also makes it easier to create well defined projects for Multi User development.
It is also a good practice to prefix logical table names with either Dim-, Fact- ,or Fact Compound -.
This allows you to easily see how the tables are being used. It also groups the tables in the business model, so that facts are groups with facts, dimensions with dimensions  and so on.
Principle 5. Time Dimension
There are few things to keep in mind in time dimension factor.

• Always must ensure that time Dimension hierarchy is built correctly and the logical level of each time- logical table source is set correctly
• If there are multiple time dimensions within the business model, for consistency, make sure that all time dimension logical table contains the same columns and general structure. This is good for reporting purpose.

Principle 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 in to the BMM layer for reporting.
• Do not assign logical primary key for logical fact tables.
• Create meaningful name for measures
• Set aggregation rule for every logical fact columns.
• Create “dummy” measures to group facts.

Principle 7. Logical Joins
Use only logical(complex ) joins in BMM layer. And always accept default properties when creating joins.

Principle 8. Calculated Measure
Be very careful when building calculated measures.

• Use logical columns for calculations that require an aggregation rule that is applied before the calculation.
• Use physical columns for calculations that require an aggregation rule to be applied after the calculation.
  

Principle 9. Aggregates
Few important things to keep in mind about Aggregates.

• Try to ensure that each aggregate table has an effective summary ration with underlying detail.
• Ensure that the logical level of every aggregate logical table source is set correctly.
  
• Always test to ensure that aggregates tables are being used as expected.
• If an aggregates is not used ,try changing the number of elements on one of the related logical dimension levels.

Principle 10. Dimension Hierarchies
In Dimension Hierarchies few things  are very important to keep in mind

• It is best practice to create dimensional hierarchy for every logical dimension table in BMM layer.
• All Dimension must have at least two levels : the total level and detail level.
• If you are creating Dimensional Hierarchy manually, be sure to check Grand total level for the Total Level.
• Use Update Row Counts or Estimate Levels to set the number of elements for every level of every Dimension Hierarchy.
• Think about the experience of user when enabling drill down.
  

Principle 11. Avoid Snowflake schema
When there is Snowflaking in physical model,We should try to avoid Snowflaking in BMM layer and build models that use only star schema .

Use WHERE clause filters to help avoid using opaque views or complex joins in the physical layer.

OBIEE Physical Layer Design Principles/Best Practices

There are certain design principles or best practices oracle suggest in designing the OBIEE repository. If you open any standard/sample repository provided by oracle you will understand what exactly I am talking about
Today we will discuss the design principles for physical layers
Principle 1 :Import only needed tables in physical layer and do not create tables manually in physical layer.

Import Tables

Reasons to do that :Its easier to import the table then creating it manually.In that way we are avoiding the table name , column name , data type mis match possibility. Import only those tables which you need. If you need more tables at later stage in project, you can always import them.
You will be using connection pool to import the table and that helps to make sure that connectivity to source database system is working fine.
Principle 2 Create alias for the physical tables

Physical Layer Alias Table

Reasons to do that :
It helps in avoiding the circular joins.
It helps when we have confirm dimension(e.f time dimension) and we want to join dimension different way
Its easier to give the access of objects to users in case of Multi User Development Environment
Alias naming convention helps in easily finding the fact and dimension table in physical layer
We can import the key relationship in physical layer from database and maintain the relationship which is required among the alias tables
Principle 3 Set the cache  property of the physical table

Cache Property of the tables

Reasons to do that
Its better to set the caching at physical table level and not at the alias table level.This applies to most of the scenarios
It your data source is OLTP system make sure you disable to caching. As  that changing frequency would be high and you do not want to cache results.
For all the data warehouse sources have better cache persistence time set
Principle 4 connection pool(s) and its properties

Physical Layer Multiple connection Pool

Reasons to do this.
To have multiple connection pool for difference purpose. e.g for usage tracking, security implementation etc it better to have different connection pools.

COnnection Pool Property

Call interface: Use native call interface for the connection(e.g OCI10g) etc based on source database. Its faster then generic ODBC connection.
set connection pool , time out ,maximum connections etc based on server configurations and usage
connection pooling- it avoids the database connection overhead. If users run the same query it users the same connection from the pool. What does this mean is user sessions share the connection in this.
Maximum Connection :
Max what it should be :Approx 1024 kb of server memory is consumed in each connection.So tThis has to be set per server configuration and requirement.
Min what it should be: 25% of max users times the reports they execute.
e.g if you have 1000 users accessing around 10 reports  in dashboard that this number should 250 ( 25% of 1000) * 10 = 2500

“Account is locked by OCI call ... Could not connect to Oracle database”

 www.obieefans.com

Q: When I try to run a query in Answers I get “the account is locked at OCI call OCILogon”?

This can happen for various reasons – for example, when trying to rename a repository that is currently online.  You may get the error message:

  ORA-28000: the account is locked at OCI call OCLogon. [nQSError: 17014] Could not connect to Oracle database. (HY000)

or even just the “Could not connect” component of it.  What’s surprising is that the account that is locked has nothing to do with OBIEE.  It’s the Oracle database schema used by the “Connection Pool” within the repository – account “sh” in the case of the tutorials.

Logon to SQL*Plus as “sys” or “system” and issue the command:

  alter user <user name> account unlock;

Unfortunately, this error seems to tie the entire OBIEE configuration up in “knots”, so you may well have to reboot the system to get “Answers” up and working again.

OBIEE not working

www.obieefans.com 

When you boot the system sometimes the “Automatic” processes start, and sometimes they don’t.  Stop a process, change a parameter, and sometimes you can’t get it to restart.

Whenever you’re stuck make sure you do the following.  First turn any firewalls or anti-virus checkers off: it's possible for OBIEE to work perfectly well with these until an error occurs; but in order to clear the error you may have to turn them off.  Then set all the BI processes to “Manual”, perform a hard-reboot of the system, and then start the processes up in the following order:

  Oracle BI Server

  Oracle BI Presentation Services

  Oracle BI Java Host

  Oracle BI Scheduler

  Oracle BI Cluster Controller (only in a cluster configuration)

If this doesn’t work then repeat the hard-reboot several times – it's quite common to find, say, that the first two reboots don't work, but the system comes up on the third attempt!

If rebooting doesn’t work then restore all the configuration files to their installation values after saving the current configuration files – you were, of course, wise enough to keep copies of the original configuration files, now weren’t you?  If OBIEE works with the original configuration files, then there’s something about the new configuration files that is causing the problem.  Re-implement them in stages until you find the source of the problem.

But if OBIEE doesn’t work with the original configuration files, then the problem is due to some change to the Windows, OBIEE, or other application software installed on the machine.  Restore all software and configuration files that were present when you first installed OBIEE (and it worked) – from the image backup you were prescient enough to make at the time.  Then incrementally add the changes to the software that you’ve made since the OBIEE installation.  When you’ve found the software change that’s causing the problem, then restore the system to its current configuration – using the image backup you’ve just made of the current configuration, of course.  Then fix the software problem in the current version.

“SQL Statement preparation failed”

www.obieefans.com 


Q: When I try to run the usage tracking request in Presentation Services I get an error, “SQL Statement preparation failed”?

Another little Oracle oopsie.

This bug takes a little bit of detective work.  Now you may remember that towards the beginning of the tutorial you were asked to change the name of “LOGIN” to “USER_NAME” in script “SQLServer_create_nQ_UserGroup.sql” since “LOGIN” is an Oracle reserved word (despite the fact that you’ve just run several Oracle specific setup scripts, you now have to modify a script intended for SQLServer, which says wonders about the care Oracle has taken in preparing and testing the contents of its OBIEE distribution).  In any case, with this change in place the view created by the script, “NQ_LOGIN_GROUP”, has two columns “USER_NAME” and “RESP”.

If you open the repository “sh_usagetracking.rpd” with the Administration Tool and, in the Physical layer, navigate to “OBI Usage Tracking => Catalog => dbo” you’ll find that “NQ_LOGIN_GROUP” also has two columns.  One is “RESP”, but the other is ... can you guess ... “LOGIN” – not “USER_NAME”.  So the BI Server is asking the database for the value of column “LOGIN”, but the database is saying in response, “Sorry, Guv, we don’t hav’ one of them”, or, in other words, “SQL Statement preparation failed”.

So while the tutorial has asked you to update the column in the view creation script, it has supplied you with a repository in which the corresponding change has not been made, and the instructions for editing the repository in the tutorial fail to mention that this change needs to be made.  So just rename column “LOGIN” to “USER_NAME”, save the repository, and restart the BI Server.  Then, fingers crossed, all will be well.

“Unable to connect to port 9705 on machine localhost”

www.obieefans.com  
 

Q: When I click OK in the Machine Name window I get an “Unable to connect to port 9705 on machine localhost”?

The instructions early on in the tutorial ask you to start the BI Presentation Services and the BI Server, but they forget to tell you to start the BI Scheduler – that’s what the Job Manager was expecting to find on port 9705, but didn’t.