Podstawy ETL z SSIS

Warsztat przeprowadzony w ramach Stacja.IT. Podstawy tworzenia procesów ETL z wykorzystaniem SSIS (SQL Server Integration Services)

1. Podstawy ETL Z pomocą SQL Server Integration Services Warsztaty Stacja.IT Warszawa, 17.10.2016.

2. Zanim zaczniemy Czy wszyscy mają zainstalowane SSDT? Czy wszyscy mają MySQL Connector /.NET? Czy wszyscy mają lokalną wersję SQL Server? Czy wszyscy mają HeidiSQL / SSMS / SQLyog / (inne podobne)?

3. Co będziemy robić Poznamy SSIS i zbudujemy podstawowy proces ETL Zasilimy bazę danymi dotyczącymi sprzedaży i obliczymy realizację celów sprzedażowych przez handlowców

4. Konstrukcja warsztatu 9:00 – 13:00 Pierwsza część warsztatów Utworzenie projektu i pakietów zasilających bazę danymi z MySQL Kontenery, pętle, mapowania, obsługa plików CSV/XML 13:00 – 14:00 Pizza 14:00 – 17:00 Druga część warsztatów Zasilanie bazy docelowej, obsługa błędów Logowanie, wdrażanie, uruchamianie, SSIS Catalog

5. Bartosz Ratajczyk Konsultant SQL Server Programista baz danych i aplikacji Prelegent MCSE: Data Platform, MCT http://bartekr.net | [email protected]

6. O Was Czy mieliście wcześniej do czynienia z budowaniem procesów ETL? A z SQL Server Integration Services? A z SQL Server? Czego się spodziewacie po tym warsztacie?

7. Co to SSIS SSIS, czyli SQL Server Integration Services Narzędzie do realizacji procesów ETL Dostarczane wraz z całą platformą danych SQL Server od wersji SQL Server 2005 (wcześniej jako DTS)

8. ETL Extract Transfer Load Czyli przenieś dane stąd -> tam i przekształć je po drodze

9. ELT Extract Load Transfer Czyli przenieś dane stąd -> tam a potem je przekształcaj

10. Gartner Magic Quadrant 2016 https://www.gartner.com/doc/reprints?id=1-3CUJXZO&ct=160727&st=sb

11. Gartner Magic Quadrant 2015 https://www.gartner.com/doc/reprints?id=1-2KDMO20&ct=150731&st=sb

12. Narzędzia

13. SQL Server Data Tools (SSDT) Zestaw narzędzi zintegrowanych z Visual Studio Ale może też działać samodzielnie Instaluje wtedy „ Integrated Shell” Do wersji SQL Server 2008R2 znane jako Business Intelligence Development Studio (BIDS) Od wersji SQL Server 2012 pewne zamieszanie z nazewnictwem (SSDT, SSDT-BI) Ostatecznie rozwiązane w 2015 roku(?) i już tylko jedno SSDT

14. Rozszerzenia SSDT może być rozszerzane przez budowę własnych komponentów Niektóre z nich są dostarczane przez Microsoft, a niektóre przez firmy trzecie Przykłady Azure Pack (Microsoft) – dostęp do części usług Azure Task Factory ( Pragmatic Works) – zestaw dodatkowych kontrolek SSIS Community Tasks and Components https://ssisctc.codeplex.com/

15. Inne narzędzia dtexec - narzędzie do uruchamiania pakietów SSIS dtexecui == dtexec + GUI IsDeploymentWizard – wdrażanie projektów dtutil – wdrażanie pakietów

16. Elementy składowe ETL w SSIS Czyli z jakich klocków budujemy przetwarzanie

17. Rozwiązanie + Projekt

18. Projekt

19. Pakiety

20. Managery połączeń

21. Pozostałe

22. Budowa pakietu

23. Control flow

24. Data flow

25. Event handlers

26. Parametry i zmienne

27. Logowanie

28. SSIS Toolbox

29. O pakiecie raz jeszcze Pakiet to plik XML SSDT pomaga tylko go przygotować Ale jeśli się uprzeć, to można go stworzyć ręcznie (może jednak nie)

