Wednesday, October 5, 2011

OBIEE- Maintenance Mode

OBIEE Interview Question: What is Maintenance Mode? When and how it is used? Will users be able to save reports when it is ON?

Maintenance Mode is used for administration of Presentation Services. When Maintenance Mode is turned ON, users will not be able to save changes in Presentation Catalog- it goes in to a read-only mode at this point. This will make sure that webcat is consistent and no one is making changes to reports or saving new ones while you do your maintenance activities.  

When you as an Administrator/Developer are making changes (updating or migrating reports or archiving (taking backups)) in the Presentation Catalog in online mode, you should turn Maintenance Mode ON.

Maintenance Mode can be toggled/changed/switched from Answers/Analyses Administration page. You should be logged in as an Administrator to toggle Maintenance Mode. Click on Administration from Header Menu:

Here on bottom left you will find a section for Maintenance and Troubleshooting. This is where you have Toggle Maintenance Mode link. Just click on this link to turn it ON or OFF.

If a user tries to make changes while Maintenance Mode is ON, she will receive the following error:
"Unable to change edit/view mode: System is in Maintenance Mode. Please try again later."

Note: In clustered mode make sure to Toggle Maintenance Mode in Presentation Services of each computer.

Hope it helps!


Tuesday, October 4, 2011

OBIEE11g- Updating GUIDs

OBIEE Interview Question: How do you update GUIDs? 

During migration of Oracle BI artifacts it is optional to refresh Global Unique Identifiers (GUIDs), but it become necessary in case you have a different and not fan-out replica of LDAP identity stores in DEV/TEST and PROD. If not this may lead to authentication failures. If the same user name exists in DEV/TEST and PROD LDAP directory servers user GUID will have a mis-match with GUID in new directory server. 

Here’s the error I received when I moved around my BI components to a newer system.

I followed Oracle's Documentation but it did not work for me. I had to do a little different configuration to make it work on my environment. 

To resolve this issue changes in NQSConfig and instanceconfig.xml files are done when all Oracle BI Services are online. To refresh GUIDS Edit NQSConfig.INI file 
and set FMW_UPDATE_ROLE_AND_USER_REF_GUIDS to YES as shown below. Save and Close NQSConfig.INI file. 

Edit instanceconfig.xml file 
and modify or add the following:  <Catalog>

Save and Close instanceconfig.xml file. 

Now to refresh GUIDs we have to restart the BI Services using Oracle Process Manager (opmnctl command) as below.

Now, revert back your changes to NQSConfig and instanceconfig.xml and restart services again. After this the GUIDs got refreshed and I was able to log in to OBI Presentation Services/Analyses. 

Hope this helps! 



Friday, September 23, 2011

OBIEE 11g Errors- nQSError: 43113, nQSError: 43126, nQSError: 37001

My Environment: Windows 2008 R2, Oracle DB 11gR2, OBIEE

One fine day no user even administrator was able to log in to OBI. We were receiving the following errors:
Unable to Sign In- An invalid User Name or Password was entered.
When trying to open the repository in online mode, following error messages are received:
nQSError: 43113, nQSError: 43126, nQSError: 37001

We tried everything including redeploying rpd and catalog, refreshing GUIDs, creating new users with Admin privileges, playing around with BI System User, etc. with no success.

NQServer Log:
Error Message For BISystemUser: User not authenticated.
Authentication failed: invalid user/password.

NQ Admin Tool Log:
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43126] Authentication failed: invalid user/password.
[nQSError: 37001] Could not connect to the Oracle BI Server instance.

Presentation Log:
Could not create a system user connection to Oracle BI Server during start-up. Please check the error message and try again.
Authentication Failure.
Odbc driver returned an error (SQLDriverConnectW).
[nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43126] Authentication failed: invalid user/password.

After several hours of debugging we found that our DB schemas/user MDS and BIPLATFORM got locked/ expired. It was a setting in Oracle DB which makes schemas or users lock after a period of time. To check the status of account use:


You can use the following to unlock accounts:


If the password has expired make sure to revert it back to the password you used during installation. Use the following:


