Hurtownie danych a bazy danych
This presentation is the property of its rightful owner.
Sponsored Links
1 / 35

Hurtownie danych a bazy danych PowerPoint PPT Presentation


  • 130 Views
  • Uploaded on
  • Presentation posted in: General

Hurtownie danych a bazy danych. Przygotowa ł Lech Banachowski na podstawie: Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, McGrawHill, 2000 (ksi ążka i slide’y). Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wyd. PJWSTK, 2007. Wprowadzenie.

Download Presentation

Hurtownie danych a bazy danych

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Hurtownie danych a bazy danych

Hurtownie danych a bazy danych

  • Przygotował Lech Banachowski na podstawie:

  • Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, McGrawHill, 2000 (książka i slide’y).

  • Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wyd. PJWSTK, 2007.


Wprowadzenie

Wprowadzenie

  • Firmy i organizacje muszą dokonywać analizy aktualnych i historycznych danych aby zidentyfikować użyteczne wzorce i trendy w celu wspomagania strategii rozwojowych ich firmy lub organizacji.

  • Analiza jest często złożona, ma charakter interakcyjny i odkrywczy, dotyczy bardzo dużych zbiorów danych tworzonych w wyniku integracji danych pochodzących z różnych części firmy; analizowane dane nie zmieniają się (tylko przybywa ich w czasie).

    • Takie przetwarzanie danych nazywa się On-Line Analytical Processing (OLAP).

    • Poprzednio rozważaliśmyOn-line Transaction Processing (OLTP) - transakcje złożone z “krótkich” zapytań i modyfikacji.


P rzyk ad owe p ytania istotne dla os b podejmuj cych decyzje

Przykładowepytaniaistotne dla osóbpodejmujących decyzje

  • Które produkty przynoszą firmie najwięcej zysku?

  • Którzy klienci przynoszą najwięcej zysku? Co mają ze sobą wspólnego? Jak często korzystają z usług bezpośredniej pomocy telefonicznej?

  • W których regionach kraju firma osiąga największe zyski, z jakiego rodzaju działalności te zyski pochodzą?

  • Jakie usługi są najbardziej pożądane dla klientów firmy?

  • Kiedy powinno się wprowadzić na rynek nowe produkty?

  • Które produkty powinno się wycofać z rynku?

  • Którzy dostawcy są najmniej pewni?


Hurtownie danych a bazy danych

Trzy komponenty

  • Hurtownia danych: Zintegrowane dane pochodzące z wielu źródeł:

    • Ładowanie nowych danych z wielu źródeł.

    • Integracja semantyczna (np. uzgodnienie walut, miar, formatów).

  • OLAP:

    • Złożone zapytania i perspektywy SQL.

    • Zapytania podobne do operacji na arkuszach kalkulacyjnych i na wielowymiarowych danych.

    • Zapytania interakcyjne i “online”.

  • Data Mining: Eksploracyjne wyszukiwanie interesujących trendów i anomalii.


Hurtownia danych

OLAP

ZEWNĘTRZNE ŹRƠDŁA DANYCH

Hurtownia danych

EXTRACT

TRANSFORM

LOAD

REFRESH

  • Zintegrowane dane obejmujące długie okresy czasu, często rozszerzone o informacje sumaryczne.

  • Rozmiar w gigabajtach i terabajtach.

  • Oczekuje się szybkiej odpowiedzi na złożone zapytania – dlatego jest konieczne wcześniejsze przetworzenie danych.

  • Modyfikacje danych praktycznie nie występują.

HURTOWNIA

DANYCH

Repozytorium

metadanych

WSPOMAGA

DATA

MINING


P odej cie

