Typy transformacji pól w ETL

Dalszy ciąg krótkiego wprowadzenia do ETL. Tym razem chciałbym Ci troszkę więcej powiedzieć o transformacji, a dokładnie o transformacji elementów ściąganych z źródła,


Transformacje pojedynczych elementów w procesie ETL dzielą się na transformacje pojedynczego pola oraz transformacje wielu pól. Transformacje pojedynczego pola są bardzo proste. Najczęściej jest to pewna modyfikacja starej wartości w nową wartość. Oprócz wartości możemy zmienić sposób prezentacji tej wartości. Można np. zamienić stopnie Fahrenheita na stopnie Celisiusza. Takie zamiany nazywają się transformacjami algorytmicznymi (algorithmic transformation), które wymagają obliczeń matematycznych.

Szczególnym przypadkiem transformacji pojedynczego pola jest transformacja wymagająca dodatkowej tabeli z odnośnika wartości (table lookup). Poniżej przedstawiona jest transformacja, która zamienia wartość „code” ze źródła na wartość „name”, wykorzystując przy tym dodatkową tabelę z wartościami. Taka transformacja może zamieniać skróty państw na całe nazwy państw. Głównym zadaniem tej transformacji jest zwiększenie czytelności docelowych danych.

Wśród transformacji wielu pół mamy transformacje „wiele do jeden” (zapisuje się w postaci M:1). Modyfikacja ta ma za zadanie scalanie wartości z różnych pól i zapisanie ich w jednym polu. Taką transformację stosuje się, w celu identyfikacji danego elementu. Przykładem może być tworzenia unikatowego klucza, który składa się z nazwy produktu, producenta oraz numeru dodatkowego.

Drugim typem transformacji wielu pól jest transformacja z jednego pola do wielu pól (1:M). Przykładem takiej transformacji może być w sytuacji kiedy wartość źródła składała się z wielu zmiennych. Powodem, dla którego przechowuje się kilka wartości w jednym polu jest wydajność jej metody archiwizacji w bazie danych. Jednak przy takiej transformacji istnieje niebezpieczeństwo zmiany formatu przechowywania wartości w źródle co może prowadzić do zakłócenia przebiegu procesu.

Oczywiście istnieje transformacja "wiele do wielu", ale jest to połączenie dwóch powyższych typów transformacji.

Przemilczana premiera PowerShell 4.0

