Rafał Kowalski

1

Hurtownie Danych na platformie ORACLE

Rafał Kowalski

2

1. Tworzenie struktury logicznej i fizycznej Tabeli Wymiarów w

układzie Gwiazdy.

2. Tworzenie struktury logicznej i fizycznej Tabeli Wymiarów w

układzie Płatka Śniegu.

3. Tworzenie struktury logicznej i fizycznej Tabeli faktów.

4. Weryfikowanie poprawności chierarchii wymiarów.

5. Wprowadzanie zmian do struktur wymiarów.

Struktura Logiczna - WYMIARY

Rafał Kowalski

3

Wymiar czasu (z perspektywy analityka)

DATY

faktów

MIESIĄCE

KWARTAŁY

LATA

Styczeń

Luty

Grudzień

Maj

Wrzesień

Styczeń

I

II

III

I

IV

1999

2000

Rafał Kowalski

4

Hierarchie wymiaru czasu (z perspektywy analityka)

kwartaly

dni

miesiące

Dni

tygodnia

Pory

roku

lata

POZIOMY

POZIOMY

POZIOMY

HIERARCHIA AGREGACJI

„wszystko”

Tygodnie

księgowe

lata

Lata

księgowe

Dni:

Robocze

Świąteczne

lata

.
.
.

Rafał Kowalski

5

Tworzenie struktury logicznej i fizycznej na układzie gwiazdy

Wymiar i hierarchia analizy

SKLEPY.

CREATE TABLE sklepy (

sklep_id NUMBER(10) NOT NULL,

miasto VARCHAR2(30) NOT NULL,

region VARCHAR2(30) NOT NULL,

kraj VARCHAR2(30) NOT NULL );

CREATE UNIQUE INDEX sklepy_pk

ON sklepy (sklep_id) ;

ALTER TABLE sklepy ADD CONSTRAINT

sklepy_pk PRIMARY KEY (sklep_id);

CREATE TABLE sklepy (

sklep_id NUMBER(10) NOT NULL,

miasto VARCHAR2(30) NOT NULL,

region VARCHAR2(30) NOT NULL,

kraj VARCHAR2(30) NOT NULL );

CREATE UNIQUE INDEX sklepy_pk

ON sklepy (sklep_id) ;

ALTER TABLE sklepy ADD CONSTRAINT

sklepy_pk PRIMARY KEY (sklep_id);

CREATE DIMENSION sklepy_wy

LEVEL sklep IS sklepy.sklep_id

LEVEL miasto IS sklepy.miasto

LEVEL region IS sklepy.region

LEVEL kraj IS sklepy.kraj

HIERARCHY sklepy_hi (sklep CHILD OF

miasto CHILD OF

region CHILD OF kraj );

CREATE DIMENSION sklepy_wy

LEVEL sklep IS sklepy.sklep_id

LEVEL miasto IS sklepy.miasto

LEVEL region IS sklepy.region

LEVEL kraj IS sklepy.kraj

HIERARCHY sklepy_hi (sklep CHILD OF

miasto CHILD OF

region CHILD OF kraj );

Kraj

Region

Miasto

SKLEPY

sklep

Rafał Kowalski

6

Tworzenie struktury logicznej i fizycznej na układzie gwiazdy –

parametry dodatkowe

Kraj

region

Miasto

SKLEPY

sklep

CREATE TABLE sklepy (

sklep_id

number(10) NOT NULL

, nazwa

VARCHAR2(20)

, adres

VARCHAR2(30)

, miasto VARCHAR2(30) NOT NULL

, region VARCHAR2(30) NOT NULL

, kraj VARCHAR2(30) NOT NULL );

CREATE UNIQUE INDEX

sklepy_pk

ON sklepy (sklep_id);

ALTER TABLE sklepy ADD CONSTRAINT

sklepy_pk

PRIMARY KEY (sklep_id);

CREATE TABLE sklepy (

sklep_id

number(10) NOT NULL

, nazwa

VARCHAR2(20)

, adres

VARCHAR2(30)

, miasto VARCHAR2(30) NOT NULL

, region VARCHAR2(30) NOT NULL

, kraj VARCHAR2(30) NOT NULL );

CREATE UNIQUE INDEX

sklepy_pk

ON sklepy (sklep_id);

