System Session Variables in OBIEE 11g

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.
PROXY	Holds the name of the proxy user. A proxy user is a user that has been authorized to act for another user.
GROUP	Contains the groups to which the user belongs.
WEBGROUPS	Specifies the Catalog groups (Presentation Services groups) to which the user belongs, if any.
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.
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 no alterable 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
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.

MDS XML in OBIEE 11g

MDS XML is an XML format that is compatible with Oracle Metadata Services, and is a supported format for the Oracle BI repository. Oracle Business Intelligence provides the ability to save Oracle BI repository metadata as a set of XML documents in MDS XML format. Using this feature, you can integrate with third-party source control management (SCM) systems for Oracle BI repository development. The Oracle BI repository was saved as a single file called ‘rpd’. With the introduction of MDS XML, single rpd can be saved as individual xml files. 

1)  Open the repository. Navigate to File àSave As à MDS XML Documents 

      2) if we navigate to the saved location, we could see the individual xml elements corresponding to different objects in the obiee repository.

Enable / Disable Cache in OBIEE 11g

When we run the report in OBIEE for first time, it will get cached if the caching is enabled. Then when we run the report for next time, the report will be coming from the cache and physical query for the report won’t be generated. If any change happened to data in underlying database after the first report run, the changes won’t get reflected in the report as the report is coming from cache.

We could enable / disable the cache at an application level, table level or at the report level.

Application Level

To change the cache setting at application level, follow the below mentioned steps.

1.  Navigate to enterprise manager
2. Navigate to Business Intelligence --> Core Application
3. Navigate to Capacity Management --> Performance
4. Click on Lock & Edit Configuration and change the cache enabled flag.
5.  Restart the OBIEE Services

 

Table Level

We could cache settings at table level using OBIEE Admin Tool

1.  Navigate to the Physical layer of RPD and select the table for which the cache settings need to be changed.
2. Make changes in the RPD and deploy it.
3. Once the new RPD is deployed, restart the OBIEE Services
   

Report Level

To disable cache at the report level, follow the mentioned steps

1.  Navigate to the Advanced tab in Analysis
2. To disable Oracle BI Presentation Services Cache, select option shown below.
3. To disable cache at the BI Server Level, type in below mentioned command on the Prefix section
   SET VARIABLE DISABLE_CACHE_HIT = 1;
4. Click on Apply (Not Apply SQL. If you click on apply SQL, it will throw error)

Enable Evaluate in OBIEE 11g

To use the Oracle EVALUATE function in OBIEE, we need to set a parameter in the NQSConfig.INI file in OBIEE 11g.

Steps

1) Take the backup of NQSConfig.INI file located at OBIEE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn

2) Edit the original NQSConfig.INI file

This is what you will see by default in the NQSConfig.INI file
---------------------------------------------------------------

1. EVALUATE_SUPPORT_LEVEL:
2. 1: evaluate is supported for users with manageRepositories permission
3. 2: evaluate is supported for any user.
4. other: evaluate is not supported if the value is anything else.
   EVALUATE_SUPPORT_LEVEL = 0;
   ---------------------------------------------------------------

3) Change the parameter as follows: EVALUATE_SUPPORT_LEVEL = 2;

4) Save and restart the BI Server (From Enterprise Manager)

Difference between adjacent values in Same Column in OBIEE

We have Customer Name Column and create date column which stores the date of customer activities. The requirement is to calculate the time between 2 adjacent activities created by the customer.

The operations need to be performed for achieving the above mentioned business problem is summarized below.

1. Group the created date by Customer
2. Oder the data in Ascending order
3. Calculate the difference between 2 adjacent dates to find the time between 2 activities created by a customer.

Solution

The above mentioned requirement can be achieved by using calling Analytic function (LEAD, LAG) using EVALUATE. The challenge here is to access multiple rows in a table without a self-join to the table. LEAD and LAG functions helps us aces multiple rows in a table.

The Syntax for LEAD and LAG are same and is mentioned below

    LAG (value_expression [, offset] [, default]) OVER ([query_partition_clause] order_by_clause)

    LEAD (value_expression [, offset] [, default]) OVER ([query_partition_clause] order_by_clause)

    value_expression - Can be a column or a built-in function, except for other analytic functions.

    offset - The number of rows preceding/following the current row, from which the data is to be retrieved. The default value is 1.

Now let’s see, how we can call these functions in OBIEE to achieve our requirement.

Customer Name - "Customer"."Customer Full Name"

Create Date - "Service Request"."Create Date"

To get the preceding date, use LEAD function and column formula is mentioned below

EVALUATE('LEAD(%1,1) OVER (PARTITION BY %2 ORDER BY %1)',"Service Request"."Create Date","Customer"."Customer Full Name")

Similarly use the LAG function to get the following date

EVALUATE('LAG(%1,1) OVER (PARTITION BY %2 ORDER BY %1)',"Service Request"."Create Date","Customer"."Customer Full Name")

Note

1. The columns Customer Name and Create Date should be present in the Analysis for the Evaluate to work. If any of the columns is removed, it will throw error. This is a bug that Oracle is currently working on.
2.  EVALUATE functionality should be enabled in OBIEE before using it.

Custom Format Strings for Conversion into Hours, Minutes & Seconds

Follow the below mentioned steps for custom formatting.

1. Select Column Properties
2. Then in  the Data Format Tab, select Treat Numbers as Custom. Then write the data Conversion Format String.

Note: For the below mentioned format to work correctly during export to excel, you should be in latest patch 11.1.1.7.150120 ( Patch 20124371 )

 

Data Conversion Format String	Result
[duration(sec)][opt:dd]:hh:mm:ss	Formats the total of seconds as duration. For example, a duration value of 16500.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed. Second is the default unit of time.
[duration(min)][opt:dd]:hh:mm:ss	Formats the total of minutes as duration. For example, a duration value of 275.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed.
[duration(hour)][opt:dd]:hh:mm:ss	Formats the total of hours as duration. For example, a duration value of 4.58 is displayed as 04:35:00, meaning a duration of four hours, 34 minutes, and 48 seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed.
[duration(day)][opt:dd]:hh:mm:ss	Formats the total of days as duration. For example, a duration value of 2.13 is displayed as 02:03:07:12, meaning a duration of two days, three hours, seven minutes, and twelve seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed.

Change Dashboard Style in OBIEE 11g

OBIEE enables us to change the style of dashboards. There are 2 styles available in OBIEE

1.    blafp 
2.    FusionFX 

Sample images of the styles are shown below

blafp 

FusionFX

Steps to Change the Dashboard Style

Navigate to the dashboard for which you want to change the style

Click on Edit Dashboard --> Dashboard Properties

 

 

Select the style you want to apply to dashboard and then click OK. Save the dashboard.