Пошук інформації у базах даних

Однією з основних функцій маркетингової інформаційної системи є надання користувачам можливості здійснювати швидкий пошук необхідної інформації та отримання відповідей на різноманітні питання. Це пояснюється тим, що сьогодні більшість організацій мають доступ до практично необмеженої кількості даних, що містять відомості про продаж, демографічні дані, тренди, про продукти конкурентів тощо. Більша частина цих даних розподілена і знаходиться в локальних базах співробітників, в інтегрованій базі даних організації, у базах даних, зовнішніх щодо відповідної організації. Проте багато організацій не можуть ефективно перетворити ці дані в необхідну для прийняття рішень інформацію, подану у зручній формі. Наприклад, менеджери компаній нерідко не в змозі оперативно одержати відомості про обсяги продажу своїх продуктів у певних регіонах. Необхідні для цього дані, звісно, існують, проте швидкість, з якою вони можуть бути перетворені на зручну інформацію, залежить від інформаційної системи, використовуваної в компанії. Часто для збирання та аналізу потрібної інформації необхідні зусилля кількох людей протягом годин або навіть днів.

Питання, що формулюються щодо бази даних, називаються запитами. Наприклад, працюючи з базою даних, яка містить інформацію про продаж, можна отримати відповіді на такі запитання:

· Які обсяги продажу за останній тиждень, місяць, рік?

· Чи збільшилися обсяги продажу?

· Яких товарів було продано найбільше?

· Які товари приносять найбільший прибуток?

· Обсяги продажу яких товарів зменшуються?

· Скільки продано постійним покупцям?

· Як обсяги продажу розподіляються за регіонами?

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

Але для того щоб можна було скористатися тими потенційними можливостями аналізу даних, які ця програма надає кінцевому користувачу, перш за все треба мати інструменти швидкого пошуку, відбору та імпортування даних із зовнішніх джерел в Excel. Слово «зовнішні» тут означає, що дані зберігаються в електрон­ному вигляді, але у робочій книзі Excel вони відсутні. Такі дані можна імпортувати в Excel в різні способи, проте найпотужніші — це програма Microsoft Query та Web-запити.

Програма MS-Query може ефективно здійснювати доступ до різних джерел. Вона має розвинені засоби формування запитів, відображення результатів їх виконання і передавання отриманих даних на подальше оброблення. Це не лише потуж­ний, а й досить простий інструмент для отримання даних із
різних джерел. Його можна використовувати: для відбору даних з реляційної бази, наприклад Access, SQL Server або Oracal; для отримання даних, що зберігаються у вигляді списків в інших робочих книгах Excel або в текстових файлах; для з’єднання з базою даних OLAP і передавання даних в Excel у вигляді звіту зведеної таблиці. Коли дані отримуються з реляційної БД, списку Excel або текстового файла, можна об’єд­нувати дані, подані в декількох таблицях.

Для використання Query потрібне не тільки встановлення цієї програми, але й відповідні драйвери, що їх потребують зовнішні джерела даних. Для пошуку інформації в кожному конкретному джерелі даних слід застосовувати свій драйвер. Це може бути драйвер ODBC, драйвер OLE-DB або драйвер OLE-DB для куба OLAP. Так, здатність цієї програми вибирати дані з великої кількості різноманітних баз забезпечується використанням розробленого фірмою Microsoft так званого відкритого інтерфейсу з базами даних (Open Data Base Connective, ODBC). Цей інтерфейс є буфером між програмою, з якою працює користувач, і базою даних. Програма користувача має доступ лише до ODBC, а вже ODBC керує даними в спосіб, який залежить від того, в якій базі вони знаходяться. Для реалізації доступу користувача до конкретної бази даних у ODBC мають бути включені спеціальні програми, так звані драйвери. По-перше, потрібен драйвер ODBC, який забезпечує інтерфейс з програмою користувача, і, по-друге, драйвер відповідної бази даних. Разом з Windows поставляються програмне забезпечення ODBC і драйвери різних баз даних. Самі бази даних можуть розташовуватись як на машині користувача, так і на спеціальній машині для розміщення даних (машині-сервері). В останньому випадку ODBC забезпечує взаємодію між машиною-сервером і машиною користувача (машиною-клієнтом) через мережу. Незалежно від того, де знаходяться дані, користувач отримує доступ до них через MS-Query в однаковий спосіб — шляхом створення запитів. Для оброблення запитів використовується спеціальна структурована мова запитів SQL (Structured Query Language). Цю умову було розроблено фірмою IBM. Тепер вона стандартизована, але багато розробників програмних продуктів розширюють її власними елементами. Діалект мови SQL фірми Microsoft має назву MS-Query. Під час створення запиту є можливість вказувати окремі поля, для яких виконуватиметься запит, а також визначати критерії відбору записів бази даних. Все це дає змогу вибирати із загальної маси доступних даних лише необхідну інформацію. А це значно зменшує обсяг даних, які відправляються в Excel. Звичайно, можна перенести дані в Excel, а вже потім обробляти їх за допомогою функцій фільтрації. Але якщо кількість даних, які може обробляти Excel, обмежена обсягом оперативної пам’яті, то MS-Query може зв’язувати та обробляти значно більшу кількість даних. При цьому самі файли бази, з яких вибираються потрібні дані, можуть без будь-яких обмежень оброблятися відповідною системою керування базами даних. Для формування запиту зовсім не обов’язково знати мову SQL. Річ у тім, що для формування запиту є можливість використовувати Реляційний запит за зразком (Relational Query By Exampl, або RQBE). Запит за зразком — це інтерактивний засіб для вибору даних з однієї або кількох таблиць бази даних. Результатом вибірки є таблиця (таблиця запиту), яка виводиться на екран і яку можна передати на подальше оброблення в іншу програму. Формуючи запит, необхідно визначити вигляд вихідної таблиці запиту та у разі необхідності вказати критерії пошуку записів у таблиці бази даних. При цьому замість того, щоб набирати команду на мові SQL, можна заповнити форму запиту, яка розміщується у вікні запиту. Метод формування запиту шляхом заповнення форми досить простий для вивчення і розуміння. Він може застосовуватися користувачем, який має мінімальні навички роботи з Windows.