Podejście

  • Projektując hurtownię danych stosujemy inne podejście niż przy projektowaniu baz operacyjnych. Zamiast:

    • “Po co przechowywać miesięczne salda kont bankowych, skoro można je wyliczyć mając zapis ciągu wykonanych na nim operacji?”

  • stawiamy pytanie:

    • “Czemu nie wyliczyć raz i nie przechowywać miesięcznych stanów kont, skoro 90% wykonywanych analiz wymaga właśnie tak przygotowanych danych?”

  • Dobrze zaprojektowane wstępne przetworzenie danych może istotnie przyśpieszyć czas wykonywania 90% analiz!


M etody stosowane w hurtowni danych aby zmniejszy z o ono oblicze

Metody stosowane w hurtowni danych aby zmniejszyć złożoność obliczeń

  • Agregacje - wstępne wyliczenie pewnych miar przydatnych w późniejszych analizach.

  • Podział na partycje - podział tabel na części tak by zmniejszyć rozmiar danych, które trzeba będzie przeczytać w trakcie wykonywania analiz ewentualnie tak aby przetwarzać dane równolegle.


Hurtownie danych a bazy danych

Tematy

  • Dane w hurtowni danychgrupuje się według tematów, np.

    • finanse,

    • produkty,

    • pracownicy,

    • klienci,

    • sprzedaż.

  • Typowym pytaniem zadawanym przyanalizie danych jest:

    • Jakie są dane o ... w rozbiciu na ... ? np.

      • dane o sprzedaży tegorocznej i ubiegłorocznej w rozbiciu na produkty i miesiące roku;

      • dane o zyskach w rozbiciu na regiony kraju i rodzaj działalności;

      • dane o obrotach w rozbiciu na poszczególnych klientów i sektory rynku.


Wielowymiarowy model danych

Wielowymiarowy model danych

  • Mając ustalony temat, wyróżniamy dla niego dwa rodzaje danych:

    • wielkości analizowane (fakty, miary) - dane ilościowe opisujące pewne fakty: sprzedaż, zyski, obroty,

    • wielkości klasyfikujące (wymiary) - dane klasyfikujące opisywane fakty wg okoliczności ich zaistnienia - czas, miejsce, osoba itp.

  • Stąd wynika, że każdy fakt istnieje w wielowymiarowej przestrzeni np. fakt pojedynczej sprzedaży istnieje w wielowymiarowej przestrzeni, w której poszczególne wymiary to:

    • czas,

    • struktura sprzedaży,

    • struktura klientów,

    • struktura produktówitp.


Wielowymiarowa kostka

8 10 10

pid

11 12 13

30 20 50

25 8 15

1 2 3

cid

Wielowymiarowa kostka

sprzedaż

mid

cid

pid

  • Kolekcja liczbowych miar,które zależą od szeregu wymiarów.

    • Np. miara Sprzedaż, wymiary: Produkt (klucz: pid), Miejsce (klucz: mid) i Czas (klucz: cid).

Przekrój kostki

mid=1:

mid


Hurtownie danych a bazy danych

Hierarchie wymiarów

  • W naturalny sposób obrazują drogę przejścia od danych szczegółowych do coraz bardziejogólnych.

  • Dla wymiaru czasu:

    • dzień -> miesiąc -> kwartał -> rok; dzień -> tydzień -> rok

  • Dla wymiaru geograficznego:

    • miasto -> województwo -> kraj; oddział -> kraj

  • Dla wymiaru produktów:

    • opakowanie -> produkt -> grupa produktów

  • Hierarchie nie muszą być jednoznaczne np. jeden oddział firmy może obsługiwać dwa województwa, oraz jednocześnie może istnieć województwo z dwoma oddziałami.


Schemat gwiazda

Schemat gwiazda

W schemacie tym mamy jedną tabelę faktów w środku układu i zbiór tabel wymiarów, każdaz nichopisuje jeden wymiar. Identyfikatory wartości wymiarów stanowią klucze obce w tabeli faktów.

Przykład: tabela faktów sprzedaży w podziale względem wymiarów struktury sprzedaży, czasu, wymiaru geograficznego i wymiaru produktów.


