How To use INDEX Hints In OBIEE

I’ve found quite a few blogs describing how to add an oracle database hint to a physical table in OBI; but they all stop short of showing how they can be added to an Alias Object.  There is a section at the end of the blog addressing this.
Database Hints allow us to change suboptimal SQL Execution Plans; they simply allow us to provide more information to the optimizer and influence the plan executed.  A database hint will take the form as below.

SELECT
   /*+ index(T222,PK) */
   ‘ROW_WID’ as c1
FROM
   W_CUSTOMER_D T222

OBIEE Query Hints
In OBIEE we can add a database hint to a table object in the physical layer; whenever this table is referenced in a query the hint will be applied.  Importantly, a hint should use the table alias whenever a query specifies an alias for a table; the table itself should not be used.  If you look at the underlying SQL of an OIBEE query, via the Obiee view log, you will notice OBIEE will always use an alias for a table in the generated SQL; an alias taking the form such as T222 above.
The first step to adding our hint to a table is to determine the alias that OBIEE will use in the generated SQL.  Select to Query Repository from the Tools Menu of the Administration Tool.  The Query Repository Tool will open, as in the image below.  Enter the Name of the table in the Name textbox and select Physical Table from the Type menu; click query to run the tool.  Notice the use of the wildcard character, *.

In this example you can see we have a physical table in the database, W_CUSTOMER_D, and that I have created an Alias ojbect based on that table of Dim_W_CUSTOMER_D.  In the ID column the two objects have IDs of 3001:111 and 3001:222 respectively.  If we were using the table in our query, we can deduce we will use an alias of T111.  Actually we used the Alias object and the database alias generated would be T222.  You can see it is the latter segment of the ID that is used.
We can now add the hint; a hint can not be added to an OBIEE Alias object, only to an underlying Table object.  We open the properties for the table object and add the hint to the text box as below.

You can see that the hint is applied to the OBIEE Alias object, T222; but we are creating that definition on the underlying Table object.  If there were no Alias object and the table itself were used in the query then we would need to use the SQL generated alias of T111.
Hinting an Alias Object
But what do we do if we have multiple Alias’ objects sharing the same underlying Table object; this happens all the time. 
We can’t apply the hint to the Alias object itself; instead we apply it to physical joins to and from the Alias object.  The screenshot below shows where we apply the hint for the Alias table above.

You can see that we’ve populated the hint textbox referencing the SQL alias T222.  Whenever this join is used the hint will be added to the query.

Thanks,
Satya Ranki Reddy

Dynamic Variables for Previous Date Period

One of my client was looking at a report which would provide data from first day of last month to current date, so that he can compare sales trend for the last month and plan for the current month.
Best way to quickly work on it is to create variable and use it on dashboard prompt as default to provide first day of last month to current date.
To demonstrate the steps, I have created an excel database to work on the example.
Firstly, create an initialization block and dynamic variables called previous months
Click Manage > Variables to open the Variable Manager.
Click Repository > Initialization Blocks.
Right-click in the white space on the right and select New Initialization Block.

Name the initialization block as Previous_MonthsClick Edit Data Source to open the Repository Variable Init Block Data Source dialog box. Click the Browse button to open the Select Connection Pool dialog box. Select any connection pool associated with database but not excel sheet. (on the above screen shot, emp is excel database. Double-click the HR > Connection Pool object to add it to the Connection Pool field in the Repository In the Default Initialization String field, type the following SQL

SELECT
ADD_MONTHS(TRUNC(SYSDATE,’MM’),-1),
SYSDATE AS CURRENT_DATE
FROM DUAL

Click OK to close the Repository Variable Init Block Data Source dialog box. The connection pool and initialization string are added to the Repository Variable Init Block dialog box
Click Edit Data Target to open the Repository Variable Init Block Variable Target dialog box.

Use the New button to create TWO variables as FIRST_DAY_LAST_MONTH and create another as CURRENT_DATE
Click OK to close the Repository Variable Init Block Variable Target dialog box. The variables appear in the Variable Target field in the Repository Variable Init Block dialog box

Click Test and verify you get the results in the picture.
Click OK to close the Repository Variable Init Block Data Source dialog box.
Click Action > Close to close the Variable Manager
Check in changes.
Check Global Consistency. If the Consistency Check Manager displays any errors, edit the repository to correct the errors before continuing. If there are no error messages, close the Consistency Check Manager.
Save the repository.
Logon to Answers
You can use these variable anywhere in the reports like on the filters as well as on prompts.
Create any report which provides data by date range
Build the following query
1. Pull the date field with other fields
2. Add filters on the date field

Click on the results, it will pull only data within the date range. This date range will dynamically change every month to provide data from first day of previous month to current date.

You use the variables in the Date Prompt also as below
Similarly, you can you these variables on the prompt as well.
Go to prompt and pull the columns including date column

On the default to section, select server variable and type the variables as

Save the report. The report will filter date with the above date range by default.


Thanks,
Satya