Problemy związane z czasem

Opis problematyki

Przypuśćmy że mamy do czynienia z bazą operacyjną systemu CRM. W tej bazie mamy klientów firmy dokonujących zakupów różnych produktów naszej firmy. Klienci są przypisani do regionów – województw, miast, kodów pocztowych. Analitycy przeprowadzają analizy skuteczności reklam w tych obszarach. Jeśli klient zmieni adres, w bazie operacyjnej powinien znaleźć się oczywiście aktualny adres. Jeśli jednak zaktualizujemy również adres klienta w hurtowni danych, analizy będą wprowadzały w błąd. Przykładowo klient wcześniej mieszkał w obszarze X, przeprowadził się do obszaru Y. W obszarze X prowadzona była intensywna kampania reklamowa. Klient zrobił bardzo duże zamówienie w naszej firmie, a następnie przeprowadził się do obszaru Y w którym przykładowo była prowadzona inna kampania reklamowa. Jeśli adres klienta został zmieniony również w hurtowni bez zachowania jego wcześniejszego adresu, to analiza wskaże skuteczność której kampanii reklamowej i w jakim obszarze? Analiza wykaże że klient przyszedł do naszej firmy skuszony reklamą z obszaru Y a nie X. Jeśli więc będziemy przechowywać informacje o zmianach danych w czasie, taki problem nie wystąpi. Wyobraźmy sobie teraz że w bazie operacyjnej faktycznie zmieniamy adres na aktualny, ale w hurtowni mamy informację że adres z obszaru X obowiązywał do 1 czerwca 2016, a adres z obszaru Y obowiązuje od 2 czerwca 2016. Kampania reklamowa była prowadzona w okresie kwiecień-maj 2016. Czy w takiej sytuacji nasze analizy również będą wprowadzały w błąd? Z tych samych powodów z hurtowni danych nie powinniśmy usuwać informacji o osobach które przestały być naszymi klientami.

 

Rozwiązanie problemów z czasem

Zastępowanie zmienianych wartości nowymi

W tym rozwiązaniu stare wartości zastępowane są nowymi, jednak w związku z tym brakuje nam informacji o historycznych wartościach. Jest to rozwiązanie najprostsze i występujące najczęściej w bazach OLTP. Przykładowa struktura:

Informacje o klientach przechowywane są w jednej tabeli, ewentualnie adresy mogą zostać oddelegowane do osobnej tabeli. W obu wypadkach nie ma miejsca na przechowywanie danych archiwalnych i stare dane są zastępowane nowymi. Żeby zobrazować ten problem, poza tabelką klienci przygotowałem też tabelkę „zamówienia” w której umieściłem dwa zamówienia dokonane przez jednego pokazanego wcześniej klienta:

 

Dokonujemy teraz mini analizy danych która zaprezentuje nam sprzedaż wg miast:

Klient przeprowadza się teraz z Radomia do Sosnowca, aktualizujemy więc dane a następnie ponownie przeprowadzamy analizę sprzedaży:

Według analizy zamówień na sumę 3745 zł dokonali nie klienci z Radomia a z Sosnowca, co w tym przypadku nie jest prawdą. Dzieje się tak ponieważ nie przechowujemy informacji o zmianach, ani też danych historycznych.

 

Stosowanie kontroli wersji

Przy każdej zmianie atrybutów wiersza tworzony jest osobny rekord. Wszystkie atrybuty których nie zmienialiśmy pozostają nie zmienione. Tylko atrybuty podlegające aktualizacji mają nowe wartości. Pozostaje nam do rozwiązania problem identyfikacji aktualnego stanu i odróżnienia go od danych historycznych. Być może zaistnieje też konieczność wybrania wierszy które będą prezentowały stan informacji o kliencie we wskazanym czasie. Istnieje kilka metod na rozwiązanie tego problemu. Możemy dodać kolumnę z numerem wersji, dzięki czemu będziemy mogli łatwo wybrać najnowsze wersje danych, jednak nie będziemy mogli przeprowadzać analiz dotyczących wskazanego czasu. Możemy też dodać dwie kolumny z okresem obowiązywania stanu danych od i do. Zrobimy obie te wersje i porównamy.

Do tabeli klienci dodałem zarówno kolumnę z numerem wersji jak i kolumny z okresem obowiązywania stanu danych. Ponieważ w tabeli będziemy teraz przechowywać kilka wersji tego samego klienta, może pojawić się problem ze zgrupowaniem kilku wersji jednego klienta. Wartość w kolumnie ID_KLIENTA pozostanie przecież taka sama dla wszystkich wersji, więc ta kolumna nie może być kluczem głównym, ponieważ pojawiałyby się zduplikowane wartości. Kolumna ID_KLIENTA staje się więc kluczem technicznym służącym identyfikacji wersji tgo samego klienta, a do tabeli dodaję kolejną kolumnę ID która przejmie rolę klucza głównego. Na potrzeby tego przykładu dodałem też kolejną wersję danych klienta:

 

Pobieranie najnowszej wersji danych

Przypuśćmy teraz że chcemy przejrzeć aktualny stan danych dla wszystkich klientów. Przy takie jak wyżej zaprezentowana strukturze, mamy trzy możliwości dokonania tego. Możemy oprzeć wybór o najnowszy numer wersji:

select * from klienci kl where wersja=(select max(wersja) from klienci where id_klienta=kl.id_klienta);

Przy założeniu że ID będzie uzupełniane z sekwencji, a więc kolejne wersje tego samego klienta będą miały coraz większe numery ID możemy się posłużyć również tym polem:

select * from klienci kl where id=(select max(id) from klienci where id_klienta=kl.id_klienta);

I jeszcze wersja chyba najprostsza i najwydajniejsza jeśli chodzi o wykonanie zapytania – wykorzystanie pola OKRES_OBOWIAZYWANIA_DO. Przyjąłem założenie, że jeśli zmienia się stan danych dla klienta, w wierszu reprezentującym dotychczasową wersję uzupełniamy pole OKRES_OBOWIAZYWANIA_DO, a w wierszu reprezentującym wersję nową pozostawiamy to pole puste. Chcąc więc zobaczyć najnowsze wersje wszystkich klientów wystarczy wybrać wiersze z NULLem w polu OKRES_OBOWIAZYWANIA_DO:

select * from klienci where okres_obowiazywania_do is null;

 

Przeglądanie wersji danych obowiązujących we wskazanym okresie

Taką możliwość mamy tylko przy zastosowaniu pól określających okres obowiązywania wersji danych. Przykładowe zapytanie wybierające stan danych na dzień 26 maja 2015 roku:

select * from klienci

where

to_date('26-05-2015','dd-mm-yyyy') between

okres_obowiazywania_od and nvl(okres_obowiazywania_do,sysdate);

W rzeczywistości zapytania w hurtowniach obsługujących zmiany danych na przestrzeni czasu zawsze są zapytaniami przeglądającymi dane w stanie w określonym czasie, przy czym nawet jeśli tego czasu nie określamy przyjmowany jest czas „teraz”. Zastanów się nad prawidłowością zliczania klientów z wcześniej opisywanej tabeli w ten sposób:

select count(*) from klienci;

Takie zapytanie oczywiście zwróci błędny wynik, ponieważ w tabeli mamy jednego klienta ale w 2 wersjach. Należałoby więc zastosować zapytanie kształtu :

select count(*) from klienci where okres_obowiazywania_do is null;

Na koniec tego rozdziału chciałbym jeszcze zwrócić uwagę na jedną kwestię: jeśli stosujemy kolumny określające czas trwania stanu, to zakresy czasowe nigdy nie mogą na siebie nachodzić!