Server Configuration and Tuning

Some important considerations for improving query performance with the Oracle BI Server.

NQSConfig.INI File Parameters

The NQSConfig.INI file contains configuration and tuning parameters for the Oracle BI Server. There are parameters to configure disk space for temporary storage, set sort memory buffer sizes, set cache memory buffers, set virtual table page sizes, and a number of other configuration settings that allow you to take full advantage of your hardware’s capabilities.

Aggregate Tables

You should use aggregate tables to improve query performance. Aggregate tables contains precalculated summarizations of data. It is much faster to retrieve an answer from an aggregate table than to recompute the answer from thousands of rows of detail. The Oracle BI Server uses aggregate tables automatically, if they have been properly specified in the repository.

Query Caching

Enabling query caching causes the Oracle BI Server to store query results for reuse by subsequent queries. Caching can dramatically improve the apparent performance of the system for users. Read More on Cache >>

Tune and Index Underlying Databases

The Oracle BI Server sends queries to databases. For the queries to return in a timely manner, the underlying databases need to be configured, tuned, and indexed correctly. If there are queries that return slowly from the underlying databases, you can capture the SQL of the queries in the query log, then provide them to the DBA for analysis.

Purging Cache

Purging cache is the process of deleting entries from the query cache. You can purge cache entries
in the following ways:

1. Manually, using the Administration Tool Cache Manager facility (in online mode).
2. Automatically, by setting the Cache Persistence Time field in the Physical Table dialog box for a particular       table.
3. Automatically, by setting up an Oracle BI Server Event Polling Table.
4. Automatically, as the cache storage space fills up.
5. Using the ODBC functions.

How Cache System handles Making Changes to a Repository

When you modify an Oracle BI repository in online mode, any changes you make that will affect cache entries automatically result in a purge of all cache entries that reference the changed objects. The purge occurs when you check in the changes. For example, if you delete a physical table from a repository, all cache entries that reference that table are purged upon check in. Any changes made to a business model in the Business Model and Mapping layer will purge all cache entries for that 

business model.

Online Mode

When you modify an Oracle BI repository in online mode, any changes you make that will affect cache entries automatically result in a purge of all cache entries that reference the changed objects. The purge occurs when you check in the changes.

For example,

1. If you delete a physical table from a repository, all cache entries that reference that table are purged upon check in.

2. Any changes made to a business model in the Business Model and Mapping layer will purge all cache entries for that business model.

Note : In Business Model, if you made any changes like deleting any logical column, renaming a logical column etc. will result in purge of all cache entries for that business model automatically.

Offline Mode

When you modify an Oracle BI repository in offline mode, you might make changes that affect queries stored in the cache and render those cached results obsolete. Because the repository is not loaded by the server during offline mode edits, the server has no way of determining if the changes made affect any cached entries. The server therefore does not automatically purge the cache after offline changes. If you do not purge the cache, there might be invalid entries when the repository is next loaded. Unless you are sure that there are no entries in the cache that are affected by your offline changes, you should purge the cache for any business model you have modified.

Switching Between Repositories

If you intend to remove a repository from the configuration of the Oracle BI Server, make sure to purge the cache of all cache entries that reference the repository. Failure to do so will result in a corrupted cache.

Best Practices for Cache in OBIEE

Disk Space

The query cache requires dedicated disk space. How much space depends on the query volume, the size of the query result sets, and how much disk space you choose to allocate to the cache. For performance purposes, a disk should be used exclusively for caching, and it should be a high performance, high reliability type of disk system.

Administrative Tasks

There are a few administrative tasks associated with caching. You need to set the cache persistence time for each physical table appropriately, knowing how often data in that table is updated. When the frequency of the update varies, you need to keep track of when changes occur and purge the cache manually when necessary. You can also create a cache event polling table and modify applications to update the polling table when changes to the databases occur, making the system event-driven.

The Oracle BI Server also provides ODBC-extension functions for purging cache entries

programmatically. You can write your own scripts to call these functions at the appropriate times.

The ODBC functions are :

SAPurgeCacheByQuery()

SAPurgeCacheByTable()

SAPurgeCacheByDatabase()

SAPurgeAllCache()

Keeping the Cache Up To Date

If the cache entries are not purged when the data in the underlying databases changes, queries

can potentially return results that are out of date. You need to evaluate whether this is acceptable. It might be acceptable to allow the cache to contain some stale data. You need to decide what level of stale data is acceptable and then set up (and follow) a set of rules to reflect those levels.