ALTER TABLE sklepy ADD CONSTRAINT

sklepy_pk

PRIMARY KEY (sklep_id);

CREATE DIMENSION sklepy_wy

LEVEL sklep IS sklepy.sklep_id

LEVEL miasto IS sklepy.miasto

LEVEL region IS sklepy.region

LEVEL kraj IS sklepy.kraj

HRARCHY sklepy_hi (

sklep CHILD OF

miasta CHILD OF

regiony CHILD OF kraj )

ATTRIBUTE sklep DETERMINES (sklepy.nazwa,sklepy.adres);

CREATE DIMENSION sklepy_wy

LEVEL sklep IS sklepy.sklep_id

LEVEL miasto IS sklepy.miasto

LEVEL region IS sklepy.region

LEVEL kraj IS sklepy.kraj

HRARCHY sklepy_hi (

sklep CHILD OF

miasta CHILD OF

regiony CHILD OF kraj )

ATTRIBUTE sklep DETERMINES (sklepy.nazwa,sklepy.adres)

;

Rafał Kowalski

7

Tworzenie struktury fizycznej na układzie płatka śniegu 1

Wymiar / hierarchia

SKLEPY.

CREATE TABLE kraje (

kraj_id NUMBER(6)

, nazwa VARCHAR2(20) );

CREATE UNIQUE INDEX kraje_pk

ON kraje (kraj_id) ;

ALTER TABLE kraje

ADD CONSTRAINT kraje_pk PRIMARY KEY (kraj_id);

CREATE TABLE kraje (

kraj_id NUMBER(6)

,

nazwa VARCHAR2(20)

);

CREATE UNIQUE INDEX kraje_pk

ON kraje (kraj_id) ;

ALTER TABLE kraje

ADD CONSTRAINT kraje_pk PRIMARY KEY (kraj_id);

SKLEPY

MIASTA

REGIONY

KRAJE

Wymiar i jego hierarchię stworzą cztery tabele bazy

danych doprowadzone do 3NF:

SKLEPY, MIASTA, REGIONY i KRAJE

Rafał Kowalski

8

Tworzenie struktury fizycznej na układzie płatka śniegu 2

Wymiar / hierarchia

SKLEPY.

CREATE TABLE regiony (

region_id NUMBER(6)

, nazwa VARCHAR2(20)

, kraj_id

number(6) );

CREATE UNIQUE INDEX regiony_pk

ON regiony (region_id) ;

ALTER TABLE regiony

ADD CONSTRAINT regiony_pk PRIMARY KEY (region_id)

ADD CONSTRAINT regiony_kr FOREIGN KEY (kraj_id)

REFERENCES kraje (kraj_id);

CREATE TABLE regiony (

region_id NUMBER(6)

,

nazwa VARCHAR2(20)

,

kraj_id

number(6) );

CREATE UNIQUE INDEX regiony_pk

ON regiony (region_id) ;

ALTER TABLE regiony

ADD CONSTRAINT regiony_pk PRIMARY KEY (region_id)

ADD CONSTRAINT regiony_kr FOREIGN KEY (kraj_id)

REFERENCES kraje (kraj_id);

SKLEPY

MIASTA

REGIONY

KRAJE

Rafał Kowalski

9

Tworzenie struktury fizycznej na układzie płatka śniegu 3

Wymiar / hierarchia

SKLEPY.

CREATE TABLE miasta (

miasto_id

NUMBER(6)

, nazwa VARCHAR2(20),

region_id number(6) );

CREATE UNIQUE INDEX miasta_pk

ON miasta (miasto_id) ;

ALTER TABLE miasta

ADD CONSTRAINT miasta_pk PRIMARY KEY (miasto_id)

ADD CONSTRAINT miasta_re FOREIGN KEY (region_id)

REFERENCES regiony(region_id);

CREATE TABLE miasta (

miasto_id

NUMBER(6)

,

nazwa VARCHAR2(20),

region_id number(6) );

CREATE UNIQUE INDEX miasta_pk

ON miasta (miasto_id) ;

ALTER TABLE miasta

ADD CONSTRAINT miasta_pk PRIMARY KEY (miasto_id)

ADD CONSTRAINT miasta_re FOREIGN KEY (region_id)

REFERENCES regiony(region_id);

SKLEPY

MIASTA

REGIONY