Hurtownie danych a bazy danych

Tabele w hurtowni danych (c.d.)

  • Tabele wymiarów mogą posłużyćdo znajdowania odpowiedzi na pytania dotyczące klasyfikacji danych bez sięgania w ogóle do tabeli faktów, np.

    • Na jakie regiony geograficzne zostały podzielone dane?

    • Jakie województwa zawiera dany makroregion?

    • Ile pozycji będzie zawierał raport w rozbiciu na oddziały firmy?

  • Gdzie zapisywać wyniki operacji agregujących (podsumowujących)?

    • Albo używać tego samego zestawu tabel dodając w każdej tabeli wymiaru atrybut poziomu agregacji danych względem którego są agregowane wartości analizowane,

    • albo zapisywać wyniki agregacji w osobnych tabelach.


Wielowymiarowe tablice molap

Wielowymiarowe tablice - MOLAP

  • Reprezentacja kostki wielowymiarowej za pomocą wielowymiarowej tablicy ( wielowymiarowego arkusza kalkulacyjnego). Przy dużej liczbie elementów trzeba przechowywać zawartość tej tablicy na dysku i tylko częściami sprowadzać do pamięci wewnętrznej w celu wykonania obliczeń.

  • Każdą tabelę wymiaru można też reprezentować za pomocą tablicy i ze względu na niewielki jej rozmiar i częste użycie można ją przechowywać w pamięci wewnętrznej.


Operacje

Operacje

  • pivoting (zestawienie krzyżowe) – wskazanie do analizy: miary np. sprzedaży oraz dwóch wymiarów np. miasta i roku oraz przypisaniu wartościom tych wymiarów pewnej agregacji wartości wybranej miary np. sumarycznej sprzedaży w danym mieście w danym roku.


Operacje1

Operacje

  • drill-down – rozwijanie – rozwinięcie danego wymiaru do kolejnego elementu tego wymiaru – np. mając wyniki sprzedaży w rozbiciu na lata, chcemy poznać wyniki sprzedaży w rozbiciu na miesiące. Operacją odwrotną jest roll-up - zwijanie od rozbicia na bardziej szczegółowe elementy wymiaru do bardziej ogólnych.

  • slice-and-dice – wycinanie – operacja projekcji na wybrany podzbiór wymiarów dla wybranych wartości innych wymiarów np. dane o sprzedaży poszczególnych produktów (projekcja na wymiar produktów) w ubiegłym roku (selekcja na wymiarze czasu).


Implementacja hurtowni danych w oracle histogramy

IMPLEMENTACJA HURTOWNI DANYCH W ORACLEHistogramy

  • Dystrybucja wartości w kolumnie obliczana za pomocą instrukcjiANYLYZE TABLE:

    • ANALYZE TABLE Sprzedaż

    • COMPUTE STATISTICS FOR COLUMNS Wartość_sprzedaży

    • SIZE 10;

    • -- liczba przedziałów, na które dzieli się wartości sprzedaży.

  • Histogramy są używane przez optymalizator zapytań. Można je odczytać z USER_HISTOGRAMS i USER_TAB_COLUMNS.


R wnoleg e obliczenia

Równoległe obliczenia

  • Tworząc tabelę możemy określić jej stopień zrównoleglenia:

    • CREATE TABLE XXX(....)

    • PARALLEL (DEGREE 8);

    • -- stopień zrównoleglenia zapytań dla tej tabeli

  • Proces przyjmujący w serwerze bazy danych zgłoszenie użytkownika starasię przyporządkować do obsługi zapytania z tą tabelą liczbę procesów serwera równą temu stopniowi.

  • Zrównolegleniu może ulec ładowanie danych do tabeli.


Obiekty z partycjami partycjonowane

