Global Data Extraction Schedule for Business Intelligence
Global Data Extraction Schedule for Business Intelligence
A successful global business intelligence solution requires a data extraction schedule to meet the business demands of a global user base – “Get the right data to the right users at the right time”. This article will cover the last element of this statement – “right time”.
Each client will have their own customised scheduling requirements - data to be refreshed every hour, every 2 hours, daily, weekly etc. This article will cover the requirement for getting ‘day minus 1 data’ to a global business user base by 9am local time for all business users. The concept of ‘day minus 1 data’ refers to the point that the BI report will be refreshed with data till at least the previous local time midnight.
The following is a map of the world with the different time zones highlighted.
Determining the number of schedules and maximum execution time.
To achieve ‘day minus 1 data’ to a global user base, obviously 1 daily schedule is not sufficient and executing 24 schedules for each time zone is not feasible for a global business intelligence solution.
Determining the optimised number of schedules is extremely difficult. From experience of a global user base for a global business intelligence solution this can range from three to seven. I believe the ideal number is three.
The three schedules are
European & Africa
Asia & Australia
North & South America (Americas)
With a three-schedule strategy to achieve ‘day minus 1 data’ by 9am local time for a global user base then the maximum execution time can’t exceed five hours.
Determining schedule execution start time
To achieve getting ‘day minus 1 data’ to a global business user base by 9am local time, the following are start times for each schedule:
European & Africa – 00:00 UK time
North & South America (Americas) – 08:00 UK time
Asia & Australia – 16:00 UK time
Considering Daylight Saving Time (DST) Impact on global schedule
An important element in reviewing a global schedule is the Daylight-Saving time. The below diagram highlights the countries that have DST (In green). For this scenario, we’re using UK time as start time to the global schedule – when the DST changes, this will obviously impact countries that don’t use DST or have a different DST time schedule than UK (Sunday, 26 March to Sunday, 29 October for 2017) – examples below are different than UK DST.
New Zealand - Sunday, 24 September to Sunday, 2 April 2017
United States - Sunday, 12 March Sunday to 5 November 2017.
Schedule execution time per time zone
As mentioned already, the maximum execution time for a three-schedule strategy is five hours. This means that all global users will have data by 9am (local time).
The following is example for most the time zones with three-schedule strategy that executes for 5 hours for each schedule with start times of 00:00; 08:00 and 16:00 (UK time).
Please note the below timings are using summer time (day light saving - DST) - (British Summer Time (BST)).
London (UK) – European & Africa Schedule – completed by 5am local time.
Paris (France) – European & Africa Schedule – completed by 6am local time.
Helsinki (Finland) – European & Africa Schedule – completed by 7am local time.
Muscat (Oman) – European & Africa Schedule – completed by 8am local time.
Islamabad (Pakistan) – European & Africa Schedule – completed by 9am local time.
Delhi (India) - Asia & Australia schedule – completed by 01.30am local time. Please note it only has data from 20.30 the previous day – so not the full data set from previous day.
Dhaka (Bangladesh) - Asia & Australia schedule – completed by 02.00am local time. Please note it only has data from 21.00 the previous day – so not the full data set from previous day.
Bangkok (Thailand) - Asia & Australia schedule – completed by 03.00am local time. Please note it only has data from 22.00 the previous day – so not the full data set from previous day.
Singapore (Singapore) - Asia & Australia schedule – completed by 04.00am local time. Please note it only has data from 23.00 the previous day – so not the full data set from previous day.
Tokyo (Japan) - Asia & Australia schedule – completed by 05.00am local time.
Adelaide (Australia) - Asia & Australia schedule – completed by 06.30am local time.
Melbourne (Australia) - Asia & Australia schedule – completed by 07.00am local time.
Suva (Fiji) - Asia & Australia schedule – completed by 08.00am local time.
Wellington (New Zealand) - Asia & Australia schedule – completed by 09.00am local time.
Reykjavik (Iceland) – European & Africa Schedule – completed by 4am local time. Please note it only has data from 23.00 the previous day – so not the full data set from previous day.
Praia (Cape Verde) – European & Africa Schedule – completed by 3am local time. Please note it only has data from 22.00 the previous day – so not the full data set from previous day.
Nuuk (Greenland) – European & Africa Schedule – completed by 2am local time. Please note it only has data from 21.00 the previous day – so not the full data set from previous day.
Buenos Aires (Argentina) - North & South America (Americas) schedule – completed by 9am local time.
New York (North America) - North & South America (Americas) schedule – completed by 8am local time.
Chicago (North America) - North & South America (Americas) schedule – completed by 7am local time.
Denver (North America) - North & South America (Americas) schedule – completed by 6am local time.
Los Angeles (North America) - North & South America (Americas) schedule – completed by 5am local time.
Please note the below timings are using UK winter time:
London (UK) – European & Africa Schedule – completed by 5am local time.
Paris (France) – European & Africa Schedule – completed by 6am local time.
Helsinki (Finland) – European & Africa Schedule – completed by 7am local time.
Muscat (Oman) – European & Africa Schedule – completed by 9am local time.
Islamabad (Pakistan) – Asia & Australia schedule – completed by 02.00am local time. Please note it only has data from 21.00 the previous day – so not the full data set from previous day.
Delhi (India) - Asia & Australia schedule – completed by 02.30am local time. Please note it only has data from 21.30 the previous day – so not the full data set from previous day.
Dhaka (Bangladesh) - Asia & Australia schedule – completed by 03.00am local time. Please note it only has data from 22.00 the previous day – so not the full data set from previous day.
Bangkok (Thailand) - Asia & Australia schedule – completed by 04.00am local time. Please note it only has data from 23.00 the previous day – so not the full data set from previous day.
Singapore (Singapore) - Asia & Australia schedule – completed by 05.00am local time.
Tokyo (Japan) - Asia & Australia schedule – completed by 06.00am local time.
Adelaide (Australia) - Asia & Australia schedule – completed by 07.30am local time.
Melbourne (Australia) - Asia & Australia schedule – completed by 08.00am local time.
Suva (Fiji) - Asia & Australia schedule – completed by 09.00am local time.
Wellington (New Zealand) - North & South America (Americas) schedule – completed by 02.00am local time. Please note it only has data from 21.00 the previous day – so not the full data set from previous day.
Reykjavik (Iceland) – European & Africa Schedule – completed by 5am local time.
Praia (Cape Verde) – European & Africa Schedule – completed by 4am local time. Please note it only has data from 23.00 the previous day – so not the full data set from previous day.
Nuuk (Greenland) – European & Africa Schedule – completed by 2am local time. Please note it only has data from 21.00 the previous day – so not the full data set from previous day.
Buenos Aires (Argentina) - European & Africa Schedule – completed by 2am local time. Please note it only has data from 21.00 the previous day – so not the full data set from previous day.
New York (North America) - North & South America (Americas) schedule – completed by 9am local time.
Chicago (North America) - North & South America (Americas) schedule – completed by 8am local time.
Denver (North America) - North & South America (Americas) schedule – completed by 7am local time.
Los Angeles (North America) - North & South America (Americas) schedule – completed by 6am local time.
Determining end user locations and time zones
As you can see from the last section by deploying a three-strategy schedule with SLA of 5 hours will still not meet the requirement for ‘day minus 1 data’ to every single end user globally. With the start times above and SLA of 5 hours there will be countries/time zones that this will not be achieved in.
Examples below:
Delhi (India) - only has data from 20.30 the previous day (British Summer Time (BST)).
Dhaka (Bangladesh) - only has data from 21.00 the previous day (British Summer Time (BST)).
Bangkok (Thailand) - only has data from 22.00 the previous day (British Summer Time (BST)).
Singapore (Singapore) - it only has data from 23.00 the previous day (British Summer Time (BST)).
Reykjavik (Iceland) - it only has data from 23.00 the previous day (British Summer Time (BST)).
Praia (Cape Verde) - only has data from 22.00 the previous day (British Summer Time (BST)).
Nuuk (Greenland) - only has data from 21.00 the previous day (British Summer Time (BST)).
It’s important to review if there are BI business users in these countries/time zones and if there is then review the restriction of not having a full data set by 9am with the local BI super users.
Global Schedule execution days
The ‘Standard working’ week globally is Monday to Friday but there are countries that work Saturday and/or Sunday (for example Egypt’s working week is Sunday to Thursday). This element also needs to be taken into consideration when determining the days that you need to execute each of the global schedules. You need to review all markets/countries that use/will use the global business intelligence solution and review with the local super user their working week.
Even if the standard week is Monday to Friday for all business users, if you have a user base in Asia Pacific then you’ll need to execute the Asia & Australia schedule on Sunday evening @ 4pm UK time as they will need their data by 9am local time on a Monday morning.
Protecting the BI schedule
If you have achieved a maximum execution time of under 5 hours and new BI development has been deployed with a SLA extraction time of above 5 hours, then you need to advise the project team that this is not a global solution and won’t be included in all BI schedules and can be only executed once a day. This is basically protecting the BI support team from escalations from markets after go live that they’re not getting their data @ 9am local time.
Conclusion
It’s difficult to have an article that covers all scheduling requirements as each customer will have different scheduling requirements, different execution timings, different user bases across the world and different expectations of when they want to get their data by.
This article covered that the end users wants ‘day minus 1 data’ by 9am local time and had an end user base is in most of the time zones with a maximum data extraction time of 5 hours,
The key to a successful global business intelligence schedule are:
Determine the time zone of all existing and future business users.
Determine the working week for each country that uses the BI solution.
If country does not get the full data set by 9am local time, then work with the super user to ensure that this data restriction still meets their BI requirements.
Protect the schedule against future development that does not meet the SLA of 5 hours.
Business Intelligence Solutions Support Survey
If you’re open to contributing, we’d love to hear your views in a survey that we’re conducting.
The survey has 17 questions but we’ve carefully designed and tested it to ensure it can be completed quickly, it should take no longer than 10 minutes.
In return for doing so, we’ll send you a report on the study’s full findings and we think you’ll find the results to be of value to your business.
Survey