Елементи вікна MS-Query. Вікно MS-Query складається зі стандартних елементів: заголовка вікна, рядка меню, лінійки інструментів і робочого поля.

Більшу частину вікна MS-Query займає робоче поле для розташування вікон запитів. Кількість цих вікон лімітується лише наявним обсягом оперативної пам’яті. Кожне вікно запиту може поділятись на три частини:

¾ зона таблиць. Тут дається перелік полів кожної таблиці (файла) бази, дані яких використовуватимуться під час формування вихідної таблиці (таблиці запиту);

¾ зона критеріїв. Тут встановлюються умови (фільтри), за якими під час формування вихідної таблиці запиту перевіряються рядки таблиць бази даних;

¾ зона даних запиту. Тут встановлюються поля (колонки), з яких має складатися вихідна таблиця запиту, а після виконання запиту подаються рядки даних цієї таблиці.

У вікні запиту завжди відображається (у тому чи іншому вигляді) третя зона — зона даних запиту. Перша та друга зони можуть бути сховані.

У рядку меню завжди є пункт випадного меню Файл. За наявності у робочому полі хоча б одного вікна запиту рядок меню доповнюється іншими пунктами випадних меню: Правка, Перегляд, Формат, Таблиця, Критерії, Записи, Вікно.

Частина команд з цих меню, які найчастіше використовуються, дублюється на лінійці інструментів у вигляді таких кнопок: Створити запис, Відкрити запит, Зберегти запит, Перегляд SQL, Показати / сховати таблиці, Показати / сховати критерії, Додати таблицю, Критерій дорівнює, Цикл за груповими операціями, Сортувати за зростанням, Сортувати за зменшенням, Виконати запит, Автоматичний режим, Довідка.

Створення запитів. У разі створення запиту загалом потрібно визначити: таблиці бази, які використовуватимуться для отримання інформації; спосіб об’єднання даних цих таблиць; поля вихідної таблиці запиту; критерії відфільтровування необхідної інформації.

Розглянемо процес створення запиту на такому прикладі: треба визначити прізвища і телефони всіх зареєстрованих покупців. Файл реєстрації (довідник покупців) має ім’я Pokupec.dbf.

Для створення запиту слід запустити MS-Query. Запуск MS-Query здійснюється шляхом використання стандартних засобів запуску програм Windows або безпосередньо в Excel командою Дані / Отримати зовнішні дані.

У разі запуску MS-Query засобами Windows відкриється діалогове вікно MS-Query, у якому потрібно вибрати команду Файл / Створити запит або натиснути на кнопку Створити запит. На екрані з’явиться вікно для вибору джерела даних, тобто для вибору відповідного ODBC-драйвера. При запуску MS-Query з Excel відразу відкриється діалог вибору джерела даних. Після визначення джерела даних відкриється діалог Додати таблиці, в якому потрібно вказати ім’я таблиці (файла) бази, що містить необхідні дані. Коли таблицю знайдено у каталозі диска, її можна додати у два способи: або один раз клацнути мишею на імені файла у полі Ім’я таблиці,а потім на кнопці Додати,або два рази клацнути на імені файла. Для завершення операції додавання таблиць необхідно клацнути на кнопці Закрити.

Подальша робота з визначення запиту здійснюватиметься в його вікні. На початку роботи це вікно горизонтальною лінією буде поділене на дві частини: зону таблиць і зону даних запиту. «Узявшись» мишею за цю лінію, можна зміщувати її угору або униз, змінюючи таким чином розміри ділянок вікна запиту. В зоні таблиць розташовується маленьке віконце з іменем доданої таблиці бази даних (у даному разі це таблиця Pokupec) і списком її полів. Під лінією поділу ділянок вікна знаходиться рядок заголовків (назв полів) вихідної таблиці. До визначення її полів цей заголовок має лише одне порожнє поле. Після виконання запиту під рядком заголовків з’являться відібрані рядки даних.

Але для того щоб можна було виконати запит, треба визначити, з яких полів таблиці бази і в які поля (колонки) вихідної таблиці запиту вибиратиметься інформація. Це можна зробити в один з таких способів:

¾ двічі клацнути мишею на імені поля у вікні таблиці бази. Це ім’я буде перенесене у рядок заголовків зони даних. Якщо у вікні таблиці бази вибрати символ зірочки (*), то у рядок заголовків будуть перенесені назви всіх її полів;

