Двести сказанных слов не стоят и половины сделанного дела. Не ошибается тот, кто ничего не делает.
Мне доводилось значительное время работать в компании, где признавались следующие положения: право сотрудника на ошибку, отказ от системы наказаний, внедрение системы предложений, вовлечение людей фирмы в процесс совершенствования качества продукции и услуг, приближение к идее пожизненного найма. Умение признавать, предвидеть и устранять ошибки - показатель зрелости менеджмента.
Применительно к теме хранилищ данных и Business Intelligence ошибки в данных могут быть по причинам:
- - ошибки операторов ввода данных в учетных системах (человеческий фактор);
- - намеренный ввод некорректных данных в исходных системах, халатность;
- - слабый программный контроль данных, вводимых в учетных системах, перегруженность интерфейса пользователей (GUI);
- - большое количество автономных, слабосогласованных учетных систем;
- - кривая структура учетной(ых) системы, реализация наспех, нестабильность ядра системы;
- - неверное понимание, толкование бизнес-смысла данных на каком-либо / всех этапах манипулирования данными;
- - отсутствие / фрагментарность методологии процесса управления данными как единой согласованной технологической цепочки;
- - отсутствие / ангажированная или поверхностная реализация системы управления мастер-данными (MDM);
- - ошибки трансформаций данных в ETL / ELT процессах;
- - кривой дизайн многомерной модели (OLAP), ошибки в формулах, запросах;
- - список можно продолжить...
Обеспечение качества данных - большая, огромная отдельная тема. Применяя комплекс мероприятий, можно добиться значительного сокращения количества ошибок, однако полностью устранить ошибки, тем более избежать ошибок в данных хранилища, витрин вряд ли удастся. Иногда аналитическую отчетность приходится строить на таком массиве данных, какой предоставляется, и в момент встречающиеся ошибки не устранить. Иногда можно слышать доводы о несущественной доле (< 1...5 %) записей с определенной ошибкой, но при этом не принимаются во внимание значимость этих записей (например, соответствующие им денежные суммы, статусность записей), интегральная доля записей по совокупности ошибок в данных. Но если ошибки пока нельзя устранить, то их можно хотя бы оценить: на основании здравого смысла и/или сформулированных бизнес-подразделениями правил проверки разрабатываются ETL-процедуры проверки качества данных таблиц - проверки постфактум. Эти процедуры подключаются на автоматическое исполнение после загрузки данных в верхнеуровневые таблицы хранилища данных. Далее на помощь приходит технология OLAP.
Идея заключается в следующем:
- * создаются справочник, измерение типов ошибок;
- * для каждой записи наблюдаемой таблицы фактов формируется показатель - сумматор битового вектора из кодов ошибок, обнаруженных в результате исполнения проверочных процедур на соблюдение бизнес-правил;
- * измерение типов ошибок связывается с наблюдаемой таблицей фактов отношением многие-ко-многим (M2M).
А теперь подробно. В реляционном хранилище / витрине данных создадим таблицу - справочник error_list (см. ниже скрипты Oracle), в которой будем фиксировать описание процедур проверки критичных и некритичных бизнес-правил для каждой наблюдаемой таблицы фактов. В качестве кодов ошибок используем целые числа 2 в степени N, где N=0...18 (объяснение, почему именно такой диапазон, будет дано позже). Создадим таблицу error_log, в которую проверочные процедуры должны записывать коды ошибок для тех записей проверяемых таблиц фактов, где были обнаружены нарушения бизнес-правил. Суммы кодов ошибок из таблицы error_log будем подтягивать к основной таблице фактов запросом (запрос приводится ниже).
--------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE error_list ( id_error NUMERIC(16,0) NOT NULL -- Код ошибки, содержит значения 0,1,2,4,8,16.... ,error VARCHAR(150) NOT NULL -- Описание контролируемой ошибки ,error_severity NUMERIC(1,0) NOT NULL -- Уровень серьезности ошибки, допустимые значения: 1-серьезная, 2-не критичная ,error_severity_name VARCHAR(20) NOT NULL -- Наименование серьезности ошибки, допустимые значения: 'серьезная', 'не критичная' ,monitoring_table VARCHAR(50) NOT NULL -- Системное наименование таблицы, данные записей которой подлежат мониторингу ,check_created DATE NOT NULL -- Дата создания процедуры проверки ,check_author VARCHAR(150) NULL -- Автор (ФИО) процедуры проверки ,check_procedure VARCHAR(100) NULL -- Системное наименование процедуры/пакета проверки ,is_active NUMERIC(1,0) NOT NULL -- Флаг (1-включено, 0-отключено) необходимости исполнения процедуры контроля ) / COMMENT ON TABLE error_list IS 'Справочник ошибок' / COMMENT ON COLUMN error_list.id_error IS 'Код ошибки, содержит значения 0,1,2,4,8,16....' / COMMENT ON COLUMN error_list.error IS 'Описание контролируемой ошибки' / COMMENT ON COLUMN error_list.error_severity IS 'Уровень серьезности ошибки, допустимые значения: 1-серьезная, 2-не критичная' / COMMENT ON COLUMN error_list.error_severity_name IS 'Наименование серьезности ошибки, допустимые значения: серьезная, не критичная' / COMMENT ON COLUMN error_list.monitoring_table IS 'Системное наименование таблицы, данные записей которой подлежат мониторингу' / COMMENT ON COLUMN error_list.check_created IS 'Дата создания процедуры проверки' / COMMENT ON COLUMN error_list.check_author IS 'Автор (ФИО) процедуры проверки' / COMMENT ON COLUMN error_list.check_procedure IS 'Системное наименование процедуры/пакета проверки' / COMMENT ON COLUMN error_list.is_active IS 'Флаг (1-включено, 0-отключено) необходимости исполнения процедуры контроля' / CREATE UNIQUE INDEX ui_error_list ON error_list (monitoring_table, error_severity, id_error) --------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE error_acc ( ,id_error NUMERIC(16,0) NOT NULL -- Код ошибки, содержит значения 0,1,2,4,8,16.... ,id_error_sum NUMERIC(16,0) NOT NULL -- Сумма кодов ) COMMENT ON TABLE error_acc IS 'Bridge-таблица, для связи M2M' / COMMENT ON COLUMN error_acc.id_error IS 'Код ошибки, содержит значения 0,1,2,4,8,16....' / COMMENT ON COLUMN error_acc.id_error_sum IS 'Сумма кодов' / CREATE UNIQUE INDEX ui_error_acc ON error_acc(id_error, id_error_sum) --------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE error_log ( monitoring_table VARCHAR(50) NOT NULL -- Системное наименование таблицы, в записи которой обнаружена ошибка ,row_key_str VARCHAR(50) NOT NULL -- PK-ключ записи в таблице, в которой осуществляется мониторинг ошибок ,row_key_num NUMERIC(18,0) NULL -- PK-ключ записи целочисленный (если таковой есть в Таблице, в которой осуществляется мониторинг ошибок) ,error_severity NUMERIC(1,0) NOT NULL -- Уровень серьезности ошибки, допустимые значения: 1-серьезная, 2-не критичная ,id_error NUMERIC(16,0) NOT NULL -- Код ошибки, содержит значения 0,1,2,4,8,16.... ,error_open DATE NOT NULL -- Дата-время записи в логе, когда соотв. ошибка была обнаружена в записи таблицы, над которой осуществляется мониторинг ,error_close DATE DEFAULT '01012100' NOT NULL -- Дата-время записи в логе, когда соотв. ошибка была устранена в записи таблицы, над которой осуществляется мониторинг ) COMMENT ON TABLE error_log IS 'Лог бизнес-ошибок в данных записей таблиц, по которым осуществляется мониторинг' / COMMENT ON COLUMN error_log.monitoring_table IS 'Системное наименование таблицы, в записи которой обнаружена ошибка' / COMMENT ON COLUMN error_log.row_key_str IS 'PK-ключ записи в таблице, в которой осуществляется мониторинг ошибок' / COMMENT ON COLUMN error_log.row_key_num IS 'PK-ключ записи целочисленный (если таковой есть в таблице, в которой осуществляется мониторинг ошибок)' / COMMENT ON COLUMN error_log.error_severity IS 'Уровень серьезности ошибки, допустимые значения: 1-серьезная, 2-не критичная' / COMMENT ON COLUMN error_log.id_error IS 'Код ошибки, содержит значения 0,1,2,4,8,16....' / COMMENT ON COLUMN error_log.error_open IS 'Дата-время записи в логе, когда соотв. ошибка была обнаружена в записи таблицы, над которой осуществляется мониторинг' / COMMENT ON COLUMN error_log.error_close IS 'Дата-время записи в логе, когда соотв. ошибка была устранена в записи таблицы, над которой осуществляется мониторинг' --------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------- -- Запрос для подготовки данных таблицы error_acc WITH t AS ( SELECT POWER(2, LEVEL-1) AS k FROM dual CONNECT BY LEVEL <= 18 ) SELECT k AS id_error ,SUBSTR(SYS_CONNECT_BY_PATH (k, '+'), 2, 2000) AS summ FROM t CONNECT BY k > PRIOR k -- ORDER BY 1, 2 -- 10 типов ошибок - 1 023 записей -- 18 типов ошибок - 262 143 записей -- 20 типов ошибок - 1 048 575 записей --------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------- -- Пример запроса к основной таблице (по которой мониторинг) + сумма кодов ошибок по ее записям: WITH z AS ( SELECT row_key_str ,SUM(CASE WHEN error_severity=1 THEN id_error ELSE 0 END) AS id_err_sum ,SUM(CASE WHEN error_severity=2 THEN id_error ELSE 0 END) AS id_err_sum2 FROM Error_Log WHERE monitoring_table='REQUEST' AND TRUNC(SYSDATE)<=error_close -- выбираются только записи-логи ошибок, которые еще не закрыты (не устранены ошибки в данных) GROUP BY row_key_str ) SELECT b.* ,CAST(NVL(z.id_err_sum,0) AS NUMBER(16,0)) AS id_err_sum ,CAST(NVL(z.id_err_sum2,0) AS NUMBER(16,0)) AS id_err_sum2 FROM requests b LEFT JOIN z ON b.id_request=z.row_key_str ---------------------------------------------------------------------------------------------------------------------------------
Кстати, все источники данных для OLAP-кубов лучше оформлять в виде представлений (views) на стороне реляционной базы данных - хранилища или витрины, а в представлении источника данных (Data Source View - DSV) проекта Analysis Services применять именованные запросы. Преимущества использования представлений реляционной базы данных:
- * логика выборки данных сосредоточена в одном централизованном месте;
- * представления можно переписать, бесшовно (ну или почти) переориентировать на другие источники, не ломая DSV проекта OLAP, т.е. представления являются программным интерфейсом, "расшивкой" между разными системами;
- * на период разработки, тестирования BI-решения, прописав в представлениях условия WHERE, можно уменьшить объем извлекаемых данных (но все же реальных данных!) для OLAP-кубов, и тем самым сократить время полного процессинга кубов;
- * запросы в представлениях можно захинтовать, задействовать всю мощь RDBMS;
- * в некоторых случаях (например, когда базовым источником данных является база Oracle) возможно выполнить преобразования форматов полей таблиц, например, из Numeric в Numeric(8,0);
- * представления могут пригодиться и для решения других задач, других потребителей данных.
Еще нам потребуется bridge-таблица error_acc. Это статическая таблица, ее можно заполнить один раз и забыть. Выше был приведен Oracle запрос, генерирующий исходные данные для этой таблицы. Окончательную подготовку данных и расчет суммы кодов (формирование поля id_error_sum) удобнее выполнить в MS Excel версии 2007 и выше:
Готовый с данными Excel-файл error_acc прилагается к данной статье.
Свяжем наши таблицы между собой, причем свяжем дважды: один контур (error_list, error_acc) - для серьезных ошибок, а другой (error_list2, error_acc2) - для не критичных ошибок. Представление id_err_sum (id_err_sum2) - это уникальные значения поля id_err_sum таблицы error_acc (error_acc2).
Теперь настал момент пояснить, почему был выбран диапазон N=0...18. Справочник ошибок error_list связан с таблицей фактов request отношением M2M. Число 2 в степени 18 дает 262 143 записей, а 2 в степени 22 - аж 4 194 304 записей, т.е. при N>18 при незначительном увеличении количества записей в справочнике типов ошибок error_list получаем взрывной рост количества записей в factless-таблице error_acc. С точки зрения производительности не желательно иметь большое количество записей для промежуточной группы мер. К тому же следует подвергнуть сомнению те записи таблицы фактов, на которые приходится одновременно более 18 ошибок - какова полезность таких записей? И если подобных записей много, то какой бизнес-анализ смогут выполнять пользователи?
Тем не менее, про запас, для мониторинга ошибок в очень широких таблицах создадим два измерения - для серьезных и для не критичных ошибок:
На базе error_acc, error_acc2 создадим группы мер, а сами меры - количество записей скроем от пользователей:
Измерения и группы мер свяжем нижеуказанным способом, причем измерения куба error_id_sum, error_id_sum2 также спрячем:
Развернем проект на сервере Analysis Services, соберем куб и посмотрим, какая получается картина по качеству данных:
Данное решение можно применять для:
- * контроля соблюдения бизнес-правил, мониторинга ошибок данных;
- * отсечения, фильтрации по типам ошибок проблемных записей в OLAP-кубе и наглядного понимания при построении графиков, как подобные записи влияют на текущий контекст;
- * экспресс оценки качества данных в целом, определения % абсолютно безупречных записей.