For example, suppose your application analyzes corporate data from a large conglomerate, and you are performing yearly summaries of the different divisions in the company. New data is not going to materially affect your queries because the new data will only affect next year’s summaries. In this case, the tradeoffs for deciding whether to purge the cache might favor leaving the entries in the cache.

Suppose, however, that your databases are updated three times a day and you are performing

queries on the current day’s activities. In this case, you will need to purge the cache much more

often, or perhaps consider not using it at all.

Whatever your situation, you need to evaluate what is acceptable as far as having noncurrent

information returned to the users.

Caches in OBIEE

Main Caches in OBIEE : 

• Presentation Server Cache
• Query Cache (BI Server Cache)

Other Caches in OBIEE : 

• QUERY_PLAN
• DRILLDOWN_INFO
• DRILLDOWN_QUERY
• session variable via a INIT_BLOCK

Session variable
If you use the row-wise initialization, the result of the query can be cached in the main memory

cache of OBIEE server.

.

Query Cache Architecture

The query cache consists of cache storage space, cache metadata, and cache detection in query

compilation.

The process of accessing the cache metadata is very fast. If the metadata shows a cache hit, the

bulk of the query processing is eliminated, and the results are immediately returned to the user. The process of adding the new results to the cache is independent of the results being returned to the user; the only effect on the running query is the resources consumed in the process of writing the cached results.

When a user request a query, the Presentation Server translate the query into Logical Query and send it to the BI Server. The BI Server searches the Cache Metadata for a matched Logical Query. If the matched Logical query is found (called as Cache Hit), then the results are retrieved from Query Cache by BI Server and sent it to Presentation Server. If the matched query is not found, then the BI server will generate the Physical Query and send it to the underlying Database. The Database processes the physical query and return the result to the BI Server, the Bi Server will send back the result set to Presentation Server and at the same time store the result set in the Query Cache.

Query Cache in OBIEE

The Oracle BI Server can save the results of a query in cache files and then reuse those results later when a similar query is requested. So, for any query request, the database processing is required only once, not every time the query is run.

When a user requests any query, BI Presentation Server generates Logical Query and handover it to the BI server.[Before handover the Logical SQL to BI Server, the Presentation Server checks the Presentation Server cache for similar Logical SQL. If found, the Presentation Server will get the result from that cache and return to the user without contacting the BI Server. Read more...] Then, the BI Server checks the disk-based cache of query result sets (query  cache). If the similar query and its result set are stored in query cache, then BI Server will retrieve the data from the query cache and return it to the Presentation Server. So, the use of cache saves database processing, processing time, and the query response time will be fast.

Advantages of Cache in OBIEE :

Dramatic improvement of query performance : BI Server stores the query results in local cache. The similar subsequent queries can use those results. So, the BI Server does not have to access the backend database. This improves the query performance.

Less network traffic and Reduction in database processing: If the result of a query is stored in a cache, then the result for a similar subsequent queries do not have to come over the network to the BI Server. Not running the query on the database frees the database server to do other work. If the database uses a charge back system, it could save money in the budget as well.

Reduction in Oracle BI Server processing overhead : Another benefit of using the cache to answer a query is savings in processing time on the Oracle BI Server, especially if the query results are retrieved from multiple databases. Depending on the query, there might be considerable join and sort processing in the server. If the query is already calculated, this processing is avoided, freeing server resources for other tasks.

Tip : If there are more than one source tables(those tables may be resides in different sources) for a query, then BI Server will generate separate physical queries and send them to the corresponding databases and get the results. When the BI Server gets the result set, it has to do the appropriate joins and handover it Presentation Server. 

Presentation Server Cache in OBIEE

Presentation Server  has its own cache, stores the result set of queries in that cache. When the user requests any query, and if the query is similar to the one which is stored in cache memory, then the Presentation Server will retrieve the result for this query  from the cache and return the result to the user without contacting the BI Server. This improves the query response time, eliminates BI server processing time.

The files for the Presentation Services cache:

• Have names such as nQS_xxxx_x_xxxxxx.TMP.
• Are stored in the following directory:
       C:\OracleBIData\tmp

Refer the following link to know more about the presentation cache : 

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

Restrict the Number of Rows Per Page in Table View

By default, 25 rows will be displayed per page in a table view.

If you want to display more or less than 25 rows per page, you need to edit the table view properties.
Follow the below steps :
1. Click on Edit view of table view.

