ETL (extract, transform, load — «извлечь, преобразовать, загрузить») — это процесс, который забирает данные из рабочих систем, приводит их к единому виду и кладёт в отдельное хранилище. Хранилище данных (data warehouse) — база, куда данные из многих систем собраны, очищены и хранятся с историей, чтобы по ним строить отчёты, не трогая рабочие системы. Витрина — часть хранилища, подготовленная под вопросы одного подразделения.
ELT — тот же путь в другом порядке: данные сначала загружают как есть, а преобразования выполняет сама база после загрузки. Для заказчика важнее названия три вещи: кто отвечает за каждый показатель, что происходит при сбое загрузки и можно ли объяснить любую цифру. Как мы строим такую цепочку, описано на странице «BI и аналитика для бизнеса».
Зачем отдельное хранилище, если отчёты можно строить в 1С
Для одной системы и простых вопросов отчётов самой системы хватает. Отдельное хранилище нужно, когда вопрос выходит за пределы одной системы или когда отчёты начинают мешать работе.
| Вопрос | Рабочая система | Хранилище |
|---|---|---|
| Для чего построена | Для записи: оформить заказ, провести документ | Для чтения: сложить, сгруппировать, сравнить периоды |
| Что хранит | Текущее состояние; прошлое часто перезаписано | Историю изменений и срезы на даты |
| Сколько систем видит | Одну | Несколько, с общими справочниками и определениями |
| Тяжёлый отчёт | Может замедлить работу пользователей | Работает на своём сервере и рабочие системы не нагружает |
| Определение показателя | У каждой системы своё | Одно, записанное и утверждённое владельцем |
Отчёты самой 1С остаются нужны: они считают по одной базе и по её правилам. Хранилище дополняет их и не заменяет.
Путь данных от системы до отчёта: шесть шагов на условном примере
Условный пример. Директор производственной компании спрашивает, сколько часов простоя в прошлом месяце вызвано тем, что не оказалось запчасти. Ответ лежит в двух системах: заявки на ремонт ведутся в одной, склад запчастей — в 1С, и ни одна из них такого отчёта не даёт.
- Вопрос и определение. Что считать простоем из-за запчасти: заявку с такой причиной или время от заказа детали до её поступления. Определение записывает и утверждает владелец показателя.
- Извлечение (extract). Из системы заявок берём заявки с временем начала и окончания и причиной, из 1С — заказы запчастей и поступления. Забираем через программный интерфейс или выгрузку, а не ходим в базу напрямую.
- Загрузка (load) в сырой слой. Данные ложатся в хранилище как есть, с временем загрузки. Вручную их не правят: любую ошибку потом можно сверить с источником.
- Преобразование (transform). Станок в заявках называется «Пресс 3», в 1С — «Пресс гидравлический ПГ-3»: строится таблица соответствия. Время приводится к одному поясу, дубли заявок объединяются, статусы сводятся к общему набору.
- Витрина. Считается показатель «часы простоя из-за отсутствия запчасти» по станкам и месяцам. Расчёт хранится как описанный запрос под контролем версий, чтобы его можно было прочитать и проверить.
- Отчёт и контроль. Панель читает только витрину, рядом с цифрой стоит время обновления. Контрольные значения сверяют с расчётом владельца, а остановку загрузки видно сразу.
Шаги со второго по четвёртый — это перенос данных, шаги пятый и шестой — аналитика поверх хранилища. В этом примере сначала идёт загрузка в сырой слой, потом преобразование внутри хранилища, то есть ELT. В классическом ETL третий и четвёртый шаги поменялись бы местами: данные очищались бы до записи. Чаще всего больше времени уходит не на технику, а на первый и четвёртый шаги: определения и таблицы соответствия.
ETL и ELT: в чём разница и что выбрать
Разница в том, где и когда данные преобразуются. Ни один порядок не лучше другого сам по себе, они решают разные задачи.
| Критерий | ETL | ELT |
|---|---|---|
| Где преобразуются данные | Вне хранилища, в отдельной программе загрузки | Внутри хранилища, запросами после загрузки |
| Что лежит в хранилище | Уже очищенные данные | Сначала сырые, затем очищенные данные и витрины |
| Когда подходит | Преобразования сложные или лишнее нельзя пускать в хранилище, например персональные данные | База достаточно мощная, а правила удобно писать на SQL и часто менять |
| Что проще | Контролировать, что попало в хранилище | Пересчитать историю по новым правилам: сырые данные уже есть |
| Риск | Правило изменили, а исходных данных в хранилище нет: пересчитать нельзя | Сырые данные с ошибками и персональными сведениями лежат рядом с чистыми, права нужны на каждом слое |
На практике границы размыты: простые исправления делают при загрузке, сложные расчёты в базе. В небольшом хранилище разница почти незаметна. Куда важнее, чтобы повторный запуск загрузки не удваивал данные, а каждая загрузка оставляла запись в журнале.
Хранилище, витрина и озеро данных: чем отличаются
Три слова, которые часто используют как синонимы. Различие в том, что хранится, для кого и где оно ломается.
| Понятие | Что хранит | Для кого | Где ломается |
|---|---|---|---|
| Хранилище | Очищенные данные из многих систем с историей | Аналитики и все витрины | Строят «на всё» и не заканчивают: нет владельца, определения спорные |
| Витрина | Готовые таблицы и показатели под вопросы одного подразделения | Руководитель, отдел | Витрин много, определения в них разные: две цифры выручки |
| Озеро данных | Файлы и записи любого вида как есть: выгрузки, журналы, документы | Инженеры и аналитики данных | Превращается в свалку: неясно, что там лежит и можно ли этому верить |
| Рабочая база | Текущие данные одной системы | Пользователи этой системы | Её используют как хранилище, и отчёты тормозят работу |
Начинать можно с хранилища на PostgreSQL: его хватает, пока запросы укладываются в нужное время. Специализированные колоночные базы нужны, когда объём и скорость запросов этого требуют, а не заранее.
Что ломает цифры: история справочников, повторы, удаления и время
Эти ситуации не видны на демонстрации и всплывают через месяцы.
Справочник изменился: к какому менеджеру отнести прошлые заказы
Клиента передали от одного менеджера другому. Если в хранилище только текущее значение, все прошлые заказы окажутся у нового менеджера, а отчёт за прошлый квартал изменится задним числом. Поэтому для справочников, влияющих на отчёты, хранят периоды действия значения: с какой по какую дату клиент был закреплён за кем.
Загрузка упала на середине
Если её просто перезапустить, а она дописывает строки, часть данных удвоится. Правильно, когда загрузка заменяет данные по ключу и повторный запуск даёт тот же результат.
Записи удалили в источнике
Загрузка только изменённого не замечает удалений, если источник не оставляет метку. Для таких таблиц периодически выполняют полную сверку с источником.
Время и закрытые периоды
Источники хранят время в разных поясах, а период в учётной системе могут исправить задним числом. У записи хранят и время события, и время загрузки, а закрытые периоды фиксируют: отчёт за закрытый месяц не должен меняться.
Когда нужно хранилище, а когда хватит отчётов 1С или таблиц
| Признак | Хватит отчётов и таблиц | Пора строить хранилище |
|---|---|---|
| Источники | Один, вопросы касаются его данных | Два и больше, вопросы сквозные |
| Цифры | Расхождений нет | На совещании две цифры по одному показателю |
| Сборка отчёта | Нажать кнопку | Человек собирает файл вручную каждую неделю |
| Нагрузка | Отчёты не мешают работе | Тяжёлые отчёты замедляют рабочие системы |
| История | Нужны только текущие данные | Нужны срезы на прошлые даты и сравнение периодов |
| Владелец данных | Не нужен | Назначен и готов утверждать определения |
Последняя строка определяет всё. Без человека, который отвечает за данные, хранилище станет ещё одним источником цифр, которым не верят. Если признаков пока один-два, начните со сводной таблицы с записанными определениями; порядок разобран в статье «BI или Excel: когда нужна аналитическая система». Справочники, которые расходятся между системами, чистят отдельно, как в статье «НСИ при внедрении ERP».
Типичные ошибки: хранилище без владельца данных и ещё пять
| Ошибка | К чему приводит | Как избежать |
|---|---|---|
| Хранилище без владельца данных | Определения никто не утверждает, цифры оспариваются | Назначить владельца каждого показателя до разработки |
| Строят «на всё» | Долго, дорого, а к сроку ничего не работает | Один круг вопросов и одно подразделение, расширять по этапам |
| Начали с панелей | Красивый экран над данными, которым не верят | Сначала определения и загрузка, потом экран |
| Нет журнала загрузок | Остановку замечают по жалобам пользователей | Каждая загрузка пишет время, число строк и итог; остановка оповещает ответственного |
| Правят данные в хранилище | Расхождение с источником нельзя объяснить | Исправлять в источнике, а в хранилище держать правила в коде |
| Не сверяют с источником | Ошибка в загрузке живёт месяцами | Сверять контрольные показатели с расчётом владельца данных |
Что делаем мы и с чего начать
Мы собираем такую цепочку на вашем сервере: от словаря показателей и загрузки из 1С, CRM и таблиц до витрин и панелей (страница «BI и аналитика для бизнеса»). Первый шаг — обследование: 200 000 ₽ и 2–3 недели за один-два источника, 350 000 ₽ и 3–4 недели за несколько систем, 500 000 ₽ и 4–6 недель за производство или контур. На выходе словарь показателей, схема загрузки и вилка бюджета. Сумма вычитается из первого этапа, если вы продолжаете с нами.
Витрина и панели для одного подразделения стоят от 250 000 ₽ (нижняя ступень порталов, кабинетов и витрин, см. «Порталы, личные кабинеты и витрины данных»). Лёгкий пилот — 150 000–400 000 ₽, аналитика по нескольким направлениям — 400 000–1 200 000 ₽, вывод в эксплуатацию — от 1 500 000 ₽ этапами по 300 000–700 000 ₽, сопровождение — от 20 000 ₽ в месяц. Хранилище на всю компанию мы не внедряем: берём один круг вопросов. Цены и сроки — на странице «Тарифы».
Частые вопросы
Что такое ETL простыми словами?
Процесс переноса данных из рабочих систем в хранилище в три шага: забрать данные из источников, привести их к единому виду и записать в хранилище. По дороге исправляют форматы, объединяют дубли и сопоставляют справочники, чтобы данные разных систем можно было сложить.
Чем ETL отличается от ELT?
Порядком шагов. В ETL данные преобразуют до загрузки, в ELT сначала загружают как есть, а преобразования выполняет база. ELT удобнее, когда хочется пересчитывать историю по новым правилам, ETL — когда в хранилище нельзя пускать лишнее. На небольшом хранилище разница почти незаметна.
Чем хранилище данных отличается от обычной базы данных?
Устройством и назначением. Рабочая база построена для записи и хранит текущее состояние одной системы. Хранилище построено для чтения и анализа, собирает данные многих систем и хранит историю. Технически оба могут стоять на одной и той же СУБД, например PostgreSQL.
Что такое витрина данных и чем она отличается от хранилища?
Витрина — готовые таблицы и показатели под вопросы одного подразделения или одной задачи, построенные на данных хранилища. Хранилище шире: в нём все очищенные данные с историей, а витрин на нём может быть несколько.
Нужно ли хранилище, если уже есть BI?
BI показывает данные, хранилище их готовит. Если панели построены на ручных выгрузках, а определения у каждой свои, чинить нужно загрузку и определения, а не диаграммы. Хранилище даёт BI надёжную основу, но без владельца данных не поможет.
Что такое озеро данных и нужно ли оно?
Озеро хранит файлы и записи любого вида как есть, без заранее заданной структуры. Оно нужно, когда сырые данные приходят в разном виде и их разбирают инженеры данных. Для управленческой отчётности по учётным системам обычно достаточно хранилища с сырым слоем.
Сколько стоит построить хранилище данных?
Начинаем с обследования: 200 000 ₽ за один-два источника, 350 000 ₽ за несколько систем, 500 000 ₽ для производства или контура. Витрина и панели для одного подразделения — от 250 000 ₽, лёгкий пилот — 150 000–400 000 ₽, несколько направлений — 400 000–1 200 000 ₽. Хранилище на всю компанию мы не внедряем.
Где хранить данные, если они ограниченного доступа?
В хранилище на ваших серверах или у выбранного вами хостера. Для данных ограниченного доступа облачные сервисы аналитики чаще не подходят по требованиям заказчика. Персональные данные граждан России обычно должны храниться на серверах в России.