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.


 

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

31289. Дослідження комбінаційних схем, реалізованих за методом декомпозиції 1.2 MB
  Знайти гарантовано мінімальний вираз для довільної функції можна лише перебравши всі варіанти різних способів групування в процесі мінімізації що реально лише для невеликої кількості аргументів. З точки зору підходів до спрощення логічних виразів функції з якими має справу схемотехнік доцільно розділити на три групи: функції невеликої кількості аргументів €œоб’єктивні€ функції багатьох аргументів €œсуб’єктивні€ функції багатьох аргументів. До першої групи відносять функції трьохп’яти аргументів. Статистичний аналіз реальних схем...
31290. Дослідження схем синхронних та асинхронних цифрових автоматів з пам’яттю в пакеті Electronics Workbench 2.88 MB
  При моделюванні роботи синхронного автомата синхросерію слід подавати з генератора коливань обравши прямокутну форму імпульсів з параметрами близькими до вказаних на рис. Побудування логічних вентилів при синтезі синхронного автомата Якщо потрібно сформувати пам’ять автомата на Ттригерах не слід шукати їх в бібліотеці елементів так як їх фізично не існує необхідно побудувати Т тригер з JK тригера походячи з таблиці переходів. Часові діаграми роботи автомата слід скопіювати через буфер до редактора Paint або іншого графічного...
31291. Вивчення структури контролера КРВМ-2 та його засобів вводу-виводу 677.5 KB
  ЯПВВ - комірка програмованого вводу-виводу. Забезпечує зв’язок з зовнішніми об’єктами за будь-яким напрямком. До складу комірки входить мікросхема КР580ВВ55, порти якої з’єднані із зовнішніми приладами через шинні підсилювачі К589АП16, 2 шинних формувача КР580ВА86, мікросхеми К555ИД4 (здвоєний дешифратор 2 входи – 4 виходи), мікросхеми К155ТМ8 (4 D-тригери), К155ЛА3 (4 елементи 2І-НІ).
31292. Розрахунок генераторів пилкоподібної напруги 408 KB
  широко використовуються генератори пилкоподібної лінійнозмінної напруги. Часову діаграму пилкоподібної напруги наведено на рис.1 Часова діаграма пилкоподібної напруги Основними параметрами такої напруги є: тривалість робочого і зворотного ходу пилкоподібної напруги; період проходження імпульсів ; амплітуда імпульсів ; коефіцієнт нелінійності і коефіцієнт використання напруги джерела живлення .
31293. Розрахунок схем активних фільтрів 778 KB
  Апроксимація характеристик активних фільтрів зводиться до вибору таких коефіцієнтів цих поліномів що забезпечують найкраще в тому чи іншому значенні наближення до бажаних амплітудночастотної АЧХ чи фазочастотної характеристик фільтра.1 де відносна частота; частота зрізу; порядок фільтра. В фільтрі Чебишева апроксимуюча функція вибирається так щоб в смузі пропускання фільтра отримати відхилення його характеристики від ідеальної що не перевищує деякої заданої величини.2 де постійний коефіцієнт що визначає нерівномірність АЧХ...
31294. Мінімізація логічних функцій 449.5 KB
  Основна задача при побудові систем керування дискретними обєктами і процесами на основі логічних функцій: приведення логічних функцій керування до найбільш простого виду при якому система керування буде виконувати свої задачі. Для ручної мінімізації логічних функцій використовуються карти Карно і діаграми Вейча причому останні будують як розгорнення кубів на площині карти Карно.
31295. Тема: Синтез комбінаційних схем на мікросхемах середнього ступеня інтеграції Мета заняття:Закріпити отр. 1.08 MB
  Традиційно ця назва застосовується до вузлів робота яких не описується досить простим алгоритмом а задається таблицею відповідності входів і виходів.1 Якщо декодер має входів виходів і використовує всі можливі набори вхідних змінних то . Число входів і виходів декодера вказують таким чином: декодер 38 читається €œтри на вісім€ 416 410 неповний декодер. Мультиплексор – це функціональний вузол що здійснює підключення комутацію одного з декількох входів даних до виходу.