2. Click on Table Properties, then enter the value for Rows Per Page option in Edit view window.

3. Click on OK and check the result.

To see all the rows, click on "arrow star button", then all the rows will be displayed at a time.

Restict Number of Row for Table View in OBIEE

OBIEE Presentation server provides an ability to restrict the number of rows to be displayed in table view.

The Administrator can set the number of rows to be displayed in table view by configuring the InstanceConfig.xml file.  

Assume that, a table called Emp_Olem consists of 1000 rows, by default all the rows will be display in table view. Administrator can restrict to 10 (or any number he can specify ) rows. 

Below are the steps to achieve this :

1. Open instanceconfig.xml file.

2. Locate <ResultRowLimit >  tag and define the number of rows to be displayed.

         <ResultRowLimit >10</ResultRowLimit >

     If the above tag is not there in your config file, then add it.

        <ServerInstance>

             <ResultRowLimit >10</ResultRowLimit >

        <ServerInstance>

     

 3. Save the file and restart the Presentation Server.

 4. Now, check the result.

Drag some logical columns into Answers then click on Results.

Only ten rows are displaying even though the table having 1000 rows. But in Pivote Table, all the rows will be display. This applies only to table view.

Note : BI server retries all the rows, ie 1000 rows from the database but display only ten 10 rows in table view.

We can also configure number of rows to be display per page. To read about this click here.

Level Based Measure in OBIEE

A level-based measure is a column whose values are always calculated to a specific level of  aggregation. For example, a company might want to measure its revenue based on the country, based on the region, and based on the city. You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue.

To set up these calculations, you need to build a dimensional hierarchy in your repository that contains the levels Grandtotal, Country, Region, and City. This hierarchy will contain the metadata that defines a one-to-many relationship between Country and Region and a one-to-many relationship between Region and City. For each country, there are many regions but each region is in only one country. Similarly, for each region, there are many cities but each city is in only one region.

Next, you need to create the logical column CountryRevenue in fact table  in BMM layer.
Right click on the fact table -> New Object -> Logical Column.

Then, Give a name to the measure, then under Aggregation tab, select the appropriate aggregation rule(SUM is the Aggregation for the measure CountryRevenue, in my case), then under the Level tab, select the appropriate logical level (country is the logical level for the measure CountryRevenue, in my case). Then say OK.

Repeat the same process for the other tow measures (RegionRevenue, and CityRevenue).

Now map the these logical measures to the physical source (Revenue is the physical source for these measures in my case.) To map the column CountryRevenue to the physical source column Revenue, follow the below steps :

Double click on the measure, called CountryRevenue, goto Data Type.You will find the logical Table source, double click on it.

Uncheck the option Show Mapped Columns, then all the unmapped columns will be displayed. In our case CountryRevenue,RegionRevenue, and CityRevenue are the unmapped columns. So, you should be able see all these columns there.

Now click on the dotted button (highligted in above picture) beside the measure CountryRevenue to map to physical source column. Then select the appropriate phisical source column from the Expression Builder and say OK.

Repeat the same procedure for other two columns.

Final step is, make sure that all the three measures should be listed under the corresponding logical Level in the dimension. In my case, the CoutryRevenue must listed under Country logical level, RegionRevenue must listed under Region logical level, CityRevenue must listed under City logical level in the dimension Product.

These measures are specific to the defined logical level. So CountryRevenue measure will always gives us the Revenue to the Country level, even if you add the Region dimension column in between Country and CountryRevenue  columns. RegionRevenue measure will always gives us the Revenue to the Country level, even if you add the City dimension column in between Region and RegionRevenue  columns.CityRevenue measure will always gives us the Revenue to the City level.

Thank you,

Disable the CAche Hit to Query Cache for a particular Request.

We can disable the Cach Hit for a particular request, by overriding/Change the value of System session variable called DISABLE_CACHE_HIT  to 1 in Answers.

Bypass Oracle BI Presentation Services Cache

We can By pass the presentation cach, by selecting the check box option for Bypass Oracle BI Presentation Services Cache in Advanced tab of any Answers.

Manipulating Session Variables from Answers

As my knowledge, session variables can be manipulated in Answers or dashboard of Presentation services in two possible ways.

• Request Variable
• NQSSetSessionValue 

 Request Variable

A request variable is a variable that is used to temporarily override the value of a session variable. The request variables are defined in the Presentation Services using either :

