Initialization blocks Execution Precedence in OBIEE 11g

Execution Precedence:

When a repository has multiple initialization blocks, you can set the order (establish the precedence) in which the blocks will be initialized.

First, you open the block that you want to be executed last and then add the initialization blocks that you want to be executed before the block you have open. 

For example, suppose a repository has two initialization blocks, A and B. You open initialization block B, and then specify that block A will execute before block B. This causes block A to execute according to block B's schedule, in addition to its own.

To establish execution precedence:

1. In the Administration Tool, select Manage, then select Variables.
   
2. In the Variable Manager dialog, double-click the last initialization block that you want to be initialized.
   
3. In the [Repository|Session] Variable Initialization Block dialog, click Edit Execution Precedence.
   
4. In the [Repository|Session] Variable Initialization Block Execution Precedence dialog, click Add.
   Add is only available if there are initialization blocks that have not yet been selected.
   
5. In the Browse dialog, select the blocks that should be initialized before the block that you have open, and then click OK.
   
6. To remove a block, in the [Repository|Session] Variable Initialization Block Execution Precedence dialog, select the block you want to remove and click Remove.
   
7. Click OK.
   
8. If you want the initialization block to be required, in the [Repository|Session] Variable Initialization Block dialog, select the Required for authentication option.
   
9. Click OK.

Screen Shot :....

-Asifkhan P.

ID Column (Or Double Column) in OBIEE 11g

With OBIEE11g we have the ability to assign ID columns for descriptive columns more commonly known as Double Columns. This feature has following advantages

1. In BI EE 10g, there was no automated way of filtering on ID’s when end users chose the description values in the prompts. The Double column feature provides this ability in 11g.
2. In many implementations where data is captured in multiple languages, the descriptions might be stored in different languages. But the filtering of data will be on ID’s (which will be the same across languages). Double column feature provides that ability now.
3. Descriptor ID feature is also very useful when you want data to be grouped by code along with name in report without selecting the code in report. 
4. This can be used to avoid summing up of data under single name value where same name belongs to different codes.

-Asifkhan P.

Implicit Fact Column

The implicit fact column is used for queries that only select dimension attributes (no facts) from two or more dimensions. This setting will guide Oralce BI server in case of multiple paths.

A User may request a report where it may have only Dimensions and no Fact columns such as No of Customers in an Year or Product Category and Product Description. When these are requested the server may sometimes gets confused as from which fact table should it join to fetch the Data. So it joins to nearest fact table and pulls the data through it. So the result obtained may be different from what user is expecting.

So, to avoid this sort of confusion  We can create in a Dummy Fact table in a Physical Layer and Join all Dimensions to this Fact table in BMM Layer. Then in the Presentation Catalog we assign dummy fact table column as implicit fact column . So, Whenever a report on Dimensions are requested it joins through dummy fact table and fetch data.

To set an implicit fact column:

•   In presentation layer, double click the presentation catalog/ go to properties of presentation catalog.
•   In the general tab, find the Implicit Fact Column section.
•   Click on Set… button, this will open the browse window to select a fact column from the fact tables available in the presentation catalog.
•   Select the column.
•   Click OK.
•   Ensure that the selected Implicit Fact Column is displayed in the Implicit Fact Column section in the general properties of Presentation Catalog.
•   Click OK.
•   Save your changes in the repository.

Screen Shot ...

-Asifkhan P.

LTS Priority Ordering in OBIEE 11g

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have user queries that can be fulfilled by both a data warehouse and an OLTP source. Often, access to an operational system is "expensive," while access to a data warehouse is "cheap." In this situation, you can assign a higher priority to the data warehouse to ensure that all queries are fulfilled by the data warehouse if possible.

Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.

Priority Group	Designation
0	the highest-priority source (the default)
1	a lowest-priority source

-Asifkhan P.

Content Level of LTS in obiee

To use a source correctly, the Oracle BI Server has to know what each source contains in terms of the business model. Therefore, you need to define aggregation content for each logical table source of a fact table. 