KRAJE

Rafał Kowalski

10

Tworzenie struktury fizycznej na układzie płatka śniegu 4

Wymiar / hierarchia

SKLEPY.

CREATE TABLE sklepy (

sklep_id

NUMBER(6)

, nazwa VARCHAR2(20)

, adres VARCHAR2(30)

, miasto_id number(6) );

CREATE UNIQUE INDEX sklepy_pk

ON sklepy (sklep_id) ;

ALTER TABLE sklepy

ADD CONSTRAINT sklepy_pk PRIMARY KEY (sklep_id)

ADD CONSTRAINT sklepy_ms FOREIGN KEY (miasto_id)

REFERENCES miasta(miasto_id);

CREATE TABLE sklepy (

sklep_id

NUMBER(6)

,

nazwa VARCHAR2(20)

, adres VARCHAR2(30)

,

miasto_id number(6) );

CREATE UNIQUE INDEX sklepy_pk

ON sklepy (sklep_id) ;

ALTER TABLE sklepy

ADD CONSTRAINT sklepy_pk PRIMARY KEY (sklep_id)

ADD CONSTRAINT sklepy_ms FOREIGN KEY (miasto_id)

REFERENCES miasta(miasto_id);

SKLEPY

MIASTA

REGIONY

KRAJE

Rafał Kowalski

11

Tworzenie struktury logicznej na układzie płatka śniegu

Wymiar i hierarchia

wymiaru SKLEPY.

CREATE DIMENSION sklepy_wy

LEVEL sklep IS sklepy.sklep_id

LEVEL miasto IS miasta.miasto_id

LEVEL region IS regiony.region_id

LEVEL kraj IS kraje.kraj_id

HIERARCHY sklepy_hi

(

sklep CHILD OF

miasto CHILD OF

region CHILD OF kraj

JOIN KEY sklepy.miasto_id

REFERENCES miasto

JOIN KEY miasta.region_id

REFERENCES region

JOIN KEY regiony.kraj_id

REFERENCES kraj )

ATTRIBUTE sklep DETERMINES ( sklepy.nazwa

,sklepy.adres)

ATTRIBUTE miasto DETERMINES (miasta.nazwa)

ATTRIBUTE region DETERMINES (regiony.nazwa)

ATTRIBUTE kraj DETERMINES (kraje.nazwa);

CREATE DIMENSION sklepy_wy

LEVEL sklep IS sklepy.sklep_id

LEVEL miasto IS miasta.miasto_id

LEVEL region IS regiony.region_id

LEVEL kraj IS kraje.kraj_id

HIERARCHY sklepy_hi

(

sklep CHILD OF

miasto CHILD OF

region CHILD OF kraj

JOIN KEY sklepy.miasto_id

REFERENCES miasto

JOIN KEY miasta.region_id

REFERENCES region

JOIN KEY regiony.kraj_id

REFERENCES kraj )

ATTRIBUTE sklep DETERMINES ( sklepy.nazwa

,sklepy.adres)

ATTRIBUTE miasto DETERMINES (miasta.nazwa)

ATTRIBUTE region DETERMINES (regiony.nazwa)

ATTRIBUTE kraj DETERMINES (kraje.nazwa);

SKLEPY

MIASTA

REGIONY

KRAJE

Rafał Kowalski

12

Narzędzia do obsługi wymiarów

(1)

Pakiet (Package) procedur do wyświetlania struktury wymiarów.

DBMS_DIMENSION

DBMS_DIMENSION

Program do zarządzania serwerami Oracle.

Oracle Enterprise Manager

Oracle Enterprise Manager

DESCRIBE_DIMENSION

VALIDATE_DIMENSION

DIMENSION_EXCEPTION

Oracle Warehouse Builder

Oracle Warehouse Builder

Rafał Kowalski

13

dbms_dimension.DESCRIBE_DIMENSION

-- describing dimension

SET serveroutput ON;

EXECUTE

dbms_dimension.describe_dimension(

’HD_LOGIN.NAZWA_WYMIARU’

);

Bez ustawienia zmiennej środowiskowej

SERVEROUTPUT

procedura niczego nie wyświetli !!!.

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

W przypadku programu Oracle SQL Developer:

Rafał Kowalski

14

dbms_dimension.VALIDATE_DIMENSION