• Dashboard prompts or
• SET VARIABLE statement.

A variable whose name should not match that of a repository variable or a security system session variable(USER, GROUP etc..).

Using request variables we can change the value of session variables for any request or for all requests in any dashboard. But we can not change the value permanently through out the session. If we want to change the value of session variable for the entire session, then we can use the ODBC function NQSSetSessionValue. To know how to use request variable in presentation services, Click Here >>.

NQSSetSessionValue 

NQSSetSessionValue is an ODBC function, can be used to change the value of session variable for the whole session.

The syntax for this function is : 

                                               call NQSSetSessionValue('DataType VariableName=Value;')

We can call this function from from Issue SQL Directly section of Presentaion Services. To know how to use request variable in presentation services, Cick Here >>.

Error : [nQSError: 13015] You do not have the permission to set the value of the variable

View Display Error

Error getting drill information: SET VARIABLE LOB='APD' SELECT item.item saw_0, valueof(nq_session.LOB) saw_1 FROM OLEM   Error Details Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P Odbc driver returned an error (SQLExecDirectW). State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 13015] You do not have the permission to set the value of the variable 'LOB'. (HY000) SQL Issued: {call NQSGetLevelDrillability('SET VARIABLE LOB=''APD'' SELECT item.item saw_0, valueof(nq_session.LOB) saw_1 FROM OLEM')}

 This is the common error, the developers get, when he try to change the value of session variable using request variable in request prefix field. ( refer how to change the value of session variable using Request Variable ).

Solution :
 Go to properties of session variable all check the option "Enable everyone to set value"

Follow the below steps :

1. Open Administration Tool. Go to Manager > Variables.

2. Select System or Non-System variable (depends on type of your variable) under the Session  from left pane. Then the corresponding variables will be listed on right pane.

3. Right click on the variable(for which your getting error in presentation service when you tried to change its value) and select properties.

4. check the option "Enable everyone to set value" (by default this option is unchecked) and click OK.

Now, check the request in presentation service. Log out from that session and again login to presentation services and the same report. If you get the same error again, please let me know by your comments.

NQSSetSessionValue - ODBC function

NQSSetSessionValue() is an ODBC function, can be used to change the value of session variable for the entire    session (ie. not only for a particular request or a dashboard).

The syntax for this function is : 

                                               call NQSSetSessionValue('DataType VariableName=Value;')

We can call this function from from Issue SQL Directly section of Presentaion Services.

Example :

Assume that, a user logon to the system, he has the value 2 for the session variable LOGLEVEL. If the user decides to increase the value for LOGLEVEL for the entire session. Then he can use the ODBC function to change the value of  LOGLEVEL.

Request Varaiable in OBIEE

A request variable is a variable that is used to temporarily override the value of a session variable. The request variables can be defined in two places of Presentation Services, as my knowledge.

• Dashboard prompts or
• SET VARIABLE statement.

A variable whose name should not match that of a repository variable or a security system session variable(USER, GROUP etc..).

  

Request Variable in a Request : 

 We can define Request Variables in any request to override the session variable, using the SET VARIABLE code. When we define a request variable for any request, then the scope and availability of that variable is within the same request. Outside of this request, the session variable will be having the actual value, not the overridden value.

 Example :  Assume that there is a session variable called LOB  and someone logged into the presentation services, for this session the value of LOB is 'APD'. Then he can change this value for any particular request using the following steps :

Before request variable, the session variable LOB has the value 'APD'.

To change the value of LOB to the value 'Liability', Go to Advanced tab of Answers.

Go down and find the PREFIX clause, and write the following code in that.

 Then Click on Result tab and check the result. Now you will find the changed value Liability.

The request variable overrides the value of session variable only for this request, the session variable will have the actual value 'APD' outside of this request.

Request Variable in Dashboard Prompt  

We can use Request variable in Dashboard prompts to change the value of session variable. The scope of this variable would be Page of Dashboard, depends on the selection.  

Example : Lets use the same session variable LOB (which we used for request) for this example. 

The below request have used the session variable LOB and value of this variable for the current session is 'APD'.

In dashboard prompt,  take the required logical column, select Request Variable from Set Variable drop-down and write the request variable name, LOB is in our case. 

Put the same report and dashboard prompt into the dashboard.

Now select any value from prompt and click on Go.

In my case, I have selected the value 'Property'. The request variable, changed the default value of session variable to the selected value( from prompt).

