[nQSError: 43023] Number of sessions exceeded the limit.

Oracle Business Intelligence - When a user tries to login, he will get an error called- [nQSError: 43023] Number of sessions exceeded the limit, because the maximum number of users are already logged in. to know more about this error please visit this link - http://obieeblog-jagga.blogspot.in/2012/08/nqserror-43023-number-of-sessions.html.
   

                            

NQSConfig.ini is a configuration file, where we can configure the OBI Server however we want. MAX_SESSION_LIMIT is a parameter in NQSConfig.ini file to configure the maximum number of session that can be opened at a time. For example, if you set a value 10 to this parameter(MAX_SESSION_LIMIT = 10), then only 10 users can be login. Assume that, 10 users are already logged in, now if any user tries to login then he will get the following error, because the session limit is reached already. The users has to wait until any one of the user(who have logged in) logged out from the session.

                             

To resolve this problem you just follow the below instructions.

Open the NQSConfig.INI file and locate the [ SERVER ] section,  check the number for MAX_SESSION_LIMIT = 1000 ; parameter. (by default this parameter value will be 1000)

If the value for this parameter is less than the number of concurrent users of your application, then increase the number as it should be equal or greater than the number  concurrent users of your application.

If I am wrong, feel free to suggest me please.

[nQSError: 22040] To use AGO function, the storage level of the query ('[time1.time_key]') must be a static level. (HY000)

This error may occurs most probably due to improper Time dimension hierarchy.

To use AGO or TODATE time series functions, it is recommended that, we should define the detailed level also in the Time dimension hierarchy.

Eg : Suppose you have a time dimension table, that includes columns Time_key, Date_value, Month, Quarter, and Year. And If you have created the Time dimension hierarchy with the levels such as YEAR, QUARTER and Month only (there is no level defined for the column Date_value, which describes the granularity (level of detail)of the row.).

• This works fine, for drill down, level based measures and to define aggregate tables. 
• But when you try to use AGO or TODATE function with this Time dimension hierarchy, you will get the above mentioned error.

Solution : Just include one more logical level for the Time dimension hierarchy and add the date_value(or a column which holds granularity for the row.) as a logical key.

Maximum Connections Parameter in Connection Pool

This parameter specifies the maximum number of concurrent connections allowed at any point of time. The default value for this parameter is 10.

For each connection pool, you must specify the maximum number of concurrent connections allowed. After this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool or, if no other connection pools exists, the connection request waits until a connection becomes available.

Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Test and consult with your DBA to make sure the data source can handle the number of connections specified in the connection pool. 

In addition to the potential load  associated with the database resources, the Oracle BI Server allocates shared memory for each connection upon server startup. This raises the number of connections and increases Oracle BI Server memory usage.
 
Simple Calculation to determine the Max Connections for your Applications. 

Make sure the “Max Connections” parameter on your connection pools are appropriately set (not too low….not too high!). If in doubt, you can use the following formula which assumes that no more than 4% of your users will ever be logged on and running a report at any one moment:

Max Connections =   Total Users   *   0.04   *    Max Reports on a Dashboard 

So if you have 1000 users and you have no more than 4 reports on any one dashboard, then your  “Max Connections” should be set to 160. 

Note : Max reports on a dashboard includes reports and prompts.

[38087] None of the levels in time Dimension '' defines a chronological key.

For a Time Dimension, it is mandatory that at least one logical level must have set Chronological key. If a chronological key is not set for a Time dimension, then we will get the following error when you check the consistency.

Solution : Expand the Time dimension, go to properties of any logical level, then go to keys tab and check the Chronological Key tab.

Restricting user access to Connection Pool

We can restrict any user from accessing the Connection Pool using the Permission option in Connection Pool.

Just click on the Permission button, then you will get "Permission - Connection Pool " window. By default, this window will show only user Groups. To see users also, check the Show all users/groups option.

For each user and group, you can allow or disallow access privileges for an object by clicking in the check box to toggle among the following options:

• A check mark indicates that a permission is granted.
• An X indicates that a permission is denied.
• An empty check box indicates that a permission has not been modified. 

In the above picture, we can find one user group (Test) and two users (U1,U2). Now, let us deny access to the Group called Test.To deny access, just make the check box cross (X), as shown below.

All the users of that group cant access the connection pool. That is when the user runs a report, then he will get an error like : [nQSError: 19007] The user does not have sufficient privilege to access the database

Note : Assume that,
           you have created two connection pools(eg. CP1 and CP2) for a Data source in the physical layer,
           and you have a user called U1 and you have denied access to the connection Pool C1.
 When a user U1 requests connection, as the user don not have permission to Connection pool C1,
 OBI Server will route the request to the second Connection Pool C2. So, If you want any user to be                     denied to access the underlying data source, remove access to all connection poll, not any single connection pool.

Connection Pool

The connection pool is an object in the Physical layer, it contains information about the connection between the Oracle BI Server and that data source.

