Основні функції ETL-систем

ETL — абревіатура від Extract, Transform, Load. Це системи корпоративного класу, які застосовуються, щоб привести до одних довідників і завантажити в DWH і EPM дані з декількох різних облікових систем.

Ймовірно, більшості цікавляться добре знайомі принципи роботи ETL, але як такої статті, що описує концепцію ETL без прив'язки до конкретного продукту, на я Хабре не знайшов. Це і послужило приводом написати окремий текст.

Хочу обмовитися, що опис архітектури відображає мій особистий досвід роботи з ETL-інструментами і моє особисте розуміння «нормального» застосування ETL — проміжним шаром між OLTP системами і OLAP системою або корпоративним сховищем.
Хоча в принципі існують ETL, який можна поставити між будь-якими системами, краще інтеграцію між обліковими системами вирішувати зв'язкою MDM і ESB. Якщо ж вам для інтеграції двох залежних облікових систем необхідний функціонал ETL, то це помилка проектування, яку треба виправляти доопрацюванням цих систем.

Навіщо потрібна ETL система

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

Цей бардак є завжди, він буває двох видів:
  1. Як випадкові помилки, що виникли на рівні введення, передавання даних, або із-за помилок;
  2. відмінності в довідниках і деталізації даних між суміжними ІТ-системами.
При цьому якщо перший вид бардаку побороти можна, то другий вид здебільшого не є помилкою — контрольовані відмінності в структурі даних, це нормальна оптимізація під цілі конкретної системи.

З-за цієї особливості ETL-системи повинні в ідеалі вирішувати не одну, а дві задачі:
  1. Привести всі дані до єдиної системи значень і деталізації, попутно забезпечивши їх якість і надійність;
  2. Забезпечити аудиторський слід при перетворенні (Transform) даних, щоб після перетворення можна було зрозуміти, з яких саме вихідних даних і сум зібралася кожна строчка перетворених даних.
Пам'ятати про цих двох задачах буває дуже корисно, особливо якщо ви пишете ETL-процес вручну, або робите його з використанням фреймворків низької готовності, в яких не задана готова структура проміжних таблиць.
Легко випустити другу задачу і мати багато проблем з пошуком причин помилок у трансформованих даних.

Як працює ETL система

Всі основні функції ETL системи уміщаються в наступний процес:



У розрізі потоку даних це кілька систем-джерел (зазвичай OLTP) і система приймач (зазвичай OLAP), а так само п'ять стадій перетворення між ними:



  1. Процес завантаження — Його задача затягнути в ETL дані довільного якості для подальшої обробки на цьому етапі важливо звірити суми прийшли рядків, якщо у вихідній системі більше рядків, ніж у RawData то значить — завантаження пройшла з помилкою;
  2. Процес валідації даних — на цьому етапі дані послідовно перевіряються на коректність і повноту, складається звіт про помилки для виправлення;
  3. Процес меппінга даних з цільовою моделлю — на цьому етапі до валидированной таблиці прилаштовується ще n-стовпців за кількістю довідників цільової моделі даних, а потім за таблицями мэппингов в кожній прибудованої клітинці в кожному рядку проставляються значення цільових довідників. Значення можуть проставлятися як 1:1, так і *:1 і 1:**: * для настроювання останніх двох варіантів використовують формули і скрипти меппінга, реалізовані в ETL-інструменті;
  4. Процес агрегації даних — цей процес потрібен з-за різниці деталізації даних OLTP і OLAP системах. OLAP-системи — це, по суті, повністю денормализованная таблиця фактів і навколишні її таблиці довідників (зірочка/сніжинка), максимальна деталізація сум OLAP — це кількість перестановок всіх елементів всіх довідників. А OLTP система може містити декілька сум для одного і того ж набору елементів довідників. Можна було б вбивати OLTP-деталізацію ще на вході в ETL, але тоді ми втратили б «аудиторський слід». Цей слід потрібен для побудови Drill-down звіту, який показує — з яких рядків OLTP, сформувалася сума в клітинці OLAP-системи. Тому спочатку робиться меппінг на деталізації OLTP, а потім в окремій таблиці дані «схлопывают» для завантаження в OLAP;
  5. Вивантаження в цільову систему — це технічний процес використання коннектора і передачі даних в цільову систему.


Особливості архітектури

Реалізація процесів 4 і 5 з точки зору архітектури тривіальна, всі складнощі мають технічний характер, а от реалізація процесів 1, 2 і 3, вимагає додаткового пояснення.

