BI / ETL / DWH

Using Hints in OBIEE RPD and Answers

1. Using Hints in OBIEE (Rpd and Answers)

OBIEE supporting hints is kind of limited. The below session brief the usage of hints in obiee. Please note that you may not be able to implement all hints available with OBIEE.

1.1 Physical Object – OBIEE Repository

Hints can be applied in the table/Object in the physical layer. When applied this hint, whenever any request hit the table, this hint will be applied in the physical query generated by OBIEE.

· Step 1

Open the OBIEE rpd and checkout the physical table where you want to apply the hint

· Step 2

Go to the General Tab and apply the Hint as shown below

Here I’m implementing the Full hint

Hint Syntax:

/*+ Full (tab_name) */

Table Name: WC_PURCH_COSTREDUC_FORECAST_F

Note: You can also implement the hints without arguments here like “FACT”

· Step 3

Check in the changes

· Queries

Example:

OBIEE Physical Query before implementing the FULL Hint

select T862336.FSCL_PER_NAME_YEAR as c1,

T862336.FSCL_PER_NAME_QTR as c2,

T862336.FSCL_PER_NAME_MONTH as c3,

sum(T863204.FORECAST_SAVING) as c4,

T862336.FSCL_MONTH as c5,

T862336.FSCL_QTR as c6,

T862336.FSCL_YEAR as c7

from

WC_SCM_INT_ORG_D_V T888692,

W_FSCL_MONTH_D T862336,

WC_PURCH_COSTREDUC_FORECAST_F T863204

where ( T862336.ROW_WID = T863204.MONTH_NO and T862336.FSCL_PER_NAME_YEAR = '2009' and T863204.INVENTORY_ORG_WID = T888692.ROW_WID and T863204.PART_DELETE_FLAG = 'N' and T888692.USER_GROUP = 'OBIEE_CORP_SCM_GLOBAL' )

group by T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR

order by c1, c2, c3

OBIEE Physical Query after implementing the FULL Hint

select /*+ full(T863204) */ T862336.FSCL_PER_NAME_YEAR as c1,

T862336.FSCL_PER_NAME_QTR as c2,

T862336.FSCL_PER_NAME_MONTH as c3,

sum(T863204.FORECAST_SAVING) as c4,

T862336.FSCL_MONTH as c5,

T862336.FSCL_QTR as c6,

T862336.FSCL_YEAR as c7

from

WC_SCM_INT_ORG_D_V T888692,

W_FSCL_MONTH_D T862336,

WC_PURCH_COSTREDUC_FORECAST_F T863204

group by T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR

order by c1, c2, c3

Note:

The T863204 is the OBIEE table Id for WC_PURCH_COSTREDUC_FORECAST_F table. This will be taken care internally. We can directly give the table name in the hint or this OBIEE physical table id (which can be obtained from by querying rpd).

1.2 Physical Join – OBIEE Repository

Hints can be applied in the physical join of tables in the physical layer. When applied this hint, whenever any request which consumes the join, this hint will be applied in the physical query generated by OBIEE.

· Step 1

Open the OBIEE rpd and checkout the physical join where you want to apply the hint.

· Step 2

Apply the hint as shown below

Here I’m implementing the USE_MERGE hint

Hint Syntax:

/*+ USE_MERGE(Table1 Table2) */

Table1 Name: W_FSCL_MONTH_D

Table2 Name: WC_PURCH_COSTREDUC_FORECAST_F

Caution: if you need to implement this hint on a Join where an Alias table is present, Make sure that you pass the OBIEE table ID instead of Table name in the hint.

· Step 3

Check in the changes

· Queries

Example:

OBIEE Physical Query before implementing the MERGE Hint

select T862336.FSCL_PER_NAME_YEAR as c1,

     T862336.FSCL_PER_NAME_QTR as c2,

     T862336.FSCL_PER_NAME_MONTH as c3,

     sum(T863204.FORECAST_SAVING) as c4,

     T862336.FSCL_MONTH as c5,

     T862336.FSCL_QTR as c6,

     T862336.FSCL_YEAR as c7

     from

     WC_SCM_INT_ORG_D_V T888692,

     W_FSCL_MONTH_D T862336,

     WC_PURCH_COSTREDUC_FORECAST_F T863204

where  ( T862336.ROW_WID = T863204.MONTH_NO and T862336.FSCL_PER_NAME_YEAR = '2006' and T863204.INVENTORY_ORG_WID = T888692.ROW_WID and T863204.PART_DELETE_FLAG = 'N' and T888692.USER_GROUP = 'OBIEE_CORP_SCM_GLOBAL' )

group by T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR

order by c1, c2, c3

OBIEE Physical Query after implementing the MERGE Hint