Obiekty z partycjami (partycjonowane)

  • Na różnych stacjach dyskowych - zrównoleglenie we/wy dyskowego

    • CREATE Klienci(

    • Id NUMBER(5) PRIMARY KEY,

    • Kraj CHAR(2), ......)

    • PARTITION BY RANGE (Kraj)

    • -- klucz partycji: najlepiej aby nie ulegał modyfikacji

    • (PARTITION p1 VALUES LESS THAN ('C')

    • TABLESPACE Data01;

    • PARTITION p2 VALUES LESS THAN ('I')

    • TABLESPACE Data02;

    • ...

    • PARTITION p19 VALUES LESS THAN MAXVALUE

    • TABLESPACE Data19);


Indeksy bitmapowe

Nazwisko

Płeć

Stanowisko

Kruszewska

M

Dyrektor

Jankowski

M

Sprzedawca

Malinowski

M

Sprzedawca

Gazda

K

Kasjer

Wiśniewski

M

Kasjer

Bojanowska

K

Sprzedawca

Indeksy bitmapowe

CREATE BITMAP INDEX Ind_P_OsONOsoby(Płeć);

CREATE BITMAP INDEX Ind_P_OsONOsoby(Stanowisko);

Indeks na Płeć Indeks na Stanowisko

Nr wiersza M K D S K

-----------------------------------------

1 1 0 1 0 0

2 1 0 0 1 0

3 1 0 0 1 0

4 0 1 0 0 1

5 1 0 0 0 1

6 0 1 0 1 0

SELECT Nazwisko FROM Osoby WHERE

(Płeć = 'K' AND Stanowisko ='K')OR

(Płeć = 'M' AND Stanowisko = 'D')

sprowadza się do wykonania trzech operacji na wektorach bitowych:

([000101] AND [000110]) OR ([111010] AND [100000]) = [100100]

dając jako wynik zapytania pierwszą i czwartą osobę (miejsca jedynek w wektorze)


Implementacja indeksu bitmapowego

Implementacja indeksu bitmapowego

Dwie pomocnicze struktury danych:

  • jedna, która w oparciu o wartość atrybutu pozwala szybko wyznaczyć przypisany jej wektor bitów (np. struktura danych B+ drzewa);

  • druga, która w oparciu o numer wiersza (pozycję jedynki w wektorze bitów) pozwala szybko wyznaczyć ten wiersz (np. tablica lub B+ drzewo).


Optymalizacja star query

Jedna duża tabela faktów i wiele drobnych tabeli wymiarów (zastosowanie indeksów bitmapowych). Zapytanie:

Optymalizacja "star query"

SELECT * FROM Sprzedaż, Miejsce, Towar, Czas

WHERE Sprzedaż.Id_miejsca = Miejsce.Id

AND Sprzedaż.Id_towaru = Towar.Id

AND Sprzedaż.Id_czasu = Czas.Id

AND Miejsce.Miasto IN ('WAW','KRA','RAD')

AND Towar.Kategoria = 'OPROGRAMOWANIE'

AND Czas.Rok > 1996

jest transformowane na:

SELECT * FROM Sprzedaż

WHERE

Sprzedaż.Id_miejsca IN (SELECT Miasto.Id FROM Miasto

WHERE Miejsce.Miasto. IN ('WAW','KRA','RAD'))

AND Sprzedaż.Id_towaru IN (SELECT Towar.Id FROM Towar

WHERE Towar.Kategoria = 'OPROGRAMOWANIE')

AND Sprzedaż.Id_czasu IN (SELECT Czas.Id FROM Czas

WHERE Czas.Rok > 1996)

  • Najpierw:

  • oblicza się podzapytania,

  • następnie stosuje się indeksy bitmapowe i operacje na wektorach bitów aby znaleźć fakty spełniające jednocześnie wszystkie trzy warunki IN.


Perspektywy zmaterializowane migawki

Perspektywy zmaterializowane (migawki)