Процес завантаження
При проектуванні процесу завантаження даних необхідно пам'ятати про те що:

  1. Треба враховувати вимоги бізнесу по тривалості всього процесу. Наприклад: Якщо дані повинні бути завантажені протягом тижня з моменту готовності вихідних системах, і відбувається 40 ітерацій завантаження до отримання нормальної якості, то тривалість завантаження пакета не може бути більше 1-го години. (При цьому якщо в середньому відбувається не більше 40 завантажень, то процес завантаження не може бути більше 30 хвилин, тому що в половині випадків буде більше 40 ітерацій, ну або точніше треба вважати ймовірності:) ) Головне якщо ви не укладаєтеся в свій розрахунок, то не сподівайтеся на диво — зносите і все робити заново т. к. ви не впишіть;
  2. Дані можуть завантажуватися набігаючої хвилі — з послідовним оновленням даних одного і того ж періоду в майбутньому протягом декількох послідовних періодів. (наприклад: оновлення прогнозу закінчення року кожен місяць). Тому крім довідника «Період», повинен бути передбачений технічний довідник «Період завантаження», який дозволить ізолювати процеси завантаження даних в різних періодах та не втратити історію зміни цифр;
  3. Дані мають звичай бути перегружаемыми багато разів, і добре, якщо буде технічний довідник «Версія» як мінімум з двома елементами «Робоча» та «Фінальна», для відділення вичищених даних. Крім того створення персональних версій, однією сумарної і однієї фінальної дозволяє добре контролювати завантаження в кілька потоків;
  4. Дані завжди містять помилки: Перезавантажувати весь пакет [50GB -> +8] це дуже економно по земельних ресурсах і ви, швидше за все, не впишіться в регламент, отже, треба грамотно ділити завантажений пакет файлів і так проектувати систему, щоб вона дозволяла оновлювати пакет з маленьких частин. З мого досвіду кращий спосіб — технічна аналітика «файл», і інтерфейс, який дозволяє знести всі дані лише з одного файлу, і вставити замість нього оновлені. А сам пакет розумно ділити на файли за кількістю виконавців, відповідальних за їх заповнення (або адміни систем готують вивантаження, користувачі заповнюють вручну);
  5. При проектуванні поділу пакету на частини треба ще враховувати можливість так званого «збагачення» даних (наприклад: Коли 12 січня вважають податки минулого року за правилами управлінського обліку, а в березні-квітні перевантажують суми на пораховані з бухгалтерського), це вирішується з одного боку правильним проектуванням поділу пакету даних на частини так, щоб для збагачення треба було перевантажити ціле кількість файлів (не 2,345 файлу), а з іншого боку введенням ще одного технічного довідника з періодами збагачення, щоб не втратити історію змін з цих причин).


Процес валідації
Даний процес відповідає за виявлення помилок і прогалин в даних, переданих в ETL.
Саме програмування або налаштування формул перевірки не викликає питань, головне питання — як вирахувати можливі види помилок в даних, і за якими ознаками їх ідентифікувати?
Можливі види помилок у даних залежать від того, якого роду шкали застосовуються для цих даних. (Посилання на прекрасний пост, що пояснює, які існують види шкалhttp://habrahabr.ru/post/246983/).

Ближче до практики в кожному з типів переданих даних в 95% випадків можливі наступні помилки:

Типи даних Всередині поля По відношенню до інших полів Сумісність форматів при передачі між системами
Перерахування і текст
  1. Не зі списку дозволених значень
  2. Відсутність обов'язкових значень

  3. Не відповідність формату (Всі договори повинні нумеруватися «ДГВхххх..»)
  1. Не зі списку дозволених значень для пов'язаний елемент
  2. Відсутність обов'язкових елементів для пов'язаний елемент

  3. Не відповідність формату пов'язаного елемента(наприклад: для продукту «АІС» всі договори повинні нумеруватися «АИСхххх..»)
  1. допустимі Символи в одному форматі, неприпустимі в іншому
  2. Кодування

  3. Зворотна сумісність (Елемент довідника був змінений в цільовій системі без додавання меппінга)
  4. Нові значення (немає меппінга)
  5. Застарілі значення (не зі списку дозволених в цільовій системі)
і порядки
  1. Не число
  2. Не в кордонах дозволеного інтервалу значень

  3. Пропущено порядкове значення (наприклад: дані не дійшли)
  1. Не виконується відношення y=ax+b (наприклад: ПДВ і прибуток, або Зустрічні суми дорівнюють)

  2. Елементу «А» присвоєно неправильний порядковий номер
  3. Різниці за рахунок різних правил округлення значень (наприклад: в 1С і SAP ніколи не сходиться розрахований ПДВ)
  1. Переповнення
  2. Втрата точності і знаків

  3. Несумісність форматів при конвертації в число
Дати і періоди
  1. День тижня не відповідає даті
  2. Сума одиниць часу не відповідає з-за різниці робочі/робочі/святкові/скорочені дні

  1. Несумісність формату дати при передачі текстом (наприклад: ISO 8601 в UnixTime, або різні формати ISO 8601)

  2. Помилка точки відліку і точності при передачі числом (наприклад: TimeStamp DateTime)


Відповідно перевірки на помилки реалізуються або формулами, або скриптами в редакторі конкретного ETL-інструменту.
А якщо взагалі по великому рахунку, то велика частина ваших валідації буде на відповідність довідників, а це [select * from a where a.field not in (select...) ]
При цьому для збереження аудиторського сліду розумно зберігати в системі дві окремі таблиці — rawdata і cleandata з підтримкою зв'язку 1:1 між рядками.

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

  1. Таблиця замэпленных даних повинна включати одночасно два набору полів — старих і нових аналітик, щоб можна було зробити select по вихідним аналітикам і подивитися, які цільові аналітики їм присвоєні, і навпаки:



  2. Таблиця замэпленных елементів повинна мати окремий PK-поле, щоб при зв'язку 1:**: * можна було створити багато рядків у MappedData для одного рядка в CleanData і зберегти аудиторський слід
  3. Таблиця MappedData повинна бути окремою від CleanData з тих же причин що і пункт 2


Висновок

В принципі це все архітектурні прийоми, які мені сподобалися в тих ETL інструментах, якими я користувався.

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

Сподіваюся, ця інформація буде вам корисна.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.