14616

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

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

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

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

Украинкский

2013-06-08

231.29 KB

3 чел.

Лабораторна робота № 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.


 

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

48823. Правове регулювання інвестиційної діяльності в спеціальних (вільних) економічних зонах в Україні 152.5 KB
  Вільні економічні зони Вез міцно увійшли у світову господарську практику та діють у різних державах. Зараз у світі по різним даним від 400 до 2000 Вез але до них не можна віднести ні одну українську оскільки ті утворення які в Україні називаються вільними економічними зонами не задовольняють міжнародні вимоги які виставляються до такого роду зонам. Крім того не звертаючи уваги на неодноразові спроби створення реально функціонуючих Вез в Україні вони остаються у вітчизняній економіці явищем скоріш екзотичним...
48824. Повышение эффективности транспортного обслуживания процессов ремонта магистральных нефтепроводов методом вырезки дефектного участка путем снижения суммарных затрат 27.71 MB
  Данные, полученные на основании контрольных измерений, испытаний и профилактических осмотров, систематизируют и используют в качестве основы для планирования и проведения ремонтных работ, в том числе и ремонтных работ связанных с вырезкой «катушки». На проведении ремонтов методом вырезки дефекта задействована спецтехника и оборудование.
48825. Проектирование общественного здания в РБ, г. Давлеканово 560.5 KB
  Проектируемое здание расположено на участке со спокойным рельефом с небольшим уклоном в северо-восточную сторону. Проектируемое здание расположено с отступом от красной линии. Главный фасад расположен на северо-восток. На участке расположены: жилой дом №1, жилой дом №2, больница, здание банка, парк, дом быта, кинотеатр, автомобильная стоянка.
48826. Кольца, полукольца, мера на полукольце 409 KB
  Кольцо множеств есть система множеств, замкнутая по отношению к взятию суммы и пересечения, вычитанию и образованию симметрической разности. Любое кольцо содержит пустое множество Ø, так как всегда А\A=Ø. Система, состоящая только из пустого множества, представляет собой наименьшее возможное кольцо множеств.
48828. ОРГАНІЗАЦІЯ ДОСЛІДНОЇ ДІЛЬНИЦІ ВИРОБНИЦТВА ОКТАН-КОРЕКТОРА 578.5 KB
  У цій частині курсового проекту розраховуємо дослідну дільницю виробництва Октан - коректора. Вихідними даними для розрахунку будуть: Програма випуску – 100 штук за 1 день. Режим роботи дільниці, що проектується, – одна зміна тривалістю 8 годин. Планово-операційна карта виготовлення виробу.
48829. ОРГАНІЗАЦІЯ ДОСЛІДНОЇ ДІЛЬНИЦІ ВИРОБНИЦТВА «ПІДСИЛЮВАЧ НЧ» 555.5 KB
  Розрахунок необхідної кількості робітників по професіям і розрядам проводиться на основі планово-операційної карти шляхом ділення об’єму роботи по кожній професії і розряду на дійсний фонд часу роботи одного робітника.
48830. Разработка одиночного стрелочного перевода 1.99 MB
  Расчет угла и марки крестовины Марка крестовины зависит от угла между рабочими гранями сердечника крестовины. Угол крестовины определяют из уравнения проекции расчетного контура стрелочного перевода на вертикальную ось.16 где конструктивные размеры обеспечивающие сборку переднего стыка крестовины мм; – длина накладки равная 820 мм для рельсов типа Р50; постоянный запас мм; показатель марки.18 Полученную таким путем показатель крестовины округляют в большую стороны.