Удивительно, разработчики хранилищ данных не знают / мало знают, что такое профилирование данных (Data Profiling). Профилирование данных - исследование данных для выяснения статистических характеристик данных: характер распределения величин, наличие выбросов, параметры выборки, а также качества данных: наличие пропущенных значений, нарушения целостности данных, нарушения бизнес-зависимостей между значениями полей, другие ошибки в данных. Профилирование часто выполняют, исследуя данные систем-источников, перед разработкой процедур ETL.
Общепризнанный эксперт Ральф Кимбалл в своей статье "Совет №59 Удивительная польза профилирования данных" пишет: "...Профилирование данных - это систематический упреждающий анализ данных систем-источников.
С помощью профилирования может быть сделано:
- * Общее решение "Продолжать или не продолжать" для всего проекта! Профилирование может обнаружить что данные, от которых зависит проект, просто не содержат информации на базе которой могут приниматься планируемые решения. Значение такого результата трудно переоценить, несмотря на разочарования;
- * Проблемы с качеством данных в системах-источниках, которые должны быть решены (внесением изменений в источники) до продолжения проекта. Несмотря на немного меньший драматизм, чем отмена всего проекта, эти изменения являются большой внешней зависимостью, которой нужно уметь управлять, чтобы достичь успеха с внедрением хранилищем данных.
- * Проблемы с качеством данных, которые могут быть решены в ETL процессе, после того как данные были извлечены из источников. Понимание этих проблем влияет на логику трансформаций в ETL и на механизм обработки исключений. Эти проблемы помогают в оценке времени, ежедневно требуемого для ручной обработки и исправления ошибок.
- * Непредвиденные бизнес-правила, иерархические структуры, отношения внешний ключ – первичный ключ. Понимание данных на детальном уровне предостережет от серьезных проблем в разработанной ETL системе."
Посредством анализа данных единичных полей, обнаружения наличия зависимостей между полями одной таблицы, проверки того, какие поля могут быть первичными или внешними ключами, выявление дубликатов и других проверок можно получить быстрый срез картины, оценить степень бедствия, остудить чьи-то слишком радужные головы (со стороны Заказчика / Руководства), выработать план дальнейших мероприятий.
Тактично высказывая Заказчику предположения, аргументируя сложности с качеством исходных данных, можно избежать / минимизировать количество неприятных "сюрпризов", возникающих в конце проекта (например, ошибки при сборке многомерной модели OLAP или аномальные выбросы в графических отчетах). Работы по профилированию данных разумно включить в этап предпроектного обследования, на который Заказчик часто не соглашается, не готов платить, апеллируя: "За что я плачу деньги? Что я получу в итоге?". Так вот, профилирование данных (как часть предпроекта) - это своего рода расплата за поспешное внедрение учетных систем, лоскутную автоматизацию, отсутствие актуальной или какой-либо технической документации.
Наконец, занимаясь профилированием, вы будете выглядеть так, словно хорошо разбираетесь в местной специфике процессов компании, поскольку правильно заданный вопрос наполовину содержит ответ (проверено лично).
Осуществлять профилирование данных можно "вручную", исполняя SQL-запросы и систематизируя их результаты в отдельном файле. Однако при большом количестве таблиц, источников это утомительно, чрезвычайно трудоемко. Поставщики промышленных систем интеграции - Informatica, Oracle Data Integrator, IBM InfoSphere DataStage, SAP Data Integrator предоставляют подсистемы профилирования данных, интегрированные в ETL-продукты. Эти платформы стоят хороших денег и ориентированы на IT-специалистов. Корпорация Microsoft также предприняла попытки в данном направлении (см. профилирование данных в SQL Server Integration Services).
В тоже время есть Поставщики, специализирующиеся сугубо по направлению профилирования, качества данных. Некоторые из них предлагают свои программные продукты бесплатно, ориентируясь не только на профессиональных разработчиков, но и на бизнес-пользователей, системных аналитиков:
У каждого из представленных программных продуктов есть свои отличительные, сильные стороны, впрочем, есть и недостатки (каждый выбирает, исходя из своего субъективного мнения и решаемых задач).
Ознакомимся поближе с темой профилирования данных на примере функциональности программы Datiris Profiler 4.0. Прежде всего, посмотрим какую статистику позволяет снимать эта программа:
- * Минимальная длина строки поля (тип данных - строка);
- * Максимальная длина строки поля (тип данных - строка);
- * Средняя длина строки поля (тип данных - строка);
- * Медиана длины строки поля (тип данных - строка);
- * Минимальное значение поля (тип данных - строка/число/дата);
- * Максимальное значение поля (тип данных - строка/число/дата);
- * Количество записей с минимальным значением поля (тип данных - строка/число/дата);
- * Количество записей с максимальным значением поля (тип данных - строка/число/дата);
- * Среднее значение поля (тип данных - число/дата);
- * Медиана значений поля (тип данных - число/дата);
- * Количество уникальных (Distinct Count) значений поля (тип данных - строка/число/дата);
- * Процент количества уникальных значений поля от общего количества записей (тип данных - строка/число/дата);
- * Количество записей с незаполненным (NULL) значением поля (тип данных - строка/число/дата);
- * Процент количества записей с незаполненным значением поля от общего количества записей (тип данных - строка/число/дата);
- * Количество записей с 0 значением поля (тип данных - число);
- * Процент количества записей с 0 значением поля от общего количества записей (тип данных - число);
* Количество записей с пустым (" ") значением поля (тип данных - строка); - * Процент количества записей с пустым (" ") значением поля от общего количества записей (тип данных - строка);
- * Количество записей с значениями поля, соответствующим некоторому шаблону (тип данных - строка);
- * Ранг каждого значения поля (тип данных - строка/число/дата);
- * Количество записей с значениями поля, соответствующим заранее предопределенному домену;
- * Процент записей с значениями поля, соответствующим заранее предопределенному домену;
- * Количество уникальных записей (Distinct Count) с значениями поля, соответствующим заранее предопределенному домену;
- * Процент количества уникальных записей (Distinct Count) с значениями поля, соответствующим заранее предопределенному домену;
- * Количество записей таблицы, которые по полю-внешнему ключу (Foreign Key) соответствуют первичному ключу (Primary Key) другой таблицы;
- * Процент количества записей таблицы, которые по полю-внешнему ключу (Foreign Key) соответствуют первичному ключу (Primary Key) другой таблицы;
- * Количество записей таблицы, которые по полю-внешнему ключу (Foreign Key) НЕ соответствуют первичному ключу (Primary Key) другой таблицы;
- * Процент количества записей таблицы, которые по полю-внешнему ключу (Foreign Key) НЕ соответствуют первичному ключу (Primary Key) другой таблицы;
- * и некоторая другая статистика.
Вот какой функционал Поставщик заявляет в своем программном продукте:
- * Поддержка широкого спектра источников данных: Oracle Database, MS SQL Server, MS Access, (электронные таблицы Excel, текстовые файлы, файлы FoxPro, источники данных ODBC - посредством связанных таблиц в MS Access);
- * Огромный набор статистических метрик позволяет получить четкое представление о данных с разных точек зрения;
- * Межтабличный анализ позволяет выявить осиротевшие записи на основе полностью настраиваемого условия соединения (+ дополнительные фильтры) двух таблиц;
- * Определение правил для домена и проверка данных на удовлетворение этим правилам;
- * Drill down результатов профилирования позволяет исследовать источник данных в режиме реального времени;
- * Пакетное профилирование позволяет автоматизировать анализ данных сотен таблиц посредством одного клика;
- * Интерфейс командной строки позволяет выполнять профилирование данных по расписанию;
- * С помощью фильтров можно определить подмножество данных для профилирования;
- * Шаблоны профилирования позволяют настроить сложные задания один раз для многократного дальнейшего использования;
- * Тэги позволяют централизовать наблюдения и ключевые выводы о данных для всех участников проектной команды;
- * Сохранение метаданных, результатов профилирования, истории профилирования в репозитории - базе данных Oracle, MS SQL Server, MS Access;
- * Надежная система безопасности ограничения доступа пользователей к определенным источникам данных;
- * Экспорт результатов профилирования в MS Excel;
- * Интуитивно понятный интерфейс позволяет сразу воспользоваться мощным функционалом продукта: - бизнес-аналитикам; - разработчикам хранилищ данных; - специалистам по качеству данных.
Теперь перейдем к практической части. Я взял справочник марок и моделей автомобилей, импортировал его в базу данных MS SQL Server, а затем намеренно внес искажения в данные. Открываем программу Datiris Profiler, из главного меню входим в окно определения источников данных, создаем подключение к исходным данным:
Возвращаемся в главное меню и переходим (Capture Data Profiles) к форме профилирования данных:
Все параметры, настройки шаблонов профилирования, а также результаты профилирования прозрачно сохраняются в базе данных репозитория:
Проанализируем поле ID. Исходя из комментария поле должно выступать в качестве уникального идентификатора записи об авто-мото, в тоже время процент количества уникальных значений поля от общего количества записей DISTINCT_PCT = 99,98, что говорит о наличии дублирующихся значений, а точнее - об единственном неуникальном идентификаторе: общее количество записей в таблице (Total Row Count) = 4713, а количество уникальных значений поля (DISTINCT_CNT) = 4712. Впрочем, в таблице это поле не объявлено как первичный ключ (DECLARED_PK_FLAG = N). Тип поля - целочисленный (Integer), не содержит пустых значений (NULL_CNT = 0), не содержит нулевых значений (ZERO_CNT = 0), тем не менее однократно (NUMERIC_LOW_VALUE_CNT = 1) встречается значение NUMERIC_LOW_VALUE = -1, которое, скорее всего, не характерно для валидного идентификатора записи.
Проанализируем поле SubCategory. Поле символьное, декларированная длина поля (DECLARED_MAX_LENGTH) = 70, что в 3,5 раза больше встречающейся максимальной длины строки (STRING_MAX_LENGTH) = 19. Наименьшее строковое значение (STRING_LOW_VALUE) = "_маршрутка" присутствует (STRING_LOW_VALUE_CNT) = 3 раза, что, скорее всего, похоже на аномальные значения. Кроме того, данное поле сильно разрежено: процент пустых значений (NULL_PCT) = 80,84%, а количество уникальных значений (DISTINCT_CNT) = 6.
Проанализируем поле Mark. Поле символьное, декларированная длина поля (DECLARED_MAX_LENGTH) = 150, что почти в 7 раз больше встречающейся максимальной длины строки (STRING_MAX_LENGTH) = 22. Наибольшее строковое значение (STRING_HIGH_VALUE) = "ДЭУ" присутствует (STRING_HIGH_VALUE_CNT) = 2 раза, что похоже на аномальные значения, особенно учитывая тот факт, что наименования марок автомобилей по-русски должны храниться в поле Mark_ru. Данное поле объявлено как допускающее пустые значения (DECLARED_NULL_FLAG) = Y, однако в действительности пустых значений нет (NULL_CNT) = 0, равно как нет и строк, состоящих только из пробелов (BLANK_CNT) = 0.
Проанализируем поле Model. Поле символьное, декларированная длина поля (DECLARED_MAX_LENGTH) = 255, что в 7,5 раз больше встречающейся максимальной длины строки (STRING_MAX_LENGTH) = 34. Поле на 76,7% (DISTINCT_PCT) состоит из уникальных значений, в тоже время в 13,11% (NULL_PCT) случаях обнаружены пустые значения. По одному разу (STRING_LOW_VALUE_CNT, STRING_HIGH_VALUE_CNT) встречаются некорректные наименьшее значение (STRING_LOW_VALUE) = "#(wt236" и наибольшее значение (STRING_HIGH_VALUE) = "Юрий Долгорукий & ?????".
Просмотр статистики шаблонов (PATTERN) значений полей позволяет увидеть распределение количества (PATTERN_CNT), процентные соотношения (PATTERN_PCT) значений по ранжированным группам (PATTERN_RANK), а при желании провалиться (Drill Down) до исходных записей:
Просмотр значений полей позволяет увидеть распределение количества (VAL_CNT), процентные соотношения (VAL_PCT) значений по ранжированным группам (VAL_RANK), и при желании также спуститься к оригинальным записям.
Помимо межтабличного анализа (проверки условия соединения двух таблиц), проверки данных на удовлетворение правилам домена возможна настройка проверок значений полей на соблюдение определенных бизнес-правил, например, значения 6-8 разрядов в двадцатизначном лицевом счете учета основного долга (ссудной задолженности) должны соответствовать валюте кредитного договора.
Вывод: справочник марок и моделей авто-мото средств содержит незначительные ошибки, которые можно устранить, предварительно проанализировав возможное влияние исправлений на другие таблицы источника данных.
При массивном профилировании данных сотен таблиц становятся актуальными следующие вопросы:
- Производительность сбора статистики с [широких] таблиц с количеством записей десятки-сотни миллионов записей. Так, в разговоре с представителем Ataccama утверждалось, что сначала осуществляется однократный перебор всех записей (Full Scan) таблицы, а затем применяется оригинальная технология расчета статистики и сохранения результатов в собственном формате;
- Требование гибкой, мощной функциональности табличных Grid-контролов (например, Infragistics WinGrid) с возможностью быстрой индивидуальной настройки (видимость, порядок и ширина столбцов, заморозка столбцов и строк, предустановленные фильтры, группировки, сортировки, раскраска ячеек по условию, фильтры в один Click, вложенный Grid и др.) и сохранением настроек для использования в последующих сессиях;
- Наличие развитой графической визуализации результатов профилирования данных - различные типы интерактивных диаграмм (синхронизация с табличными представлениями, возможность Drill Down на графиках) с сохранением настроек для последующего сравнительного анализа.
Функциональность других программных продуктов класса Data Profiling во многом пересекается с вышеописанной, какую программу использовать - выбор за Вами.
Резюмируя все вышесказанное, надо сказать, что тема профилирования данных становится более востребованной в отечественных проектах. Будем надеяться, эта одна из дорог, которая приведет нас к повышению культуры работы с данными.