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.


 

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

15537. Протокол як додатковий інформаційний документ 31 KB
  ПРОТОКОЛ Протокол документ що фіксує хід обговорення питань і прийняття рішень на зборах засіданнях нарадах. Комерційні підприємства складають протоколи засідань Ради директорів засновників протоколи загальних зборів акціонерів тощо. Протокол веде секрет...
15538. Трудова книжка 48.5 KB
  Трудова́ кни́жка основний документ що підтверджує трудову діяльність громадян і служить для встановлення загального стажу безперервного стажу а також спеціального стажу.Під час оформлення на роботу трудова книжка заповнюється працівниками відділу кадрів у п'ятид...
15539. Трудова книжка. Порядок її ведення 53 KB
  Трудова книжка. Порядок її ведення Трудова книжка це основний документ про трудову діяльність працівника який підтверджує страж роботи а також дає змогу власнику при прийнятті на роботу мати уяву про досвід роботи працівника. ...
15540. Цели настоящего Федерального закона 43.5 KB
  Цели настоящего Федерального закона Целями настоящего Федерального закона являются развитие рынков товаров работ и услуг на основе соблюдения принципов добросовестной конкуренции обеспечение в Российской Федерации единства экономического пространства реализация п
15541. Сфера применения настоящего Федерального закона 76.5 KB
  Сфера применения настоящего Федерального закона1. Настоящий Федеральный закон применяется к отношениям в сфере рекламы независимо от места ее производства если распространение рекламы осуществляется на территории Российской Федерации.2. Настоящий Федеральный закон не...
15542. Основные понятия, используемые в настоящем Федеральном законе 71 KB
  Основные понятия используемые в настоящем Федеральном законеВ целях настоящего Федерального закона используются следующие основные понятия:1 реклама информация распространенная любым способом в любой форме и с использованием любых средств адресованная неопреде
15543. Законодательство Российской Федерации о рекламе 37 KB
  Законодательство Российской Федерации о рекламе Законодательство Российской Федерации о рекламе состоит из настоящего Федерального закона. Отношения возникающие в процессе производства размещения и распространения рекламы могут регулироваться также принятыми в с
15544. Общие требования к рекламе 138.5 KB
  5. Общие требования к рекламе1. Реклама должна быть добросовестной и достоверной. Недобросовестная реклама и недостоверная реклама не допускаются.2. Недобросовестной признается реклама которая:1 содержит некорректные сравнения рекламируемого товара с находящимися в обо...