Obsługa stref czasowych w hurtowni danych

Zaczynamy projektować elementy składowe hurtowni danych i musimy być w stanie obsługiwać wszystkie strefy czasowe (nasi klienci pochodzą z całego świata). Po przeczytaniu dyskusji online (i książek) powszechnym rozwiązaniem wydaje się mieć osobny wymiar daty i godziny oraz znacznik czasu w tabelach faktów.

Jednak pytanie, na które trudno mi odpowiedzieć, brzmi: co właściwie ma dla mnie wymiar daty i godziny, biorąc pod uwagę moje wymagania dotyczące dynamicznej strefy czasowej? Wymiar czasu ma trochę więcej sensu, ale mam trudności z wymiarem daty. Ogólne podejście do projektowania wymiaru daty zwykle obejmuje takie właściwości, jak nazwa dnia, dzień tygodnia, nazwa miesiąca itp. Problem, który mam ze wszystkim, to jest o 23:00 we wtorek, 31 grudnia 2013 r. W UTC jest środa , 1 stycznia 2014 r. We wszystkich strefach czasowych po UTC + 2.

Więc jeśli będę musiał wykonać wszystkie te konwersje stref czasowych dla każdego zapytania (i raportu), jaki jest sens posiadania i przechowywania tych właściwości, których prawdopodobnie nigdy nie użyję (wydaje się)? Niektóre osoby sugerują tworzenie wierszy faktów dla każdej strefy czasowej, ale wydaje mi się to śmieszne. Musimy być w stanie przechowywać miliony rekordów każdego miesiąca.

Inni sugerują posiadanie tabeli mostu strefy czasowej, co choć ma sens, wydaje się również dodatkową złożonością i dodatkowymi połączeniami, aby osiągnąć coś, co moje aplikacje klienckie i raporty powinny z łatwością ustalić na podstawie daty (raportowanie będzie przede wszystkim oparte na Internecie gdzie istnieje niezliczona ilość bibliotek pomocnych w konwertowaniu, wyświetlaniu i formatowaniu dat).

Jedyne, co mogę wymyślić, to łatwość i być może wydajność grupowania według daty i godziny, ale jak kiepska jest praktyka grupowania według dat (korzystamy z MS SQL, ale będziemy sprawdzać miliony wierszy) lub powinniśmy rozważyć po prostu niezwykle proste wymiary daty i godziny, w większości z nie więcej niż godzinami, dniami, miesiącami i rokami, ponieważ większość literałów, takich jak poniedziałek, nie miałaby większego znaczenia, kiedy w grę wchodzą strefy czasowe?

Po pierwsze...

Rozdzielenie Datime/Timena Datewymiar i Timewymiar jest zdecydowanie właściwą drogą.

Aby zarządzać wieloma strefami czasowymi, musisz zduplikować DateKeyi TimeKey, aby mieć następujące elementy:

• LocalDateKey
• LocalTimeKey
• UtcDateKey
• UtcTimeKey

Mówisz...

Problem z tym wszystkim, że 23:00 we wtorek 31 grudnia 2013 r. W UTC to środa, 1 stycznia 2014 r. We wszystkich strefach czasowych po UTC + 2.

Mając 4 kolumny, które wymieniłem powyżej, będziesz mógł połączyć tabelę faktów z wymiarem Data i / lub Czas za pomocą aliasów tabeli (w terminologii Kimball te aliowane tabele wymiarów są znane jako „Wymiary do odgrywania ról”), więc masz coś takiego:

/*
    Assumes the following:
        - [DateLongName] has the format of this example "Tuesday, December 31, 2013"
        - [TimeShortName] has the format of this example "11:00 PM"
        - Both [DateLongName] & [TimeShortName] are strings
*/
select
    -- Returns a string matching this example  "11:00 PM Tuesday, December 31, 2013"
    localTime.TimeShortName + ' ' + localDate.DateLongName
    ,utcTime.TimeShortName + ' ' + utcDate.DateLongName
    ,f.*
from
    FactTableName  AS f

    -- Local Date and Local Time joins          
    inner join dbo.Date  AS localDate
        on localDate.DateKey = f.LocalDateKey

    inner join dbo.Time  AS localTime
        on localTime.TimeKey = f.LocalTimeKey 

    -- Utc Date and Utc Time joins    
    inner join dbo.Date  AS utcDate
        on utcDate.DateKey = f.UtcDateKey

    inner join dbo.Time  AS utcTime
        on utcTime.TimeKey = f.UtcTimeKey 

W zamknięciu...

Podczas budowania mart data, a nie bazy danych OLTP, generowanie czasu lokalnego i utc powinno odbywać się w twoim ETL , a NIE w żadnej aplikacji po stronie klienta z następujących powodów (oprócz lokalizacji czasu UTC do perspektywa czytelnika raportu):

• Obecność obliczeń we wszelkich zapytaniach nakłada na nie dodatkowe obciążenie wydajności, pomnożone przez liczbę uruchomień tego zapytania dla wszystkich posiadanych raportów (ma to znaczenie przy czytaniu milionów wierszy)
• Dodatkowe obciążenie związane z zapewnieniem poprawności obliczeń w każdym zapytaniu (szczególnie jeśli weźmiesz pod uwagę czas letni)
• Zapobiegaj skanowaniu zakresów indeksów, których częścią jest kolumna, ponieważ wykonasz obliczenia w kolumnie, które zmuszają zapytania do skanowania indeksów zamiast przeszukiwania (które są zwykle droższe, ponieważ każda strona danych musi zostać odczytana); ten znany jest jako nie- sargable .
  • Edytuj ze względu na komentarze: dotyczy to przeniesienia konwersji w dół do rzeczywistego zapytania .
• Korzystając z koncepcji dostępności dodatkowych dat i godzin UTC, nic nie stoi na przeszkodzie, abyś przyjął tę koncepcję i rozszerzył ją, dzwoniąc pod ten numer StandardisedDateKey, lub CorporateHQDateKeyzamiast tabeli dat UTC standaryzujesz w oparciu o inny standard uzgodniony biznesowo
• Posiadanie dwóch oddzielnych typów kolumn (lokalny i UTC), umożliwia porównanie obok siebie w odległości geograficznej. Pomyśl -> ktoś w Australii wchodzi rekord, który jest o czasie, zarówno lokalnym a UTC, ktoś w Nowym Jorku czyta raport z lokalną (Australia) Data i czas i reprezentacji New York daty i czasu UTC, co widząc, że coś ich australijski odpowiednik zrobił w środku dnia (czas australijski) w środku nocy ich czas (czas nowojorski). To porównanie czasu jest niezbędne w przedsiębiorstwach międzynarodowych.

Z góry przepraszam za zwięzłość tej odpowiedzi i planuję rozwinąć ją, gdy nie będę w pracy.

Z pewnością istnieją zalety posiadania tabel dat i godzin, ponieważ pozwalają one na łatwą agregację danych. W wielu przypadkach jest to najprostszy sposób sortowania według tego rodzaju rzeczy według miesiąca lub dni roboczych. Nie musi to jednak zastępować przydatności znacznika czasu. W twoim szczególnym przypadku znacznik czasu UTC. Po uzyskaniu znacznika czasu wystarczy zmienić go na czas lokalny w warstwie raportu lub prezentacji. Aby uniknąć skanowania zasięgu, upewnij się, że konwertujesz również zakres żądań na czas UTC.

Jeśli masz jakiekolwiek pytania lub komentarze, możesz je zadać.