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