Po zaprojektowaniu tabel faktów i wymiarów następnie projektuje się perspektywy zmaterializowane określające wymagane agregacjedanych z powyższych tabel. Na perspektywie zmaterializowanej można zakładać indeksy, więc ma ona te same własności co tabela.

  • CREATE MATERIALIZED VIEW Sprzedaz_mv

  • ENABLE QUERY REWRITE

  • AS

  • SELECT s.Nazwa_sklepu, SUM(f.Wielkosc) AS Suma

  • FROM Sklep s, Sprzedaz f

  • WHERE f.IdSklepu = s.IdSklepu

  • GROUP BY s.Nazwa_sklepu;

ENABLE QUERY REWRITE - zapytania pisane w terminach tabel faktów i wymiarów są przekształcane przez optymalizator do zapytań korzystających z perspektyw zmaterializowanych.


Od wie anie zawarto ci perspektywy zmaterializowanej

Odświeżanie zawartości perspektywy zmaterializowanej

  • COMPLETE – przez powtórne wykonanie zapytania.

  • FAST– skorzystanie z dzienników zapisujących zmiany w tabelach bazowych (MATERIALIZED VIEW LOG).

  • ON DEMAND – przez wykonanie explicite instrukcji odświeżającej (DBMS_MVIEW.REFRESH).

  • ON COMMIT – automatycznie po każdym COMMIT dotyczącym tabel bazowych.


Grupowanie rollup

Grupowanie ROLLUP

  • SELECT Czas.rok, Miejsce.miasto, Towar.kategoria, SUM(Sprzedaż.wartość)AS "W sumie"FROM Sprzedaż NATURAL JOIN Miejsce NATURAL JOIN Towar NATURAL JOIN CzasGROUP BY ROLLUP(Czas.Rok, Miejsce.Miasto, Towar.Kategoria);

  • ROK MIASTO  KATEGORIA W sumie ------ ------------- ------------------- ---------- 1996 Warszawa Komputery     100000 1996 Warszawa Telewizory     50000 .... 1996 Warszawa       *          1000000 ..... 1996   *             *         2000000 1997 Warszawa Komputery   1500000 ....  *       *            * 10000000


Grupowanie cube

GrupowanieCUBE

  • ROLLUP +*   Warszawa    Komputery    1000000 ... *   Warszawa         *       9000000 ... *       *      Komputery    8000000 ....1996   *       Komputery   500000


Funkcje analityczne

Funkcje analityczne

nazwa_funkcji_grupowej(argument,...) OVER (klauzula_okna)

Dla każdego wynikowego wiersza zapytania określamy zbiór powiązanych z nim wierszy - nazywany oknem tego wiersza. Definiuje się go za pomocą tzw. klauzuli okna.


Klauzula okna

Klauzula okna

  • PARTITION BY wyrażenie, ...  określa podział całego zbioru wynikowego wierszy na grupy; jeśli zostanie opuszczona, cały zbiór wynikowych wierszy stanowi jedną grupę. Wiersze wchodzące w skład okna są zawsze ograniczone (zawarte) do jednej grupy.

  • ORDER BY wyrażenie, ...       określa porządek wierszy w ramach podziału określonego w grupie przez podklauzulę PARTITION BY.

  • ROWS specyfikacja_okna      specyfikuje okno poprzez określenie liczby wierszy;

  • RANGE specyfikacja_okna     specyfikuje okno poprzez określenie zakresu wierszy.


Partition by

PARTITION BY

  • PARTITION jest podobne do GROUP BY z tym, że wartości podsumowujące są załączane do każdego wiersza w grupie, a nie tylko raz dla całej grupy. Oknem wiersza jest jego grupa.

  • SELECT e.Ename, e.Sal,       SUM(e.Sal) OVER (PARTITION BY e.Deptno) AS Suma,       Round(100*e.Sal/(SUM(e.Sal) OVER (PARTITION BY e.Deptno)))ASProc_zarobFROM Emp eORDER BY e.Deptno, e.Ename;

  • ENAME SAL SUMA PROC_ZAROB ---------- ------- --------- --------------------CLARK 2450 8750 28 KING 5000 8750 57 MILLER 1300 8750 15 ADAMS 1100 10875 10 FORD 3000 10875 28 ………………