This did the trick and we were able to login to OBI Presentation Services.


Sunday, June 26, 2011

OBIEE 11g: table does not exist- nQSError: 43119

OBIEE 11g: ORA-00942: table or view does not exist at OCI call OCIStmtExecute Error

I am working on OBIEE 11g - Database is Oracle 10g-

Set up: I have a presentation hierarchy Region>Country>State>City build from a single Geography Dimension. I have three fact columns coming from three materialized views. 

Now, when this Presentation Hierarchy and three fact columns are used to create a request, and I try to drill down, BI query is throwing a "table does not exist" error. I get the following error.

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P 
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

Instead of the presentation hierarchy if I use high level column (Region), I am able to drill down and get correct numbers. I am able to create a report with Region, Country, State, City, Fact1, Fact2, and Fact3 without getting an error. 

With the error, I also saw the query log with too long query and many SAWITHn, SACOMMONnnnn clauses. 

I enabled PERF_PREFER_INTERNAL_STITCH_JOIN in DB Features in repository and now the hierarchy and reports work fine. You will get a warning that DB Features do not match default values during consistency check, you can ignore that.

Hope it helps someone. 


Monday, May 23, 2011

OBIEE 11g- Deploy RPD - Create New Catalog

Steps to deploy repository and create a new web catalog in OBIEE 11g
  • Go to http://{localhost}:7001/em and login
  • From left pane, expand Business Intelligence and click on coreapplication
  • Go to Deployment tab then Repository sub-tab
  • In the Change Center: click Lock and Edit Configuration
  • Click Close when you receive the confirmation message Lock and Edit Configuration - Completed Successfully.
  • In the Upload BI Repository section: Click Browse and navigate to the directory with the yourrepository.rpd repository. Select the yourrepository.rpd and click Open
  • Enter your-repository-password as password and click Apply.
  • (Optional: If you want to create a separate catalog; In the Presentation Service Repository section, you will have a  string like this one: $ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/$COMPONENT_NAME/catalog/SampleApp
  • Delete the default catalog- SampleApp in this case and enter a name that you want to give to your catalog e.g. MyNewCatalog you should have something like this $ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/$COMPONENT_NAME/catalog/MyNewCatalog)
  • In the Change Center: click Activate Changes
  • Click Close when message Activate Changes - Completed Successfully message appears.
  • Go to Overview tab and click Restart. Click Yes to confirm restart.
  • Click Close when you receive the message Restart All - Completed Successfully.
  • Go to http://{localhost}:9704/analytics login to create reports in new rpd with Your-New-SA subject area.
More information in OBIEE 11g Documentation Libraray:


Sunday, April 17, 2011

OBIEE 11g Query Log- Log Level

How to define log level in OBIEE 11g? 

When enabled, OBIEE creates a query log in NQQuery.log file which is located by default at {Middleware_Home/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1. The query long becomes very handy in testing environment and troubleshooting. Click here for more details on Query Log and Log Level.

In 10g, it was simple. Open your repository in Oracle BI Administration tool, from the file menu go to Manage> Security > Users. Double click a user to go to properties and set the logging level.

Well, let's try the same in OBIEE 11g, let's go to Manage> Identity.

Where are my users?

Now, in OBIEE 11g, the users security/identify store is defined in Weblogic server. To define log level open your repository in online mode in Oracle BI Admin tool, go to Manage> Identity. Now, you can see the defined users. 

Double click on a user name to see properties. Set logging level by using up and down arrow. Log level is defined from 1 to 5. Click here to get more information on logging level. 

Click OK, check in your changes, save the repository. Restart BI Services and next time this user logs in you will be able to see the query logs being generated.



Wednesday, April 13, 2011

OBIEE Interview Question- Alias Table

What are the benefits of using Aliases in OBIEE?

It is a best practice to create aliases for all tables. Aliases are created in the Physical Layer of the repository. Some of the benefits of using aliases are given below:

·        You can create a join relationship which is different than the actual foreign key relationship in the database. This helps in defining relationships to meet business model criteria.
·         An alias is used to avoid circular joins and to configure self joins. (for more details on Fan Trap and Circular Joins check this blog.)
·         Aliases can function in numerous ways to define business model e.g. a table can act both as a dimension and a fact using aliases.
·         You can rename aliases to make physical and business model easier to understand. 

Click here to read more about aliases in Oracle Documentation. 
More information in this wiki/blog:

Reference: Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1)