select  /*+ USE_MERGE(W_FSCL_MONTH_D WC_PURCH_COSTREDUC_FORECAST_F) */  T862336.FSCL_PER_NAME_YEAR as c1,

     T862336.FSCL_PER_NAME_QTR as c2,

     T862336.FSCL_PER_NAME_MONTH as c3,

     sum(T863204.FORECAST_SAVING) as c4,

     T862336.FSCL_MONTH as c5,

     T862336.FSCL_QTR as c6,

     T862336.FSCL_YEAR as c7

     from

     WC_SCM_INT_ORG_D_V T888692,

     W_FSCL_MONTH_D T862336,

     WC_PURCH_COSTREDUC_FORECAST_F T863204

where  ( T862336.ROW_WID = T863204.MONTH_NO and T862336.FSCL_PER_NAME_YEAR = '2006' and T863204.INVENTORY_ORG_WID = T888692.ROW_WID and T863204.PART_DELETE_FLAG = 'N' and T888692.USER_GROUP = 'OBIEE_CORP_SCM_GLOBAL' )

group by T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR

order by c1, c2, c3

· Example 2 – Report Hints

Lets try to implement the hint

'/*+ordered use_nl(T266471) parallel(e, 4) */

EVALUATE('/*+ordered use_nl(T266471) parallel(e, 4) */ %1', "Dim - Time"."Fiscal Year")

1.3 OBIEE Reports

· Step 1

Identify the “OBIEE physical table id” (physical layer object) for the object (here table) you want to implement the hint.

To get the Physical table id

1. Go to OBIEE Rpd à toolà query repository and query the Physical layer object as shown below. Here we are querying the physical table W_FSCL_MONTHCOSTREDUC_FORECAST_F. Give the name of the object and select the Type as Physical Table

2. In the result window, you will get an IUD field.

Here it is 3001:863204. Take the second part of this id after “:” and prefix the ID with the letter “T”. This will be the table id we have to use in the report to represent the table.

The derived table id from the above example is “T863204”

· Step 2

Open the report you want to implement hint in the report editor.

· Step 3

Create new column, put the column in the first position in the report, Hide the column and edit the function(fx) for the column as shown below.

Caution: If you are not hiding the column you will see the hint in the group by clause.

Let’s try to implement the FULL Hint via report

Hint Syntax:

/*+ Full (Table Name) */

Table Name: WC_PURCH_COSTREDUC_FORECAST_F

Here I will use an Evaluate function to wrap the hint. The syntax of Evaluate function is EVALUATE('Database Function(%1)', 1st parameter) Eg: EVALUATE('Upper(%1)', 'oracle') will give output as ORACLE. We will use the same technique to implement the hint but with a small change. We will pass the hint with the value (if needed) and then pass the parameter as the Table.Coulmn from the subject area.

So, now our EVALUATE function will look like

EVALUATE(‘/*+FULL(T863204)*/ %1, “Dim- Time”.”Fiscal Year”)

Where

/*+FULL(T863204)*/ à is the hint with Table Id passed as parameter

%1 àis the first Parameter

“Dim- Time”.”Fiscal Year” àis the subject area table’s column which I’m passing as the value for the first parameter

· Step 4

Save and Run the report

· Queries

Example:

OBIEE Physical Query before implementing the FULL Hint in REPORT

WITH

SAWITH0 AS (select D1.c1 as c1,

     D1.c2 as c2,

     D1.c3 as c3,

     D1.c4 as c4,

     D1.c5 as c5,

     D1.c6 as c6,

     D1.c7 as c7,

     D1.c8 as c8

from

     (select T862336.FSCL_PER_NAME_YEAR as c1,

               T862336.FSCL_PER_NAME_YEAR as c2,

               T862336.FSCL_PER_NAME_QTR as c3,

               T862336.FSCL_PER_NAME_MONTH as c4,

               sum(T863204.FORECAST_SAVING) as c5,

               T862336.FSCL_MONTH as c6,

               T862336.FSCL_QTR as c7,

               T862336.FSCL_YEAR as c8,

               ROW_NUMBER() OVER (PARTITION BY T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR ORDER BY T862336.FSCL_MONTH ASC, T862336.FSCL_QTR ASC, T862336.FSCL_YEAR ASC, T862336.FSCL_PER_NAME_MONTH ASC, T862336.FSCL_PER_NAME_QTR ASC, T862336.FSCL_PER_NAME_YEAR ASC) as c9

          from

               WC_SCM_INT_ORG_D_V T888692,

               W_FSCL_MONTH_D T862336,

               WC_PURCH_COSTREDUC_FORECAST_F T863204

          where  ( T862336.ROW_WID = T863204.MONTH_NO and T863204.INVENTORY_ORG_WID = T888692.ROW_WID and T863204.PART_DELETE_FLAG = 'N' and T888692.USER_GROUP = 'OBIEE_CORP_SCM_GLOBAL' and (T862336.FSCL_PER_NAME_YEAR in ('2008', '2009')) )

          group by T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR

     ) D1

where  ( D1.c9 = 1 ) )

