KB485848: Resolving Timezone Aware filters

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Description

Filters are always converted to UTC timezone, since the underlying database is in UTC. To understand how the filters are resolved, we will divide the scenarios in 2 sections.

1. Resolving filter in Live Connect mode for a Dossier or Report.
2. Resolving filter for an Intelligent Cube.

Resolving filter in Live Connect mode for a Dossier or Report

• In LiveConnect mode all the timezone calculations, including the filter is pushed down to the Warehouse SQL.
• Filter element values that are selected, are internally converted from runtime timezone to UTC.

Example1 New York Timezone(GMT-5) : If someone creates a filter like below:
Attribute in ('12/15/2022 10:00:00 AM')
if this Dossier/Report in run by a user in New York Timezone (GMT-5), sql generated by Strategy Engine, looks like below, which converts 12/15/2022 10:00:00 AM from New York Timezone to UTC Timezone :
DB.COL in('12/15/2022 03:00:00 PM')
Example2 Australian Eastern Timezone(GMT +10) : If someone uses the same filter in other timezone:
The same Dossier/Report in run by a user in Australian Eastern Timezone (GMT +10), sql generated by Strategy Engine, looks like below, which converts 12/15/2022 10:00:00 AM from Australian Eastern Timezone to UTC Timezone :
DB.COL in('12/15/2022 12:00:00 AM')

Resolving filter for an Intelligent Cube

• Data in the Intelligent cube is stored in UTC format. Time zone conversion is achieved at runtime, the filter is stored by default in Project Default Timezone.

• Filter element values that are selected, are internally converted from the timezone selected above to UTC.
• Examples from LiveConnect section apply likewise for Intelligent Cubes as well, with the only difference that Filters are converted from only Project Default/ User Specified timezone in Internationalization tab of Cube Editor to UTC.