Saturday, April 9, 2011

OBIEE 11g Customization

OBIEE Dashboard Logo Customization

Clients usually want that their company’s logo appears in the dashboard page.

This can be achieved by replacing Oracle’s logo (oracle_logo.PNG) from 


Please refer Oracle’s whitepaper- ‘Customizing Oracle Business Intelligence Enterprise Edition 11g’ for more information on customization by clicking here.


Tuesday, March 15, 2011

OBIEE Error: BEA-001129, ORA-12514, WSM-02311

OBIEE Error: BEA-001129, ORA-12514, WSM-02311
New errors came up when I started my PC and OBIEE about two weeks ago. I started OBIEE services and http://localhost:9704/analytics page never showed up. I did a quick opmnctl check and found that Oracle BI Scheduler and Oracle Presentation Services are down.

I found the following from the logs:
Message ID: BEA-001129
Received exception while creating connection for pool "mds-owsm":
Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Message ID: WSM-02311
java.rmi.RemoteException: EJB Exception:
 nested exception is: \n oracle.adf.share.ADFShareException: MDSConfigurationException encountered in parseADFConfiguration
Failed to retrieve requested documents due to underlying error "java.rmi.RemoteException: EJB Exception:
nested exception is:
oracle.adf.share.ADFShareException: MDSConfigurationException encountered in parseADFConfiguration

Now, the only error that I can relate to was ORA-12514. This is an Oracle error so I thought to check if my connections to underlying reporting database are fine and the services are running.

I was able to successfully log on to my reporting database.

I tried logging into the database where my Oracle BI Schema resides. This is the database I used to create BI Schema using RCU for my OBIEE 11g installation.

ORA-12514 – this is exactly what I was looking for.
I checked to see if the services for this database instance are running.

To my surprise the services were running! I was expecting the Oracle services down for this instance. I went to EM for Oralce database and found this.

I restarted the Oracle services for this database to bring them up. Then, I restarted Oracle BI Services and checked status of the services after restart. This time all the services were alive.

As expected I was able to see the login screen this time.


Sunday, February 13, 2011

OBIEE Error 39008

OBIEE Error 39008: Logical dimension table X has a source that does not join to any fact source.

I am using Oracle Business Intelligence version to create a new repository. My usual procedure is to create a repository in iterative manner, developing a simple repository using some of the tables and finish a simple model and repository without adding too much of details. I keep adding more details with each subsequent iteration.  

I just finished my first iteration of repository development and it looks like this

The consistency checker gives no error or warnings.

In my second iteration I created a dimension/hierarchy.

I now did a consistency check of my repository and got 12 warnings.

OBIEE Error 39008: Logical dimension table X has a source that does not join to any fact source.

Now there are three things to check to check for 39008 Error/Warning:
1.     Make sure that there is a physical/foreign key join between the LTSs.
2.     Make sure that there is a complex join between the logical tables.
3.     Make sure that the granularity between the dimension and the fact is same. (Check content tab in LTS)

Everything seems to be in place in my repository. Here is the trick that solved my problem and remove all consistency warnings.

Go to BMM layer and expand your fact table and it’s LTS.

Now, double click on the LTS to view properties and go to content tab.

Now, here is the trick. In the drop down box for 'Aggregation content, group by' select 'Column' instead of 'Logical Level'

Click OK.

Now do a consistency check. In case you don’t know the shortcut, you can press Ctrl+k to do a consistency check.


All the 39008 warnings are gone.

Now go back to LTS of your fact table in BMM layer and check that the 'Aggregation content, group by' setting has reverted back to 'Logical Level'.

This is a documented procedure by Oracle. More information on OBIEE 39008 Warning: