Data Warehouse Clock Structure for Contact Center Time Zone Reporting
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current contact center reporting systems face challenges in efficiently handling international time zones and daylight saving rules, leading to increased database size, decreased performance, and the inability to dynamically support multiple time zones without significant downtime.
Innovation Solution
A data warehouse clock structure is implemented, where the date dimension includes time information down to the hour or quarter hour, allowing for multiple time zone representations, and the time dimension is independent of time zone, enabling accurate handling of daylight saving transitions and allowing the selection of specific time zones during reporting, reducing database size and enabling dynamic time zone support.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a single universal time zone (UTC) is used to store all contact center data, then database structure is simple and consistent, but the system cannot dynamically support multiple international time zones and daylight saving rules
Solution Approach 1:
The patent segments the time zone handling by creating separate dimension tables for different time zones (e.g., EST_dim, PST_dim, CET_dim). Each dimension table contains time-specific attributes like daylight saving indicators and UTC offsets for that particular time zone. This segmentation allows the system to support multiple time zones independently without complicating the core fact tables, as each time zone's data is organized in its own dedicated structure.
2Adaptability or versatility
If multiple time zone data are stored in fact tables to support international reporting, then multiple time zones can be supported, but database size increases significantly and performance decreases
Solution Approach 1:
The patent extracts time zone-specific data from the fact tables and places it into separate dimension tables. The fact tables retain only essential foreign keys (date_key, time_key) that reference the dimension tables. This extraction removes redundant time zone information from the large fact tables, significantly reducing their size and improving query performance, while the dimension tables provide all necessary time zone context for reporting.
Solution Approach 2:
The patent creates copied representations of time zone data in dimension tables that can be efficiently joined to fact tables. Each dimension table contains copied time zone attributes (daylight saving indicators, UTC offsets, time zone names) that are referenced by foreign keys in the fact tables. This copying approach allows multiple time zones to be supported without duplicating entire fact table rows, maintaining performance while enabling versatility.
3Adaptability or versatility
If time zone conversion is performed in reporting software, then flexibility in time zone selection is achieved, but the complexity of handling daylight saving transitions and time zone rules increases significantly
Solution Approach 1:
The patent performs preliminary action by pre-calculating and storing time zone-specific attributes in dimension tables during data loading. Daylight saving indicators, UTC offsets, and time zone names are computed in advance and stored in the dimension tables. This eliminates the need for complex runtime calculations when generating reports, as the reporting software simply needs to join fact tables with the appropriate dimension table based on the selected time zone, significantly reducing computational complexity.
4Measurement precision
If date dimension includes time information at fine granularity (down to seconds), then precise time tracking is achieved, but the size of the date dimension table becomes enormous
Solution Approach 1:
The patent segments time information into two separate dimension tables: a date dimension table that contains coarse-grained temporal information (year, quarter, month, week, day) and a time dimension table that contains fine-grained time information (hour, minute, second, millisecond). This segmentation allows precise time tracking to be achieved by combining data from both tables, while the date dimension table remains manageable in size. The foreign key relationship between the two tables enables precise time tracking without creating an enormous single table.
Data Source
AI summary
In one embodiment, a computer readable medium is provided that includes a date time zone dimension table comprising a date key and a time zone key, the date key identifying a selected point in time relative to a selected temporal origin and the time zone key identifying one of a plurality of possible time zones, and at least one attribute, the at least one attribute describing time information for a selected date and time zone key pair.