14616

Моделювання процесів оперативного аналізу даних

Лабораторная работа

Информатика, кибернетика и программирование

Лабораторна робота № 6 з дисципліни: Технології сховищ даних на тему: Моделювання процесів оперативного аналізу даних Мета роботи: Вивчення порядку методів та засобів створення аналітичних та підсумкових даних і документів на основі технології...

Украинкский

2013-06-08

231.29 KB

4 чел.

Лабораторна робота № 6

з дисципліни:

«Технології сховищ даних»

на тему:

«Моделювання процесів оперативного аналізу даних»


Мета роботи: Вивчення порядку, методів та засобів створення аналітичних та підсумкових даних і документів на основі технології OLAP.

 

Теоретичні відомості

OLAP (On-Line Analytical Processing) – системи аналітичного опрацювання, також відомі як системи підтримки прийняття рішення СППР (Decision Support System, DSS), орієнтовані на надання користувачам могутніх механізмів для швидкого і багатобічного аналізу даних. Причина використання OLAP для опрацювання запитів — це швидкість. Реляційні БД зберігають суть в окремих таблицях, які зазвичай добре нормалізовані. Ця структура зручна для операційних БД (системи OLTP), але складні багатотабличні запити в ній виконуються відносно поволі. OLAP робить миттєвий знімок реляційної БД і структурує її в просторову модель для запитів. Заявлений час опрацювання запитів в OLAP складає близько 0.1 % від аналогічних запитів до реляційної БД.

OLAP-структура, створена з робочих даних, називається OLAP-куб (OLAP-сховище). Куб створюється із з'єднання таблиць із застосуванням схеми «зірка».

Хоча OLAP і не є необхідним атрибутом сховища даних, він все частіше застосовується для аналізу накопичених у цьому сховищі відомостей.

Компоненти, що входять у типове сховище, подані на рис. 1.

Рис. 1.  Структура сховища даних з OLAP.

Оперативні дані збираються з різних джерел, очищаються, інтеґруються і переміщуються у реляційне сховище. При цьому вони вже доступні для аналізу за допомогою різних засобів побудови звітів. Потім дані (повністю або частково) готуються для OLAP-аналізу. Вони можуть бути завантажені до спеціальної БД OLAP або залишені у реляційному сховищі. Найважливішим його елементом є метадані, тобто інформація про структуру, розміщення і трансформацію даних. Завдяки ним забезпечується ефективна взаємодія різних компонентів сховища.

Підсумовуючи, можна визначити OLAP як сукупність засобів багатовимірного аналізу даних, накопичених в сховищі. Теоретично засоби OLAP можна застосовувати і безпосередньо до оперативних даних або їх точних копій (щоб не заважати оперативним користувачам). Але тоді виникає ризик певної непослідовності, а саме, аналізу оперативних даних, які безпосередньо для аналізу непридатні.

Види OLAP-рішень подані в таблиці 1.

Таблиця 1.  Вплив OLAP на керівні рішення

Тип аналізу

Вплив на керівні рішення

Аналіз росту продажів (по підприємствах)

Планування і оптимізація сезонних продажів різноманітних продуктів

Аналіз росту продажів (по клієнтах підприємств)

Поточне планування доставки продуктів підприємствам

Аналіз реґіональних продажів через підприємства

Ефективніше керування ресурсами на підприємстві (час доставки, кількість, внутрішні переміщення між підприємствами).

Аналіз прибутковості продажів (по підприємствах)

Аналіз росту цінних паперів

Аналіз продажів і торгових структур

Аналіз «продаж -запас-постачання»

Аналіз відхилень у продажах і постачанні, заплановано/у наявності (по підприємствах)

Необхідна попередня оцінка результатів діяльності підприємства; з метою своєчасного виправлення відхилень і коригування продажів з виробничим планом

Аналіз росту цін (по підприємствах)

Визначення оптимального відношення між попитом і пропозицією

Аналіз структури клієнтів підприємства

Цей аналіз проводиться з метою оптимізації умов для найбільших клієнтів і визначення оптимального рівня цін

Аналіз структури і росту одержання рахунків дебіторів (по підприємствам)

