Using Hints in OBIEE RPD and Answers

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

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

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---------------------------------*/