¾ виділити поле в таблиці і, тримаючи натиснутою ліву кнопку миші, перетягти його у зону даних;

¾ у зоні даних клацнути мишею у порожньому полі рядка заголовків. У полі з’явиться вертикальна риска (курсор уведення) і кнопка списку полів таблиці бази даних. Тепер можна або ввести з клавіатури ім’я поля таблиці бази, або натиснути на кнопку і вибрати його у списку, який відкриється після цього натискання;

¾ з меню Записи вибрати команду Додати колонку.

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

Для даного прикладу у вихідну таблицю запиту потрібно додати два поля таблиці Pokupec: Telefon і Fio. Кожен раз з додаванням нового поля виконуватиметься обробка запиту і відповідна колонка вихідної таблиці заповнюватиметься даними. Це пояснюється тим, що за умовчання встановлено режим Запитувати автоматично. У разі значних розмірів бази це відбирає багато часу. Тому є сенс відключити автоматичне виконання запиту. Для цього можна або перевести кнопку Автоматичний режим у стан «вимкнуто», або зняти «галочку» з пункту меню Записи / Автоматичний режим. Після вимкнення режиму Запитувати автоматично кожен раз для виконання запиту потрібно буде або натискати на кнопку Виконати запит, або вибирати пункт меню Записи / Виконати запит.

Перегляд таблиці даних запиту. Після виконання запиту в зоні даних з’являться відібрані рядки даних. Для перегортання сторінок даних використовуються стандартні засоби: клавіша Page Down (наступна сторінка), клавіша Page Up (попередня сторінка) і вертикальна лінійка прокрутки. Для перегляду довгих записів, яким не вистачає місця на екрані, використовуються клавіші зі стрілками (, ®) і горизонтальна лінійка прокрутки. Для збільшення кількості рядків на сторінці необхідно збільшити її висоту.

Щоб збільшити висоту сторінки, треба сховати верхню частину вікна запиту, де знаходяться таблиці даних. Для цього знімається позначка з пункту меню Вигляд / Таблиці або натискається кнопка Відображення таблиць. Повторне виконання однієї з вказаних команд відтворює зображення таблиць бази даних на екрані.

Для швидкого переходу до потрібного запису за його номером можна використати меню, комбінації клавіш і спеціальні кнопки, що знаходяться у нижньому лівому куту.

За допомогою пункту меню Записи / Перейти можна виконати перехід до конкретного запису за його номером у файлі бази даних.

Другий спосіб переходу до потрібного запису — натискання відповідної комбінації клавіш: Сtrl + PgDn — наступний запис; Ctrl + PgUp — попередній запис; Ctrl + End — останній запис; Ctrl + Home — перший запис.

Третій спосіб швидкого переходу до потрібного запису — введення номера запису у спеціальне поле з написом Запис або натискання на відповідну кнопку переходу (до першого, до попереднього, до наступного і до останнього запису) з лівої і правої сторони цього поля. Вводячи число у поле Запис, можна перейти до потрібного запису за його номером.

Редагування даних. Таблиця із записами може знаходитись або у режимі Захист від редагування, за якого внесення будь-яких змін у запис неможливе, або у режимі Редагування, який дає змогу модифікувати дані бази. Переключення з одного режиму на інший виконується за допомогою пункту меню Записи/Дозволити правку.

На початку кожного запису в зоні даних може знаходитися маркер запису (зірочка означає вільний запис, чорний трикутник — поточний запис), а під час редагування запису у його маркері з’являється символ олівця (!). Хоча дані редагуються у зоні таблиці запиту, модифікований запис зберігається на диску, як тільки покажчик поточного запису перейде на інший запис. Отже, користувач має можливість редагувати записи таблиці бази даних, при цьому збереження модифікованих записів виконуватиметься автоматично, без будь-яких додаткових дій з боку користувача. Після збереження запису символ внесення змін у цей запис зникає.

Для виправлення помилок в даних використовуються такі клавіші: Delete — для вилучення символу за курсором; Backspace — для вилучення символу перед курсором; Esc — для відмови від змін у поточному запису.

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

Окремий запис виділяється клацанням мишею на маркері цього запису. Кілька записів, які утворюють суцільну зону, виділяються протягуванням курсору миші по їхніх маркерах.

Окрема колонка виділяється клацанням мишею на заголовку цієї колонки. Кілька колонок, які утворюють суцільну зону, виділяються протягуванням курсору миші по їхніх заголовках.

Після виділення поля (колонки) чи запису (рядка) їх можна вилучити. Для цього або в меню Правка обирається пункт Вилучити,або натискується клавіша Delete. Якщо вилучається запис, то відкривається діалог, який вимагає підтвердження щодо вилучення запису.

Зміна зовнішнього вигляду таблиці запитів. У меню Формат є стандартні пункти Шрифт, Висота рядка і Ширина колонки,які визначають зовнішній вигляд таблиці. До стандартних операцій належать також коригування висоти рядків і ширини колонок за допомогою миші.

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

Будь-яку колонку можна усунути з екрана без її вилучення з таблиці запиту. Для цього треба виділити якесь поле цієї колонки або її всю і виконати команду Формат / Сховати колонки. Для того щоб показати приховану колонку, необхідно виконати команду Формат / Показати колонки. При цьому відкривається діалог Показати колонки, в якому перелічено імена колонок. У видимих колонок біля імені є відповідні позначки.

