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:

You can also go to http://{server}:{port}/analytics/saw.dll?Admin

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!

Cheers!

Deepak

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 

(location:\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1) 

and set FMW_UPDATE_ROLE_AND_USER_REF_GUIDS to YES as shown below. Save and Close NQSConfig.INI file. 

Edit instanceconfig.xml file 

(location:\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1) 

and modify or add the following:  <Catalog>
<UpgradeAndExit>false</UpgradeAndExit>
<UpdateAccountGUIDs>UpdateAndStartNormally</UpdateAccountGUIDs>
</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! 

Cheers! 

Deepak

References:

http://download.oracle.com/docs/cd/E21764_01/core.1111/e10105/testprod.htm#BABCFDGB

http://download.oracle.com/docs/cd/E21764_01/bi.1111/e10541/configfileref.htm#sthref1056

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

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

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:


SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS;


You can use the following to unlock accounts:

ALTER USER MDS ACCOUNT UNLOCK;

ALTER USER BIPLATFORM ACCOUNT UNLOCK;

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

ALTER USER MDS IDENTIFIED BY Admin123;

ALTER USER BIPLATFORM IDENTIFIED BY Admin123;

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

Cheers!

Deepak

OBIEE 10g Migration Steps- Linux

I recently end up going back to 10g version and the following script helped me a lot during migration process between DEV/QA/PROD. Let’s assume we are migrating from QA to PROD for this post. I usually use putty to perform these steps. Following steps can be a good guideline.

1.  Start Putty and point to QA box. Shutdown OBIEE services on QA box.

./run-sa.sh stop

./run-saw.sh stop

2.  Make a new deployment directory (mmddyyyy is date of deployment this gives a versioning technique/ trackback control on deployment)

mkdir rpdcatmmddyyyy

3.  Copy Catalog & RPD into deployment directory

Change directory to the new deployment folder you created.

cd rpdcatmmddyyyy

-Copy rpd and catalog to this directory

cp -R /u01/app/obiee/OracleBIData/web/catalog/SalesCatalog .

cp /u01/app/obiee/OracleBI/server/Repository/salesrepository.rpd .

If you have made changes to configuration files, copy them over to this deployment folder as well e.g. I created a new xml file for writeback

cp /u01/app/obiee/OracleBI/web/msgdb/customMessages/writeback.xml .

4.  SCP the deployment directory to PROD box

Go back to home directory by issuing cd command and then use SCP as below

cd

scp -r rpdcatmmddyyyy obieeprod@prodboxname:/home/obieeprod

5.  Start another session of putty pointing to PROD box. Archive Catalog & RPD in target platform.

Stop BI Services in PROD box

 ./run-sa.sh stop

./run-saw.sh stop

cd /u01/app/obiee/OracleBIData/web/catalog/

mv SalesCatalog SalesCatalog.mmddyyyy

cd /u01/app/obiee/OracleBI/server/Repository/

mv SalesRepository.rpd SalesRepository.rpd.mmddyyyy                                

6.  Move Catalog & RPD to proper directories from the deployment directory you moved to PROD box

cd /u01/app/obiee/OracleBI/server/Repository/

cp /home/obieeprd/rpdcatmmddyyyy/salesrepository.rpd .

cd /u01/app/obiee/OracleBIData/web/catalog/

cp -R /home/obieeprd/rpdcatmmddyyyy/SalesCatalog .

cd /u01/app/obiee/OracleBI/web/msgdb/customMessages/

cp /home/obieeqa/rpdcatmmddyyyy/writeback.xml .

7.  Start OBIEE in QA and POD

./run-sa.sh start

./run-saw.sh start

8. If you have not pointed PROD rpd to PROD DB then do it in online mode now.

Hope someone working on OBIEE 10g will benefit from this post.

Cheers!

Deepak

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 - 11.1.1.3. Database is Oracle 10g- 10.2.0.3

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. 

Cheers!

Deepak

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: http://download.oracle.com/docs/cd/E21764_01/bi.htm

Cheers!

Deepak

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.

References: 

http://obiee11gqna.blogspot.com/2010/11/obiee-interview-question-in-obiee-10g.html

http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10541/logging.htm#CBAIGGID

http://gerardnico.com/wiki/dat/obiee/log#diagnostic_log_files

Cheers, 

Deepak

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: http://gerardnico.com/wiki/dat/obiee/alias

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

Cheers!

Deepak

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 

{Middleware_Home}\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\sk_blafp\b_mozilla_4

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

Cheers!

Deepak

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.



Cheers!
Deepak

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 11.1.1.3.0 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.

Tadaa….

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:

http://download.oracle.com/docs/cd/E14847_01/bia.796/e14222/chapter.htm#BABJEAIH

Cheers!

Deepak