Tuesday, November 30, 2010

OBIEE Error- nQSError: 10058, nQSError: 16001, ORA-01035

OBIEE Error: Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

While working on Answers late yester-night, I got the following error:

Odbc driver returned an error (SQLExecDirectW).
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 16001] ODBC error state: 08004 code: 1035 message: [Oracle][ODBC][Ora]ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege. (HY000)
SQL Issued: SELECT (My Query Here)

With a little help from Google, I found the following:

Oracle Error :: ORA-01035

ORACLE only available to users with RESTRICTED SESSION privilege

 

Cause

Logins are disallowed because an instance started in restricted mode. Only users with RESTRICTED SESSION system privilege can log on.

 

Action

Request that Oracle be restarted without the restricted option or obtain the RESTRICTED SESSION system privilege.

Actually, this error occurred because the Oracle database was being refreshed at the client site with the latest data. You can ask your DBA for RESTRICTED SESSION system privilege or wait for the database refresh t finish.

Cheers!
Deepak

Sunday, November 28, 2010

OBIEE Interview Question- Dimensional Hierarchy


What is dimensional hierarchy? How you define number of elements at each level? Can it be done automatically?

A dimensional hierarchy is created in the BMM layer of a repository to calculate level based measures and to create interactive reports/dashboards with drill down functionality.

A dimension hierarchy can be created manually as well as automatically. Manual process involves right clicking on BMM layer> New Object> Dimension, entering a name; right clickking on this new dimension and create New Level.

Levels are base on the business definition or hierarchy of a dimension, a classic example; year> quarter> Month> week> Day. A hierarchy will have at least two levels, Grand Total Level and Detail Level. For each level we have to define the number of elements. This is always 1 for the Grand Total Level and next levels are the ratio from one level on top to the bottom.

When creating a dimension automatically, we have to right click on the dimension and click Create Dimension. This will automatically create the two levels Grand Total and Detail Level.

We can actually automatically calculate number of elements at each level. The procedure is to open a repository in online mode. Right click on business model or dimension hierarchy and click on estimate levels.


Cheers!
Deepak

Tuesday, November 9, 2010

OBIEE 10g: Log Level in Production Environment

OBIEE Interview Question: What should be log level in production environment?

This is in continuation of my previous post regarding log levels.

The log level is set to 0 (zero) in the production environment. Tracking query logging is a resource intensive process and can strain the system, especially when you are dealing with hundreds of users in the production environment. In production environment you would rather use the Usage Tracking as the production level logging facility.


Log levels are usually set to some value when we are in the testing phase of the OBIEE deployment. As described in my previous post, in case of troubleshooting, we can use temporary log level. This can be done by using the following just before the query.

Set Variable LOGLEVEL=n;

More details are available in Oracle Business Intelligence Server Administration Guide linked here.

Cheers!
Deepak

Saturday, November 6, 2010

OBIEE 10g: Log Level

OBIEE Interview Question: In OBIEE 10g, what is log level? Or what are different log levels? Or tell me about your experience with query logs in OBIEE 10g.

When enabled, OBIEE creates a query log in NQQuery.log file which is located by default at {installation drive}\OracleBI\server\Log.  The log can be viewed using a text editor or the nQLogViewer. The query long becomes very handy in testing environment and troubleshooting.

The size of NQQuery log file is defined in USER_LOG section of  NQSConfig.INI file located by default at {installation drive}\OracleBI\server\Config. 

A sample Log file definition from NQSConfig.INI file is given below:

#################################################################
[ USER_LOG ]

USER_LOG_FILE_SIZE = 10 MB;   // default size
CODE_PAGE = "UTF8";           // ANSI, UTF8, 1252, etc.
#################################################################

How to set logging level?
Logging level is enables and set at individual level. To define log level
In the Administration Tool, select Manage > Security.
The Security Manager dialog box appears.
Double-click the user’s user ID.
The User dialog box appears.
Set the logging level by clicking the Up or Down arrows next to the Logging Level field.

To disable logging level set the logging level to 0.

Log levels and corresponding information is given below in the table.

Logging Level
Information That Is Logged
Level 0
No logging.
Level 1
Logs the SQL statement issued from the client application.
Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing.
Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.
Level 2
Logs everything logged in Level 1.
Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.
Level 3
Logs everything logged in Level 2.
Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails.
Do not select this level without the assistance of Technical Support.
Level 4
Logs everything logged in Level 3.
Additionally, logs the query execution plan. Do not select this level without the assistance of Technical Support.
Level 5
Logs everything logged in Level 4.
Additionally, logs intermediate row counts at various points in the execution plan.
Do not select this level without the assistance of Technical Support.
Level 6 and Level 7
Reserved for future use.


It is also important to note that logging level can be set at session level by using a session variable. In case if a user has two different log levels sat at the repository and at the session level, the session level will have precedence over the log level set in the repository.

In case of troubleshooting, I have also used temporary log level. This can be done by using the following just before the query.

Set Variable LOGLEVEL=n;

For more details about query logs and log level refer to Oracle Business Intelligence Server Administration Guide available here.

Cheers!
Deepak