The aggregation content rule defines at what level of granularity the data is stored in this fact table. For each dimension that relates to this fact logical table, define the level of granularity, making sure that every related dimension is defined.

Steps:

1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical table source.
2. In the Logical Table Source dialog, click the Content tab. 
3. If a logical source is an aggregate table and you have defined logical dimensions, select Logical Level from the Aggregation content, group-by list. Then, in the Logical Level list, select the appropriate level for each logical dimension table to which the logical fact table is joined.

-Asifkhan P.

Row-Wise Initialization

Row-Wise Initialization

You can use the row-wise initialization option to create session variables dynamically and set their values when a session begins. The names and values of the session variables reside in an external database that you access through a connection pool. The variables receive their values from the initialization string that you type in the Initialization Block dialog.

For example, suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. The table contains three columns:

• USERID, containing values that represent the unique identifiers of the users
• NAME, containing values that represent session variable names
• VALUE, containing values that represent session variable values

Sample Session Variables Database Table

USERID	NAME	VALUE
JOHN	LEVEL	4
JOHN	STATUS	FULL-TIME
JANE	LEVEL	8
JANE	STATUS	FULL-TIME
JANE	GRADE	AAA

To use row-wise initialization, create an initialization block and select the Row-wise initialization option. For this example, you would provide the following SQL statement for the initialization string:

SELECT NAME, VALUE
FROM RW_SESSION_VARS
WHERE USERID='VALUEOF(NQ_SESSION.USERID)'

Note that NQ_SESSION.USERID has already been initialized using another initialization block.

The following session variables would be created:

• When John connects to the Oracle BI Server, his session contains two session variables from row-wise initialization: LEVEL, containing the value 4, and STATUS, containing the value FULL_TIME.
• When Jane connects to the Oracle BI Server, her session contains three session variables from row-wise initialization: LEVEL, containing the value 8; STATUS, containing the value FULL-TIME; andGRADE, containing the value AAA.

Initializing a Variable with a List of Values

You can also use the row-wise initialization option to initialize a variable with a list of values. You can then use the SQL IN operator to test for values in a specified list.

For example, using the table values in the previous example, you would type the following SQL statement for the initialization string:

SELECT 'LIST_OF_USERS', USERID
FROM RW_SESSION_VARS
WHERE NAME='STATUS' AND VALUE='FULL-TIME'

This SQL statement populates the variable LIST_OF_USERS with a list, separated by colons, of the values JOHN and JANE (for example, JOHN:JANE). You can then use this variable in a filter, as shown in the following WHERE clause:

WHERE TABLE.USER_NAME = valueof(NQ_SESSION.LIST_OF_USERS)

The variable LIST_OF_USERS contains a list of values, that is, one or more values. This logical WHERE clause expands into a physical IN clause, as shown in the following statement:

WHERE TABLE.USER_NAME IN ('JOHN', 'JANE')

Note : This information is from Oracle documentation, Thanks to Oracle!!

-Asifkhan P.

Difference between Alias Table vs Duplicate Table

Today my colleague asked me "what is difference between Alias Table and Duplicate Table?"

Here is the answer.....

Alias Table	Duplicate Table
Its a mirror image of the Parent Table	Its a Copy of the Parent Table
We can't edit or modify Alias Table	We can edit/modify/delete columns in Duplicate table
Changes made in Parent Table is automatically reflected in Alias table	Changes made in Parent table are not reflected in Duplicate table
Used : When there is a need to avoid self-join or circular join	Used when there is a need to use few columns of Parent table.

Its always good to use an Alias table, as it is more convenient in maintaining the RPD .


-Asifkhan P.

OBIEE 11g : RPD Consistency Check

Consistency Check is a feature of the Administration Tool that checks whether a repository has met certain requirements, such as the following:

•  All logical columns are mapped directly or indirectly to one or more physical columns.
•  All logical dimension tables have a logical key.
•  All logical tables have a logical join relationship to another logical table.
•  There are at least two logical tables in the business model: a logical fact table and a logical dimension table. Both can map to the same physical table.
•  There are no circular logical join relationships.
•  A subject area exists for the business model.