30. Nasze zadanie

31. Badamy czy realizowane są cele sprzedażowe Stworzymy proces zasilające bazę danych: danymi o sprzedawcach (i ich miejscu w strukturze organizacyjnej) danymi o produktach i grupach, do których należą danymi sprzedażowymi nałożonymi celami sprzedażowymi A potem obliczymy każdemu sprzedawcy realizację jego celów

32. Model danych Tutaj diagram zależności między tabelami (z SSMS)

33. Źródła danych Baza MySQL Pliki CSV, TXT Pliki XML

34. Warstwy Nie zasilamy danych od razu do bazy docelowej Wykorzystujemy warstwę „ Stage ” Taka lokalna kopia danych źródłowych Może przechowywać albo kompletne zbiory, albo „przyrosty” Nie chcemy obciążać systemów źródłowych Rozbijając zasilanie na warstwy łatwiej śledzić przepływ danych Dodatkowa baza na przechowywanie metadanych zasilania

35. Po co nam metadane zasilania? Pozwalają śledzić procesy zasilające tak jak my chcemy Dają pogląd z iloma źródłami danych mamy do czynienia Prezentują ilości przetwarzanych rekordów Ustawiony poziom wbudowanego logowania pakietów nie prezentuje wystarczających informacji

36. Nasz model metadanych

37. I nasz model relacyjny (dane docelowe)

38. Standardy nazewnictwa http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/29/suggested-best-practises-and-naming-conventions.aspx

39. ZADANIE 1 Budowa podstawowego projektu i pakietu SSIS

40. Cel zadania Poznać strukturę projektu SSIS i sposób tworzenia jego pakietów Zasilić bazę danymi sprzedawców i prostej struktury organizacyjnej Zapisać informacje o przetwarzaniu w bazie metadanych Projekt i pierwszy pakiet tworzymy wspólnie. Pozostałe pakiety to Wasza samodzielna praca.

41. Źródło danych – baza MySQL

42. Wada sterownika MySQL .NET Należy użyć polecenia SQL, np. SELECT * FROM tabela

43. Tego problemu nie ma z ODBC

44. START (Zadanie 1)

45. Trochę więcej o komponentach Czyli co tam można znaleźć w SSIS Toolbox

46. Elementy Control Flow

47. Precedence constrains

48. Kontenery

49. Elementy Data Flow

50. ZADANIE 2 Zasilanie bazy danych informacjami z plików

51. Cel zadania Zasilić bazę informacjami z plików CSV, TXT i XML Produkty i grupy produktów: pliki XML Cele sprzedażowe: pliki TXT Informacje sprzedażowe: pliki CSV Zapisać informacje o przetwarzaniu w bazie metadanych

52. XML Source dla jednego pliku

53. Wykorzystanie kontenera ForEach

54. Parametryzacja kontenera ForEach

55. Parametryzacja XML Source

56. Flat file connection manager Utwórz lokalny manager połączenia do plików (będzie można użyć zmiennych)

57. Haczyki Pliki ze sprzedażą wczytujemy w pętli Musimy użyć nazwy pliku jako parametru w managerze połączeń Pliki produktów mogą mieć nieprawidłowy schemat XSD Musimy nadać prawidłowy typ danych dla ProduktId (np. unsignedShort )

58. START (Zadanie 2)

59. PRZERWA NA PIZZĘ

60. ZADANIE 3 Zasilanie danych docelowych, debugowanie, obsługa błędów

61. Cel zadania Zasilamy na podstawie danych w Stage docelową bazę danych W Stage są dane niezupełnie zgodne z naszym systemem relacyjnym Musimy przetworzyć dane w locie Albo do postaci pośredniej i z niej do docelowej Po zasileniu obliczamy realizację celów Baza relacyjna zawiera już część danych Definicje okresów sprzedażowych Cele sprzedażowe

62. Jeszcze raz model relacyjny

63. Zasilanie bazy relacyjnej Wykorzystujemy odrębny projekt z predefiniowanymi ustawieniami Zasilamy danymi, które pobraliśmy do Stage Jedna tabela docelowa == jeden pakiet Pamiętamy o utworzeniu metadanych przepływów

