Hogyan kezeljük az időzónákat az adattárházban?

Adott egy multinacionális vállalat, amely eltérő időzónájú országokban is értékesít. A kérdés az, hogy milyen időponton szerepeltessük az eseményeket az adattárházban? Lokális időponton, vagy valamilyen egyezményes (pl.: UTC) időpontra konvertáltan? Esetleg mindkettőn?

Időzónák

időzónák

A kérdés megválaszolásához nézzük meg mit szeretnének a felhasználók. Vegyünk példának egy légitársaságot (csak fiktív példa, a valóság nem ilyen :-) ):

  • A marketing látni szeretné, hogy hányan repültek reggel, délelőtt, délután, stb. Ehhez lokális időpontra összegezve kell kimutatnunk az indulási időpontokat
  • Ugyanakkor mindezek mellett a kontrollingnak szüksége van olyan adatokra is, amelyekből megállapíthatják, hogy mennyi időt töltöttek repüléssel a gépek. Ehhez viszont olyan időpontra van szükségük, amely az időzónáktól függetlenül, egységesen mutatja a felszállási és leszállási időpontokat. (UTC idő)

Látszik tehát, hogy bizonyos esetekben a lokális és az UTC időpont is szükséges lehet. Ha ezt elfogadjuk, akkor már csak egy kérdés maradt:

Hogyan kezeljük mind a két időpontot?

  • a ténytáblában 2 külön oszlopban tárolhatnánk a két időpontot (lokális idő; UTC idő)
  • csak a lokális időpontot tárolnánk le, és mellé eltárolnánk az időzónát is.

Kezdjük ez utóbbival:

1. Lokális idő + időzóna

Ha az időzónás tárolás mellett döntenénk, akkor lekérdezés időben kéne a felhasználóknak konvertálni az időpontokat UTC időre.

2. Lokális idő + UTC idő

Ha viszont a betöltés során letároljuk mindkét időpontot, akkor a felhasználók mentesülnek az állandó konvertálástól. Igaz, hogy így vesztünk némi helyet a lemezen, de nem feltétlenül a helytakarékosságra kell optimalizálnunk...

Konklúzió: Célszerű mindkét időpontot letárolni, mert ez az ami nagyobb elégedettséggel tölti el felhasználóinkat. Persze csak akkor, ha az UTC idő tényleg szükséges...

Kővári Attila - BI projekt

hozzászólás

Normálisabb adatbázis-kezelők (értsd, ami alatt érdemes adattárházat építeni) mind rendelkeznek olyan adattípussal, ami eltárolja az időzónát is. Ha külön oszlopban kell a kettőt eltárolni, az már régen rossz.

