How to report data dynamically based on user's personal time zone setting -- Part 1

Ever since the introduction of global time zone concept, the information that business want to see has a whole different meanings. The transaction with certain amount took place at certain time means the world of difference if considering the time zone in where it occurred. Therefore, viewing data at user's preferred time has always been requirements. One of the trivia situation where time zone matters a lot is when it comes to stock markets. An user in California looks at his stock at 10:00 am in front of his office, he should know that whatever he sees happen at 1: 00 PM  NY time.
Therefore, the question comes when reporting in OBIEE that, at what specific time did this transaction occur? If an user is based out of Japan, he is likely to change his locale settings to Tokyo time and therefore, the transaction that he cares about show display in Tokyo time even if the transaction itself happened elsewhere and recorded at a different time zone. So how do we implement this kind of reporting capability in OBIEE?
Without thinking too much, we intuitively think we should add time zone information into the user table (If external authentication is used) and create session variables to capture the time zone and add the value to the conversation. At a high level, this may work although I haven't tried, but the issue is that the users can change their personal locale preference whenever they want and they should expect different results. Their changing locale doesn't trigger the same update at the DB level, therefore, it won't reflect the update. Therefore, this is not ideal.
The most simple and straight-forward way of changing the display of date & time in reports is by modifying the instanceconfig.xml file by adding the following entry:
- 0 
 
Having done that, it will show you the difference of displaying date and time base on user's locale preference. We can see from below that we have a report with default locale setting and data being displayed is at GMT time (the time where OBIEE server machine is):


Now, change the time zone to Bangkok time, which is GMT + 7:
 
Now refresh the dashboard and the displaying date and time changes. The difference is perfectly 7 hours forward:


So now the job is done right? That's what we would think and I wish it was that easy so I didn't have to put "Part1" in the title of this article. You see, although the display changes perfectly correct, there is no actual date and time conversion happening at the query level. In other words, the query that is fired to the DB will still pick up date at default time zone, its just when it displays at the presentation, it changes accordingly.
So why is this a problem? Let me illustrate with the following example:
Let's say we have the following reports displayed with UTC time and users want to look at data by selecting the date and the hours where transaction is recorded. So let's add hour numbers into the report:



So far, the hours agree with the time stamp column, this assures that the correct hours are being filtered against. Now let's change the locale back to GMT + 7 Bangkok time and refresh the dashboard:

As you can see, now the hours are still showing what was in the previous time zone, but the displayed time stamp has been changed into Bangkok time. You see the problem?
Let's say the Bangkok user looks at this report and see the transactions occurring at hours between 2 PM to 3 PM his local time, and then he wants to look at all of the records between 2PM and 3PM, he will then change the filter value to 'between' 2 and 3. Then he is going to get the wrong record. Because without actual data level conversation, in the above example, if the user want to see the data at 2pm Bangkok time, he will have to enter 7 in the filter instead of 3. How would he know?
Therefore, something more needs to be done in order to create a truly internationalized dashboard.
I have figured out the way to do it, although it is not working 100% perfect, it is definitely very good.