If we have more than one request in the dashboard, the same will be affected for all the requests in that  dashboard. Outside of this dashboard, the value of the session variable will be the actual value only.

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.

To know more about Request Variable, like how it works, what actually it is, Please refer the following link.
http://everythingoracle.com/obieerv.htm

Thank you,

Variables in the Oracle BI Repository

There are two classes of variables:

1. Repository variables (Server Variables): holds the same value across all sessions

2. Session variables: holds a value specific to each user session.

Caution:

Values in repository and session variables are not secure, because object permissions do not apply to variables. Anybody who knows or can guess the name of the variable can use it in an expression in Answers or in a Logical SQL query. Because of this, do not put sensitive data like passwords in session or repository variables.

Repository variables: 

A repository variable has a single value at any point in time, I.e., holds the same value across all sessions. Repository variables can be used instead of literals or constants.

There are two types of repository variables:

1.  Static Repository Variables

2. Dynamic Repository Variables.

Static  Repository Variables: 

Static repository variables holds constant values for all sessions in the server. This value persists, and does not change until an administrator decides to change it.

Assume that, you are using a value 'California' (head office of client) very often in formula of reports or in Expression Builder of BMM layer in repository. Then instead of using direct value, we can assign this constant value to a static variable and use this variable wherever required. That will be more understandable to users than direct values.

Lets create a static repository variable called HEAD_OFFICE.

1. In the Administration Tool, select Manage > Variables.

Select Static from left pane, then right click on the right pane of the Variables and select New Static variable.

In the Variable dialog, type a name(HEAD_OFFICE in our case) for the variable.

Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables. 

To add a Default initializer value, type the value in the Default initializer box, or click the Expression Builder button to use Expression Builder.

If you initialize a variable using a character string, enclose the string in single quotes ( ' ). Static repository variables must have default initializers that are constant values.

Type the value  'California' in the Default initializer box for our case.

3.      Click OK.

Now, to see all the static variables in the server, select Static under Variables in Variable manager, then you can see all the static variables in right pane.

Dynamic  Repository Variables: 

These variables can have values that vary over time . 

When defining a dynamic repository variable, you create an initialization block. You also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable.

When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable are purged automatically.

Dynamic repository variables are useful for defining the content of logical table sources. For example, we need to refer count of employee in some places, and then we can create a dynamic variable called COUNT_OF_EMP and schedule it to every 1 day (because a new employee may join on any day).

To create a repository variable:

Lets create a Dynamic variable called  COUNT_OF_EMP, we have table called Emp. The query to get count of employees is : select count( distinct empkey) from EMP.

1. In the Administration Tool, select Manage, then select Variables.

2. In the Variable Manager dialog, select dynamic from left pane and right click on right pane, then  select New Dynamic Variable.

3. In the Variable dialog, type a name for the variable.

Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.

4.  To create a new initialization block, click New.

5.      In Repository Init Block dialogue, set the initialization block name, schedule the refresh interval(in my case I have given 1 hour as refresh interval. So the BI server will send the query to database and get the data for every one hour). 

Notice that in our case, I have scheduled the variable refresh interval  to one hour. So the value of this variable will be refreshed for every hour by the BI Server.

6.      Next, click on the Edit Data Source, write the SQL Query, select the Connection Pool and click on OK.

7.      Next click on Edit Data Target, and define the Dynamic Variables and click on OK.

8.   Click OK.

Session Variables :
Session variables are similar to dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.

Unlike a repository variable, there are as many instances of a session variable as there are active sessions on the Oracle BI Server. Each instance of a session variable could be initialized to a different value.

Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. If a user is authenticated successfully, session variables can be used to set filters and permissions for that session.

Session Variables can be further classified as :
• System Session Variables
• Nonsystem Session Variables
System Session Variables
System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and nonsystem session variables).

When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL, set the filter to the variable NQ_SESSION.LOGLEVEL.