Дані запиту можуть упорядковуватися за зростанням або за зменшенням значень полів.

Для виконання операцій впорядкування необхідно виділити потрібну колонку і клацнути на одній з наведених кнопок: ліва кнопка призначена для впорядкування даних за зростанням, а права — за убуванням значень відповідного поля. Критерії впорядкування можуть бути також вказані у діалозі Сортування, який відкривається після вибору команди Записи / Сортувати.

Можна виконувати впорядкування і за кількома полями, якщо їх вказати у списку. При цьому слід ураховувати, що впорядкування великої кількості даних потребує чимало часу.

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

Перенесення даних в Excel. Перший спосіб перенесення даних потребує таких підготовчих дій: виділяються дані, які треба перенести в Excel; виконується команда Правка/Копіювати або натискується комбінація клавіш Ctrl+C.

Завдяки цим діям зміст виділеної зони буде перенесений у буфер тимчасового збереження і після активізації Excel командою Правка / Вставить або натисканням комбінації клавіш Ctrl+V його можна вставити у будь-яке місце робочого аркуша. У робочий аркуш будуть вставлені лише дані без найменувань полів. Якщо перед поверненням у Excel виконати команду Файл / Зберегти,то це дасть змогу потім відкривати запит командою Файл / Відкрити запит.

Другий спосіб може бути використаний лише тоді, коли створення запиту починається в Excel з допомогою команди Дані / Отримати зовнішні дані. Для повернення в Excel з передаванням даних необхідно виконати команду меню Файл / Повернути дані в Microsoft Excel. Відкриється діалог Отримання зовнішніх даних,в якому запитується, в якому місці робочої книги Excel і як мають бути розміщені ті дані, що передаються з MS-Query. Якщо у цьому діалоговому вікні встановити параметр Зберегти запит, то у подальшому можна буде викликати MS-Query, подвійно клацнувши на цих даних. Запит буде виконано знову. Якщо встановити параметр Включати імена полів,то в Excel будуть передані імена полів бази даних.

MS-Query передає в Excel лише копію оригінальних даних. Тому у разі їх зміни відповідні зміни в Excel не відбуваються. Актуалізацію даних можна виконати за допомогою команди Дані / Оновити дані. Крім того, якщо після повернення з MS-Query виконати команду Дані / Отримати зовнішні дані,то відкриється діалог, в якому крім вже відомих параметрів для включення імен рядків і колонок є кнопка Оновити дані,яка виконує актуалізацію даних, і Правка запиту,яка дає змогу знову повернутися в MS-Query для редагування запиту.

Фільтрування даних. Критерії відбору. Одна з найважливіших функцій будь-якої інформаційної системи — відбір або фільтрування даних. Виконання цієї функції досягається шляхом накладання критеріїв відбору. Саме вони дають змогу отримувати інформацію, яка задовольняє ті чи інші потреби.

Для того щоб визначити потрібні критерії, необхідно відобразити зону критеріїв у вікні запиту. Це досягається або увімкненням кнопки Показати / Сховати критерії, або виконанням команди Перегляд / Критерії. Після цього з’являється зона критеріїв.

Перший рядок цієї зони, який позначено як Поле, призначається для занесення назв полів (колонок) таблиці бази даних, значення яких перевірятимуться під час фільтрації записів. Клітини інших рядків призначені для введення умов, яким мають відповідати ці поля відібраних записів бази даних. Умови, що знаходяться в одному рядку області критеріїв, поєднуються між собою оператором «І», а ті, що знаходяться у різних рядках, — оператором «Або». За виконання команди кожний рядок таблиці бази даних перевіряється на відповідність умовам кожного рядка критеріїв. Якщо рядок таблиці бази відповідає умовам хоча б одного рядка критеріїв, то цей рядок бази з’являється в зоні даних запиту.

Для визначення поля таблиці бази даних, значення якого перевірятиметься за фільтрації записів, можна застосувати один з таких способів:

¾ увести з клавіатури ім’я поля таблиці бази даних у будь-яке порожнє поле першого рядка зони критеріїв;

¾ поставити курсор миші на ім’я поля таблиці бази даних, натиснути ліву кнопку миші і, не відпускаючи її, перетягнути поле у зону критеріїв;

¾ клацнути мишею на порожньому полі першого рядка зони критеріїв. На цьому полі буде встановлено кнопку випадного списку з іменами полів таблиці даних. Треба розкрити цей список (клацнув­ши мишею на кнопці списку) і вибрати зі списку потрібне ім’я;

¾ виконати команду Умови / Додати умови.

У разі використання останнього способу розкривається діалогове вікно Додавання умови, до якого можна ввести всі необхідні умови відбору (фільтрування) записів таблиці бази даних. Коли ж використовуються інші способи, вводиться лише одна умова. Її можна ввести або з клавіатури, або через діалогове вікно. В останньому випадку треба два рази клацнути лівою кнопкою миші на полі введення умови. Формуючи умови, найчастіше застосовують такі оператори:

дорівнює	=		між	Between
не дорівнює	<>		не між	Not Between
більше	>		схоже на	Like
більше або дорівнює	>=		не схоже на	Not Like
менше	<		порожнє	Is Null
менше або дорівнює	<=		не порожнє	Is Not Null
міститься у	In		і	And
не міститься у	Not In		або	Or

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

Скажімо, потрібно отримати прізвища і телефони тих покупців, які живуть у Києві. Один із способів одержання такої інформації складається з таких дій:

¾ якщо на екрані відсутня зона для введення критеріїв, клацнути лівою кнопкою миші по кнопці Відображення умов;

¾ поставити курсор миші на ім’я поля Misto таблиці Pokupec, натиснути ліву кнопку миші і, не відпускаючи її, перетягнути назву поля у зону критеріїв;

¾ у рядку «Значення» зони критеріїв під полем Misto двічі клацнути лівою кнопкою миші;

¾ у діалоговому вікні Зміна умови натиснути на кнопку Значення;

¾ після розкриття списку значень поля Misto, які є у таблиці Pokupec, вибрати потрібне значення (тобто Київ) і натиснути кнопку ОК;

¾ після повернення у діалогове вікно Правка критерію натис­нути кнопку ОК.

Наслідком цих дій буде занесення в зону критеріїв умови відбору даних з таблиці бази даних.

Умова:	MISTO
Значення:	Київ
або:

Значно розширює можливості відбору символьної інформації застосування шаблонів, які будуються за допомогою спеціальних символів (процента і підкреслення). Символ процента (%) означає будь-яку кількість символів, а символ підкреслення (_) — лише один. Якщо, наприклад, треба отримати дані за усіма товарами, назва яких починається з літери «Л», то у разі уведення такої умови з клавіатури потрібно набрати Like ‘Л%’. Для того щоб спростити застосування подібних конструкцій, у діалогових вікнах, призначених для введення умов, є такі оператори: починається з (Like ‘...%’), не починається з (Not Like ‘...%’), закінчується на (Like ‘%...’), не закінчується на (Not Like ‘%...’).

Інколи варто знати, чи містить дане поле бази якесь значення, чи воно порожнє. Під порожнім полем розуміється поле, в яке ще не вводилася ніяка інформація. Якщо поле вміщує нуль або пропуски, то воно вже не є порожнім. Критерій порожнього поля виглядає так: Is Null (є порожнім). Перевірку на те, що поле не є порожнім, можна виконати за допомогою оператора Is Not Null.

У разі введення умов для числових полів лапки не використовуються.