@utldim.sql

-- create dimension_exceptions table !!!

VARIABLE

stmt_id

VARCHAR2(30);

EXECUTE

:stmt_id

:= ’

HD_LOGIN

’;

EXECUTE dbms_dimension.validate_dimension (

’HD_LOGIN.NAZWA_WYMIARU’

, FALSE, TRUE,

:stmt_id

);

Weryfikacja wyłącznie nowych wierszy z
tabeli wymiaru:

TRUE / FALSE.

W

eryfikacja wypełnienia wszystkich

kolumn wierszy wymiaru

TRUE / FALSE.

Rafał Kowalski

15

tabela: DIMENSION_EXCEPTION

Nazwa wymiaru

DIMENSION_NAME

Identyfikator
błędnego wiersza
tabeli.

BAD_ROWID

Nazwa tabeli

TABLE_NAME

Wskazanie typu
referencji

Nazwa schematu

RELATIONSHIP

OWNER

-- Lista zlych tabel wymiarow

SELECT DISTINCT owner, table_name, dimension_name, relationship

FROM dimension_exceptions

WHERE statement_id =

:stmt_id

;

-- Lista zlych wierszy

SELECT *

FROM

TABELA_WYMIARU

WHERE rowid IN (SELECT bad_rowid FROM dimension_exceptions

WHERE statement_id =

:stmt_id

);

Jak sobie poradzić:

Lista pól tabeli:

Rafał Kowalski

16

Wprowadzanie zmian do struktur wymiarów

ADD

HIERARCHY

[definicja_zależności_hierarchii] ;

ADD

ATTRIBUTE

[nazwa_atrybutu]

DETERMINES

[nazwa tabeli].[nazwa_atrybutu] ;

ADD

LEVEL

[nazwa_poziomu]

IS

[nazwa tabeli].[nazwa_atrybutu] ;

DROP

LEVEL

[nazwa_poziomu] ;

DROP

HIERARCHY

[nazwa_hierarchii] ;

DROP

ATTRIBUTE

[nazwa_atrybutu] ;

ALTER DIMENSION

[nazwa_wymiaru]

Niepotrzebny wymiar można skasować przy pomocy polecenia:

DROP DIMENSION

[nazwa_wymiaru] ;

Rafał Kowalski

17

Zatwierdzanie wymiaru

Jeżeli dokonamy jakichkolwiek zmian w obiektach, do

których bazy, na których został zbudowany wymiar,

staje się on automatycznie

NIEWAŻNY (INVALID)

.

UWAGA

ALL

_

DIMENSIONS

Stan wymiarów można w każdej chwili

skontrolować wyświetlając informacje z atrybutu

invalid

, widoku systemowego:

albo przy pomocy poleceń programu Oracle Enterprise Manager.

ALTER DIMENSION

[nazwa_wymiaru]

COMPILE ;

Nieważny wymiar należy ponownie zatwierdzić przy pomocy polecenia:

Rafał Kowalski

18

PODSUMOWANIE

Tworzenie struktury fizycznej.

CREATE TABLE . . .

. . . CONSTRAINT . . .

. . . . . . PRIMARY KEY . . .

. . . . . . FOREIGN KEY . . .

CREATE UNIQUE INDEX . . .

CREATE TABLE . . .

. . . CONSTRAINT . . .

. . . . . . PRIMARY KEY . . .

. . . . . . FOREIGN KEY . . .

CREATE UNIQUE INDEX . . .

Tworzenie struktury logicznej.

CREATE DIMENSION . . .

. . . LEVEL . . .

. . . HIERARCHY . . .

. . . DETERMINES . . .

ALTER DIMENSION . . .

ALTER DIMENSION . . . COMPILE ;

CREATE DIMENSION . . .

. . . LEVEL . . .

. . . HIERARCHY . . .

. . . DETERMINES . . .

ALTER DIMENSION . . .

ALTER DIMENSION . . . COMPILE ;

Weryfikacja wymiarów.

dbms_dimension.describe_dimension ( . . . )

utldim.sql

-> dimension_exceptions

dbms_dimension.validate_dimension ( . . . )

dbms_dimension.describe_dimension ( . . . )

utldim.sql

-> dimension_exceptions

dbms_dimension.validate_dimension ( . . . )

Rafał Kowalski

19

KONIEC