Projekt hurtowni danych: Połączony wymiar Data i godzina vs. Oddzielne wymiary Dzień i godzina oraz strefy czasowe

Właśnie rozpoczynamy projektowanie nowej hurtowni danych i staramy się zaprojektować, jak będą działać nasze wymiary daty i godziny. Musimy być w stanie obsługiwać wiele stref czasowych (prawdopodobnie przynajmniej GMT, IST, PST i EST). Początkowo myśleliśmy, że będziemy mieli jeden szeroki łączny wymiar daty i czasu, może do 15 minut granulacji, w ten sposób mamy jeden klucz w naszych tabelach faktów, a wszystkie różne dane daty i godziny dla wszystkich obsługiwanych stref czasowych znajdują się w jednej tabeli wymiarów. (tj. klucz daty, data GMT, godzina GMT, data IST, godzina IST itp.)

Kimball sugeruje, aby mieć wymiar oddzielny od wymiaru pory dnia, aby zapobiec zbyt dużemu powiększeniu tabeli (zestaw narzędzi hurtowni danych, s. 240), co brzmi dobrze, ale oznaczałoby to, że mamy dwa klucze w naszych tabelach faktów dla każdej strefy czasowej musimy wesprzeć (jeden dla daty i jeden dla pory dnia).

Ponieważ jestem bardzo niedoświadczony w tym obszarze, mam nadzieję, że ktoś tam zna kompromisy między tymi dwoma podejściami, tj. Wydajność vs. zarządzanie wszystkimi różnymi kluczami strefy czasowej. Być może są też inne podejścia, widziałem, jak niektórzy mówią o oddzielnym wierszu w tabeli faktów na strefę czasową, ale wydaje się to problemem, jeśli faktyczne tabele mają miliony wierszy, to trzeba je czterokrotnie zwiększyć, aby dodać strefy czasowe .

Jeśli wykonamy 15-minutowe ziarno, będziemy mieli 131 400 (24 * 15 * 365) wierszy rocznie w naszej tabeli wymiarów daty i czasu, co nie brzmi zbyt okropnie dla wydajności, ale nie będziemy tego pewni, dopóki nie przetestujemy niektórych prototypowe zapytania. Innym problemem związanym z posiadaniem oddzielnych kluczy strefy czasowej w tabeli faktów jest to, że zapytanie musi połączyć tabelę wymiarów z inną kolumną w oparciu o pożądaną strefę czasową, być może jest to coś, czym zajmuje się SSAS, nie jestem pewien .

dzięki za wszelkie przemyślenia, -Matt

Oddzielenie daty i godziny pozwoli ci znacznie łatwiej tworzyć agregacje według czasu. na przykład: jeśli chcesz uruchomić zapytanie, aby dowiedzieć się, który okres dnia jest najbardziej zajęty. Można to łatwo wykonać przy użyciu osobnego wymiaru czasowego.

Powinieneś także mieć tylko jeden zegar czasu. Wybierz czas GMT / EST - następnie użyj tego w tabeli faktów. Jeśli chcesz uruchamiać raporty na podstawie innej strefy czasowej, po prostu przekonwertuj ją w aplikacji lub zapytaniu.

Po prostu kontynuacja tego, w jaki sposób zdecydowaliśmy się wdrożyć nasz DataWarehouse do obsługi wielu stref czasowych i być tak wydajnym, jak to tylko możliwe: postanowiliśmy stworzyć tabelę stref czasowych (identyfikator, nazwę itp.) Oraz „Strefę czasową” most ”, który wygląda następująco:

time_zone_bridge
---------------
date_key_utc
time_key_utc
timezone_id
date_key_local
time_key_local

W ten sposób możemy utrzymać nasze normalne tabele wymiarów daty i czasu małe, wszystkie nasze fakty łączą się z kluczami daty / czasu UTC, a następnie, jeśli musimy zgłosić / pogrupować według innej strefy czasowej, wystarczy dołączyć do tabeli mostków strefy czasowej i połącz lokalne klucze daty / godziny z powrotem z tabelami wymiarów daty i godziny. Wypełniamy naszą tabelę mostów stref czasowych za pomocą kodu C # wywołanego z SSIS, ponieważ było to o wiele mniej skomplikowane niż wykonywanie rzeczy TZ bezpośrednio z SqlServer.

Widziałem DateTimeodrzucenie pomysłu magazynu z połączonym wymiarem, ale nie widziałem naprawdę wyraźnego powodu. Upraszczając nieco, oto tabela faktów, którą teraz tworzę:

Transactions
(
...
CreatedDateTimeSK         INT NOT NULL,  -- Four bytes per date...
AuthorizedDateTimeSK      INT NOT NULL,
BatchSubmittedDateTimeSK  INT NOT NULL,
BatchApprovedDateTimeSK   INT NOT NULL,
SettlementDateTimeSK      INT NOT NULL,
LocalTimeZoneSK           TINYINT NOT NULL  -- ...plus one byte for the time zone
)

Te DateTimepola dołączyć do stołu DateTime:

DateTimes
(
DateTimeSK   INT NOT NULL PRIMARY KEY,
SQLDate      DATE NOT NULL,
SQLDateTime  DATETIME2(0) NOT NULL,
Year         SMALLINT NOT NULL,
Month        TINYINT NOT NULL,
Day          TINYINT NOT NULL,
Hour         TINYINT NOT NULL,
Minute       TINYINT NOT NULL CHECK (Minute IN (0, 30)),
...
)

Rozdzielczość wynosi pół godziny, więc jest 48 zapisów dziennie, 350 400 w ciągu 20 lat - całkiem możliwe do zarządzania.

Data / czas zdarzenia są tłumaczone na UTC, gdy są przechowywane, ale dzięki LocalTimeZoneSKpolu i tabeli pomostowej możemy łatwo dołączyć, aby uzyskać czas lokalny:

TimeZoneBridge
(
DateTimeSK       INT NOT NULL,
TimeZoneSK       TINYINT NOT NULL,
PRIMARY KEY (DateTimeSK, TimeZoneSK),
LocalDateTimeSK  INT NOT NULL
)

Aby uzyskać transakcje utworzone dzisiaj, czas UTC:

SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN DateTimes AS CD ON T.CreatedDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

Aby uzyskać transakcje utworzone dzisiaj, w czasie lokalnym dla transakcji:

SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN TimeZoneBridge AS TZB ON T.CreatedDateTimeSK = TZB.DateTimeSK AND T.TimeZoneSK = TZB.TimeZoneSK
  INNER JOIN DateTimes AS CD ON TZB.LocalDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

Można ulec pokusie, aby uprościć zastępując TimeZoneSKz REALoffsetu (np -5,0 dla US Central Daylight Time), ale zepsuje jeśli niektóre daty / czasy dla rekordu faktycznie są w Daylight Saving Time, a niektóre nie.

Jeśli zdarzenia dla faktów mogą się zdarzyć w różnych strefach czasowych, takich jak przesyłka lub lot, potrzebujesz pola strefy czasowej dla każdej daty i masz do pięciu bajtów na datę.