Szia Tamás, Az időzónát is tartalmazó adattípusok (pl datetimeoffset az SQL Server esetén) használatát tudatosan kerüljük az adattárház építések során. Ennek okai a következők: 1) Az adattárházakban mesterséges kulcsokat használunk a dátum, idő és egyéb adattípusok helyett. Nem használunk se datetime, se datetime2 se datetimeoffset és egyéb dátum/idő típusú adattípus. Ez nem az adatbázis-kezelő korlátja, hanem tudatos tervezési döntés. (A miértekről itt olvashatsz: https://www.biprojekt.hu/blog/Miert-hasznaljunk-mesterseges-kulcsot-az-ido-dimenziaban.htm) 2) Az adattárházakat, illetve pontosítok: a csillagsémás adattárházakat (amelyekről e blogban olvashatsz) nem a helytakarékosságra, hanem az egyszerű lekérdezhetőségre tervezzük. Sok információt redundánsan tárolunk, csak azért, hogy az üzleti felhasználók munkáját könnyítsük. Ha az üzleti felhasználóknak fontos, hogy ugyanazon mutatót UTC és lokális időpont szerint is lekérdezzék, akkor előkészítjük nekik ennek lehetősét. (még akkor is, ha tudjuk, hogy egy időzónát is tároló adattípus választása esetén lekérdezhető mind az UTC, mind a lokális időpont is) 3) Az adattárházakban konform dimenziókat használunk, azaz egy vevő, termék, stb. dimenziót építünk és minden ténytáblát ezzel a közös dimenzióval dimenzionálunk. Legyen szó a teljesítési, szállítási vagy akár születési dátumról is. Ez utóbbi esetben semmilyen információval nem bírna egy döntéshozónak például a dolgozó időzóna szerint bontott születési dátuma. Márpedig ha a konform idődimenziónk tartalmazná az időzónát is, akkor ezt a születési dátumnál is figyelembe kéne vennünk. Összefoglalva: Az időzónát is tartalmazó dátum/idő adattípus kerülése az adattárházakban nem az adatbázis-kezelő korlátokból ered, hanem tudatos tervezési döntés eredménye. Üdv, Attila

Szia Attila, A helyi idő és az egyezményes idő tárolása azért is lehet érdekes mert a megfeleltetés az időben változhat. Pl. a nappali időzóna (aka DST) használata vagy nem használata évenként változhat. A legtöbb beépített konverter pedig csak az aktuális szabályt ismeri az évekkel korábbit nem. Így előállhat, hogy a múltban más időpontot számol, mint amit akkor ténylegesen használtak. A datetime tároláshoz annyit hozzátennék, hogy az adatbáziskezelő a datetime formátumot eleve 8 byte-os integer-ként tárolja(bázishoz viszonyított eltelt nap, illetve tized- vagy sima másodperc), tehát az már eleve egy mesterséges kulcs. Ami ráadásul jól indexelhető és nagyon hatékony beépített konverterek állnak rendelkezésre ami kellően hatékonnyá teheti a keresést (Erről jut eszembe. Tábla particionálásról írtál már? Idő-dimenziójú adatoknál nagyon fontos és hatékony teljesítményjavítási lehetőség). Az egyezményes időnél szerintem a datetime jól működik. A helyi időnél ez már fogósabb lehet a fenti probléma miatt. üdv Tamás

Kedves Attila! Köszönöm az ismertetőt, de esküszöm láttam már adattárházat. És képzeld, láttam idő dimenziót is. Sőt, még denormalizálást is. De azért kösz, jó ezeket újra és újra látni. Azonban azt te sem gondolod komolyan, hogy egy időpontot (év-hónap-nap óra-perc-századmásodperc) mesterséges kulccsal fogok kötni egy datetime dimenzióhoz. Amit te írsz, az dátum esetén megállja a helyét (én is barátja vagyok a YYYMMDD formátumú mesterséges kulcsoknak), de KÖNYÖRGÖM egy másodperc pontosságú időpontot ugyan már ne számként tároljunk el. Teljesen igazad van, hogy a dátumot, esetleg óra alapú időpontot lehet denormalizálni (mer frankó kötni az idődimenzió táblához), de az IDŐPONTOT (mert ezt a szót használtad) ugyan ne kelljen már leírnom kétszer halál rendundásan a táblában, amikor az összes BI eszköznek meg tudod mondani, hogy az adott mezőt UTC vagy LTZ szerint szedje ki. :)

Szia Tamás, Ennek a blognak a hangvétele olyan, hogy azok számára is érthető legyen, akik csak most kezdenek ismerkedni a témával. Ez óhatatlanul magával hozza azt is, hogy másoknak esetleg túl szájbarágós, vagy túl „ismertetős” lehet. Ha megbántottalak vele, nem volt szándékos és elnézésedet kérem érte. Üdv, Attila.

Semmi ilyesmi, én kérek elnézést, ha túlreagáltam - sajnos ilyen a fórumozos stílusom, kicsit jobban figyelek

Szia Tamás, Köszi a hozzászólást. Az „óraátállítások” követése időzónánként tényleg nem lehet egy egyszerű feladat :-) Partícionálás? Nem írtam róla. Legalábbis összefoglaló cikket nem. De tény, hogy sokat javít a performancián. Datetime? Nem csak teljesítmény okai vannak a DateTime adattípus mellőzésének, hanem mások is. Az adattárházak betöltésekor sokszor találkozunk olyan dátumokkal, amelyek a valóságban nem léteznek. Ilyenek például a különböző forrásrendszerekből származó (hibás) dátumok, mint pl a 20110231, vagy a 13. hónap, vagy egy ismeretlen dátum. Ezeket mind-mind szerepeltetni szeretnénk az adattárházban, de nem tudjuk dátum típusú adatokkal helyettesíteni őket. Ezért használunk helyettük pl. YYYYMMDD típusú egész számokat. Így lehetőségünk van az ismeretlen dátumokat helyettesíteni pl. egy -1-es kulccsal.

Új hozzászólás