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 Details

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

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

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

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

OBIEE 11g vs OBIEE 10g

OBIEE Interview Question: What are the new features added/changes made in OBIEE 11g compared to OBIEE 10g?

There are a lot of enhancements in the OBIEE 11g as compared to OBIEE 10g. Here are few points to talk during an interview:

• OBIEE 11g uses WebLogic Server as the application server as compared to Oracle AS or OC4J in OBIEE 10g.
• The clustering process in much easier and automated in OBIEE 11g.
• We can now model lookup tables in the repository.
• The new UI called Unified Framework now combines Answers, Dashboards, and Delivers.
• A new column called the hierarchical column in introduced.
• BI Publishers is fully and seamlessly integrated with OBIEE 11g.
• New time series functions PERIOD ROLLING and AGGREGATE AT are introduced.
• In OBIEE 11g we can create KPIs to represent business metrics.
• The aggregate persistence wizard creates indexes automatically.
• The session variables get initialized when they are actually used in OBIEE 11g unlike OBIEE 10g where they were initialized as soon as a user logs in.
• OBIEE 11g now supports Ragged (Unbalanced) and Skipped Hierarchy.
• You can also define Parent-Child hierarchy in OBIEE 11g as well.
• SELECT_PHYSICAL command is supported in OBIEE 11g.

In OBIEE 11g there are some changes in the terminology as well.

• iBots are renamed as Agents.
• Requests are renamed as Analyses.
• Charts are renamed as Graphs.
• Presentation Columns are renamed as Attribute Columns.

There are of course a lot more changes and updates but these are few that you can talk about in an interview. More information is available here and here.

Cheers!

Deepak

Interview Question: OBIEE Project Life Cycle

People will usually ask an experienced OBIEE resource if she has full life cycle implementation experience in OBIEE and to tell about the phases in the BI project.

As an OBIEE Developer, not everyone work during the whole life cycle of the project. This is especially true for contractors. Considering the RUP there are four life cycle phases of a project- inception, elaboration, construction, and transition. As an OBIEE developer you will be mostly working during the designing, developing, testing, and implementing phase. Sometimes there is involvement in requirements gathering phase but more chances are to work in the refining the requirements.

After explaining this you can tell about the project life cycle as below:

1.  The initiation/inception phase: Creating business case, Project planning and feasibility study.

2.  The elaboration/planning phase: Resource Planning, Requirements gathering and analysis

3. The execution/development phase: Design and development. This is where as an OBIEE developer I worked the most.

4.  The transition/closure phase: Deployment, operations and maintenance.

Cheers!

Deepak

Interview Question: OBIEE Architecture

One question that is very interesting to know in an interview is about the OBIEE Architecture.

The following is a very interesting and informative link to understand the OBIEE Architecture.

http://www.rittmanmead.com/2010/07/09/obiee-11gr1-architecture-and-use-of-weblogic-server/

You can talk about the OBIEE 10g as well, below are some informative links.

http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b40058.pdf

http://gerardnico.com/wiki/dat/obiee/obiee_overview

When talking about the OBIEE Architecture, I am not only looking for a description about all the components present in the OBIEE but also how they communicate with each other.

Pointers: talk about the OS you used, Application Servers, Semantic Layers in OBIEE, Database, Authentication (Active Directory etc.) etc. and communication protocol.

An in depth knowledge about the architecture assures me that the candidate is well versed with not only the tool but have an overall picture of the BI at the higher/enterprise level.

Cheers!

Deeapk

OBIEE Interview Question- Performance Tuning / Top 10 Performance Tuning Strategies in OBIEE

One question that I will ask regularly in an interview is regarding Performance Tuning in OBIEE. The question is quite broad and I want to see how, as an OBIEE developer the candidate has worked towards Performance Tuning on various aspects of BI.

I am looking for the following pointers in the answer which will show the overall awareness of performance tuning in a BI project.  You should not only talk about the performance tuning at the OBIEE level but also about databases, the network, and the ETL.

1.  The first and most important and obvious one is to develop a cache management strategy.

I like to hear about how the NQSconfig.ini cache parameters were altered. If you mention about only ENABLE parameter, that’s not enough for me.  I would like to hear about MAX_ROWS_PER_CACHE_ENTRY, MAX_CACHE_ENTRY_SIZE, MAX_CACHE_ENTRIES, POPULATE_AGGREGATE_ROLLUP_HITS, USE_ADVANCED_HIT_DETECTION, MAX_SUBEXPR_SEARCH_DEPTH,  and Cluster- aware cache parameters if you have knowledge/worked on such environment.

I will talk about how the tables are made cacheable. What strategies were implemented to purge and seed caches? In most cases we will use an iBot or a chained iBot or an automated script to build cache for a suite of queries frequently used by business. We also remove the WHERE clauses so that a when filter is created at report level, it still fetch data from cache.

At this time you can also speak about event polling tables and how it was implemented.

