ETL (extract, transform, load — «извлечь, преобразовать, загрузить») — это процесс, который забирает данные из рабочих систем, приводит их к единому виду и кладёт в отдельное хранилище. Хранилище данных (data warehouse) — база, куда данные из многих систем собраны, очищены и хранятся с историей, чтобы по ним строить отчёты, не трогая рабочие системы. Витрина — часть хранилища, подготовленная под вопросы одного подразделения.

ELT — тот же путь в другом порядке: данные сначала загружают как есть, а преобразования выполняет сама база после загрузки. Для заказчика важнее названия три вещи: кто отвечает за каждый показатель, что происходит при сбое загрузки и можно ли объяснить любую цифру. Как мы строим такую цепочку, описано на странице «BI и аналитика для бизнеса».

Коротко. ETL забирает данные из систем, очищает их и кладёт в хранилище, ELT сначала загружает, а потом преобразует в самой базе. Хранилище хранит историю и общие определения, витрина отвечает на вопросы одного подразделения. Нужны они, когда отчёт собирают вручную из нескольких систем или цифры расходятся. Чаще всего ломаются не технологии, а определения показателей и отсутствие владельца данных.

Зачем отдельное хранилище, если отчёты можно строить в 1С

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

ВопросРабочая системаХранилище
Для чего построенаДля записи: оформить заказ, провести документДля чтения: сложить, сгруппировать, сравнить периоды
Что хранитТекущее состояние; прошлое часто перезаписаноИсторию изменений и срезы на даты
Сколько систем видитОднуНесколько, с общими справочниками и определениями
Тяжёлый отчётМожет замедлить работу пользователейРаботает на своём сервере и рабочие системы не нагружает
Определение показателяУ каждой системы своёОдно, записанное и утверждённое владельцем

Отчёты самой 1С остаются нужны: они считают по одной базе и по её правилам. Хранилище дополняет их и не заменяет.

Путь данных от системы до отчёта: шесть шагов на условном примере

Условный пример. Директор производственной компании спрашивает, сколько часов простоя в прошлом месяце вызвано тем, что не оказалось запчасти. Ответ лежит в двух системах: заявки на ремонт ведутся в одной, склад запчастей — в 1С, и ни одна из них такого отчёта не даёт.

  1. Вопрос и определение. Что считать простоем из-за запчасти: заявку с такой причиной или время от заказа детали до её поступления. Определение записывает и утверждает владелец показателя.
  2. Извлечение (extract). Из системы заявок берём заявки с временем начала и окончания и причиной, из 1С — заказы запчастей и поступления. Забираем через программный интерфейс или выгрузку, а не ходим в базу напрямую.
  3. Загрузка (load) в сырой слой. Данные ложатся в хранилище как есть, с временем загрузки. Вручную их не правят: любую ошибку потом можно сверить с источником.
  4. Преобразование (transform). Станок в заявках называется «Пресс 3», в 1С — «Пресс гидравлический ПГ-3»: строится таблица соответствия. Время приводится к одному поясу, дубли заявок объединяются, статусы сводятся к общему набору.
  5. Витрина. Считается показатель «часы простоя из-за отсутствия запчасти» по станкам и месяцам. Расчёт хранится как описанный запрос под контролем версий, чтобы его можно было прочитать и проверить.
  6. Отчёт и контроль. Панель читает только витрину, рядом с цифрой стоит время обновления. Контрольные значения сверяют с расчётом владельца, а остановку загрузки видно сразу.

Шаги со второго по четвёртый — это перенос данных, шаги пятый и шестой — аналитика поверх хранилища. В этом примере сначала идёт загрузка в сырой слой, потом преобразование внутри хранилища, то есть ELT. В классическом ETL третий и четвёртый шаги поменялись бы местами: данные очищались бы до записи. Чаще всего больше времени уходит не на технику, а на первый и четвёртый шаги: определения и таблицы соответствия.

ETL и ELT: в чём разница и что выбрать

Разница в том, где и когда данные преобразуются. Ни один порядок не лучше другого сам по себе, они решают разные задачи.

КритерийETLELT
Где преобразуются данныеВне хранилища, в отдельной программе загрузкиВнутри хранилища, запросами после загрузки
Что лежит в хранилищеУже очищенные данныеСначала сырые, затем очищенные данные и витрины
Когда подходитПреобразования сложные или лишнее нельзя пускать в хранилище, например персональные данныеБаза достаточно мощная, а правила удобно писать на 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 ₽. Хранилище на всю компанию мы не внедряем.

Где хранить данные, если они ограниченного доступа?

В хранилище на ваших серверах или у выбранного вами хостера. Для данных ограниченного доступа облачные сервисы аналитики чаще не подходят по требованиям заказчика. Персональные данные граждан России обычно должны храниться на серверах в России.