64. Wykorzystanie transformacji Lookup Użyjemy do mapowania danych źródłowych na docelowe Używamy domyślnego ustawienia Full cache Przekierujemy braki dopasowani dalej Mapowania: Przypisanie identyfikatora produktu po jego kodzie Przypisanie ceny jednostkowej produktu Przypisanie sprzedawcy po loginie Przypisanie sprzedawcy do punktu sprzedaży po identyfikatorze

65. Transformacja Lookup – ustawienia ogólne

66. Transformacja Lookup – źródło danych

67. Transformacja Lookup – mapowanie kolumn

68. Transformacja Lookup - wyjście

69. Przepływ danych - Sprzedaż

70. START (Zadanie 3)

71. Sprawdzenie realizacji celów SELECT * FROM dbo . vRealizacjaCelow WHERE ( RealizacjaCeluIlosc < 100 AND CzyCelIlosciowy = 1 ) OR ( RealizacjaCeluWartosc < 100 AND CzyCelWartosciowy = 1 ) ORDER BY Nazwisko , Imie , OkresId , Nazwa

72. OBSŁUGA BŁĘDÓW I analiza przepływających danych

73. Debugger (Control Flow )

74. Data Viewer (Data Flow )

75. Obsługa błędów (Control Flow ) Precedence Constraints ( Completion , Failure ) Event Handlers

76. Obsługa błędów (Data Flow ) Wykorzystanie Error Output do przekierowania do innego zbioru

77. ZADANIE 4 / DEMO Wdrażanie projektów SSIS na serwer docelowy , wykorzystanie SQL Agent

78. Sposoby wdrażania projektów SSIS Bezpośrednio z SSDT Instalacja pliku . ispac z poziomu SSMS Wykorzystanie IsDeploymentWizard

79. Uruchamianie z poziomu SQL Agent

80. PODSUMOWANIE

81. Kilka słów o czym było Zbudowaliśmy rozwiązanie ETL w SSIS Poznaliśmy struktury projektu SSIS i najczęściej używane kontrolki Znamy podstawy obsługi błędów Wiemy jak wdrażać projekty na serwer docelowy

82. Czego zupełnie nie ruszaliśmy (a o czym warto na koniec wspomnieć choć słowem) Transakcje Wdrażanie pojedynczych pakietów Zasilenia przyrostowe MDS, DQS

83. Chyba mi się spodobało – co dalej? Jakie są opcje dalszego uczenia się SSIS Oficjalne kursy Microsoft 20463 Implementing a Data Warehouse with Microsoft® SQL Server® 2014 20767 Implementing a SQL Data Warehouse Książki (np. Training Kit) Wziąć udział w projekcie Użyć Import/Export Wizard , zapisać pakiet i zacząć go modyfikować

84. 23 września - Od SASSa do LESSa - czyli ulepszanie CSSa - KRAKÓW 24 września - Clean Code + Wzorce Projektowe - ŁÓDŹ 24 września - Wprowadzenie do Machine Learning z wykorzystaniem języka Python - WARSZAWA 1 października - Wprowadzenie do technologii Cloud (Heroku, Google App Engine , Amazon AWS) - WARSZAWA 7 października - Pair Programming - WARSZAWA Nadchodzące warsztaty:

85. Chcesz wiedzieć więcej? Szkolenia pozwalają na indywidualną pracę z każdym uczestnikiem pracujemy w grupach 4-8 osobowych program może być dostosowany do oczekiwań grupy rozwiązujemy i odpowiadamy na indywidualne pytania uczestników mamy dużo więcej czasu :)

86. Szkolenie dedykowane dla Ciebie Interesuje Cię tematyka warsztatu? Zapoznaj się z programami szkoleń: Projektowanie hurtowni danych z wykorzystaniem pakietu Pentaho Business Intelligence Techniczne aspekty budowania wydajnych procesów ładowania hurtowni danych (ETL) z wykorzystaniem Pentaho Data Integration (PDI)

87. Wspierają nas