КАЗАНСКИЙ (ПРИВОЛЖСКИЙ) ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ
 Пинягина О.В.
Хранилища
данных
Методические
рекомендации
для выполнения
практических заданий
Казань – 2018
УДК 004.6
ББК 32.973.26 – 018.2
Печатается по решению Редакционно-издательского совета
ФГАОУВПО «Казанский (Приволжский) федеральный университет»,
Редакционно-издательского совета Института вычислительной
математики и информационных технологий
Протокол № _____ от______,
заседания кафедры анализа данных и исследования операций
Протокол № _____ от______.
Рецензенты:
……………………………………………………….
Пинягина О.В.
Хранилища данных. Методические рекомендации для выполнения практических заданий / О.В. Пинягина – Казань: Казанский университет, 2018. – 80 с.
Данное учебное пособие разработано для поддержки компьютерных лабораторных занятий и самостоятельной работы по курсу «Хранилища данных» для студентов, обучающихся по специальностям «Бизнес-информатика», «Прикладная информатика».
В качестве среды программирования используется Microsoft SQL Server и Deductor Studio Academic.
Электронный ресурс по данному курсу располагается на сайте кафедры анализа данных и исследования операций КФУ по адресу: http://kek.ksu.ru/EOS/DW/index.html .
Казанский университет, 2018
Пинягина О.В. 2018
Оглавление
Оглавление 3
Работа в программе SQL Server 4
Этап 1: Описание предметной области, разработка модели, создание таблиц. 4
Этап 2. Заполнение базы данными. 9
Этап 3. Запросы, представления, а также хранимые процедуры. 19
Этап 4. ETL – extract, transform, load (извлечь, преобразовать, загрузить). 29
Этап 5. ETL – extract, transform, load (извлечь, преобразовать, загрузить). 33
Работа в программе Deductor Studio 37
Этап 6а: Загрузка данных в Deductor Studio из текстовых файлов, создание хранилища данных в СУБД FireBird. 37
Этап 6б: Преобразования и визуализаторы. 52
Этап 7. Применение методов Data Mining. 73
Литература 81
Работа в программе SQL Server
Этап 1: Описание предметной области, разработка модели, создание таблиц.
Постановка задачи:
В базе данных «Аптеки» накапливается оперативная информация по реализации лекарств и других товаров через аптечную сеть, состоящую из нескольких аптек, расположенных в нескольких городах нескольких регионов. Каждый товар имеет фирму-производителя, тип, вид фасовки, текущую цену. В один чек может быть внесено несколько товаров. В зависимости от общей суммы покупки делается скидка по следующему правилу: от 1000 до 5000 р. – 2%, более 5000 р. – 5%.
По мере накопления оперативной информации возникает необходимость её анализа. У пользователей появляются вопросы:
- Сколько единиц каждого товара и на какую сумму продано в каждой аптеке?
- Сколько единиц каждого типа товаров и на какую сумму продано
в каждой аптеке?
- На какую сумму продано лекарств «от гриппа» в каждой аптеке в порядке убывания итоговой суммы?
- А можно получить суммы продаж «от гриппа» по месяцам в порядке убывания итогов?
- А если по каждой аптеке отдельно?
- А сколько реализовано товара с названием «Товар 117» в «Аптеке 10» за август текущего года?
- А можно получить такую таблицу, чтобы столбцами были, например, месяцы, а в строках находились типы товаров и итоги продаж по месяцам?
- А можно построить графики или диаграммы для продаж за текущий год? А по отдельному товару? А сразу по нескольким товарам, для сравения?
- А можно построить прогноз продаж на следующий месяц?
И т.п.
Как видим, здесь мы рассматриваем основной бизнес-процесс аптечной сети – продажу товаров покупателям. Разумеется, у организации имеются и другие бизнес-процессы: оптовая покупка товаров у поставщиков, учет сотрудников, покупка оборудования и т.п.
Начнем реализацию задачи с построения ER-модели:
Для этой базы данных используется модель «Снежинка». Центральным набором объектов (набором событий, или фактов) является «Продажа». Она представляет собой слабую сущность, которая зависит от сущностей (измерений) «Чек», «Товар» и «Отдел». В свою очередь, сущность «Товар» ссылается на измерения «Фирма-производитель» и «Тип товара», а сущность «Отдел» ссылается на сущность «Аптека».
В модели «Снежинка» всегда имеется некоторая сущность (набор фактов), которая является центром, от нее исходят лучи к сущностям следующего уровня (измерениям), от которых также могут исходить лучи к сущностям третьего уровня (измерениям), и т.п. – получается ветвящаяся структура, отдаленно напоминающая снежинку:
Структура «Звезда» проще: здесь есть только центр (факты) и лучи, указывающие на один уровень измерений:
Итак, независимо от вида модели («снежинка» или «звездочка») центр представляет собой слабую сущность или связь «многие ко многим» (которая, как вы знаете, есть скрытая слабая сущность). А все остальные связи имеют тип «многие-к-одному» (в редких случаях «один-к-одному») и направлены из центра наружу. Если в вашей модели имеется несколько связей «многие-ко-многим», либо какие-то связи «один-ко-многим» направлены из центра наружу, это означает, что вы рассматриваете не один, а несколько бизнес-процессов, т.е., ваша схема представляет собой несколько «снежинок» и/или «звездочек», обычно «склеившихся» лучами.
Далее следует написать сценарий создания таблиц базы данных на языке SQL и выполнить его в среде SQL server management studio. Рекомендуется для названия баз данных, таблиц, столбцов и т.п. не использовать русские буквы.
После создания базы данных проведем «обратное проектирование» - создадим в SQL server диаграмму базы данных:
Убедимся, что полученная диаграмма по структуре соответствует нашей ER-модели.
Задание 1. Выберите предметную область, подходящую для разработки хранилища данных. Выберите в этой предметной области один бизнес-процесс. (Можете выбрать несколько бизнес-процессов, но при этом вырастет трудоемкость всех заданий.) Обратите внимание, что в описании бизнес-процесса вашей модели обязательно должны присутствовать атрибуты типа «дата» и/или «время».
Примерная схема может быть такой: оперативная информация хранится в слабой сущности, плюс 3 или более сильных сущности (модель «звезда»), либо ещё плюс дополнительные справочники (модель «снежинка»).
Создайте таблицы в SQL Server (5 баллов). После создания БД проведите «reverse engineering» (обратное проектирование) - создайте диаграмму базы данных с помощью автоматизированных средств SQL Server (2 балла). ER-модель сдавать не обязательно, достаточно диаграммы из SQL Server.
Итого 7 баллов.
Этап 2. Заполнение базы данными.
Реальные данные в масштабах тысяч записей нам взять негде. Поэтому будем генерировать их искусственно. Следует сгенерировать не менее 10 000 записей для оперативных данных и, по крайней мере, по 10-100 записей для остальных таблиц. Для генерации данных можно использовать хранимые процедуры, а также загружать данные из внешних источников. Рассмотрим все эти возможности.
Пример реализации:
Таблицу «Тип» заполним вручную:
SELECT * FROM Type
Для заполнения таблицы «Фирма» разработаем хранимую процедуру, которая создает 50 строк примерно такого вида:
CREATE PROC insert_firms AS
DECLARE @nom INT
SET @nom=1
WHILE @nom<=50
BEGIN
INSERT INTO Firm (NumFirm, NameFirm)
VALUES (@nom, 'Фирма '+LTRIM(STR(@nom)))
SET @nom=@nom+1
END
Пусть в нашей базе будет 2 региона, 10 городов, 50 аптек и 200 отделов.
Для заполнения таблицы «Аптека» разработаем хранимую процедуру, которая создает 50 строк примерно такого вида:
Пусть в каждом городе у нас по 5 аптек, города с номерами 1 – 3 относятся к первому региону, города с номерами 4 – 10 – ко второму региону.
Здесь поле «Телефон» заполнено псевдослучайными равномерно распределенными числами в диапазоне от 100000 до 999999. Для этого используется функция RAND(), которая возвращает значение с плавающей точкой, равномерно распределенное от 0 до 1. Для того чтобы эта функция в рамках процедуры всегда генерировала один и тот же набор случайных чисел, следует ее предварительно вызвать с параметром-константой, например: SET @x=RAND(1)
Итак, функция RAND возвращает случайное значение из отрезка [0, 1]. Для того чтобы получить, например, значение из отрезка [100, 150], следует умножить полученную величину на 50 (длина отрезка) и прибавить 100 (левый конец отрезка).
Для преобразования чисел с плавающей точкой к целому типу удобно использовать функцию CEILING (это «потолок» по-русски), которая преобразует свой аргумент к ближайшему большему целому числу. Есть также симметричная ей функция FLOOR, которая преобразует аргумент к ближайшему меньшему целому числу.
Далее заполним таблицу «Отдел» (в каждой аптеке по 4 отдела):
Для заполнения таблицы «Товар» разработаем хранимую процедуру, которая создает 350 строк примерно такого вида:
Здесь поле «НомерФирмы» заполнено псевдослучайными равномерно распределенными числами в диапазоне первичного ключа таблицы «Фирма». Поле «Номер типа» заполнено такими значениями: товар 1-товар 50 относится к типу 1, товар 51-100 относится к типу 2 и т.п., . . . товар 301-350 относится к типу 7.
Для заполнения поля «Цена» мы используем такое правило: 50 % лекарств имеет цену от 0 до 200 р. и 50 % - от 201 до 3000 р.
DECLARE @price NUMERIC(4),
@x FLOAT
-- предварительно инициализируем функцию RAND(), чтобы она
-- генерировала одну и ту же последовательность чисел
SET @x = RAND(1)
. . .
-- получаем случайную величину от 0 до 1
SET @x=RAND()
IF @x<=0.5 -- с вероятностью 0.5 цена от 0 до 200
SET @price=(RAND()*200.0)
ELSE -- с вероятностью 0.5 цена от 200 до 3000
SET @price=(RAND()*2800.0+200.0)
Далее заполним таблицу «Чек». Для этого разработаем хранимую процедуру, с помощью которой создадим 30 000 строк примерно такого вида:
Здесь дата генерируется следующим образом. Для того чтобы проиллюстрировать увеличение объема продаж, создадим
8 000 чеков за 2016 год,
10 000 чеков за 2017 год и
12 000 чеков за 2018 год.
Будем генерировать месяц по следующему правилу: 30% продаж приходится на зиму, а остальное – поровну на весну, лето и осень.
Номер дня также выбирается случайно, с учетом количества дней в полученном месяце. Затем из этих частей составляется дата в виде строковой переменной в формате ‘ГГГГ-ММ-ДД’. Время – часы, минуты, секунды – тоже можно при необходимости генерировать с помощью случайных величин.
Поле «Скидка» будет заполнено позже, когда будут данные о продажах.
Наконец, заполним таблицу «Продажа».
Для заполнения этой таблицы создадим хранимую процедуру, которая перебирает все строки из таблицы «Чек». (Вспомните о возможности использования курсоров!) Предполагаем, что в одном чеке могут присутствовать продажи из разных отделов, но только из одной аптеки.
В каждом чеке может быть от 1 до 5 наименований лекарств и прочих товаров, каждое в количестве от 1 до 3 штук:
1 штука с вероятностью 0.9,
2 штуки с вероятностью 0.09,
3 штуки с вероятностью 0.01.
Для того чтобы отразить сезонность продаж разных типов лекарств, используем следующие правила:
зимой 50% проданных лекарств относятся к типу "от гриппа", остальные типы равновероятны,
весной 50% проданных лекарств относятся к типу "витамины", остальные типы равновероятны,
летом 50% проданных лекарств относятся к типу "против диареи", остальные типы равновероятны,
осенью 50% проданных лекарств относятся к типу "от кашля", остальные типы равновероятны.
Цена лекарства копируется из таблицы «Товар», позже по сумме чека будем вычислять скидки.
В процессе выполнения процедуры было создано более 89 тыс. строк. Процедура выполнялась более 5 минут! Для такого количества данных это что-то слишком медленно; похоже, следует проанализировать узкие места процедуры.
Узкими местами обычно бывают условия в запросах. Выполнение запросов можно существенно ускорить, если применять индексы. В нашей процедуре используется запрос к таблице sale с условиями на столбцы numCheck и numDept, по которым в данной таблице нет индексов. Создадим их:
CREATE INDEX saleCheck ON sale (numCheck)
CREATE INDEX saleDept ON sale (numDept)
Снова запустим ту же самую процедуру. Теперь она выполнилась за 46 секунд!
Теперь напишем процедуру для вычисления скидок (это необязательный этап, просто для данной предметной области он позволяет придать нашим искусственным данным больше правдоподобия). В зависимости от общей суммы покупки скидка вычисляется по следующему правилу: от 1000 до 5000 р. – 2%, более 5000 р. – 5%. Обратите внимание на использование конструкции CASE!
CREATE PROC Discounts AS
DECLARE cur1 CURSOR FOR SELECT NumCheck FROM Bill
DECLARE @numCheck NUMERIC(6),
@sumCheck NUMERIC(6),
@coeff INT
OPEN cur1
FETCH cur1 INTO @numCheck
WHILE @@FETCH_STATUS=0
BEGIN
-- считаем сумму чека и процент скидки
SELECT @sumCheck=SUM(price*quantity), @coeff=
(CASE
WHEN @sumCheck>5000 THEN 5
WHEN @sumCheck>1000 AND @sumCheck<=5000 THEN 2
ELSE 0
END)
FROM sale WHERE @numCheck=NumCheck
-- записываем скидку в чек
UPDATE Bill SET Discount=@coeff WHERE @numCheck=NumCheck
-- уменьшаем цену в продажах на процент скидки
UPDATE Sale SET Price=Price*(100.0-@coeff)/100.0
WHERE @numCheck=NumCheck
FETCH cur1 INTO @numCheck
END
DEALLOCATE cur1
Таблицы «Чек» и «Продажа» после выполнения процедуры подсчета скидок:
Наконец, проиллюстрируем загрузку данных из внешнего текстового файла. Пусть для аналитических целей, кроме рассмотренных выше данных о продажах, нам понадобится информация о лекарствах, которые выдавались пациентам-льготникам бесплатно. Эту информацию мы сгенерируем, например, с помощью программы, написанной на C#.
В созданном файле данные хранятся в формате:
Номер; ФИО; паспорт; лекарство; дата; количество
Обратите внимание, что в формате даты день и месяц обязательно должен состоять из двух цифр!
Создаем таблицу с такой же структурой. Чтобы проще было копировать данные, порядок полей сделаем в точности таким же, как в файле:
CREATE TABLE SocialReceipt
( fioCust VARCHAR(100) NOT NULL,
pasportCust VARCHAR(10) NOT NULL,
nameArticle VARCHAR(20) NOT NULL,
dateCust DATETIME NOT NULL,
quantityCust INT NOT NULL)
Для загрузки данных из внешнего источника в SQL server существуют разные способы. Мы воспользуемся программой bcp (bulk copy procedure – процедура массового копирования). Эта программа служит как для загрузки данных из текстового файла, так и для выгрузки данных в текстовый файл.
Программа bcp запускается из командной строки Windows (Пуск – Поиск – cmd – Enter). В нашем случае формат команды следующий:
|
