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.


 

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

69224. Статистичне вивчення динаміки соціально-економічних явищ 507.5 KB
  Для будь-якого динамічного ряду характерні перелік хронологічних дат моментів або інтервалів часу і конкретні значення відповідних статистичних показників які називають рівнями ряду. Приймаючи будь-який інтервал за одиницю послідовність рівнів можна записати так: де число рівнів довжина динамічного ряду.
69225. Предмет, метод та завдання статистики 187.5 KB
  Статистика - самостійна суспільна наука, яка має свій предмет и метод дослідження. Виникла вона з практичної необхідності суспільного життя. Уже в давньому світі з’явилась необхідність підраховувати чисельність жителів держави; рахувати людей, пригодних до військової справи...
69226. Графічний метод зображення статистичних даних 334.5 KB
  Вимоги до статистичного графіка та його основні елементи. Слід нагадати що побудова графіка виправдана якщо він дає будьякі переваги порівняно з цифрами які зведені в ряди або таблиці. Основні елементи графіка.
69227. Статистичне зведення та групування 188 KB
  Значення коефіцієнтів наведено в таблиці. Статистичні таблиці їх значення в статистиці види таблиць. Статистичні таблиці призначені для найбільш раціонального наочного та систематизованого викладення результатів зведення і групування статистичних даних.
69228. Індекси. Суть індексів, їх особливості як узагальнюючих показників 143.5 KB
  Термін індекс (іпdех) є синонімом певної узагальнюючої характеристики. Наприклад, індекс реальних доходів населення за рік, індекс курсової вартості цінних паперів за місяць, регіональний індекс злочинності тощо. Кожний індекс є співвідношенням двох значень показника...
69229. Історія формування та розвитку статистики. Статистика у феодальному суспільстві та в епоху відродження. Розповсюдження товарно–грошових відносин та розвиток статистики 74 KB
  Історія вітчизняної соціально-економічної статистики Розвиток державної статистики визначається багатьма умовами і чинниками економічного соціального організаційного характеру. Становлення державної статистики можна віднести до кінця XVII початку XVIII в.
69230. Організація державної статистики в Україні та її структура 128 KB
  Цей Закон регулює правові відносини в галузі статистики і ведення первинного обліку визначає повноваження та функції органів державної статистики і створює основу для ведення державної інформаційної системи України з метою одержання достовірної статистичної інформації...
69231. Організація міжнародної статистики 155.5 KB
  Таким чином послідовна розробка міжнародних рекомендацій в області статистики дозволила МСІ закласти наукові основи міжнародних класифікацій по найважливіших розділах статистики і їх застосуванні але вже опосередковано через діяльність Ліги Націй і через ООН...
69232. Статистика продуктивності праці 120 KB
  Статистика продуктивності праці. Поняття продуктивності праці та завдання її статистичного вивчення. Показники рівня продуктивності праці та методи їх обчислення. Індексний метод у вивченні динаміки продуктивності праці.