Data Warehouse

Data Warehouse
Daten übernehmen und
vorbereiten
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 1
Datenstruktur
im Data Warehouse
• Star - Schema
• Snowflake - Schema
• Starflake - Schema
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 2
Star Schema
Kunde
Lieferanten
Verkaufstransaktionen
Ort
Produkte
Zeit
Fakten
Worzyk
FH Anhalt
Dimensionsdaten
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 3
Snowflake Schema
Fakten
Snowflake Dimensionsdaten
Verkaufstransaktionen
Ort
Produkte
Art
Region
Woche
Zeit
SSV
Monat
Worzyk
FH Anhalt
Osterverkauf
Farbe
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 4
Starflake Schema
Fakten
Dimensionsdaten
Snowflake Dimensionsdaten
Lieferanten
Kunde
Verkaufstransaktionen
Ort
Ort
Zeit
Region
Woche
Zeit
SSV
Worzyk
FH Anhalt
Produkte
Monat
Osterverkauf
Art
Produkte
Farbe
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 5
Oracle Data Mining Architektur
OWB
Client
Datenbank
z.B.
Oracle 8.0.5
Data Warehouse
Net8
Oracle
Warehouse Builder
8i 8.1.6
Worzyk
FH Anhalt
Repository
Darwin
Client
OCI
ODBC
Oracle
Intelligent WebHouse
Darwin
Sun Solaris
HP UX
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 6
Beispieldatenbank
Quelle
Zahlungsart
Bestellung Kopf
Verkäufer
R-2
R-1
R-3
R-5
Lieferung
Bestellung Artikel
R-6
Kalender
Segment
Rechnung
R-7
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 7
Beispieldatenbank
OWB
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 8
Beispieldatenbank
Quelle
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 9
Beispieldatenbank
Quelle
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 10
Beispieldatenbank Quelle
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 11
Beispieldatenbank
Quelle
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 12
Beispieldatenbank
Quelle
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 13
Beispieldatenbank
Ziel (Warehouse Schema)
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 14
Beispieldatenbank OWB
Ziel (Faktentabelle)
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 15
Beispieldatenbank OWB
Ziel (Dimensionen)
Levels und Hierarchien
Beispiel: Zeit
H1: YearL->QuarterL->MonthL->WeekL->DayL
H2: YearL->WeekL->DayL
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 16
Beispieldatenbank OWB
Ziel (Warehouse Schema)
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 17
Beispieldatenbank
Ziel (Warehouse Schema)
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 18
Tabellen
ta_probant
probant
aufgaben_nr
ergebnis_1
ergebnis_2
ergebnis_3
ergebnis_4
richtig
datum
ip_adresse
Worzyk
FH Anhalt
ta_aufgaben
aufgaben_nr
augfaben_text
loesung_1
loesung_2
loesung_3
loesung_4
kommentar_1
kommentar_2
kommentar_3
kommentar_4
ta_mathetest_historie
datum
text
ta_seite1
datum
ip_adresse
richtige_loesung
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 19
Beispieldatenbank
Quelle -> Ziel
product
Worzyk
FH Anhalt
channel
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 20
Beispiel
Mathetest
• Wie sehen die zeitlichen Verläufe aus
• Wie lange braucht der einzelnen
Probant pro Frage
• Welche Einträge sind plausibel
• Welche Daten der Eltern sind
plausibel?
• Wie korrelieren die Antworten auf die
Testfragen?
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 21
Rohdaten
30.6.196017.4.19571
deeeeeeeee5
24.03.195126.01.19481
00.00.0000.00.0040
15.11.195023.01.194814
1409530805481
29.7´5429.9´523
01.01.0001.01.009
20.05.195623.03.19531.
1.1.19601.1.19601
ab0
ab0
Worzyk
FH Anhalt
5
17
7
10
1
1
54
54
54
2
3
54
Apr
Apr
Apr
Apr
Apr
Jul
Sep
Sep
Oct
Oct
Jan
Jan
15
15
15
17
18
13
27
27
26
30
10
11
2000 1:28PM
2000 1:29PM
2000 1:29PM
2000 4:07PM
2000 2:41PM
2000 4:34PM
2000 8:20AM
2000 2:05PM
2000 9:54PM
2000 12:29PM
2001 1:46PM
2001 6:57PM
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 22
Import der Faktendaten
Sybase -> Oracle
1. select – insert
2. dump - restore
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 23
Sybase
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 24
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 25
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 26
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 27
Faktentabelle
drop table ta_zeitreihe;
create table ta_zeitreihe(
schluessel number(6) not null,
probant char(24) not null,
aufgaben_nr number(3) not null,
ergebnis_1 number(1) null,
ergebnis_2 number(2) null,
ergebnis_3 number(1) null,
ergebnis_4 number(4) null,
richtig char(1) not null,
datum date null,
ip_adresse char(15) null,
jahrestag char(7) null
);
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 28
select - insert
Faktentabelle
select
"insert into ta_zeitreihe values (sq_zeitreihe.nextval,"+"'"
+probant+"',"+convert(char(2),aufgaben_nr)+","
+convert(char(1),isnull(ergebnis_1,0))+","
+convert(char(1),isnull(ergebnis_2,0))+","
+convert(char(1),isnull(ergebnis_3,0))+","
+convert(char(1),isnull(ergebnis_4,0))+",'"+richtig
+"',to_date('"+convert(char(12),datum,104)+convert(char(8),datu
m,108)
+"','dd.mm.yyyy hh24:mi:ss')"
+",'"+ip_adresse+"');"
from ta_probant
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 29
SQL*Loader
Übersicht
Input
Datenfiles
Log
File
Daten
bank
Worzyk
FH Anhalt
SQL*Loader
Loader
Control
File
Bad
File
Discard
File
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 30
Funktionen von SQL*Loader
•
•
•
•
Mehrere Eingabedateien gleichzeitig
SQL-Funktionen für die Eingabefelder
Laden mehrerer Tabellen in einem Lauf
Zusammenfassen mehrerer Zeilen zu
einem logischen Datensatz
• Generierung von Schlüsseln
• Eingabe von Platte, Band, named pipes
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 31
Control file
• Enthält
– Pfade für Eingabe, Log, fehlerhafte
Datensätze, verworfene Datensätze
– Struktur der Eingabedaten
– Feldprüfungen
– Zieltabellen
– Vorschriften zur Fehlerbehandlung
• wird beim Aufruf übergeben
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 32
Control file
Beispiel
LOAD DATA
INFILE 'example.dat'
INTO TABLE emp
(empno
POSITION(01:04)
ename
POSITION(06:15)
job
POSITION(17:25)
mgr
POSITION(27:30)
sal
POSITION(32:39)
comm
POSITION(41:48)
...
Worzyk
FH Anhalt
INTEGER
CHAR,
CHAR,
INTEGER
DECIMAL
DECIMAL
EXTERNAL,
EXTERNAL,
EXTERNAL,
EXTERNAL,
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 33
Control file
Beispiel
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 34
Control file
Beispiel
load data
infile 'example.dat' "fix 11"
badfile 'example.bad'
discardfile 'example.dsc'
discardmax 999
truncate
into table example
(rown position(1-5), cmnt position(6-10))
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 35
Filtern der Datensätze
Eingabe
SQL*Loader Feldprüfung
Discard
File
SQL*Loader Auswahl
Bad
File
DBMS
Worzyk
FH Anhalt
Daten
bank
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 36
Syntax control file
-- comment
OPTIONS (options)
LOAD
CONTINUE_LOAD
DATA
READBUFFERS n
infile_clause
INSERT
APPEND
REPLACE
Concatenation_clause
into_table_clause
PRESERVE BLANKS
Worzyk
FH Anhalt
BEGINDATA
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 37
Syntax control file
infile_clause::=
INFILE
input_filename
*
BADFILE
DISCARDFILE
bad_file_name
discard_file_name
DISCARDS
n
DISCARDMAX
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 38
Syntax control file
concatenation_clause::=
n
CONCATENATE
(n)
COTINUEIF
pos_spec_operator
THIS
(
‘ char_string‘
)
NEXT
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 39
Syntax control file
pos_spec_operator::=
)
( start
:
-
end
=
!=
<>
=
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 40
Syntax control file
into_table_clause::=
INTO TABLE tablename
INSERT
APPEND
REPLACE
AND
WHEN
FIELDS
delimiter_spec
field_condition
,
(
Worzyk
FH Anhalt
column_name
column_spec
)
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 41
Syntax control file
delimiter_spec::=
termination_spec
enclosure_spec
termination_spec
enclosure_spec
OPTIONALLY
WHITESPACE
TERMINATED
by
‘ char‘
ENCLOSED
by
Worzyk
FH Anhalt
‘ char‘
AND
‘ char‘
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 42
Syntax control file
column_spec::=
POSITION
pos_spec
(*
datatype_spec
)
+n
NULLIF field_condition
DEFAULTIF field_condition
“ sql_string“
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 43
Syntax control file
datatype_spec::=
delimiter_spec
INTEGER
FLOAT
EXTERNAL
(length)
DECIMAL
( precision
)
, scale
EXTERNAL
(length)
CHAR
(length)
DATE
(length)
Worzyk
FH Anhalt
“ mask“
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 44
Aufruf des SQL*Loader
SQLLDR80 schlüsselwort=wert [, schlüsselwort=wert ]
Gültige Schlüsselworte:
USERID - ORACLE userid/password
CONTROL - controlfile
LOG - logfile
BAD - badfile
DATA - datafile
LOAD - Anzahl der einzulesenden logischen Datensätze
ERRORS - Anzahl der erlaubten Fehler
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 45
Mathetest
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 46
Mathetest
Auswertung pro Stunde
select to_char(datum,'hh24'), count(*)
from ta_zeitreihe
group by to_char(datum,'hh24')
order by to_char(datum,'hh24');
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 47
Mathetest
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 48
Mathetest
Auswertung pro Wochentag
select to_char(datum,'day'), count(*),
to_char(datum,'d')
from ta_zeitreihe
group by to_char(datum,'day'),
to_char(datum,'d')
order by to_char(datum,'d');
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 49
Mathetest
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 50
Mathetest
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 51
Mathetest
• Faktentabelle
– Alle Einträge der Originaltabelle und zusätzlich ein
eindeutiger Zähler und Tagesnummer
• Dimensionstabelle
– Jeder Tag seit Beginn des Tests mit: fortlaufende
Nummer, Tagesnummer, Monat, Jahr, Datum
• Dimensionstabelle
– zu jedem Eintrag in der Faktentabelle: Wochentag,
Stunde, Tagesnummer
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 52
Dimensionstabelle
TAG
---------1
2
3
4
5
6
7
8
9
10
11
Worzyk
FH Anhalt
DATUM
-------13.04.00
14.04.00
15.04.00
16.04.00
17.04.00
18.04.00
19.04.00
20.04.00
21.04.00
22.04.00
23.04.00
JAHREST
MONAT
------- ---------2000104
4
2000105
4
2000106
4
2000107
4
2000108
4
2000109
4
2000110
4
2000111
4
2000112
4
2000113
4
2000114
4
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 53
Datumsfunktionen von Oracle
ADD_MONTHS
Syntax
ADD_MONTHS(d,n)
Purpose
Returns the date d plus n months. The argument n can be
any integer. If d is the last day of the month or if the
resulting month has fewer days than the day component
of d, then the result is the last day of the resulting month.
Otherwise, the result has the same day component as d.
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 54
Datumsfunktionen von Oracle
LAST_DAY
Syntax
LAST_DAY(d)
Purpose
Returns the date of the last day of the month that contains
d. You might use this function to determine how many
days are left in the current month.
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 55
Datumsfunktionen von Oracle
NEXT_DAY
Syntax
NEXT_DAY(d, char)
Purpose
Returns the date of the first weekday named by char
that is later than the date d. The argument char must
be a day of the week in your session's date
language-either the full name or the abbreviation.
The minimum number of letters required is the
number of letters in the abbreviated version; any
characters immediately following the valid
abbreviation are ignored. The return value has the
same hours, minutes, and seconds component as the
Worzyk
FH Anhalt
argument d.
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 56
Datumsfunktionen von Oracle
ROUND
Syntax
ROUND(d[,fmt])
Purpose
Returns d rounded to the unit specified by the format model
fmt. If you omit fmt, d is rounded to the nearest day. See
"ROUND and TRUNC" for the permitted format models
to use in fmt.
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 57
Datumsformate in Oracle
YYYY
Q
MM
M ONTH
M ON
WW
IW
W
DDD
DD
D
DAY
DY
J
HH
HH24
MI
SS
Worzyk
FH Anhalt
4-stellige Jahreszahl
Quartal
M onat 01-12
M onatsname
Abkürzung des M onatsnamens
Wochennr
Wochennr nach ISO
Woche im M onat
Tag im Jahr 1-366
Tag im M onat 1-31
Tag in der Woche
Tagesname
Abkürzung des Tagesnamens
Julianischer Tag; Tag 1 ist 1. Januar 4712 vor Chr.
Tagesstunde 1-12
Tagesstunde 1-24
M inute
Sekunde
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 58
Datumsfunktionen von Sybase
dateadd - adds an interval to a specified date.
It takes three arguments-- the datepart, a
number, and a date. The result is a
datetime value equal to the date plus the
number of date parts.
select newpubdate = dateadd(day, 21, pubdate)
from titles
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 59
Datumsfunktionen von Sybase
datediff - calculates the number of date parts
between two specified dates. It takes three
arguments. The first is a date part. The
second and third are dates, either datetime or
smalldatetime values. The result is a signed
integer value equal to date2 - date1, in date
parts.
select newdate = datediff(day, pubdate, getdate())
from titles
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 60
Datumsfunktionen von Sybase
datename - produces the specified datepart
(the first argument) of the specified date
(the second argument) as a character
string. Takes either a datetime or
smalldatetime value as its second
argument.
select datename(month getdate())
Worzyk
FH Anhalt
November
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 61
Datumsfunktionen von Sybase
datepart - produces the specified datepart
(the first argument) of the specified date
(the second argument) as an integer. Takes
either a datetime or smalldatetime value as
its second argument.
select datepart(month getdate())
Worzyk
FH Anhalt
11
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 62
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 63
Datenkonvertierung Sybase
convert (datatype [(length) | (precision[,
scale])], expression[, style])
Converts between a wide variety of
datatypes and reformats date/time and
money data for display purposes.
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 64
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 65
Dimensionstabelle
TAG
---------1
2
3
4
5
6
7
8
9
10
11
Worzyk
FH Anhalt
DATUM
-------13.04.00
14.04.00
15.04.00
16.04.00
17.04.00
18.04.00
19.04.00
20.04.00
21.04.00
22.04.00
23.04.00
JAHREST
MONAT
------- ---------2000104
4
2000105
4
2000106
4
2000107
4
2000108
4
2000109
4
2000110
4
2000111
4
2000112
4
2000113
4
2000114
4
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 66
Dimensionstabelle
Worzyk
FH Anhalt
drop table ta_Dim_zeit_1;
create table ta_dim_zeit_1(
tag_nr number(3) not null,
Datum date null,
jahrestag char(7),
monat number(2)
);
select to_number(to_char(max(datum),'J'))
- to_number(to_char(min(datum),'J'))+1
from ta_zeitreihe;
insert into ta_dim_zeit_1 (tag_nr)
select schluessel
from ta_zeitreihe
Datenbanksysteme 3 Sommer 2003
where schluessel <= 382
Datenübernahme - 67
Dimensionstabelle
select to_char(min(datum),'J'), min(datum)
from ta_zeitreihe;
update ta_dim_zeit_1
set datum =
to_date(to_char(2451647+tag_nr,'9999999'),'J');
update ta_dim_zeit_1
set jahrestag =
to_char(datum,'YYYY')||to_char(datum,'ddd'),
monat = to_char(datum,'MM');
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 68
Auswertung pro Tag
select to_char(d1.datum, 'yy.mm.dd'),
count(z.schluessel) "Anzahl"
from ta_dim_zeit_1 d1,
ta_zeitreihe z
where d1.jahrestag = z.jahrestag (+)
group by to_char(d1.datum, 'yy.mm.dd'),
d1.jahrestag
order by to_char(d1.datum, 'yy.mm.dd');
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 69
Worzyk
FH Anhalt
Datenbanksysteme 3 Sommer 2003
Datenübernahme - 70