The Physical layer in the Administration Tool contains at least one connection pool for each database. When you create the physical layer by importing a schema for a data source, the connection pool is created automatically.

You can configure multiple connection pools for a database.It is recommended that you create a dedicated connection pool for initialization blocks.

About Connection Pools for Initialization Blocks

It is recommended that you create a dedicated connection pool for initialization blocks. This connection pool should not be used for queries.

Additionally, it is recommended that you isolate the connections pools for different types of initialization blocks. This also makes sure that authentication and login-specific initialization blocks do not slow down the login process. The following types should have separate connection pools:

• All Authentication and login-specific initialization blocks.
• All initialization blocks that set session variables.
• All initialization blocks that set repository variables. 

Setting Up General Properties For Connection Pools

Name : We can set any appropriate name for the connection pool based on its usage. Usage in the sense, there may be multiple connection pools for session variables, repository variables, authentication and log in purpose.

Call Interface : specify the interface through which you want the application to communicate with underlying database.To communicate with oracle databases select either odbc or native call interfaces (oci 8i/9i, oci 10g/11g). 

• when a odbc is selected "Data source name:" is the name of the System DSN in odbc data source administrator. 
• when a oci call interface is selected "Data source name:" is the TNS Name in the tnsnames.ora file.

3. Maximum Connections: This parameter specifies the maximum number of concurrent connections allowed.

The default is 10.

For each connection pool, you must specify the maximum number of concurrent connections allowed. After this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool or, if no other connection pools exist, the connection request waits until a connection becomes available.

Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Test and consult with your DBA to make sure the data source can handle the number of connections specified in the connection pool. 

In addition to the potential load  associated with the database resources, the Oracle BI Server allocates shared memory for each connection upon server startup. This raises the number of connections and increases Oracle BI Server memory usage.

Make sure the “Max Connections” parameter on your connection pools are appropriately set (not too

low….not too high!).    If in doubt, you can use the following formula which assumes that no more

than 4% of your users will ever be logged on and running a report at any one moment:

Max Connections =   Total Users   *   0.04   *    Max Reports on a Dashboard

So if you have 1000 users and you have no more than 4 reports on any one dashboard, then your
“Max Connections” should be set to 160.

Require fully qualified table names : Select this check box, if the database requires it. When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.

Eg: Assume that, there is a table called Emp in Physical Layer, which has imported from a default schema dbo of the database OLE.

• When we check the option Require fully qualified table names for connection pool, then the requests will contain the full qualified table name like - OLE.dbo.Emp.                                                                                                                                                select ename from OLE.dbo.Emp
• When we uncheck the option Require fully qualified table names for connection pool, then the requests will contain only table name itself like - Emp.                                                                                    select ename from Emp

Data source name : The drop-down list shows the System DSNs configured on your system. A data source name that is configured to access the database to which you want to connect. The data source name needs to contain valid log on information for a data source. If the information is invalid, the database log on specified in the DSN will fail. 

Shared logon :  Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same user name and password.

If this option is selected, then all connections to the database that use the connection pool will use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN (or in user configuration).

If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.

 

Enable connection pooling :  Allows a single database connection to remain open for the specified time for use by future query requests. Connection pooling saves the overhead of opening and closing a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.

  Timeout (Minutes) : Specifies the amount of time, in minutes, that a connection to the data source will remain open after a request completes. During this time, new requests use this connection rather than open a new one. The time is reset after each completed connection request.

If you set the timeout to 0, connection pooling is disabled; that is, each connection to the data source terminates immediately when the request completes. Any new connections either use another connection pool or open a new connection.

How to check Session Variable's value in Administration Tool ?

We can check the session related information in Administration Tool using an option called, Session Manager.

Manage--> Session.

In Session Manager, we can find two section,

 the first section shows all the users who have logged in, 

the second section has two tabs, Requests and Variables.
Requests :  The Requests tab shows all the requests issued by the user(the user which is selected in the first section of the Session Manager.)

Variables : The Variables tab shows all the variables and their values assigned for that user session.

Alias Table in OBIEE

Alias table means it is a reference table for the physical Table. When you create any alias table, then the alias table will inherit all the properties of the physical table.

We can not modify the alias table properties, like
1. can not rename the column name.
2. Can not change the  column data type .
3. Can not delete the columns

But we can only change the alias table name according to our naming convention.

Steps to create Alias table :
1. Right click on the physical table, New Object -->Alias

2. Give the name for alias table. Then click on OK.

Advantages of Alias Table :
1. OBIEE does not support Circular join in BMM layer, so we can overcome from this problem by creating alias table for one of the table in that circular join.

2. OBIEE does not support self join, so we can overcome from this problem by creating alias table.

3. If a fact table having relationship with dimention table on more than one key, then we can create aliases for the dimension talbe, then we can join fact table to these alias table on different keys.
Eg : consider, there is a order table, it has two columns in that, namely, order_date and required_date, both columns have to be joined to the Time dimension. In such situation we can create alias for the Time dimension, like Order_Date_dim and Required_date_dim. Now join the order table to these tables on different keys.