You can check consistency for the entire repository or for a single object:

For entire repository: select File > Check Global Consistency.
For an individual repository object: select the object and then select Tools > Show Consistency Checker, or right-click the object and select Check Consistency. You can select multiple objects and check their consistency.

You can save a repository in offline mode even though the business models may be inconsistent.

Using the validaterpd Utility to Check Repository Consistency :

You can use the Oracle BI Server utility validaterpd to check the validity of all metadata objects in a repository on any platform supported by the Oracle BI Server. Running this utility performs the same validation checks as the Consistency Check Manager in the Administration Tool. Please note, don't forget to run bi-init.cmd / bi-init-sh before running this utility.

UtilityLocation: ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup

How to Run: validaterpd -R repository_name -O output_file_name [-P repository_password]

Here repository_password is optional, generally not recommended. Check output_file_name file for messages.

--Asif Pathan

Sort Column based on other column

Today one of my Colleague asked me, how to sort month column in generic way i.e Jan --> Dec. This we can achieve using "Sort Order Column" option in BMM layer. It is a common requirement to display one field while sort by another. 

For example Month Names comes Jan > Feb > Mar, etc should always be sorted by Month Number and not the Month Name. Then you can do this....

Open RPD > Navigate to the "Month Name" in logical layer > open column properties > click on General Tab > Specify "Month Number" in 'Sort Order Column'

Below screen will show you the steps......

.

Repository Variables

A repository variable has a single value at any point in time. These variables also called as Oracle BI Server variables. Repository variable values will be updated whenever server is restarted or started and based on schedule. Syntax VALUEOF(Variablename)

Repository variables are 2 types.

Static Repository Variables:

1. If you want to use a constant value in ‘n’ no. of places then we will go for static

Dynamic Repository Variables:
1. These variables will be associated with initialization block.

Scenario: Handling partitions & fragmentation content condition dynamically.

i. Static.

ii. Dynamic.

Eg: Database name, username of connection pools

2. A SQL query is called as initialization block.

3. These initialization blocks will be executed with oracle BI server refresh and

based on the schedule of initialization block.

4. Schedule option is available only for dynamic repository variable initialization

blocks.

Authentication: External table authentication:

You can maintain lists of users and their passwords in an external database table and use this table for authentication purposes. The external database table contains user names and passwords, and could contain other information, including group membership and display names used for Oracle BI Presentation Services users. 

External table authentication uses session variables that you define using the Variable Manager in the Administration Tool.

Session variables get their values when a user begins a session by logging on. Certain session variables, called system variables, have special uses. The variable USER is a system variable that is used with external table authentication.

To set up external table authentication:

1. Import information about the external table into the Physical layer.
2. Select Manage, then Variables in the Administration Tool to open the Variable Manager.
3. Select Initialization Blocks in the left pane.
4. Right-click in the right pane and select New Initialization Block.
5. In the Initialization Block dialog box, enter a name for the initialization block.
6. Select Database from the Data Source Connection list.
7. Click Browse to search for the name of the connection pool this block uses.
8. In the Initialization String area, enter the SQL statement that is issued at authentication time.
   The values returned by the database in the columns in the SQL statement is assigned to variables. The order of the variables and the order of the columns determines which columns are assigned to which variables. Consider the SQL in the following example:
   

   SELECT username, grp_name, SalesRep, 2 FROM securitylogons WHERE username = ':USER' and pwd = ':PASSWORD'
   

   This SQL contains two constraints in the WHERE clause:
   
   • :USER (note the colon) equals the name the user entered when logging on.
   • :PASSWORD (note the colon) equals the password the user entered.
   The query returns data only if the user name and password match values found in the specified table.
   You should test the SQL statement outside of the Oracle BI Server, substituting valid values for :USER and :PASSWORD to verify that a row of data returns.