За уведення з клавіатури умов для полів типу дати або часу замість лапок слід використовувати знаки номера (#) (наприклад, <= #01.01.02#). Уводячи умову в діалоговому вікні, цього знаку треба уникати, оскільки інакше буде помилка.

Розглянемо кілька прикладів конструювання критеріїв.

Вибірка з використанням оператора In («міститься у» чи «належить»). Формулювання запиту: отримати інформацію про товари, ціна яких міститься у 760, 925, 997 (тобто ціна яких дорів­нює або 760, або 925, або 997).

Умова (поле):	CINA
Значення:	In (760,925,997)
або:

Вибірка з використанням оператора Between («між»). Формулювання запиту: вибрати відомості про товари, ціни на які знаходяться у діапазоні від 775 до 1280 включно.

Умова (поле):	CINA
Значення:	Between 775 And 1280
або:

Вибірка з використанням оператора Like ‘%...’ («закінчується на»). Формулювання запиту: отримати інформацію (файл Zakaz.dbf) про замовлення на комп’ютери з тактовою частотою 1000 МГц (у файлі Zakaz назва таких комп’ютерів та їх код закінчується на 1000).

Умова (поле):	KODZ
Значення:	Like ‘%1000’
або:

Вибірка з обчисленням дати. Формулювання запиту: використовуючи данi про продаж (файл Zakaz.dbf), з’ясувати, які товари було продано протягом семи днів, починаючи з 17.03.02 р.

Умова (поле):	DATAP
Значення:	Between #17.03.02# And #17.03.02#+7
або:

Вибірка з використанням зв’язаних умов. У багатьох випадках однієї умови у запиті буває недостатньо. За визначення кількох умов необхідно враховувати їх взаємодію. Чи буде вибрано запис у разі обов’язкового виконання всіх умов, чи достатньо, щоб виконувалася будь-яка з них? У першому випадку критерій відбору створюється за пов’язування умов за допомогою логічного «І «. Запис обирається лише тоді, коли виконуються всі умови. Другий вид логічного зв’язку — зв’язок за допомогою логічного «АБО». У цьому випадку запис обирається, якщо виконується хоча б одна умова.

Формулювання запиту: Отримати інформацію про замовлення (файл Zakaz.dbf), які були зроблені після 12.03.01 р. і вартість яких була не нижча 960 грн (тобто отримати інформацію із записів, у яких значення поля DATAZ більше за 12.03.01 і в яких значення добутка полів KILZ і CINA дорівнює або більше 960). За введеного критерію запис обиратиметься тільки тоді, коли обидві умови виконуються.

Умова (поле):	DATAZ	CINA*KILZ
Значення:	>#12/03/01#
або:		>=960

Формулювання запиту: Отримати інформацію про замовлення, які були зроблені після 12.03.01 р. або вартість яких не нижча 960 грн. За введеного критерію запис обиратиметься у разі виконання хоча б однієї умови.

Зв’язки як по «І», так і по «Або» можуть багаторазово зустрічатися в одному запиті.

Умова (поле):	DATAZ	CINA*KILZ
Значення:	>#12/03/01#
або:		>=960

Оброблення даних. У процесі отримання даних з бази вони можуть зазнати деякої попередньої обробки. Це досягається за рахунок використання під час визначення полів вихідної таблиці запиту не просто назв полів бази, а виразів з них або функцій для обчислення групових значень.

Обчислення виразів. Розглянемо такий приклад. У таблиці бази даних Tovar (Товар) є поле Cina (Ціна), а в таблиці даних запиту потрібно відобразити ціну з урахуванням 20 % торгової націнки.

Це можна виконати таким чином:

¾ з меню Записи виконати команду Додати колонку, що приведе до появи діалогового вікна з такою самою назвою;

¾ у рядку Поле цього діалогового вікна увести формулу Cina*1.2;

¾ як заголовок можна використати або той самий рядок з
формулою, або будь-який пояснювальний текст, наприклад, ‘Ціна з націнкою’. Пояснювальний текст уводиться у рядок Заголовок колонки.

Обчислення групових значень. Розглянуті способи побудови запитів далеко не завжди дають змогу знайти відповіді на питання, що виникають. Наприклад, навіть такий простий запит, як «Скільки є покупців?», неможливо висловити, використовуючи розглянуті вище можливості. Тому для визначення ознак, що характеризують деяку групу записів, у мові SQL існує низка спеціальних функцій, притаманних усім її діалектам:

Функція	Обчислює для кожної групи записів
Сума	Суму значень заданого параметра
Середнє	Середнє значення заданого параметра
Число	Кількість рядків
Мінімум	Найменше значення заданого параметра
Максимум	Найбільше значення заданого параметра

Кожна з цих функцій оперує сукупністю значень указаного параметра (за винятком функції Число) і обчислює єдине значення для кожної заданої групи записів, яке характеризує цю групу. Параметром переважно є ім’я певного поля таблиці даних.

Для визначення потрібної функції використовується кнопка Цикл за груповими операціями або пункт меню Записи/Додати колонку.

Функції Сума і Число дають змогу дуже просто обчислювати різного роду підсумки. Наприклад, можна визначити загальну кількість покупців, виконавши такі дії:

¾ створюючи запит, до зони таблиць бази додати довідник покупців (файл Pokupec. dbf);

¾ визначити поле таблиці бази, яке використовуватиметься як параметр функції (наприклад, поле KODP), і мишею перетягнути його заголовок у зону даних запиту;

¾ увести функцію, яка оперуватиме параметром. Для цього в зоні даних запиту виділити колонку KODP і натискати у лінійці інструментів на кнопку Цикл за груповими операціями, допоки у заголовку таблиці даних запиту не з’явиться Число KODP;

¾ якщо вимкнуто Автоматичний режим, то натиснути кнопку Виконати запит.

У разі виконання цих дій під заголовком Число KODP з’я­виться підрахована загальна кількість записів у таблиці Pokupec. Оскільки таблиця Pokupec — це довідник покупців, де кожному покупцеві відповідає один запис, то кількість її записів збігатиметься з кількістю зареєстрованих покупців.

Альтернативний спосіб створення запиту полягає у виборі з меню Записи команди Додати колонку. При цьому відкриється діалог, у якому потрібно виконати такі дії:

¾ у першому рядку Поле, клацнувши мишею на кнопці зі стрілкою, розкрити список полів і вибрати поле бази KODP;

¾ в останньому рядку Групова операція розкрити список функцій і вибрати функцію Число;

¾ послідовно клацнути мишею на кнопках Додати і Закрити.

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

Для обчислення лише певних записів до запиту потрібно включити відповідний критерій відбору.

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

Умова (поле):	MISTO
Значення:	‘Львів’
або:

Для обчислення підсумкових значень для окремих груп записів у вихідну таблицю даних запиту необхідно внести поля групування записів. Якщо з попереднього запиту вилучити критерій відбору записів, а у вихідну таблицю запиту додати поле MISTO, то після його виконання у полі Число (KODP) буде показано кількість зареєстрованих покупців для кожного міста.

Для аналізу продажу (його обсягів та вартості) по окремих днях до вихідної таблиці запиту необхідно додати поля Число (KILP), Сума KILP*CINA і DATAP. У першому виводитиметься інформація про кількість продажу за день, у другому — вар­тість продажу, а у третьому — дата продажу. Крім того, поле
дати продажу (DATAP) необхідне ще й для групування записів за датою.

Використання функцій у критеріях відбору. Розглянуті функції для обчислення групових значень можна використовувати не тільки у полях вихідної таблиці запиту, а й у зоні критеріїв для відбору відповідних груп записів.

Припустимо, що потрібно визначити коди товарів, які за період з 13.03.02 р. по 20.03.02 р. мали попит більш як у одного покупця (були куплені більш як одним покупцем). Визначити запит на отримання цієї інформації можна таким чином:

¾ вибрати таблицю бази з даними з продажу (Zakaz.dbf);

¾ у вихідну таблицю запиту занести поле з кодом товару (KODT);

¾ виконати команду Критерії / Додати критерії, що приведе до відкривання однойменного діалогового вікна;

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

Групова операція:	Число
Поле:	KODT
Оператор:	більше
Значення:

¾ натиснути кнопку Додати (для перенесення встановленої умови у зоні критеріїв);

¾ у рядках діалогового вікна встановити нові значення для введення другої умови (період з 13.03.02 до 20.03.02) і, нарешті, послідовно натиснути кнопки Додати і Закрити.

Групова операція:
Поле:	DATAP
Оператор:	між
Значення:	13.03.02;20.03.02

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

Умова (поле):	Число(KODT)	DATAP
Значення:	>1	Between #13/03/02# And #20/03/02#

Об’єднання таблиць. Поки що ми розглядали запити, які вибирали дані лише з однієї таблиці. Але в запиті можна використовувати одночасно кілька таблиць бази даних і таким чином отримувати вихідну таблицю, яка матиме інформацію з усіх цих вхідних таблиць. Здатність об’єднувати кілька таблиць в одну є однією з найпотужніших можливостей мови SQL.

Розпочнемо з досить простого об’єднання двох таблиць. Об’єднаємо список замовлень з адресами покупців, які зробили ці замовлення. Для цього, створюючи новий запит у діалоговому вікні Додати таблицю,потрібно вибрати два файли: Zakaz.dbf і Pokupec.dbf і лише після цього закрити діалог, натиснувши кнопку Закрити. Цей діалог не закривається автоматично. І якщо кілька разів натискати на кнопку Додати, то відбудеться додавання тої самої таблиці. За правильного виконання операції додавання у зоні вхідних таблиць вікна запиту з’являться два списки з переліком полів таблиці Zakaz і Pokupec.

У таблиці Pokupec кожному покупцеві відповідає певний код, який знаходиться у полі KODP і для різних покупців має різне значення. Таким чином, таблиця Pokupec — це довідник покупців, у якому кожного покупця можна знайти за його кодом.

У таблиці Zakaz кожному замовленню також відповідає певний код покупця. Але кількість записів з однаковим кодом у цій таблиці не обмежена (вона визначається кількістю замовлень, зроблених конкретним покупцем). Для того щоб довідкові відомості з таблиці Pokupec можна було використовувати у разі виведення інформації з таблиці Zakaz, необхідно визначити зв’язки між записами цих двох таблиць. MS-Query не встановлює зв’язки авто­матично. Це повинен робити користувач. Слід зауважити, що імена полів з кодом покупця у таблицях Zakaz і Pokupec можуть збігатися (Zakaz. KODР і Pokupec. KODP), а можуть бути й різними.

Визначення зв’язків (відношень) між таблицями. Для того щоб визначити зв’язки між двома таблицями, потрібно виконати такі дії:

¾ бажано виключити режим Запитувати автоматично (у разі великих розмірів таблиць це значно зменшить витрати часу);

¾ установити курсор миші на ім’я потрібного поля в таблиці, до якої слід приєднати іншу (у нашому прикладі — поле KODP таблиці Zakaz, тобто Zakaz. KODP);

¾ натиснути ліву кнопку миші і, не відпускаючи її, перемістити курсор миші на ім’я поля другої таблиці (у нашому прикладі — поле KODP таблиці Pokupec, тобто Pokupec. KODP). Між іменами двох полів різних таблиць з’явиться лінія, що відбиває зв’язок таблиць за відповідними полями.

Альтернативний спосіб визначення співвідношення між двома таблицями — виконати в меню Таблиця команду Об’єднання.

Визначення властивостей зв’язків. У MS-Query є можливість установлювати різні властивості зв’язків між таблицями.

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

Але трапляється, що необхідно побачити всі рядки з однієї таблиці і додатково деяку інформацію з іншої для тих рядків першої таблиці, які мають відповідний рядок, або для рядків іншої таблиці. Такий тип зв’язку має назву Зовнішнє об’єднання.

Щоб дізнатися про тип встановленого зв’язку або змінити його, треба клацнути мишею на відповідній лінії між таблицями. Ця лінія зв’язку стане виділеною. Подвійне клацання на цьому зв’язку або виконання команди Таблиця / Об’єднання не тільки відкриє діалог Об’єднання, а й покаже параметри саме цього зв’язку. У діалоговому вікні можна встановити будь-який із вказаних типів зв’язку. За умовчання встановлюється перший тип з умовою «дорівнює». Саме такий тип зв’язку потрібно встановити для об’єднання списку замовлень з адресами покупців. Після встановлення зв’язку можна, наприклад, отримати список прізвищ і телефонів покупців з Києва, які замовили 486-й комп’ютер.

Створення нової таблиці. За допомогою MS-Query можна створювати нові таблиці для баз даних. Для цього потрібно виконати команду меню Файл / Визначення таблиці. Відкриється діалог Вибір таблиці. У цьому діалозі потрібно вибрати таблицю, яка використовуватиметься як шаблон для нової. Якщо двічі клацнути на імені таблиці (файла бази даних), то відкриється діалог Перегляд визначення таблиці. У відповідних полях цього діалогу можна визначити ім’я та тип поля (символьне, чисельне і т. ін.) таблиці, довжину цього поля. Можна також додавати нові поля (кнопка Додати) та вилучати існуючі (кнопка Вилучити). Для того щоб завершити створення таблиці і записати її на диск, у першому рядку (Ім’я таблиці) діалогу слід замінити ім’я попередньої таблиці (шаблону) на нове ім’я. За спроби створити таблицю з таким самим ім’ям MS-Query попередить, що така таблиця не може бути створена.

Сховища даних

Спосіб отримання інформації безпосередньо з оперативних баз даних великих корпоративних систем створює для кінцевих користувачів значні труднощі. По-перше, це змушує користувачів формувати складні запити до даних, що зберігаються в різних місцях мережі й у різних форматах. По-друге, коли дані нагромаджуються в декількох незв’язаних оперативних базах даних, вони часто багато в чому дублюються, але при цьому ніяк не узгоджуються. У такому разі достовірну комплексну інформацію одержати практично неможливо, незважаючи на її надмірність.

Навіть якщо всі дані зберігаються на центральному сервері БД, розібратися в їх складних структурах кінцевому користувачеві, як правило, дуже складно. Тому виникає потреба у поперед­ній обробці оперативних даних, що збираються в різних відділах організації, не тільки для того, щоб зібрати їх в одному місці, а й для того, щоб узгодити їх та, виходячи з особливостей задач, які потрібно розв’язувати, очистити від непотрібних даних і зберегти в простій, зрозумілій та зручній для формулювання нерегламентованих запитів структурі. Саме це й є основною причиною появи так званих сховищ, або складів, даних, які мають зберігати лише необхідну, актуальну і точну інформацію з метою доставки її певним особам у потрібний час для прийняття ними обґрунтованих і своєчасних рішень.

Потреба у повноцінній максимально швидкій інформаційній підтримці процесів прийняття рішень змушує не звертати увагу на очевидну ваду сховищ даних — на те, що вони містять явно надлишкову (оскільки вона дублює дані, що є в базах або файлах оперативних систем) інформацію.

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

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

Рис. 2.1.1. Основні компоненти сховища даних

Дані реляційного сховища доступні для аналізу за допомогою різноманітних засобів побудови звітів. Але ті традиційні звіти, які створюються програмістами і призначені для безпосереднього використання особами, що приймають рішення, хоча і є надзвичайно простими у застосуванні, проте жорстко обмежені у функціональності (позбавлені гнучкості). Вони отримуються за допомогою певної множини запитів і, будучи достатніми для повсякденного застосування, неспроможні відповісти на всі запитання до наявних даних, що можуть виникнути при прийнятті рішень. Результатом виконання цих запитів, як правило, є багатосторінкові звіти. Особа, що приймає рішення, не може їх «прокрутити», «розгорнути» або «згорнути», щоб одержати бажане уявлення даних. Тому після ретельного вивчення цих звітів у неї з’являється нова серія запитань. Проте кожний новий непередбачений запит має бути спочатку формально описаний, закодований програмістом — тільки після цього він може бути виконаний. Час чекання в такому разі може сягати кількох годин і навіть днів, що здебільшого неприйнятне. Отже, зовнішня простота отримання інформації, чого активно домагається більшість замовників інфор­маційно-аналітичних систем, обертається катастрофічною втратою гнучкості. За не дуже великих обсягів даних у реляційному сховищі та не дуже складних взаємозв’язків між таблицями непоганий спосіб виходу з цієї ситуації — відбір потрібних даних і перенесення їх для наступного аналізу в Excel за допомогою програми MS Query. Але у разі роботи з великою кількістю реляційних таблиць така задача залишається надто складною і проведення оперативного аналізу даних стає практично неможливим.

Тому попереднє збирання, очищення та інтегрування даних — це ще не все, що потрібно кінцевому користувачеві. Йому потріб­ні також:

¾ зручніша, ніж це можуть забезпечити реляційні таблиці, структура даних (з погляду спрощення доступу до даних);

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

¾ інструменти для здобуття знань, тобто інструменти оброблення даних, що дають змогу автоматизувати різні види отримання корисної аналітичної інформації, на основі якої можна виявляти приховані тенденції, будувати стратегію розвитку, знахо­дити нові рішення. Тільки за наявності таких інструментів можна проводити серйозні аналітичні дослідження на базі всієї доступної інформації про діяльність організації.

Одним із засобів вирішення перших двох проблем є OLAP (On-Line Analytical Processing — оперативне аналітичне оброблення) — технологія організації даних відповідно до методів їх аналізу, що зменшує витрати часу і зусилля на створення необхідних звітів. Хоча OLAP і не є необхідним атрибутом сховища даних, але саме він найчастіше застосовується для аналізу нагромаджених у сховищі відомостей.

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

У дуже великих організаціях підрозділи верхніх рівнів досить незалежні і мають розвинену організаційну та інформаційну структуру. При цьому потреби та пріоритети розв’язання певних задач також різняться. З іншого боку, інформація, необхідна для підтримки прийняття рішень на більш високому рівні, збирається і зберігається на більш низькому організаційному рівні в детальнішому вигляді. В таких організаціях навіть велика група адміністраторів не може контролювати сховище, що містить усі дані, та управляти ним. Поняття єдиного сховища даних у такому разі реалізується в концепції ієрархічних сховищ даних.