Range

RANGE

Dla każdego pracownika wyznacz liczbę pracowników, których zarobki są co najwyżej 50zł mniejsze i co najwyżej150zł większe od zarobków tego pracownika. W tym przypadku okno obliczeniowe dla danego pracownika o zarobkach e.Sal nie zależy od departamentu i obejmuje wszystkich pracowników, których zarobki są w przedziale [e.Sal-50, e.Sal+150]. (Jest określona tylko jedna grupa - zbiór wszystkich wierszy.)


Hurtownie danych a bazy danych

  • SELECT e.Ename, e.Sal,COUNT(*) OVER (ORDER BY e.Sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS LicznikFROM Emp e;

  • Oto wyniki:ENAME  SAL LICZNIK ------------ ------ ------------SMITH      800        2 JAMES      950        2 ADAMS   1100      3 WARD     1250       3 MARTIN 1250       3 MILLER  1300        3 TURNER 1500       2 ALLEN    1600       1 ………..


Hurtownie danych a bazy danych

ROWS

Dla każdego pracownika podaj numer jego kierownika, datę zatrudnienia, zarobki oraz średnią wartość zarobków pracowników zatrudnionych bezpośrednio przed i po zatrudnieniu tego pracownika (włącznie z tym pracownikiem) wśród pracowników tego kierownika.

SELECT mgr, ename, hiredate, sal,AVG(sal) OVER (PARTITION BY mgr ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS aveFROM Emp;


Odm oracle data mining

ODM - Oracle Data Mining

Oracle rozpoczął w wersji 10g wprowadzanie pojęć i algorytmów data-miningowych na razie głównie przy pomocy pakietu DBMS_DATA_MINING(jeszcze nie na poziomie SQL i specjalnych obiektów zapisywanych w bazie danych). Centralne pojęcie to model określający parametry, algorytm data-miningowy i wprowadzone dane uczące.

Dane do analizy – zbiór punktów w przestrzeni wielowymiarowej - są dostarczane w jednej tabeli. Wiersze są nazywane przypadkami (cases). Kolumna ID przypadku dostarcza jednoznacznego identyfikatora np. CUSTOMER ID w tabeli klientów. Kolumny są nazywane atrybutami.

Np. model regresji może przewidzieć poziom dochodów klienta (atrybut typu target) w oparciu o datę urodzenia i płeć (atrybuty typu predictors).


Parametry procedury create model

Parametry procedury CREATE_MODEL

model_name -- nazwa modelu

mining_function -- stała reprezentująca rodzaj problemu eksploracji danych np. klasyfikacja, clustering, regresja

data_table_name-- nazwa tabeli z danymi „uczącymi” model case_id_column_name

target_column_name-- NULL dla modeli deskryptywnych, nie NULL dla modeli predyktywnych

settings_table_name-- ustawienia dla funkcji i algorytmu (np. nazwa algorytmu eksploracyjnego)


Procedura apply model

Procedura APPLY (model)

Stosuje podany model eksploracyjny do podanych danych i generuje wyniki w tablicy APPLY. Operacja APPLY jest też nazywanascoring. Dla modeli predyktywnych, operacja APPLY generuje wynik w kolumnie docelowej. Dla modeli deskryptywnych jak clustering, operacja APPLY przyporządkowuje każdemu przypadkowi prawdopodobieństwo należenia do klastra.

DBMS_DATA_MINING.APPLY ( model_name IN VARCHAR2, data_table_name IN VARCHAR2, case_id_column_name IN VARCHAR2, result_table_name IN VARCHAR2, -- schemat tworzonej przez APPLY tabeli zależy od algorytmu );


  • Login