Цей аналіз проводиться для рішення питання з рахунками дебіторів, що безпосередньо пов'язано з керуванням рухом грошових коштів; установлення різних кредитних ліній для різних клієнтів; визначення взаємозв'язку між знижками й умовами платежу

Аналіз руху вхідних і вихідних коштів(по підприємствам)

Необхідно керувати рухом грошових коштів компанії

Хід роботи

  1.  Виберемо середню кількість забитих голів у матчі:

select Date, AVG(Results.Score1 + Results.Score2) as GoalsAvarage

from Matches

join Results

 on Results.ResultId = Matches.ResultId

group by Matches.Date

order by GoalsAvarage desc

Рис. 1. Середні кількості голів у матчі

  1.  Виберемо найбільш розгромні результати у кожен день:

select Date, MAX(ABS(Results.Score1 - Results.Score2)) as MaxGoalsDifference

from Matches

join Results

 on Results.ResultId = Matches.ResultId

group by Matches.Date

order by MaxGoalsDifference desc

 

Рис. 2. Найбільш розгромні результати по днях

  1.  Виберемо кількість ставок кожного користувача:

select Users.Login, COUNT(Bets.UserId)

from Users

join Bets on Bets.UserId = Users.UserId

group by Users.Login

Рис. 3. Кількість ставок по користувачах

  1.  Створимо функцію, яка повертатиме список груп, у яких перебуває заданий користувач:

create function UserGroups(@userid int)

returns nvarchar(max) as

begin

declare @res nvarchar(max);

set @res = '';

 

select @res = @res + Name + ', '

from Groups

 join GroupUserTickets on GroupUserTickets.UserId = @userid

 where Groups.GroupId = GroupUserTickets.GroupId

return @res;

end;

select [Login], dbo.UserGroups([UserId])

from Users

Рис. 4. Результат вибірки з застосуванням створеної функції

  1.  Створимо процедуру, яка обробляє ставки, після отримання результату матчу, на який ставили:

create procedure ProcessBets(@matchId int) as

update GroupUserTickets

set UserRateInGroup = ISNULL(UserRateInGroup, 0) + 2

from GroupUserTickets

inner join Users on GroupUserTickets.UserId = Users.UserId

inner join Bets on Bets.UserId = Users.UserId

inner join Matches on Matches.MatchId = Bets.MatchId

inner join GroupMatchTickets on GroupMatchTickets.MatchId = Matches.MatchId and GroupMatchTickets.GroupId = GroupUserTickets.GroupId

where Matches.ResultId = Bets.ResultId

 and Matches.MatchId = @matchId

go

 

select * from BetBattles.dbo.GroupUserTickets

exec BetBattles.dbo.ProcessBets 25

go

select * from BetBattles.dbo.GroupUserTickets

Рис. 5. Результати до і після виконання процедури, що обробляє ставки

Створена у процедура є далеко не ідеальною, тому покращимо її так, щоб не можна було двічі оновлювати рейтинг користувача завдяки одній ставці:

create procedure ProcessBets(@matchId int) as

begin transaction

update GroupUserTickets

set UserRateInGroup = ISNULL(UserRateInGroup, 0) + 2

from GroupUserTickets

inner join Users on GroupUserTickets.UserId = Users.UserId

inner join Bets on Bets.UserId = Users.UserId

inner join Matches on Matches.MatchId = Bets.MatchId

inner join GroupMatchTickets on GroupMatchTickets.MatchId = Matches.MatchId and GroupMatchTickets.GroupId = GroupUserTickets.GroupId

where Matches.ResultId = Bets.ResultId

 and Matches.MatchId = @matchId

 and Bets.IsReleased = 0

 

update Bets

set IsReleased = 1

from GroupUserTickets

inner join Users on GroupUserTickets.UserId = Users.UserId

inner join Bets on Bets.UserId = Users.UserId

inner join Matches on Matches.MatchId = Bets.MatchId

inner join GroupMatchTickets on GroupMatchTickets.MatchId = Matches.MatchId and GroupMatchTickets.GroupId = GroupUserTickets.GroupId