9. If this query returns data, then the user is authenticated and session variables are populated. Because this query returns four columns, four session variables are populated. Create these variables (USER, GROUP, DISPLAYNAME, and LOGLEVEL) by clicking New in the Variables tab.
   If a variable is not in the desired order, click the variable you want to reorder and use the Up and Down buttons to move it.
10. Click OK to save the initialization block.

Test it by login in the analytics.

Order of Authentication:

First request goes to LDAP for authentication. If it failes then then request will go for External tables.

Hierarchies

In the Business Model and Mapping layer, a dimension object represents a hierarchical organization of logical columns (attributes). One or more logical dimension tables can be associated with at most one dimension object.

Hierarchies are 3 three types.
1. Level based hierarchies.
2. Parent child hierarchies (11g new features)
3. Unbalanced hierarchies (11g new features)

1. Level based hierarchies:

One to many relationship among columns is called as level based hierarchies. Level-based hierarchies are those in which members are of several types, and members of the same type occur only at a single level.

Each business model can have one or more dimensions, each dimension can have one or more logical levels, and each logical level has one or more attributes (columns) associated with it.

Eg:
1. Time hierarchies
Year --> quarter --> month --> day.
2. Customer hierarchies
Region --> district --> sales rep --> customer

A dimension contains two or more logical levels. The recommended sequence for creating logical levels is to create a Grand Total level and then create child levels, working down to the lowest level. The following are the parts of a dimension:

• Grand Total level. A special level representing the grand total for a dimension. Each dimension can have just one Grand Total level. A Grand Total level does not contain dimensional attributes and does not have a level key. However, you can associate measures with a Grand Total level. The aggregation level for those measures will always be the grand total for the dimension.
• Level. All levels, except the Grand Total level, need to have at least one column. However, it is not necessary to explicitly associate all of the columns from a table with logical levels. Any column that you do not associate with a logical level is automatically associated with the lowest level in the dimension that corresponds to that dimension table. All logical columns in the same dimension table have to be associated with the same dimension.
• Hierarchy. Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level and a common root (all) level.
  For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with a common leaf level of Day. Day has two named parent levels called Fiscal Year and Calendar Year, which are both children of the All root level.
  
• Level keys. Each logical level (except the topmost level defined as a Grand Total level) must have one or more attributes that compose a level key. The level key defines the unique elements in each logical level. The dimension table logical key has to be associated with the lowest level of a dimension and has to be the level key for that level.
  
• Time dimensions and chronological keys. You can identify a dimension as a time dimension. At least one level of a time dimension must have a chronological key. The following is a list of some guidelines you should use when setting up and using time dimensions:
• At least one level of a time dimension must have a chronological key. 
• All time series measures using the AGO, TODATE, and PERIODROLLING functions must be on time levels. AGO, TODATE, and PERIODROLLING aggregates are created as derived logical columns. 
• AGO, TODATE, and PERIODROLLING functionality is not supported either on fragmented dimensional logical table sources, or on fact sources fragmented on the same time dimension. Fact sources may be fragmented on other dimensions.

2. Unbalanced (or ragged) hierarchy:

An unbalanced (or ragged) hierarchy is a hierarchy where the leaves (members with no children) do not necessarily have the same depth. For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.

3. Skip-level hierarchy:

A skip-level hierarchy is a hierarchy where there are members that do not have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city 'Washington, D.C.' does not belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington, D.C.) and below.

Hierarchies are useful to

1. Drill down.

2. Drill up.

3. Drill across (or) drill by.

4. Level based measures.

5. Aggregate navigation.

6. Time series measures.

1. Drill down:

1. Navigating from high level to low level is called as drilldown.

2. Above testing process we can consider as drill down.(year --> quarter --> month --> day)

2. Drill up:

1. Navigating from low level to high level is called as drill up(Day --> Month --> Quarter --> Year)

Process:

1. Open RPD in online mode --> double click on day level --> preferred drill path --> click on add --> select month level --> click on ok.

