Summary: How to create BI platform that globalizes reports across all time zones

So after all of the trials and errors, understanding of the complexity and trickiness of globalizing (or internationalizing) reports across all time zones, I am going to summarize all of the steps in this entry.

First of all, let me go over the requirements and issues once again when it comes to this type of reports. In the nutshell, the requirement is simply to answer the question of what happen to my business at this particular time in my local time zone.

In other words, my stock is trading in New York and I want to know what is the stock price at 3pm California time. Or, my account is in Europe, I want to know all of the transactions at 2am Tokyo time. Even, what is the average trade volume of this stock from 10am to 1pm my local time in Berlin for the stock being traded in London. For the particular project that this requirement is applied to, I want to know all of the signal drops in my telecom network from 8am to 9am Greenwich mean time, or show me all of the interfaces that exceeds the throughput from 8am to 9am my local time.

Traditionally, the data are typically managed at the ETL layer and the data source feeds data with their local time zone.

Below is the approach I used:


1. Enable OBIEE's time zone setting by editing the instanceconfig.xml file
Detail can be found in this article


2: Store all of the data at standard UTC time in the DB. Then create a DB table that stores all of the time zones and offset hours there for OBIEE to report against. The offset value is then available for used as presentation variable from OBIEE dashboard. These steps can be seen in this article

3. Apply the conversion using the offset values obtained from step 2 to the date and timestamp fields of the report. Depending on how the logic is, your formula maybe different. This article explains how this is applied in my case.


So, that's all there is to it.

Now one shortcoming of this approach is that the day light saving conversion will have to be done manually by updating the external table's offset column. Potentially, a function or SP maybe used to automate the process. However, day light saving is a very complicated thing by its nature, different country have different laws on when it is changed. There is no way around it. This is something we will just have to get used to it.

Thanks,

Until next time.