where Matches.ResultId = Bets.ResultId

 and Matches.MatchId = @matchId

commit transaction

Змінена процедура не дає можливості двічі оновити рейтинг користувача за однією ставкою, оскільки оновлює лише ті ставки, які ще не були оновлені (за це відповідає поле IsReleased). Крім того, після обробки всіх ставок це поле теж оновлюється, при чому всередині транзакції – це забезпечує цілісність даних та роботу бази даних так, як було задумано на початку її проектування.

Для перевірки роботи нової процедури виконаємо наступний запит:

select * from BetBattles.dbo.GroupUserTickets

exec BetBattles.dbo.ProcessBets 25

go

select * from BetBattles.dbo.GroupUserTickets

exec BetBattles.dbo.ProcessBets 25

go

select * from BetBattles.dbo.GroupUserTickets

Рис. 6. Результати до і після виконання процедури, що обробляє ставки з використанням транзакцій

Як бачимо, в першій вибірці рейтинг користувача був 2, на другій, в результаті оновлення ставки він став рівним 4, а на третій він залишився незмінним, оскільки процедура не оновила повторно вже оновлену ставку.

Для тестування роботи транзакцій у випадку помилки змінимо процедуру так:

create procedure ProcessBets(@matchId int) as

begin transaction

update GroupUserTickets

set UserRateInGroup = ISNULL(UserRateInGroup, 0) + 2

from GroupUserTickets

inner join Users on GroupUserTickets.UserId = Users.UserId

inner join Bets on Bets.UserId = Users.UserId

inner join Matches on Matches.MatchId = Bets.MatchId

inner join GroupMatchTickets on GroupMatchTickets.MatchId = Matches.MatchId and GroupMatchTickets.GroupId = GroupUserTickets.GroupId

where Matches.ResultId = Bets.ResultId

 and Matches.MatchId = @matchId

 and Bets.IsReleased = 0;

 

raiserror('Error during updating bets', 18, 0)

 

update Bets

set IsReleased = 1

from GroupUserTickets

inner join Users on GroupUserTickets.UserId = Users.UserId

inner join Bets on Bets.UserId = Users.UserId

inner join Matches on Matches.MatchId = Bets.MatchId

inner join GroupMatchTickets on GroupMatchTickets.MatchId = Matches.MatchId and GroupMatchTickets.GroupId = GroupUserTickets.GroupId

where Matches.ResultId = Bets.ResultId

 and Matches.MatchId = @matchId

commit transaction

go

Після цього виконаємо наступний запит:

update Bets

set IsReleased = 0

where BetId = 10

select * from BetBattles.dbo.GroupUserTickets

exec BetBattles.dbo.ProcessBets 60

go

select * from BetBattles.dbo.GroupUserTickets

Цей запит відміняє мітку обробки ставки, виводить рейтинг користувача у групі, виконує процедуру та знову виводить рейтинги. Виконання такого запиту призводить до такого повідомлення:

(1 row(s) affected)