2. Check in changes & reload server metadata.

3. Develop a report with yymmdd , dollars.

4. Click on yymmdd --> column properties --> interaction --> under value --> primary interaction as drill --> click on ok.

5. In results click on any one no. and observe month data.

3. Drill across:

1. Navigating from one hierarchy to another hierarchy is called as drill across.

Eg: Day --> Region (drill across)

Process:

1. Go to Rpd --> double click on day level remove month column --> click on add --> select region --> click on select --> check in.

2. Reload server metadata.

3. Develop a report with yymmdd , dollars.

4. Click on yymmdd --> column properties --> interaction  --> primary interaction as drill --> click on ok --> click on results.

5. Click on any value & observe the output.

4. Making two or more columns as target:

1. Go to Rpd --> double click on day level --> keys -->select primary key --> edit -->

select two columns(Day and YYYYMMDD) --> enable use for display --> click on ok again ok --> check in --> reload server metadata --> test your work.

Multi User Development Environment (MUDE)

The Multiuser Development feature enables over one hundred development users to work in parallel on a shared, enterprise repository. 

Setup of MUDE

We have 3 steps
1. Creating projects.
2. Copy RPD into shared path & rename RPD.
3. Define multi user directions.

Step 1: Creating Projects

1. A piece of RPD is called as project.
2. RPD will be divided into projects based on Business Model or subject area.
3. On one project two or more members can work.
Process:
Open RPD in offline mode --> Go to manage menu -->  Click on projects -->  Click on
action menu --> Click on new project -->  name it as sales -->  Expand Business Models
-->  expand subject area supplier sales -->  select sales fact --> Click on add -->  expand
presentation -->  select supplier sales -->  Click on add -->  Click on ok

Step 2: Creating a shared folder and moving RPD into shared folder

1. Create a shared folder in C:\Documents and Settings\All Users\Documents with
the name of SHARED_RPD
2. Copy above RPD from repositories folder to C:\Documents and Settings\All
Users\Documents\ SHARED_RPD
3. Rename RPD in shared_rpd folder as shared.rpd

Step 3: Define Multi user directory

Go to administrator tool -->  tools menu -->  Click on options -->  Under "Multiuser development directory" --> Click on browse and
point it to C:\Documents and Settings\All Users\Documents\ SHARED_RPD -->  provide
some name as RRITEC(we can provide any name ) -->  Click on ok

Using MUDE Environment :

In using we have below steps.
1) Check out.
2) Change metadata.
3) Publish.
4) Move RPD from shared folder to repository folder.
5) Test.

Step 1: Check out

1. Open administration tool -->  go to file menu --> go to multi user --> Click on
check out --> repository password Click on ok --> name it as
USER1RPD --> save
2. open another instance of administration tool --> go to multi user --> Click on
check out --> password: --> name it as USER2RPD -->save.

Step 2: Change Metadata

1. In USER1RPD --> Right click on sales facts fact table --> click on new object -->
Click on logical column --> name it as user1_column --> click on column source
--> select Derived from existing columns using an expression -->type 1 -->
Click on ok
2. Drag and drop into presentation layer sales facts --> save.
3. In USER2RPD -->Right click on sales facts fact table --> click on new object -->
Click on logical column --> name it as user2_column --> click on column source
--> select Derived from existing columns using an expression --> type 2 -->
Click on ok
4. Drag and drop into presentation layer sales facts --> save.

Step 3: Publish

1. In USER1RPD -->Go to file menu --> Click on multi user -->Click on publish to
network
2. In USER2RPD --> Go to file menu --> Click on multi user --> Click on publish to
network

Step 4: Move RPD from shared folder to repository folder

1. Copy RPD from shared folder C:\Documents and Settings\All Users\Documents\SHARED_RPD to repository folder C:\OBI11g.6\instances\instance2\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

-Asifkkhan P.

Session Variables

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. For information about using session variables when setting up security, see "Managing Session Variables" in Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition.

This section contains the following topics:

• 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.

System Session Variables

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.