Korzystałem z Windows 8 i miałem możliwość aktualizacji systemu do Windows 8.1. Po aktualizacji (oprócz problemów z Apple Boot Camp'em oraz powrotu starego-dobrego przycisku Start) to nie zauważyłem jakieś większej różnicy z poprzednią wersją.

Któregoś dnia wywołałem polecenie $PSVersionTable, a tu dostaje taki wynik:

Okazuje się, że mam PowerShell w wersji 4.0. Ta wersja należy do pakietu Windows Management Framework 4.0 i ten pakiet można pobrać stąd. Instalując Windows 8.1 dostajesz do tego PowerShell v4. Nowy PS jest domyślnie dostępny dla systemów operacyjnym Windows 8.1 oraz Windows Server 2012 R2.


Wiem, już dawno nie byłem na PowerShell Magazine, a polska grupa użytkowników PowerShell jeszcze ma do nadrobienia i mogłem sam zainteresować się nową wersja, ale dziwi mnie to, że premiera PS została przemilczano przez Microsoft i pozostawiona bez większego odzewu.


W najbliższych dniach sprawdzę co jest nowego w PS v4.

Wyciągnięta nauka z statystyki

Zastanawiam się, po co te wszystkie metody statycznej analizy danych, szukanie reguł i zależności, grupowanie elementów i inne bardziej lub mniej złożone metody analizy skoro możemy mieć błędne dane. Najważniejszą naukę jaką zdobyłem z analizy danych jest to, że metody analizy dobrze działają, ale dane zostały źle uzyskane.

Znasz to przysłowie:

Statystyka nie kłamie,
ale kłamcy liczą.

albo jak J.Stalin mawiał:
Nieważne, kto głosuje, ważne, kto liczy głosy.

Nawet w dzisiejszych czasach da się oszukać ludzi na wielką skalę.

Data Marty - uszczuplona hurtownia danych

Wcześniej pisałem o procesie ETL i w nim wspominałem o Data Martach jako zbiorze załadowanych danych. Data Marty są nazywane tematycznymi hurtownie danych. Są tworzone w celu zapewnienia wsparcia procesu podejmowania decyzji osobom odpowiedzialnym za konkretny obszar biznesowy. W przypadkach, gdy tylko część danych jest poddawana analizie, warto rozważyć użycie data martów. Data Mart jest generowany na bazie hurtowni danych i zawiera zagregowane dane zorientowane na jeden wybrany temat, które są często wyświetlane oraz łatwo i szybko dostępne dla użytkowników.

Hurtownia danych operuje na poziomie wszystkich dostępnych danych, przy czym tematyczna hurtownia danych jest używana z reguły przez jeden obszar danych w niej zawartych dotyczących jednego konkretnego tematu biznesowego. Dla banków może to być obszar produktów kredytowych lub obszar transakcji kontraktów krótkoterminowych

Typową i najczęściej spotykaną architektura w korporacjach jest jedna globalna hurtowna danych i bezpośrednio zależne i czerpiących z niej dane Data Marty.

Powyżej przedstawiony jest sposób działania Data Martów. Dostęp do nich mają tylko wyznaczone osoby (lub grupy ludzi). Dział raportujący ma dostęp tylko do danych potrzebnych do tworzenia i przeglądania raportów. Dział analiz ma dostęp do Data Martów do analizy oraz raportów. Natomiast grupa administracyjna ma dostęp do wszystkich obszarów danych, gdyż zarządzają tymi danymi.

Kluczowym problemem jest utrzymanie spójności z hurtownią danych. Najważniejsze te kwestie to definicja danych, sposób aktualizacji oraz zarządzanie danymi. Sposobem na ominięcie części problemów jest tworzenie data martu niezależnego od hurtowni danych. Zdarza się to najczęściej, gdy do utworzenia data martu wymagane jest dodatkowe źródło danych spoza hurtowni. Wtedy tematyczne hurtownie danych są zasilane i zarządzane przez procesy ETL (Extract-Translate-Load). Należy jednak pamiętać o tym, że tego typu architektura jest narażone na ryzyko niespójności w rozumowaniu danych.

Najczęstsze powody tworzenia data martów to:
- większa denormalizacja
- dane z zagregowanymi danymi
- dane ze specyficznego okresu czasowego istnienia tych danych ( np. wartość akcji z poprzedniego roku)
- dane dostępne tylko dla specyficznej grupy (dane tylko dla analityków, testerów lub administratorów)

Kodowanie w stylu Rammstein

Natrafiłem na zdjęcie przedstawiające kod, który się czyta w melodii utworu "du hast" zespołu Rammstein. Jako wielki fan tego zespołu bardzo mi się spodobał ten kod :)

Co to jest ETL ?

Jak zaczynałem swoją pracę przy bardzo dużym projekcie ETL, to na początku nie wiedziałem co to jest i do tej pory nie ma zbyt dużo materiałów na ten temat. Przedstawię Ci krótki wstęp do ETL.

Skrót ETL oznacza ekstrakcję (extract), transformację (transform) i ładowanie (load). Jest to metoda procesowania danych. ETL składa się z 3 etapów:
1) ekstrakcja danych ze źródła danych - proces ten determinuje bazowe źródła dla hurtowni danych.
2) transformacja danych - etap ten ma za zadanie standaryzować dane, filtrację oraz sprawdzanie reguł biznesowych.
3) ładowanie danych do docelowego zbioru danych - etap zapisu wygenerowanych danych.

Na rysunku przedstawiony jest ogólny schemat działania procesu ETL. Do danych wejściowych (source) należą aplikacje (m.in. bazy danych), dodatkowe pliki oraz inne pomocne dane (m.in. metadane). Proces ETL przetwarza te dane i zapisuje je do wyznaczonego zbioru danych (target). Docelowy zbiór danych jest określony przez projektanta i może to być hurtowania danych, data mart lub zbiór plików zapisanych na serwerze. W praktyce 80% czasu spędzonego na rozwijaniu systemu BI jest poświęcone na rozwijanie procesu ETL. Jest to najbardziej czasochłonne zadanie przy budowaniu hurtowni danych i jednocześnie wymaga najwięcej wiedzy i doświadczenia. Bardzo często proces ETL nazywany jest procesem integracji danych, natomiast narzędzia ETL nazywane są platformą integracyjną.

Pierwsza częścią - ekstrakcja danych ze źródła danych - jest to najbardziej wymagający etap całego ETL. Większość hurtowni danych integruje dane z różnych źródeł danych. Każde źródło danych może się różnić organizacją danych czy sposobem dostępu. Najczęściej spotykanym źródłem danych są relacyjne bazy danych oraz pliki w formacie CSV. Celem ekstrakcji danych jest przekonwertowanie danych do ujednoliconego formatu, który jest przystosowany dla procesu transformacji.