(17 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

Msg 50000, Level 18, State 0, Procedure ProcessBets, Line 14

Error during updating bets

(17 row(s) affected)

В ньому, що зміна мітки та перша вибірка проходять успішно, потім відбувається помилка під час виконання процедури, та успішно проходить друга вибірка.

Як видно на рисунку 2 зміни, що процедура зробила до виникнення помилки ніяк не вплинули на стан бази, оскільки помилка виникла під час незавершеної транзакції і зміни не набули чинності.

Рис. 7. Демонстрація властивостей транзакцій

  1.  За допомогою директиви SHOW INDEX визначимо наявні індекси для таблиць Matches та Commands.

SELECT T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME, I.is_unique, I.is_primary_key

 FROM SYS.TABLES T

      INNER JOIN SYS.INDEXES I

   ON I.OBJECT_ID = T.OBJECT_ID

      INNER JOIN SYS.INDEX_COLUMNS IC

   ON IC.OBJECT_ID = T.OBJECT_ID

      INNER JOIN SYS.COLUMNS C

   ON C.OBJECT_ID = T.OBJECT_ID

  where T.name = 'Matches'

  AND IC.INDEX_ID = I.INDEX_ID

  AND IC.COLUMN_ID = C.COLUMN_ID

Рис.8. Індекси таблиці Matches

SELECT T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME, I.is_unique, I.is_primary_key

 FROM SYS.TABLES T

      INNER JOIN SYS.INDEXES I

   ON I.OBJECT_ID = T.OBJECT_ID

      INNER JOIN SYS.INDEX_COLUMNS IC

   ON IC.OBJECT_ID = T.OBJECT_ID

      INNER JOIN SYS.COLUMNS C

   ON C.OBJECT_ID = T.OBJECT_ID

  where T.name = 'Commands'

  AND IC.INDEX_ID = I.INDEX_ID

  AND IC.COLUMN_ID = C.COLUMN_ID

Рис.9. Індекси таблиці Commands

2. Створимо новий індекс для таблиці Matches i Commands. У БД є декілька запитів, які здійснюють вибірку даних за датою матчу (поле Date), а пошук матчів за іменем команди теж буде непоганою ідеєю. Створення індексів для цих полів повинно оптимізувати виконання запитів.

CREATE INDEX MatchDateIndex ON Matches (Date);

 SELECT T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME, I.is_unique, I.is_primary_key

 FROM SYS.TABLES T

      INNER JOIN SYS.INDEXES I

   ON I.OBJECT_ID = T.OBJECT_ID

      INNER JOIN SYS.INDEX_COLUMNS IC

   ON IC.OBJECT_ID = T.OBJECT_ID

      INNER JOIN SYS.COLUMNS C

   ON C.OBJECT_ID = T.OBJECT_ID

  where T.name = 'Matches'

  AND IC.INDEX_ID = I.INDEX_ID

  AND IC.COLUMN_ID = C.COLUMN_ID

Рис.10. Додано новий індекс до таблиці Matches

CREATE INDEX CommandNameIndex ON Commands (Name);

SELECT T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME, I.is_unique, I.is_primary_key

FROM SYS.TABLES T

  INNER JOIN SYS.INDEXES I

ON I.OBJECT_ID = T.OBJECT_ID

  INNER JOIN SYS.INDEX_COLUMNS IC

ON IC.OBJECT_ID = T.OBJECT_ID

  INNER JOIN SYS.COLUMNS C

ON C.OBJECT_ID = T.OBJECT_ID

where T.name = 'Commands'

AND IC.INDEX_ID = I.INDEX_ID

AND IC.COLUMN_ID = C.COLUMN_ID

Рис.11. Додано новий індекс до таблиці Commands

3. Виконаємо аналіз виконання складного запиту з однієї з попередніх робіт використовуючи EXPLAIN та опцію STRAIGHT_JOIN.

set SHOWPLAN_TEXT ON

go

select Groups.GroupId, Command1.Name, Command2.Name, Matches.[Date]

from Matches

left join GroupMatchTickets on GroupMatchTickets.MatchId = Matches.MatchId

left join Groups on Groups.GroupId = GroupMatchTickets.GroupId

inner join Commands Command1 on Command1.CommandId = Matches.Command1Id

inner join Commands Command2 on Command2.CommandId = Matches.Command2Id

Рис.5. Виведення аналізу роботи запиту

Висновок: Під час виконання даної лабораторної роботи, я вивчив порядок, методи та засоби створення аналітичних та підсумкових даних і документів на основі технології OLAP.


 

А также другие работы, которые могут Вас заинтересовать

85036. Формирование взаимоотношений со сверстниками противоположного пола 30.74 KB
  Формирование взаимоотношений со сверстниками противоположного пола Цель урока. Сформировать убеждение что каждый человек должен рассматривать вопросы взаимоотношений с лицами противоположного пола с позиций норм нравственности и обеспечения сохранения своего здоровья и здоровья окружающих. Обратить внимание учащихся на особое место которое начинают занимать в их жизни взаимоотношения со сверстниками противоположного пола. Интерес к вопросам пола и первая влюбленность у многих возникают очень рано часто задолго до наступления полового...
85037. Взаимоотношения подростка и общества. Правовая ответственность несовершеннолетних 30.94 KB
  Проанализировать основные причины правонарушений совершаемых в подростковом возрасте; разобрать с учащимися отдельные положения Уголовного кодекса РФ предусматривающие уголовную ответственность несовершеннолетних. Основные причины правонарушений совершаемых подростками. Эти знания позволят каждому избежать правонарушений которые совершаются случайно за компанию и не стать преступником. Изучение совершенных подростками правонарушений показывает что в большинстве случаев эти поступки явились следствием притязаний молодых людей на...
85038. Здоровый образ жизни и безопасность жизнедеятельности 31.03 KB
  Сформировать у учащихся целостное представление о том что здоровый образ жизни залог безопасности человека в повседневной жизни и в различных опасных и чрезвычайных ситуациях. Общие понятия о культуре безопасности жизнедеятельности. Уровень культуры безопасности жизнедеятельности как критерий определения уровня здоровья и безопасности. Подчеркнуть что общая культура населения нашей страны в области безопасности во многом не соответствует реальным условиям жизнедеятельности она отстает от стремительных темпов развития цивилизации.
85039. Первая медицинская помощь пострадавшим и ее значение 31.59 KB
  Познакомить учащихся с правилами оказания первой помощи и средствами которые могут быть использованы при этом. Сформировать у них убеждение в том что каждый человек должен владеть приемами в оказании первой медицинской помощи пострадавшим. Общие правила оказания первой медицинской помощи. Средства используемые при оказании первой медицинской помощи.
85040. Первая медицинская помощь при отравлениях аварийно химически опасными веществами (АХОВ) 25.42 KB
  Первая медицинская помощь при отравлениях аварийно химически опасными веществами АХОВ Цель урока. Познакомить учащихся с правилами оказания первой медицинской помощи при отравлении наиболее распространенными аварийно химически опасными веществами аммиаком и хлором. Если такой возможности нет повторить с учащимися какие химически опасные вещества относятся к АХОВ напомнить что наиболее распространенными АХОВ являются аммиак и хлор. Контрольные вопросы Какие вещества называются аварийно химически опасными Каковы признаки отравления...
85041. Первая медицинская помощь при травмах. Первая медицинская помощь при утоплении 27.1 KB
  Познакомить учащихся с правилами оказания первой медицинской помощи при переломах вывихах растяжениях и разрывах связок. Познакомить учащихся с правилами оказания первой медицинской помощи при утоплении. Объяснить правила оказания первой медицинской помощи при травмах: при переломах; при вывихах растяжениях и разрывах связок; при растяжении мышц и сухожилий. При этом подчеркнуть что объем мероприятий первой медицинской помощи зависит от тяжести состояния пострадавшего.
85042. Пожары в жилых и общественных зданиях, их причины и последствия 31.81 KB
  Дать учащимся представление о значении огня в жизнедеятельности человека о причинах возникновения пожаров и их возможных последствиях для безопасности человека. Изучаемые вопросы Значение огня в жизнедеятельности человека. Используя материалы курса истории обсудить с учащимися значение огня в жизнедеятельности человека на разных этапах развития общества. Благодаря использованию огня человек становился все меньше зависимым от природных условий существования.
85043. Профилактика пожаров в повседневной жизни и организация защиты населения. Права, обязанности и ответственность граждан в области пожарной безопасности 31.93 KB
  Права обязанности и ответственность граждан в области пожарной безопасности. Разъяснить учащимся значение профилактики пожаров; обсудить основные направления деятельности человека по обеспечению пожарной безопасности. Сформировать у учащихся чувство ответственного отношения к правилам пожарной безопасности к рекомендациям специалистов по правилам поведения для обеспечения личной безопасности во время пожара. Основные направления деятельности человека по обеспечению пожарной безопасности.
85044. Безопасное поведение на водоемах в различных условиях 33.55 KB
  Оказание помощи терпящим бедствие на воде. Закрепить знания правил безопасного поведения на воде во время купания во время водных походов в условиях аварийной ситуации возникшей во время водного пожара. Познакомить учащихся с основными способами оказания помощи терпящим бедствие на воде. Изучаемые вопросы Значение воды в жизнедеятельности человека безопасность на воде.