select SAWITH0.c1 as c1,

     SAWITH0.c2 as c2,

     SAWITH0.c3 as c3,

     SAWITH0.c4 as c4,

     SAWITH0.c5 as c5

from

     SAWITH0

order by c1, c2, c3, c4

OBIEE Physical Query after implementing the FULL Hint in REPORT

WITH

SAWITH0 AS (select D1.c1 as c1,

     D1.c2 as c2,

     D1.c3 as c3,

     D1.c4 as c4,

     D1.c5 as c5,

     D1.c6 as c6,

     D1.c7 as c7,

     D1.c8 as c8

from

     (select /*+FULL(T863204) */ T862336.FSCL_PER_NAME_YEAR as c1,

               T862336.FSCL_PER_NAME_YEAR as c2,

               T862336.FSCL_PER_NAME_QTR as c3,

               T862336.FSCL_PER_NAME_MONTH as c4,

               sum(T863204.FORECAST_SAVING) as c5,

               T862336.FSCL_MONTH as c6,

               T862336.FSCL_QTR as c7,

               T862336.FSCL_YEAR as c8,

               ROW_NUMBER() OVER (PARTITION BY T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR ORDER BY T862336.FSCL_MONTH ASC, T862336.FSCL_QTR ASC, T862336.FSCL_YEAR ASC, T862336.FSCL_PER_NAME_MONTH ASC, T862336.FSCL_PER_NAME_QTR ASC, T862336.FSCL_PER_NAME_YEAR ASC) as c9

          from

               WC_SCM_INT_ORG_D_V T888692,

               W_FSCL_MONTH_D T862336,

               WC_PURCH_COSTREDUC_FORECAST_F T863204

          where  ( T862336.ROW_WID = T863204.MONTH_NO and T863204.INVENTORY_ORG_WID = T888692.ROW_WID and T863204.PART_DELETE_FLAG = 'N' and T888692.USER_GROUP = 'OBIEE_CORP_SCM_GLOBAL' and (T862336.FSCL_PER_NAME_YEAR in ('2008', '2009')) )

          group by T862336.FSCL_MONTH, T862336.FSCL_QTR, T862336.FSCL_YEAR, T862336.FSCL_PER_NAME_MONTH, T862336.FSCL_PER_NAME_QTR, T862336.FSCL_PER_NAME_YEAR

     ) D1

where  ( D1.c9 = 1 ) )

select SAWITH0.c1 as c1,

     SAWITH0.c2 as c2,

     SAWITH0.c3 as c3,

     SAWITH0.c4 as c4,

     SAWITH0.c5 as c5

from

     SAWITH0

order by c1, c2, c3, c4

Example -2

Lets try to implement nested loop hint

Syntax:

'/*+ordered use_nl(Table Name ) parallel(e, 4) */ %

Evaluate Function to be given in the report col:

EVALUATE('/*+ordered use_nl(T266471) parallel(e, 4) */ %1', "Dim - Time"."Fiscal Year")

Where  T266471 is the table id forn OBIEE rpd

Dim - Time"."Fiscal Year is the column we are calling.

/*--------------------------------CREATED_BY : JJN CREATED_DATE : 07-Jul-2009---------------------------------*/

OBIEE variable types :

· Repository Variables

o Static Variables

o Dynamic Variables

· Session Variables

o System

§ Secutity

§ Others

o Non-System

To create any variable Click Manage > Variable

This opens variable Manager as shown below.

Repository Variable(Static)

· Initialized only when BI server is started

· Is a hard coded value like string, number , etc

· Value can only be changed by logging in to the rpd file

Repository Variable(Dynamic)

· Initialized when BI server is started

· Assigned value dynamically based on the result of a query

· Value is dependent of the sql provided in Initialization Block

Create a dynamic variable and provide a Name and Default Initializer.Create a new initialization block by clicking on the New button in the above screen.

Provide Intialization Block variable Name

Click on “Edit Data Source” button and provide the sql to be used for the variable, e.g.

select lower(sys_context('USERENV','SESSION_USER'))||

'@'||

lower(sys_context('USERENV','DB_NAME'))

from dual;

Provide Connection pool name. Note, a separate connection pool should be created for initialization blocks to execute te sql used for fetching data for the variable.

Test the sql by clicking on test button.

Session Variable(System)

· Initialized when a Analytics web user logs (creates a new session)

· Initialization depends on a Initialization block, similar to Dynamic repository Variable

· Only system reserved variables can be created and the following is the list . Ones in Blue are Security related 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.
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..
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.

Session Variable(Non-Session)

· Initialized when a Analytics web user logs (creates a new session)

· Initialization depends on a Initialization block, similar to Dynamic repository Variable

Row wise initialization of variables

If a variable is marked for row wise initialization it means it returns an array of values. Below are the steps. E.g. if we want a variable to store last 10 years here is how we create it :