Складиране на данни (data warehousing)
TRANSCRIPT
СКЛАДИРАНЕ НА ДАННИ
(DATA WAREHOUSING)
доц. д-р Цветанка Георгиева-Трифонова
СЪДЪРЖАНИЕ
Преглед на складирането на данни
Склад за данни (data warehouse)
Архитектура на склад за данни
Проектиране на таблици-размерности и факт-таблици
Извличане, трансформиране и зареждане на данни
2 2 Цветанка Георгиева Моделиране на информационни системи
ПРЕГЛЕД НА СКЛАДИРАНЕТО НА ДАННИ
3
База от данни (database)
събиране, съхраняване и достъп до данни;
трансакционни информационни системи (transaction processing systems);
съгласуваност, възстановяване, минимизирането на конфликтите.
Склад за данни (data warehouse)
архивните, обобщени и обединени данни;
системи за онлайн аналитична обработка (online analytical processing – OLAP);
минимизиране на времето за отговор на заявки, осъществяващи достъп до огромно количество данни и извършващи много сканирания, съединения и обобщавания.
3 3 Цветанка Георгиева Моделиране на информационни системи
СКЛАД ЗА ДАННИ - ДЕФИНИЦИЯ
Складът за данни се дефинира като:
тематично ориентирана, интегрирана, времевариантна,
неизменчива съвкупност от данни, подпомагаща
вземането на решения;
база от данни, която предоставя достъп до цялата
информация на дадена организация;
съхранява информация като хранилище за данни и дава
на потребителите достъп до данни за извършване на
изследователски анализ.
4 4 Цветанка Георгиева Моделиране на информационни системи
W. H. Inmon, Tech Topic: What is a Data Warehouse?, Prism Solutions, Inc.
1995
Складовете за данни и OLTP базите от данни имат различно
предназначение, което определя и различията в тяхното проектиране
Склад за данни OLTP база от данни
Проектирана за анализ на бизнес
измерения по категории и атрибути
Проектирана за бизнес операции в
реално време
Оптимизирана за масови
зареждания на данни и сложни,
непредвидими заявки, които
осъществяват достъп до много от
редовете в отделните таблици
Оптимизирана за обичайно
множество от трансакции,
обикновено модифициращи или
извличащи в даден момент по един
ред от съответните таблици
Зареждана със съвместими и
валидни данни; не изисква
проверка на валидността в реално
време
Оптимизирана за проверка на
валидността на въвежданите данни
по време на трансакция
Поддържа няколко едновременни
потребители, отнасящи се до OLTP
Поддържа хиляди едновременни
потребители
5 5 Цветанка Георгиева Моделиране на информационни системи
СКЛАД ЗА ДАННИ (2)
АРХИТЕКТУРА НА СКЛАД ЗА ДАННИ
6
Източници на данни Реализиране на добре работеща система за събиране,
съхраняване и осигуряване на достъп до първичната информация.
Предварителна обработка Извличане на данните;
Използват се данни от разнообразни източници;
Решаване на проблеми, свързани с имена, мерки, релевантност, др.
Трансформиране на данните; Откриване и премахване на грешни данни;
Обработка на липсващи данни;
Откриване и анализиране на силно отличаващите се стойности (outliers), др.
Зареждане на данните в куб с данни; Периодично обновяване.
Съхраняване на данните Инструменти за крайните потребители
Анализи, заявки, отчети, добиване на данни.
6 6 Цветанка Георгиева Моделиране на информационни системи
АРХИТЕКТУРА НА СКЛАД ЗА ДАННИ (2)
7
МНОГОМЕРЕН МОДЕЛ НА ДАННИТЕ
Включва няколко числови мерки (measures), които подлежат
на анализ (например продадени количества, цени);
Всяка мярка зависи от множество от размерности
(dimensions).
Физически се реализира посредством два типа таблици:
таблици-размерности (таблици на размерностите;
dimension tables);
факт-таблици (таблици на фактите; таблици за
фактическите стойности; fact tables).
8 8 Цветанка Георгиева Моделиране на информационни системи
МНОГОМЕРЕН МОДЕЛ НА ДАННИТЕ (2)
Схема “звезда” (star schema)
9 9 Цветанка Георгиева Моделиране на информационни системи
МНОГОМЕРЕН МОДЕЛ НА ДАННИТЕ (3)
Схема “снежинка” (snowflake schema)
10 10 Цветанка Георгиева Моделиране на информационни системи
ПРОЕКТИРАНЕ НА ТАБЛИЦИ-РАЗМЕРНОСТИ И ФАКТ-
ТАБЛИЦИ
Ключ-заместител (surrogate key)
Първичния ключ за една таблица-размерност;
Независим е от всякакви ключове, осигурени от
оперативните бази от данни-източници;
Факт-таблицата съдържа колони с ограничения
външен ключ, рефериращи първичните ключове на
всяка размерност.
11 11 Цветанка Георгиева Моделиране на информационни системи
ПРОЕКТИРАНЕ НА ТАБЛИЦИ-РАЗМЕРНОСТИ И ФАКТ-
ТАБЛИЦИ (2)
Ключове-заместители в таблиците-размерности 12 12 Цветанка Георгиева Моделиране на информационни системи
ПРОЕКТИРАНЕ НА ТАБЛИЦИ-РАЗМЕРНОСТИ И ФАКТ-
ТАБЛИЦИ (3)
Предимства от използването на ключове-заместители
защитават системата от промени в системата-източник;
позволяват на системата да интегрира данни от
множество системи-източници;
осигуряват възможност за проследяване на промените в
атрибутите на размерностите във времето;
използването на ключове-заместители от целочислен тип
води до подобряване на производителността на
обработката на заявките.
13 13 Цветанка Георгиева Моделиране на информационни системи
ПРОЕКТИРАНЕ НА ТАБЛИЦИ-РАЗМЕРНОСТИ И ФАКТ-
ТАБЛИЦИ (4)
Бавно променящи се размерности (slowly changing
dimensions – SCDs)
размерности, притежаващи атрибут, стойностите на който
допускат промени;
Подходи за проектиране, позволяващи да се реализират
бавно променящи се размерности:
Тип 1 (Type 1) – припокриване на записа в таблицата-
размерност;
Тип 2 (Type 2) – добавяне на нов запис в таблицата-
размерност;
Тип 3 (Type 3) – създаване на ново поле в таблицата-
размерност.
14 14 Цветанка Георгиева Моделиране на информационни системи
БАВНО ПРОМЕНЯЩИ СЕ РАЗМЕРНОСТИ ТИП 1 – ПРИМЕР
UPDATE Subjects
SET SubjectName = 'Бази от данни'
WHERE SubjectName = 'Проектиране на бази от данни'
15 15 Цветанка Георгиева Моделиране на информационни системи
БАВНО ПРОМЕНЯЩИ СЕ РАЗМЕРНОСТИ ТИП 2 – ПРИМЕР
INSERT INTO Subjects
(SubjectID, SubjectName, Current_row,
Start_Date, End_Date)
SELECT SubjectID,'Бази от данни','True',GetDate(),NULL
FROM Subjects
WHERE SubjectName = 'Проектиране на бази от данни'
AND Current_row = 'True' AND End_Date IS NULL
UPDATE Subjects
SET Current_row = 'False', End_Date = GetDate()
WHERE SubjectName = 'Проектиране на бази от данни'
AND Current_row = 'True'
16 16 Цветанка Георгиева Моделиране на информационни системи
БАВНО ПРОМЕНЯЩИ СЕ РАЗМЕРНОСТИ ТИП 3 – ПРИМЕР
UPDATE Subjects
SET CurrentSubjectName = 'Бази от данни'
WHERE SubjectName = 'Проектиране на бази от данни'
17 17 Цветанка Георгиева Моделиране на информационни системи
ИЗВЛИЧАНЕ, ТРАНСФОРМИРАНЕ И ЗАРЕЖДАНЕ НА
ДАННИ
Обработка на таблиците-размерности
Попълване на новите данни в таблиците-размерности на
базата от данни на склада за данни
INSERT INTO StudentsDW..Subjects
(SubjectID, SubjectName)
SELECT d.SubjectID, d.SubjectName
FROM StudentsDB..Subjects d
LEFT JOIN StudentsDW..Subjects w
ON d.SubjectID = w.SubjectID
WHERE w.SubjectID IS NULL
18 18 Цветанка Георгиева
ИЗВЛИЧАНЕ, ТРАНСФОРМИРАНЕ И ЗАРЕЖДАНЕ НА
ДАННИ (2)
Попълване на новите редове в таблицата-размерност за
време периодично
INSERT INTO Dates (Date, Semester, SchoolYear)
VALUES ('2009/01/01', 'зимен', '2009/2010')
INSERT INTO Dates (Date, Semester, SchoolYear)
VALUES ('2009/01/01', 'летен', '2009/2010')
19 19 Цветанка Георгиева Моделиране на информационни системи
ИЗВЛИЧАНЕ, ТРАНСФОРМИРАНЕ И ЗАРЕЖДАНЕ НА
ДАННИ (3)
Управляване на Тип 1 бавно променящи се
размерности
Пример – поддържане на атрибута TeacherName на
таблицата-размерност Teachers:
CREATE PROCEDURE Refresh_TeacherName_DW
AS
UPDATE StudentsDW..Teachers
SET TeacherName = t.TeacherName
FROM StudentsDB..Teachers t
INNER JOIN StudentsDW..Teachers w
ON t.TeacherID = w.TeacherID
WHERE t.TeacherName <> w.TeacherName
20 20 Цветанка Георгиева Моделиране на информационни системи
Управляване на Тип 2 бавно променящи се размерности
Пример – поддържане на атрибута учебен предмет
SubjectName на таблицата-размерност Subjects.
CREATE PROCEDURE Refresh_SubjectName_DW
AS
INSERT INTO Subjects
(SubjectID, SubjectName, Current_row,
Start_Date, End_Date)
SELECT
s.SubjectID,s.SubjectName,'True',GetDate(),NULL
FROM StudentsDB..Subjects s
INNER JOIN StudentsDW..Subjects w
ON s.SubjectID = w.SubjectID
WHERE s.SubjectName <> w.SubjectName
AND w.Current_row = 'True' AND End_Date IS NULL
... 21 21 Цветанка Георгиева Моделиране на информационни системи
Управляване на Тип 2 бавно променящи се размерности
Пример – поддържане на атрибута учебен предмет
SubjectName на таблицата-размерност Subjects.
...
UPDATE Subjects
SET Current_row = 'False',
End_Date = GetDate()
FROM StudentsDB..Subjects s
INNER JOIN StudentsDW..Subjects w
ON s.SubjectID = w.SubjectID
WHERE s.SubjectName <> w.SubjectName
AND w.Current_row = 'True'
22 22 Цветанка Георгиева Моделиране на информационни системи
ИЗВЛИЧАНЕ, ТРАНСФОРМИРАНЕ И ЗАРЕЖДАНЕ НА
ДАННИ (4)
Управляване на Тип 3 бавно променящи се размерности
Пример – поддържане на атрибута SubjectName на
таблицата-размерност Subjects:
CREATE PROCEDURE Refresh_SubjectName_DW_Type3
AS
UPDATE Subjects
SET CurrentSubjectName = s.SubjectName
FROM StudentsDB..Subjects s
INNER JOIN StudentsDW..Subjects w
ON s.SubjectID = w.SubjectID
WHERE s.SubjectName <> w.CurrentSubjectName
OR w.CurrentSubjectName IS NULL
23 23 Цветанка Георгиева Моделиране на информационни системи
ИЗВЛИЧАНЕ, ТРАНСФОРМИРАНЕ И ЗАРЕЖДАНЕ НА
ДАННИ (5)
Обработка на факт-таблиците
За да бъдат получени стойностите на ключовете-
заместители за факт-таблицата, се налага поддържане на
допълнителна таблица за обработка (staging table).
Пример
24 24 Цветанка Георгиева Моделиране на информационни системи
ИЗВЛИЧАНЕ, ТРАНСФОРМИРАНЕ И ЗАРЕЖДАНЕ НА
ДАННИ (6)
Попълване на новите данни във факт-таблицата в базата от
данни на склада за данни:
Първият етап включва:
въвеждане на новите данни в допълнителната таблица
за обработка;
получаването на стойностите на ключовете-
заместители с помощта на стойностите на първичните
ключове на таблиците от OLTP базата от данни, които
се съхраняват в съответните таблици-размерности.
На втория етап е необходимо всички нови редове в
таблицата за обработка (въведени след първия етап) да
се добавят във факт-таблицата.
25 25 Цветанка Георгиева Моделиране на информационни системи
CREATE PROCEDURE InsertIntoTeach_fact_Staging
AS
INSERT INTO Teach_fact_Staging
(SpecialityID, TeacherID, SubjectID, Date,
LecturesTotal, ExercisesTotal)
SELECT t.SpecialityID, t.TeacherID, t.SubjectID,
CONVERT(char(14), GetDate(), 120) + '0:00',
SUM(t.Lectures), SUM(t.Exercises)
FROM StudentsDB..Teach t
LEFT JOIN Teach_fact_Staging ts
ON t.SpecialityID = ts.SpecialityID
AND t.TeacherID = ts.TeacherID
AND t.SubjectID = ts.SubjectID
AND DATEDIFF(day, GetDate(), ts.Date) = 0
WHERE ts.SpecialityID IS NULL
GROUP BY t.SpecialityID, t.TeacherID, t.SubjectID
UPDATE Teach_fact_Staging
SET SpecialityKey = s.SpecialityKey
FROM Teach_fact_Staging t
INNER JOIN Specialities s
ON t.SpecialityID = s.SpecialityID
WHERE t.SpecialityKey = 0
...
Първи етап
Аналогично за
останалите
размерности
Попълване на новите данни във факт-таблицата в базата от
данни на склада за данни – втори етап
CREATE PROCEDURE InsertInto_Teach_fact
AS
INSERT INTO Teach_fact
(SpecialityKey, TeacherKey,
SubjectKey, DateKey,
LecturesTotal, ExercisesTotal)
SELECT ts.SpecialityKey, ts.TeacherKey,
ts.SubjectKey, ts.DateKey,
ts.LecturesTotal, ts.ExercisesTotal
FROM Teach_fact_Staging ts
LEFT JOIN Teach_fact tw
ON ts.SpecialityKey = tw.SpecialityKey
AND ts.TeacherKey = tw.TeacherKey
AND ts.SubjectKey = tw.SubjectKey
AND ts.DateKey = tw.DateKey
WHERE tw.SpecialityKey IS NULL
27 27 Цветанка Георгиева Моделиране на информационни системи
ЛИТЕРАТУРА
Connolly T., C. Begg, Database Systems: A Practical Approach
to Design, Implementation and Management, Fourth edition,
Addison-Wesley, 2004
Inmon W. H., Building the Data Warehouse, Wiley Publishing,
Inc., 2005
Kimball R., The Data Warehouse Toolkit: The Definitive Guide to
Dimensional Modeling, Wiley, 3rd edition, 2013
Mundy J., W. Thornthwaite, R. Kimball, The Microsoft Data
Warehouse Toolkit: With SQL Server 2005 and the Microsoft
Business Intelligence Toolset, John Wiley & Sons, 2006
Rainardi V., Building a Data Warehouse: With Examples in SQL
Server, Apress, 2007
28 28 Цветанка Георгиева Моделиране на информационни системи