•OBIEE 10G/11G - BI Server (Logical Query|Result) Cache
•OBIEE 11G - Installation Sample Application version 825
•OBIEE - Security
•OBIEE - Which Usage Tracking time measure should be used to gauge the performance of SQL retrieval time ?
•Exalytics - Timesten Configuration
Init Block / Session Variables
It is recommended that you create a dedicated connection pool for initialization blocks.
Another thing to check is whether or not the init blocks are using the same connection pool that the queries are using. If so, best practices dictate that init blocks (especially authentication) be given their own connection pool, otherwise you might have a situation where nobody can authenticate because the init blocks are waiting along with all the other queries for a connection pool thread.
When a user logs one these Init Blocks are run against the database to populate the session variables, each will use up a connection. If you are using the same connection pool as the one used for your data queries, this means the limit of Max connections will be more quickly reached, and would result in new users being unable to log on as the Init Block queries are queuing up, and cannot be run when the user logs on.
So we recommend that you create dedicated connection pools for your session variables, and associate these new connection pools to the Init Blocks that populate your session variables. You can initially set the Max connections on these connection pools to 64.
Connection Pool Parameters
Maximum connection
The rule of thumb is to use the number of simultaneous users times the number of requests on a dashboard. Also important is to know how much connections can be handled by the database, which is specific per database system (like DB2 has to start agents, Oracle needs to configure #connections in the init.ora
The Maximum Connections setting governs the number of active queries on the db. When the limit is reached, then the system waits for a connection to become available. As a single request can generate multiple db queries, it is possible for a maximum connections of 64 to be reached with less than 64 users. Also, a dashboard can have lots of requests so it is possible for a few users to occasionally hit the limit.
The default value of 10 is usually too low (it will cause queuing) but setting it too high will eat up memory Approx 1024 kb of server memory is consumed per connection and the BI Server process may not even start.
As a “rule of thumb”, calculate it using the following theory:
•At any one time, 10-20% of your users will be logged in
•Of those users logged in, only 10-20% will actually be running queries
•There are a maximum of X reports on any one Dashboard Page
So if you have 1000 users in total and a maximum of 4 reports per dashboard page:
•Maximum Connections = 1000 x 0.2 x 0.2 x 4 = 160
Or in other word:
Maximum number of connections =
20% of concurrent users
* number of reports per dashboard.
The sum of all of the 'Maximum connections' in a repository cannot exceed 800.
Call interface
The call interface represents the driver that you use to connect to the data source. You have three methods:
•ODBC
•OCI
•XML. Xml is a build-in drivers to be able to connect/read XML files in a specific format.
Execute Queries Asynchronously
“Execute Queries Asynchronously” parameter in the connection pool, this defines a parameter of our communication with the Oracle database. If this option is checked, the query is run asynchronously, and can be cancelled by the Analytics Server while the query is running, and using the same connection. If the option is not checked, the query is run synchronously and the Database will not receive the cancel call until the query has completed. Asynchronous mode is more efficient but it is less 'safe', as it is more complex to handle. It is useful to test your queries with the option unchecked (safer) ,as part of the troubleshooting.
User and Password
In place of the id and password, you can use the intern variable :USER and :PASSWORD for pass through.
Isolation Level
For ODBC and DB2 gateways only. Isolation Level :
•Committed Read
Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.
•Dirty Read
0 Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.
•Repeatable Read
Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.
•Serialization
Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.
Shared logon
OBIEE - How to define one different database credential for each user (the shared logon) ?
DSN
The data source name is
•for ODBC, the data source name
•for OCI a connect identifier
Parameters Supported
If this option is not selected, and the database features table supports "parameters", special code executes that allows the Oracle BI Server to push filters (or calculations) with parameters to the database. The Oracle BI Server does this by simulating parameter support within the gateway/adapter layer by sending extra SQLPrepare calls to the database.
Support
Security
If you have this error :
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 19007] The user does not have sufficient privilege to access the database ORCL. (HY000)
SQL Issued: SELECT B.DATA saw_0 FROM SH ORDER BY saw_0
It comes from the OBIEE connection pools security parameters from the database. Suppose that we have only one connection pool for a database, the image below show that nobody have been granted to access the connection excepted the Administrator :
•OBIEE 10G/11G - BI Server (Logical Query|Result) Cache
•OBIEE 11G - Installation Sample Application version 825
•OBIEE - Security
•OBIEE - Which Usage Tracking time measure should be used to gauge the performance of SQL retrieval time ?
•Exalytics - Timesten Configuration
Init Block / Session Variables
It is recommended that you create a dedicated connection pool for initialization blocks.
Another thing to check is whether or not the init blocks are using the same connection pool that the queries are using. If so, best practices dictate that init blocks (especially authentication) be given their own connection pool, otherwise you might have a situation where nobody can authenticate because the init blocks are waiting along with all the other queries for a connection pool thread.
When a user logs one these Init Blocks are run against the database to populate the session variables, each will use up a connection. If you are using the same connection pool as the one used for your data queries, this means the limit of Max connections will be more quickly reached, and would result in new users being unable to log on as the Init Block queries are queuing up, and cannot be run when the user logs on.
So we recommend that you create dedicated connection pools for your session variables, and associate these new connection pools to the Init Blocks that populate your session variables. You can initially set the Max connections on these connection pools to 64.
Connection Pool Parameters
Maximum connection
The rule of thumb is to use the number of simultaneous users times the number of requests on a dashboard. Also important is to know how much connections can be handled by the database, which is specific per database system (like DB2 has to start agents, Oracle needs to configure #connections in the init.ora
The Maximum Connections setting governs the number of active queries on the db. When the limit is reached, then the system waits for a connection to become available. As a single request can generate multiple db queries, it is possible for a maximum connections of 64 to be reached with less than 64 users. Also, a dashboard can have lots of requests so it is possible for a few users to occasionally hit the limit.
The default value of 10 is usually too low (it will cause queuing) but setting it too high will eat up memory Approx 1024 kb of server memory is consumed per connection and the BI Server process may not even start.
As a “rule of thumb”, calculate it using the following theory:
•At any one time, 10-20% of your users will be logged in
•Of those users logged in, only 10-20% will actually be running queries
•There are a maximum of X reports on any one Dashboard Page
So if you have 1000 users in total and a maximum of 4 reports per dashboard page:
•Maximum Connections = 1000 x 0.2 x 0.2 x 4 = 160
Or in other word:
Maximum number of connections =
20% of concurrent users
* number of reports per dashboard.
The sum of all of the 'Maximum connections' in a repository cannot exceed 800.
Call interface
The call interface represents the driver that you use to connect to the data source. You have three methods:
•ODBC
•OCI
•XML. Xml is a build-in drivers to be able to connect/read XML files in a specific format.
Execute Queries Asynchronously
“Execute Queries Asynchronously” parameter in the connection pool, this defines a parameter of our communication with the Oracle database. If this option is checked, the query is run asynchronously, and can be cancelled by the Analytics Server while the query is running, and using the same connection. If the option is not checked, the query is run synchronously and the Database will not receive the cancel call until the query has completed. Asynchronous mode is more efficient but it is less 'safe', as it is more complex to handle. It is useful to test your queries with the option unchecked (safer) ,as part of the troubleshooting.
User and Password
In place of the id and password, you can use the intern variable :USER and :PASSWORD for pass through.
Isolation Level
For ODBC and DB2 gateways only. Isolation Level :
•Committed Read
Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.
•Dirty Read
0 Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.
•Repeatable Read
Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.
•Serialization
Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.
Shared logon
OBIEE - How to define one different database credential for each user (the shared logon) ?
DSN
The data source name is
•for ODBC, the data source name
•for OCI a connect identifier
Parameters Supported
If this option is not selected, and the database features table supports "parameters", special code executes that allows the Oracle BI Server to push filters (or calculations) with parameters to the database. The Oracle BI Server does this by simulating parameter support within the gateway/adapter layer by sending extra SQLPrepare calls to the database.
Support
Security
If you have this error :
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 19007] The user does not have sufficient privilege to access the database ORCL. (HY000)
SQL Issued: SELECT B.DATA saw_0 FROM SH ORDER BY saw_0
It comes from the OBIEE connection pools security parameters from the database. Suppose that we have only one connection pool for a database, the image below show that nobody have been granted to access the connection excepted the Administrator :
I saw a post in the OTN forums asking how to disable the pdf option in the print menu for dashboards, if you want to do this for a particular dashboard then you can do this through javascript, here is a simple example:
var dateHeaderDiv = document.getElementById("dateHeader");
var dateHeaderPos = findPos(dateHeaderDiv);
dateHeaderDivYOffset = dateHeaderPos[1];
dateHeaderDivXOffset = dateHeaderPos[0];
var menuLink = getElementsByClassName("NQWMenuItem");
//Get the appropriate function for getting the HTML print
//output of the current page
for(i = 0; i < menuLink.length; i++)
{
if(menuLink[i].name == "html")
{
var onClickText = menuLink[i].onclick;
}
}
//Find the print icon and make it display the print page instead of the
//default menu for pdf or html print (because pdf print does not work
//properly with javascript narrative views)
var pdfParent = getElementsByClassName("DashboardFormatLinks");
That acutally removes the PDF option and makes the print icon kick off the HTML print page straight away without showing the menu, it works in 10.1.3.4.1. You can then add that as a static text item with the HTML option ticked and add that to the dashboard.
If you want to remove the option to print to pdf all across the application then first find the controlmessages.xml file which lives in OracleBI\web\msgdb\messages and copy it to OracleBIData\web\msgdb\customMessages, if it the customMessages folder is not there then create it. Now open the file, it looks like this:
<?xml version="1.0" encoding="utf-8"?> <!--DO NOT MODIFY THIS FILE. THIS FILE IS AUTOMATICALLY GENERATED AND IS REPLACED UPON UPGRADE OR REINSTALL.--><!--Contents of this file are Copyright (C) 2001-2005 by Siebel Systems, inc.--><!--Consult your Siebel Analytics Web documentation for how to override messages. Note that some or all messages may not be overriden unless your license specifically allows for it.--><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" system="ControlMessagesSys" table="Messages">
The logical table created at the BMM layer can be based on the data from a single physical layer table, when it is called single Logical Table source. When the specific logical layer table is dependent on the columns of different physical layer tables, it is called Multiple LTS. Most of the time we will be dealing with Multiple LTS.
It’s a question that OBIEE developers often ask. And there is a good reason for the uncertainty that shrouds this species of variable. The OBIEE documentation only contains a single reference:
In the Set Variable list, you can choose whether or not to populate a variable for the dashboard prompt using a server request variable or a presentation variable.
This “singularly informative” statement – as is so often the case with the OBIEE documentation – leaves us none the wiser as to what a request variable does or doesn’t do.
After playing around with request variables for a while, most developers conclude that they have something to do with setting session variables, but, frustratingly, sometimes this “setting” seems to work and sometimes it doesn’t.
Definition
So exactly what is a request variable? Well, put succinctly:
A request variable is a variable that is used to temporarily override the value of a session variable.
And, with a little more precision, a request variable is:
A variable that is defined within the Presentation Services;
A variable whose scope (page or dashboard) is that of the associated dashboard prompt that defines the request variable and its value;
A variable whose name should not match that of a repository variable or a security system session variable; and
A variable that overrides the value of the corresponding session variable (should it exist) for the purpose of BI Server request parsing and physical SQL construction for all requests that are issued from dashboard pages that fall within the scope of the variable’s definition.
Note, using a request variable does not change the actual value of a session variable – neither temporarily nor permanently. It simply appears to change the value for certain purposes during the execution of a request.
How it works
Request Variable Definition
Here’s the output from a simple dashboard request to select the values of column “LOC” from table “DEPT” in the standard Oracle schema “SCOTT”:
Dashboard Request
In the absence of a request variable being defined within the scope of the dashboard page from which the request is invoked, the logical SQL sent to the BI Server is (with a little formatting) as follows:
Logical SQL without Request Variable
Now let’s assume that we add a dashboard prompt defined to populate a request variable “SN_TEST”:
Dashboard Prompt with Request Variable Definition
We enter a value of “Value” in the edit box:
Dashboard Prompt with Request
Then when we press the “Go” button the specified value is assigned to the request variable within the Presentation Services (in this respect a request variable is much like a presentation variable). Internally, in terms of pseudocode, we can imagine the following assignment:
Note that if we just had the “Go” button and no request then no logical SQL would be sent to the BI Server – assigning a value to a request variable does not, in and of itself, modify anything in the BI Server.
Request Variable Dispatch
Once a request variable has been set, then whenever any request that falls within the scope of the prompt (page or dashboard) is sent to the BI Server, a request variable assignment is added as a prefix to the logical SQL that defines the request. In the case of our example, the logical SQL becomes:
Logical SQL with Request Variable
We can see that a request variable assignment, “SN_TEST=’Value’”, has been added as part of the prefix to the select statement. So now we can see why the variable is called a “request variable”: it’s a variable that accompanies requests on their journey from the Presentation Services to the BI Server.
Note that a request variable can have any name we like. It does not have to match the name of an existing server variable, though it’s unlikely to be of much use unless it does.
Note that this request variable assignment will be sent for all requests sent from the dashboard page if the scope of the prompt is the page and for all requests sent from all dashboard pages belonging to the context dashboard if the scope of the prompt is the dashboard. The request variable assignment is sent irrespective of whether or not requests reference the value of the request variable.
Request Variable Processing
Once a request, accompanied by a request variable assignment, reaches the BI Server then how is it processed? The following pseudocode illustrates the observed behaviour while processing a request:
IF <request contains request variable assignment> THEN
IF <request variable name equals security session variable
name> OR
<request variable name equals repository variable name>
THEN
<raise error>
ELSE
<create temporary session variable with request variable
name and assign to it the request variable value>
END IF
END IF
IF <request processing references a session variable> THEN
IF <temporary session variable of same name exists> THEN
<use value of temporary session variable>
ELSE
<use value of original session variable>
END IF
END IF
IF <temporary session variable was created> THEN
<destroy temporary session variable>
END IF
Note to avoid an error the name of the request variable must not match that of an existing repository variable or a security system session variable.
Session Variable Override is Temporary
The key point to remember, and one that many OBIEE developers find confusing given the dearth of documentation on the topic, is that if a session variable with the same name as that of a request variable already exists in the repository, then the request variable assignment does not permanently change the value of the session variable with which it is associated: it only appears to be changed, and even then only for certain purposes, for the duration of request processing and execution.
To illustrate the temporary nature of the session variable override let’s start off with an initialization block for a session variable “SN_TEST”:
Session Variable Initialization Block
If the user does not have Administration privileges, then the “Enable any user to set the value” checkbox in the variable definition pop-up window must be ticked, or, alternatively, row-wise initialization must be used instead (with row-wise initialization the privilege is granted automatically).
Let’s create two connection scripts, pre- and post-query, to log the value of the session variable before and after the request execution:
Logging Connection Scripts
Here “DP_LOG_REQUEST” is a standalone stored procedure that contains the pragma “autonomous_transaction”, an insert into a logging table, and a “commit”.
If we run our request first from Answers, without the prompt being in effect, and then run the request from the dashboard after entering a value of “Value” for “SN_TEST” and pressing the “Go” button we get the following log entries:
Session Variable Log Entries
The first pair of entries with session variable values of “Initial” shows that the session variable has its normal value assigned when the initialization block was executed. The second pair of entries with session variable values of “Value” shows that the session variable value has been temporarily overridden by the value assigned to the request variable.
But this change to the session variable reference is not a permanent change to the session variable. If we examine the value of the session variable using the Session Manager in the repository after the request has completed execution we see that its value equals that set by the initialization block:
Session Variables in Session Manager
(see the next section for what happens while the request is executing).
One consequence of this behaviour is that we cannot use a request variable to propagate a change from one dashboard to another – something that OBIEE developers often try to do. This would only be possible if the value of the session variable in the repository was changed on a permanent basis (note, changing the session variable permanently can be achieved by calling ODBC function “NQSSetSessionValue” – but that’s another article).
Change of Reference not a change of Functionality
Change of Reference
Whenever a session variable is referenced in the course of constructing the physical SQL to be sent to the database, the temporary value associated with the corresponding request variable assignment is used instead.
For example, we might add a “where” clause on the “Content” tab of the logical table source for logical table “DEPT”:
Where Clause in Logical Table Source
we might create a new logical column “Upper Location” that references the session variable:
Session Variable Reference in Logical Column Definition
and we might reference both “Upper Location” and the session variable directly within a request:
Session Variable Reference in Request
Then when we enter a value of “BOSTON” for our prompt value we get:
Dashboard Prompt and Request
(well, yes, “lower” might have been a better choice!) So all the references to the session variable, both within the incoming request SQL and within the repository, evaluate to the request variable value.
No change of Functionality
In the case of system session variables we have in addition to the value of the variable the functionality represented by that variable. A request variable assignment effectively causes the BI server to make a copy of the associated session variable, and this copy takes precedence over the original when it comes to variable references made during request processing and execution, but it does not alter the value of the original session variable or propagate the changes in functionality that are normally associated with session variable initialization.
For example, let’s set up an initialization block to populate the session variable “DISPLAYNAME”:
Initialization Block for Display Name
If the user does not have Administration privileges, then the “Enable any user to set the value” checkbox in the variable definition pop-up window must be ticked, or, alternatively, row-wise initialization must be used instead (with row-wise initialization the privilege is granted automatically).
Let’s set up a request to display the value of the session variable:
Long Running Request
Request execution will last for at least 10 seconds due to the “EVALUATE” call to standalone stored function “DF_WAIT”, a function that loops for a specified period of time before returning a value:
Evaluate Function Call
This delay allows us to examine what happens during request execution.
On entry to the dashboard we get a display name of “SIEBEL”:
Default Request Running
and, when the query completes, we can see that in the absence of the request variable being set the referenced value of the session variable “DISPLAYNAME” is also equal to “SIEBEL” as expected from the initialization block:
Default Request Completed
If we now set the value of the request variable to “ORACLE” and initiate the request, then we can see that while the request is running the display name remains unchanged:
Request Variable Request Running
When the query completes the display name still remains unchanged, but we can see that the value of session variable “DISPLAYNAME” that was referenced while the request was executing was in fact “ORACLE”:
Request Variable Request Completed
If, while the request is executing, we look in the Session Manager we can see that the request status is “executing”:
Request Executing in Session Manager
and if we look at the variable values (after a refresh):
Session Variable Values in Session Manager
we can see that the value of session variable “DISPLAYNAME” is unchanged from that set by the initialization block.
So, from the perspective of session variable references the session variable appears to have changed, but the value has not actually been changed, even while the request was executing. And the functionality associated with the session variable has not been changed either – the dashboard display name has not been updated, either temporarily or permanently, by sending the request variable assignment to the BI Server.
Coding Request Variable Assignments Directly
As well as being generated by dashboard prompts, request variable assignments can also be directly coded into the logical SQL, even if no request variable has been defined or none is within scope.
Advanced Tab – Literal Value
For example, if the code:
SET VARIABLE SN_TEST='Direct Assignment';
is entered into the “Prefix” field on the “Advanced” tab in Answers, the logical SQL is set to:
Request Logical SQL
The assigned value will be used to create a temporary session variable whenever the request is sent to the BI Server (note, adding the code to the “Postfix” field does not work in the same way).
Advanced Tab – Presentation Variable Reference
In practice, however, the value of a direct request variable assignment is likely to be more useful if a presentation variable reference is used instead of a literal. For example, let’s use a presentation variable instead of a request variable in the dashboard prompt:
Presentation Variable Definition
If we add code to the “Prefix” field on the “Advanced” tab that contains a reference to the presentation variable:
Advanced Prefix Field with Presentation Variable Reference
then we get a value for “SQL Issued” of:
Request Logical SQL with Presentation Variable Substitution
On entry to the dashboard we get the results of the default query:
Dashboard with Default Request
and when we set the presentation variable value to “Value” we get:
Dashboard with Request Variable Request
Note that even though the “SQL Issued” shows that the default value for the presentation variable has been substituted, when a request is actually issued and sent to the BI Server the contents of the “Prefix” field is re-evaluated using the current value of the presentation variable and the SQL Issued is updated before the logical SQL is sent to the BI Server.
Using a direct request variable assignment and a presentation variable produces a similar result to creating a request variable, except that the scope of the request variable assignment is restricted to a particular request, rather than to all requests that are within the scope of the prompt.
SQL Results
Though not very useful, a request variable assignment can also be incorporated into the “SQL Results” option of the “Show” column in a dashboard prompt (no request variable is defined by the prompt):
Dashboard Prompt with Custom SQL LOV
In this case, the temporary session variable would only be defined for the duration of the request that retrieves the values for the prompt and not for any subsequent requests issued from the dashboard.
In the Set Variable list, you can choose whether or not to populate a variable for the dashboard prompt using a server request variable or a presentation variable. 
