SSAS - анализ и работа над ошибками

OLAP, SSAS  SSAS Категория:  OLAP, SSAS
Опубликовал:         04.08.2013               print

Двести сказанных слов не стоят и половины сделанного дела. Не ошибается тот, кто ничего не делает.


Мне доводилось значительное время работать в компании, где признавались следующие положения: право сотрудника на ошибку, отказ от системы наказаний, внедрение системы предложений, вовлечение людей фирмы в процесс совершенствования качества продукции и услуг, приближение к идее пожизненного найма. Умение признавать, предвидеть и устранять ошибки - показатель зрелости менеджмента.


Применительно к теме хранилищ данных и 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-кубе и наглядного понимания при построении графиков, как подобные записи влияют на текущий контекст;
  • * экспресс оценки качества данных в целом, определения % абсолютно безупречных записей.


Энергия идеи   dvbi.ru                    Последнее изменение: 2017-10-15 16:15:48Z         Возрастная аудитория: 14-70         Комментариев:  0
Прикрепленные файлы:
  Bridge-таблица error_acc               Размер: 1853.36 Кб            Скачали раз: 106

Теги:   Примеры
Пожалуйста, проголосуйте и ниже поставьте лайк:   rating
0 0 0


  Comments


Следующая статья:    SSAS - обновление прав доступа пользователей
Предыдущая статья:  SSAS - контекстные SQL запросы к реляционным источникам, часть 2