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

Служба аналізу в SQL Server 2005 розширила функціональність так, що деякі сценарії тепер не вимагають наявності сховища даних для підтримуваних ними операцій Проте при роботі зі службою аналізу дуже важливо розуміти основні концепції зберігання даних

Схема зірка

– це стандартний підхід до структурованого зберігання реляційних даних OLAP У його основі лежать поняття вимірювань і заходів Вимірювання – це поділ на категорії, або угруповання даних, а міра – консолідованих значення Наприклад, в задачі консолідації обсягу продажів по кварталах і підрозділам заходом є обсяг продажів, а вимірами – квартал і підрозділ

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

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

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

Таблиця фактів складається з двох типів стовпців: ключів, повязаних з рештою вимірами схеми зірка, і стовпців цікавлять заходів

Кожна таблиця вимірювань складається з первинного ключа, за яким будується ставлення з таблицею фактів, і одного або декількох атрибутів, розбивають дані по категоріях даного виміру Наприклад, вимір замовника може містити атрибути імені, електронної адреси та індексу Загалом, вимір являє денормализация даних у системі OLTP Наприклад, вимір замовника в AdventureWorksDW управляється, серед іншого, з таблиць Sales Individual і Person Contact бази даних AdventureWorks, а також полів, виділених з шпальти XML, що описує демографічні дані

Іноді має сенс обмежити денормализация, організовуючи залежність однієї таблиці вимірювань від іншої, таким чином, змінюючи схему зірка на схему сніжинка. Як приклад на рис 432 показано, як вимірювання товарів бази даних AdventureWorks було організовано в схему сніжинка. Інформація категорій товарів і підкатегорій могла бути включена безпосередньо в таблицю DimProduct, але замість цього для створення системи категорій було створено кілька таблиць Схема сніжинка корисна для складних вимірювань, в яких в іншому випадку виникають проблеми цілісності, такі як призначення підкатегорій категоріями (див рис 432), а також для великих вимірів, де піднімається питання обсягу

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

Рис 432 Вимірювання зі схемою сніжинка

Рис 431 Проста схема зірка

•&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp коли процедура публікації даних в схему зірка занадто складна або коли уповільнення чутливо

•&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp коли проектована схема буде використовуватися в розширених запитах SQL, які ускладняться або сповільняться при використанні схеми сніжинка.

Единбурзі

Визначення схеми зірка дозволяє швидко виконувати запити OLAP до сховища даних, однак підвищені вимоги до однаковості роблять необхідним дотримання наступних правил

■ При завантаженні даних в сховищі некоректні і порожні значення повинні заміщатися своїми друкованими еквівалентами Це дозволить одноразово досліджувати

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

■ Рядки в таблиці фактів ніколи не повинні видалятися Також неприпустимі будь-які інші операції, які можуть призвести до різних результатів запитів, виконаних у різний час Часто це має на увазі відкладений імпорт виконуються транзакцій

■ Уникайте використання ключів OLTP для звязування таблиць фактів і вимірювань, незважаючи на те, що це часто досить зручно Подумайте про схему зірка, що містить фінансові дані, коли оновлюється пакет бухобліку із зміною при цьому всіх ідентифікаторів замовників Якщо відносини в сховище заснувати на стовпці ідентичності, то будь-які зміни в базі OLTP можуть бути відображені невеликими змінами даних у вимірі замовників Якщо ж буде використаний ключ OLTP, то доведеться перетворити всю таблицю фактів

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

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

Завантаження даних

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

Завантаження вимірювань

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

INSERT INTO WarehousedbodimProduct (ProductCode, ProductName)

SELECT stageCode, stageName FROM StagingdboProducts stage LEFT OUTER JOIN WarehousedbodimProduct dim ON stageCode=dimProductCode

WHERE dimProductCode is NULL