Etap transformacji jest częścią opisującą przepływ danych, który zawiera zaaplikowane reguły biznesowe oraz szczegółowy opis kalkulacji i mapowania między danymi. Niektóre dane wymagają bardzo mało, a nawet brak modyfikacji danych. W większości przypadkach etap ten składa się pezynajmniej z jednego z poniższych zadań:
- wyselekcjonowanie tylko niektórych kolumn danych z tabeli
- transformacja sposobu zapisu danych. Jeżeli dana jest zapisana w postaci binarnej (1-0) to można zapisać słownie (Tak – Nie)
- transformacja formatu zapisu danych. Część dat jest zapisana w formacie skoordynowanego czasu uniwersalnego (UTC) lub czasu lokalnego
- wyliczenie nowej wartości, która jest zależna od wartości innych zmiennych
- sortowanie według danego kryterium
- łączenie danych z innymi danymi z innych źródeł - wzbogacanie danych
- rozdzielenie kolumny na kilka kolumn
- usuwanie zduplikowanych danych
- agregacja danych. Można wprowadzić data rollup ( wiersz podsumowujący dane)
- tworzenie unikatowego klucza (surrogate key) dla wszystkich elementów
- dokonywanie transpozycji oraz tworzenie tabeli przestawnej. Jest to proces, w którym kolumny zamieniane są na wiersze.

Faza ładowania danych odnosi się do zapisu do specjalnego obiektu docelowego (najczęściej jest to hurtowna danych). Obiekt docelowy jest zależny od wymagań danego projektu. Najczęściej istniejące dane w hurtowni danych są nadpisywane przez nowo wygenerowane. Inne hurtownie danych są czyszczone i na nowo załadowane, ale takie rozwiązanie niesie ze sobą problem „pustych danych”, gdyby wystąpił problem w etapie ładowania danych.

Szukanie referencji w projektach

Na moje szczęście programuje w solucji (.sln), która posiada ponad 100 projektów (.csproj). Czasami zdarza się szukać projektu, który korzysta ze specyficznej biblioteki lub może być nie spójne w referencjonowaniu (jeden projekt jest zależny od paczki nuget, a drugi od biblioteki zapisanej na dysku).

Poniżej jest skrypt w powershellu, który dla danej wzorca nazw projektów przeszukuje wszystkie csproje i zwraca wszystkie referencje jakie są w tych plikach. Przeszukiwanie rozpoczyna się od ścieżki uruchomienia skryptu.

function Get-Reference
{
[CmdletBinding()]
Param(
     [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelinebyPropertyName=$True)] 
     [string] $projectNamePattern
)

BEGIN {
     Write-Host "Getting project References" 
     $projectFileExtension = ".csproj"
}

PROCESS{
    $searchingProjectPattern = $projectNamePattern + $projectFileExtension 
    $projectFullNames = Get-ChildItem  -Filter $searchingProjectPattern -Recurse | %{$_.FullName}

    foreach ( $projectFullName in $projectFullNames)
    {

        if( Test-Path $projectFullName)
        {       
            $projectName = [System.IO.Path]::GetFileNameWithoutExtension($projectFullName)
            [xml]$projectXml = Get-Content $projectFullName
            $itemGroups = $projectXml.Project.ItemGroup
            
            foreach ( $itemGroup in $itemGroups)
            {
                if($itemGroup.Reference -ne $null)
                {
                    foreach( $ref in $itemGroup.Reference)
                    {
                      $Object = New-Object PSObject -Property @{
                           RefPath = $ref.HintPath 
                           Name = $ref.Include
                           ProjectPath = $projectFullName
                           ProjectContent = $projectXml 
                           ProjectName = $projectName
                      }
                      $Object
                    }
                }
                
                if($itemGroup.ProjectReference -ne $null)
                {
                    foreach( $ref in $itemGroup.ProjectReference)
                    {
                      $Object = New-Object PSObject -Property @{
                           Name = $ref.Name 
                           RefPath = $ref.Include
                           ProjectPath = $projectFullname
                           ProjectContent = $projectXml 
                           ProjectName = $projectName
                      }
                      $Object
                    }
                }
            }
        }
    }
}

END{    
Write-host "`n`nHintPath => RefPath and Include => Name FOR Reference"
Write-host "Include => RefPath and Name => Name FOR ProjectReference"
}
}

A poniżej jest przykład użycia. Dajmy na to, że chcesz znaleźć referencje, które w ścieżce zawierają słowo Unity we wszystkich projektach, których nazwa zaczyna się od słowa ProjectA:

$projectNamePattern=  "ProjectA*"
$projectsPattern = @($projectNamePattern)
$projectsPattern  | Get-Reference | ? {$_.RefPath -ne $null} | ?{ $_.RefPath.Contains("Unity") }