Variable	Description
USER	Holds the value the user enters as his or her logon name. This variable is typically populated from the LDAP profile of the user.
PROXY	Holds the name of the proxy user. A proxy user is a user that has been authorized to act for another user. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about the PROXY system session variable.
GROUP	Contains the groups to which the user belongs. Exists only for compatibility with previous releases. Legacy groups are mapped to  application roles automatically.
WEBGROUPS	Specifies the Catalog groups (Presentation Services groups) to which the user belongs, if any. Note that the recommended practice is to use application roles rather than Catalog groups.
USERGUID	Contains the global unique identifier (GUID) of the user, typically populated from the LDAP profile of the user.
ROLES	Contains the application roles to which the user belongs.
ROLEGUIDS	Contains the global unique identifiers (GUIDs) for the application roles to which the user belongs. GUIDs for application roles are the same as the application role names.
PERMISSIONS	Contains the permissions held by the user, such as oracle.bi.server.impersonateUser or oracle.bi.server.manageRepository.
DISPLAYNAME	Used for Oracle BI Presentation Services. It contains the name that  is displayed to the user in the greeting in the Oracle BI Presentation  Services user interface. It is also saved as the author field for catalog objects. This variable is typically populated from the LDAP  profile of the user.
PORTALPATH	Used for Oracle BI Presentation Services. It identifies the default dashboard the user sees when logging in (the user can override this  preference after logged on).
LOGLEVEL	The value of LOGLEVEL (a number between 0 and 5) determines the logging level that the Oracle BI Server uses for user queries. This system session variable overrides a variable defined in the Users object in the Administration Tool. If the administrator user (defined upon install) has a Logging level defined as 4 and the session variable LOGLEVEL defined in the repository has a value of 0 (zero), the value of 0 applies.
REQUESTKEY	Used for Oracle BI Presentation Services. Any users with the same  nonblank request key share the same Oracle BI Presentation Services cache entries. This tells Oracle BI Presentation Services that these users have identical content filters and security in the Oracle BI Server. Sharing Oracle BI Presentation Services cache entries is a way to minimize unnecessary communication with the Oracle BI Server.
SKIN	Determines certain elements of the look and feel of the Oracle BI Presentation Services user interface. The user can alter some elements of the user interface by picking a style when logged on to Oracle BI Presentation Services. The SKIN variable points to an Oracle BI Presentation Services folder that contains the nonalterable elements (for example, figures such as GIF files). Such directories begin with sk_. For example, if a folder were called sk_companyx, the SKIN variable would be set to companyx.
DESCRIPTION	Contains a description of the user, typically populated from the LDAP profile of the user.
USERLOCALE	Contains the locale of the user, typically populated from the LDAP profile of the user.
DISABLE_CACHE_HIT	Used to enable or disable Oracle BI Server result cache hits. This variable has a possible value of 0 or 1.
DISABLE_CACHE_SEED	Used to enable or disable Oracle BI Server result cache seeding. This variable has a possible value of 0 or 1.
DISABLE_SUBREQUEST_CACHE	Used to enable or disable Oracle BI Server subrequest cache hits and seeding. This variable has a possible value of 0 or 1.
SELECT_PHYSICAL	Identifies the query as a SELECT_PHYSICAL query. See "Syntax and Usage Notes for SELECT_PHYSICAL" for more information.
DISABLE_PLAN_CACHE_HIT	Used to enable or disable Oracle BI Server plan cache hits. This variable has a possible value of 0 or 1.
DISABLE_PLAN_CACHE_SEED	Used to enable or disable Oracle BI Server plan cache seeding. This variable has a possible value of 0 or 1.
TIMEZONE	Contains the time zone of the user, typically populated from the LDAP profile of the user.

Nonsystem Session Variables

You use the same procedure to define nonsystem session variables as for system session variables.

A common use for nonsystem session variables is setting user filters. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the sales region of the user.

You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region.

When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion, set the filter to the variable NQ_SESSION.SalesRegion.

Creating Session Variables

1. In the Administration Tool, select Manage, then select Variables.
2. In the Variable Manager dialog, select Action > New > Session > Variable.
3. In the Session Variable dialog, type a variable name.
   Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.
4. For session variables, you can select the following options:
• Enable any user to set the value. Select this option to set session variables after the initialization block has populated the value (at user login) by calling the ODBC store procedure NQSSetSessionValue() or by using Request variable. For example, this option lets non-administrators to set this variable for sampling.
5. Use the Initialization Block list to select an initialization block that will be used to refresh the value on a continuing basis.
6. To add a Default Initializer value, type the value in the Default Initializer box, or click the Expression Builder button to use Expression Builder.
7. Click OK.

Syntax for using these variables :
Repository variables can be used in two ways : 
1. VALUEOF(variable name) 
2. biServer.variables['variable name']



Session variables can be used in two ways : 
1. VALUEOF(NQ_SESSION.variable name) 
2. biServer.variables['variable name']