Часто дані вимірювань джерела не повязані зовнішнім ключем з таблицею фактів джерела У цьому випадку завантаження таблиці вимірів вимагає повного сканування таблиці файлів з метою забезпечення узгодженості У наступному прикладі виконується сканування фактичних даних, витяг відповідних описів з таблиці вимірювань, коли такі доступні, або використання опису Невідомо в іншому випадку:

INSERT INTO WarehousedbodimOrderStatus (OrderStatusID, OrderStatusDesc) SELECT DISTINCT ostatus, ISNULL(mosDescription, 1 Невідомо 1)

FROM StagingdboOrders про

LEFT OUTER JOIN WarehousedbodimOrderStatus os ON оstatus = osOrderStatusID LEFT OUTER JOIN Stagingdbomap_order_status mos ON ostatus = mosNumber WHERE osOrderStatusID is NULL

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

INSERT INTO WarehousedbodimProduct (ProductCode, ProductName)

SELECT stageCode, MAX(stageName)

FROM StagingdboOrders stage

LEFT OUTER JOIN WarehousedbodimProduct dim ON stageCode=dimProductCode WHERE dimProductCode is NULL

Завантаження таблиць фактів

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

■ природний ключ, заснований на даних вимірювання (наприклад, код товару)

сурогатний ключ, який не має будь-якого звязку з даними (наприклад, стовпець ідентичності)

Сурогатні ключі використовують найчастіше вони відмінно адаптуються до даних, одержуваних з безлічі джерел, однак при завантаженні кожен з сурогатних ключів вимагає наявності обєднання Для прикладу припустимо, що наша проста таблиця фактів повязана з таблицями вимірювань dimTime, dimProduct і dimCustomer Якщо dimCustomer і DimProduct використовують сурогатні ключі, завантаження може виглядати так:

INSERT INTO WarehousedbofactOrder

(OrderDate, CustomerlD, ProductID, OrderAmount)

SELECT oDate, сCustomerlD, pProductID, ISNULL (Amount, 0)

FROM StagingdboOrders про

INNER JOIN WarehousedbodimCustomer з ON oCustCode = сCustomerCode INNER JOIN WarehousedbodimProduct p ON oCode = pProductCode

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

Процес ETL (витягання, перетворення і завантаження) складається з великого числа відносно простих дій, модифікуються в міру зміни джерела даних Централізується логіку процесу ETL, наскільки це можливо, документуйте її неочевидні аспекти та здійснюйте строгий контроль Якщо процес вимагатиме супроводу, то це спростить розкриття всіх компонентів та історії їх зміни У цьому відношенні кращими інструментами є Integration Services і SourceSafe

Знайомство зі службою аналізу

Кращий спосіб познайомитися зі службою аналізу та сховищами полягає у використанні утиліти Business Intelligence Development Studio (BEDS) для створення бази даних служби аналізу та асоційованих таблиць на базі структур, визначених у навчальній базі даних Ad venture Works Почнемо з ідентифікації та створення бази даних сховища SQL Server Потім відкриємо утиліту BEDS і створимо новий проект служби аналізу

Клацніть правою кнопкою миші на вузлі Cubes в Solution Explorer і виберіть у контекстному меню пункт New Cube На першій сторінці відкрився майстра кубів (Select Build Method) виберіть пункт Build cube without data source (Побудова куба без джерела даних), встановіть параметр Use a Cube Template і виберіть зі списку шаблон, відповідний вашої редакції SQL Server Пройдіть всі інші сторінки майстри, приймаючи запропоновані за замовчуванням параметри, за винятком сторінки Define Time Period, на якій позначте кілька додаткових періодів (наприклад, Year / Quarter / Month), щоб зробити вимір часу більш цікавим

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

Майстер кубів дозволяє виконати безліч налаштувань певної шаблоном структури, і всі необхідні зміни можна виконати в проекті служби аналізу вручну Регенерація схеми може бути виконана в будь-який час клацанням правою кнопкою миші на проекті в Solution Explorer і вибором у контекстному меню пункту Generate Relation Schema

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

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*