Posts
Previously, I have concluded my approach to implement the reports that will intelligently retrieve data corresponds to different time zone even if the displayed time stamp never changes. In other words, when any user changes its account setting to a different time zone, the report should automatically displays the data accordingly even if the values displayed on the dashboard prompt is still "Jan 01, 2001" for instance. The meaning of the report, the displayed time changes completely when the account setting changes. This wasn't easy but it was proven possible.
However, there was an issue with the report I created if you really test it deeply. Let me demonstrate here:
We have the following report that shows date, hour, hour number and the throughput. Each throughput has a corresponding time which is from the Date Dim table. It is a simple report and the time zone setting is default. So nothing fancy here.
Now, let's change the time zone to GMT + 8 Perth time and rerun the report:
As you can see below, the displayed time zone local time has changed to reflect Perth time while hour, hour date and hour number are still reflecting the default time. This issue has been mentioned previously. This is the behavior of OBIEE, it only changes the display of the time stamp based on locale time zone, it doesn't really do the actual time stamp conversion. Therefore, when you convert time stamp to hour number, it is converting the time stamp from "Hour Date" column, rather than "Hour Date Time Zone Local Time" column. Hence the confusion.
So let's change the hour filter to 10 to 12, the report comes back with correct data. How so?
Well, the user wants to know the throughput of 10am to 12pm on Jan 6th 2013 Perth time, and the report is showing these 3 throughput records of 2am to 4am, Jan 06 2013 UTC time. That's why it is good.
Now let's change the filter to between 1 to 3 and rerun it.
Then we are having problem. Because now, the user wants to see the throughput of 1am to 3am Jan 6th 2013 Perth Time, yet the report is displaying the data 5pm to 7pm Jan 6th 2013 UTC. This is wrong. It should've displayed the throughput of around 5pm to 7pm Jan 5th 2013 (the day before).
Now we know there is an issue, but how are we going about it?
Before doing anything, I need to understand how is the conversion taking place and how am I filtering the report. So I then created some dummy columns in the same report with different expression to understand what kind of behavior it is doing for this particular time zone (when Offset value = 8).
Hour date: This is nothing but hour date - 8. This will return the time stamp of the default UTC because the display of hour date is going to be plus 8.
Hour number: This column will give the hour number according to UTC time stamp
Conversation Hour Number: Here I am doing a time stamp plus 16 hours. This will give me the hour number in accordance with the Displayed Perth Time Stamp. It took me a while to figure out it is 8*2. If the offset is 4, then it should be 4*5 to get you the right hour number that matches the display.
Run the report and see what its doing:
So we see the problem with this column. Since the filter on date is Jan 6th 2013, we are getting all of the records of Jan 6th 2013 of UTC time. Therefore, the displayed local time in this case is missing 0 to 7 on the same date, and its adding 0 to 7 of the next date. This is expected behavior.
With the same logic, if I change the local to GMT -8, or Pacific Time in U.S.A, this behavior should occur the same way in reverse as shown:
My first approach is to find a way to convert this hour so it will match the displayed local time stamp both hourly and daily. Without thinking too much, I added "hour(Date, "Hour Date") +@{Offset}" to the expression and named the new column "Hour number with offset" and rerun the report for both +8 and -8 time zone:
Perth Time, +8 to UTC:
Pacific Time, -8 to UTC:
See what's going on? Looking at both cases, the conversion is giving the correct hour number from 0 to 23. However, when the result exceeds 23 (in the case of +8 time zone when adding 16 (UTC hour) with 8), it becomes 24 and it keeps going on with 8 more numbers by increment of 1; when the result is less than 0 (In the case of -8 time zone when adding 0 (UTC hour) with -8), it becomes -8 and it keeps going on with 8 more numbers by increment of 1.
I am close, but not quite there yet. I have realized that I need to do some programming and address the case of less than 0 and greater than 23. Luckily, I have found a good solution and it is working perfect. I will continue here.
Stay tune!
However, there was an issue with the report I created if you really test it deeply. Let me demonstrate here:
We have the following report that shows date, hour, hour number and the throughput. Each throughput has a corresponding time which is from the Date Dim table. It is a simple report and the time zone setting is default. So nothing fancy here.
Now, let's change the time zone to GMT + 8 Perth time and rerun the report:
As you can see below, the displayed time zone local time has changed to reflect Perth time while hour, hour date and hour number are still reflecting the default time. This issue has been mentioned previously. This is the behavior of OBIEE, it only changes the display of the time stamp based on locale time zone, it doesn't really do the actual time stamp conversion. Therefore, when you convert time stamp to hour number, it is converting the time stamp from "Hour Date" column, rather than "Hour Date Time Zone Local Time" column. Hence the confusion.
Well, the user wants to know the throughput of 10am to 12pm on Jan 6th 2013 Perth time, and the report is showing these 3 throughput records of 2am to 4am, Jan 06 2013 UTC time. That's why it is good.
Now let's change the filter to between 1 to 3 and rerun it.
Then we are having problem. Because now, the user wants to see the throughput of 1am to 3am Jan 6th 2013 Perth Time, yet the report is displaying the data 5pm to 7pm Jan 6th 2013 UTC. This is wrong. It should've displayed the throughput of around 5pm to 7pm Jan 5th 2013 (the day before).
Now we know there is an issue, but how are we going about it?
Before doing anything, I need to understand how is the conversion taking place and how am I filtering the report. So I then created some dummy columns in the same report with different expression to understand what kind of behavior it is doing for this particular time zone (when Offset value = 8).
Hour date: This is nothing but hour date - 8. This will return the time stamp of the default UTC because the display of hour date is going to be plus 8.
Hour number: This column will give the hour number according to UTC time stamp
Conversation Hour Number: Here I am doing a time stamp plus 16 hours. This will give me the hour number in accordance with the Displayed Perth Time Stamp. It took me a while to figure out it is 8*2. If the offset is 4, then it should be 4*5 to get you the right hour number that matches the display.
Run the report and see what its doing:
So we see the problem with this column. Since the filter on date is Jan 6th 2013, we are getting all of the records of Jan 6th 2013 of UTC time. Therefore, the displayed local time in this case is missing 0 to 7 on the same date, and its adding 0 to 7 of the next date. This is expected behavior.
With the same logic, if I change the local to GMT -8, or Pacific Time in U.S.A, this behavior should occur the same way in reverse as shown:
My first approach is to find a way to convert this hour so it will match the displayed local time stamp both hourly and daily. Without thinking too much, I added "hour(Date, "Hour Date") +@{Offset}" to the expression and named the new column "Hour number with offset" and rerun the report for both +8 and -8 time zone:
Perth Time, +8 to UTC:
Pacific Time, -8 to UTC:
See what's going on? Looking at both cases, the conversion is giving the correct hour number from 0 to 23. However, when the result exceeds 23 (in the case of +8 time zone when adding 16 (UTC hour) with 8), it becomes 24 and it keeps going on with 8 more numbers by increment of 1; when the result is less than 0 (In the case of -8 time zone when adding 0 (UTC hour) with -8), it becomes -8 and it keeps going on with 8 more numbers by increment of 1.
I am close, but not quite there yet. I have realized that I need to do some programming and address the case of less than 0 and greater than 23. Luckily, I have found a good solution and it is working perfect. I will continue here.
Stay tune!
0 comments:
Post a Comment