2. Create aggregate tables in the database and use aggregate navigation

The aggregate tables are made in the DB for a faster retrieval of data. Sometimes we create materialized views as well. The aggregate navigation and mapping should be done correctly at the BMM layer as well.

3. Implementing partitioning strategies at the DB level.

Usually an OBIEE developer will not be doing this but we must be aware of partitioning, the types of partitioning and how can partition help in BI projects.

4. Creating indexes in the database; this of course is done in consultation with data modelers/DBA.

5. We should also limit the number of initialization blocks for session variables as the initialization blocks are run as soon as a user logs in. In OBIEE 11g, the initialization block run/initialize as and when needed and as such do not take long time to log in.  

6. We turn off logging for all the users as writing log files consume memory and reduces performance.

7. The performance is greatly improved if push calculated measures in database. During ETL we create new calculated columns which will store pre-calculated values instead of calculating at BMM layer or Answers/report level.

8. Using database hints will also improve performance.

9. We also implement usage tracking model in the BI system. This helps us find how the users are using the BI system. We also determine the frequently run queries/dashboards and the queries creating bottlenecks. This helps us in building better strategies for performance tuning.

10. Last but not the least is the log files generated. This also helps us analyzing the actual physical SQL queries generated by the BI Server. Using Explain Plan feature helps us determine if the queries generated are optimal.

I ask readers to please comment and share your views and experience on strategies for performance tuning in OBIEE. 

Cheers!

Deepak

OBIEE interview- Security

One interview question that is always asked in an OBIEE Interview is regarding security. The question is asked in different manners e.g. Tell me how you implemented security in your recent project? Or Have you configured external table security in any project? Or In how many ways you can implement security in OBIEE? Or Have you configured an LDAP server for security in OBIEE? The answers below relate to OBIEE 10g.

Security is an important aspect of an OBIEE implementation project, and I have been involved in strategizing security in all the projects I worked on. There are various ways in which we can define security in OBIEE.

1. Define security in the repository.
2. External table authentication.
3. LDAP authentication.
4. Database authentication.
5. Custom authentication.

Define security in the repository: In the Oracle BI Administration tool we can create users and groups to define permissions and authentication. We then grant permissions to users and groups.

Note: The privileges which are exclusively granted to a user have precedence over group’s privileges. Also, in case of a conflict the least restrictive privileges apply.

LDAP Authentication: I have implemented LDAP (Lightweight Directory Access Protocol) server authentication in my recent project. We used ADSI (Active Directory Service Interface) in this project.

When asked about how LDAP is set up you can say that, in the security manager create an LDAP server. For this in security manager we go to Action> New> LDAP Server. This brings us to LDAP Server dialog box, where we fill in the parameters like Name, Host Name, Port No., LDAP Version (Default 3), Base DN, Bind DN in general settings. We also define settings in Advanced tab where we fill in Connection Time-Out, Domain Identifier, Enable/Disable SSL etc.

We then created an LDAP initialization block, which was associated with the LDAP Server. Here, we define USER as our system variable which is mapped to LDAP uid.

Note: We can also use LDAP server only to import user and group definitions. This is used when we don’t want external authentication by LDAP.

External Table Authentication:  I have used External Table Authentication in my projects. To implement this we have to create a table in the database which will have columns to define users, password, and groups, log level, display name, etc. information to define security and privileges. To use this table for authentication in OBIEE we created a new connection pool in the physical layer to connect to this db/table. Then we created an initialization block using the newly defined connection pool for this table. We then defined the initialization string (e.g. select username, password, lognumber, groupname from auth_table where username = ‘:USER’ and password = ‘:PASSWORD’). We then defined the corresponding variables (e.g. USER, PASSWORD, LOGLEVEL, GROUP etc.). We have to make sure that the order of variables is same as the initialize.

Database Authentication: I have not used this type of authentication in my projects. We first make changes in our NQSConfig.ini file. In the security section, we specify our authentication database. Then we create users in the repository which are same as the users in our database. We assign these users privileges. We import this database in the physical layer of our repository using the DSN of that particular database. For this particular connection pool we set up a non shared logon.  This connection pool will now be used to connect to the database. If you are able to connect, then you are authenticated successfully.

Custom Authentication:  I never came across a custom authentication in my career. John has written an article on custom authentication here.

In this article I tried to briefly describe the security types in available in OBIEE, this is not to much in detail but good pointers for an interview. Actual implementation will need a lot more information and details; refer Oracle BI Administration Guide; the links below might be helpful.

http://download.oracle.com/docs/cd/E10415_01/doc/nav/portal_booklist.htm

Kumar has very impressive articles on security visit http://obieeblog.wordpress.com/

Security in OBIEE 11g – Venkat’s article http://www.rittmanmead.com/2010/08/26/oracle-bi-ee-11g-authentication-authorization-weblogic-security/

Cheers!

Deepak