Приветствую.

Создавая, или даже поддерживая, существующее хранилище данных, неизбежно возникает такой этап, когда множественность желаний пользователей встречается с неизбежностью физических ограничений той СУБД, которая используется для хранилища. В действительности, никто не может иметь бесконечного места на диске, процессорной мощности или сколь угодно долгого времени на обновление данных.

В этот момент у руководства могут возникнуть вопросы, если они не возникли ранее, что именно занимает так много места в БД, почему загрузка до сих пор не закончилась и прочее подобное.

Чтобы знать, что отвечать, необходимо провести учет. Создание ХД — процесс длительный, люди, разрабатывавшие архитектуру могут быть уже далеко, я не говорю уже о том, что бизнес требования меняются, иногда, так же быстро, как выходят новые версии браузера Firefox.

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

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

После того, как этот набор был получен, я решил погуглить эту тему, и нашел статью Билла Инмона с перечнем метрик, которые неплохо было бы иметь посчитанными в большинстве ХД.

Начало перевода

Все имеет метрики. На дороге есть ограничение скорости, люди имеют вес, дни — температуру, в машинах присутствует тахометр.

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

Учитывая вышесказанное, список метрик для системы хранилища данных мог бы быть следующим:

Размер

В таблицах ХД.
В индексах ХД.
Объем области преобразования (объем временных таблиц и/или спула).

ETL

Количество ETL программ.
Частота запуска.
Количество данных, обрабатываемых ETL.
Место выполнения ETL программ.

Датамарты

Количество датамартов в ХД.
Объем данных в датамартах.
Частота пополнения данными.

Детальные данные ХД

Скорость доступа к данным (байтов в секунду).
Размер физической записи.

Структуры ХД

Количество таблиц.
Количество строк в каждой таблице.
Средний размер строки для каждой таблицы.

Экспорт данных из ХД

Количество строк на таблицу, покидающих ХД.
Частота экспорта на таблицу.
Критерии экспорта.

Запросы к ХД

Количество обрабатываемых запросов в день.
Средний объем данных по каждому запросу.

История

Период времени, в течение которого ХД хранит историю.

Другое

Использование дата майнинга.
Адаптивные датамарты.
Приложения поддержки принятия решений (DSS).
ERP приложения.
ODS.
Использование ленточных или другого типа долговременных хранилищ информации
Архивное хранилище.

Вот такими могли бы быть метрики для того, чтобы промерить ваше ХД.

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

Или вы можете разделить некоторые таблицы по принадлежности к отделам компании. Часто имеет смысл группировать данные вместе, даже если в физических таблицах БД такой группировки не предусмотрено. Конечно, вы также можете показывать, какие СУБД используются для каких таблиц, например:

Таблица ABC — Teradata
Таблица DEF — IBM UDB
И т.п.

Итак, кому же могут пригодиться метрики ХД?

Админам БД.
Архитекторам БД.
Разработчикам БД.
Пользователям ХД.
Привлеченным администраторам.
Системным программистам
И многим другим...

Конец перевода

Я выбрал 4 группы метрик, посчитав, что такой набор будет хорош для определения необходимости сбора метрик конкретно в нашем проекте многовладельческого Хранилища Данных (Multitenant DWH). Вот эти группы:

Метрика по количеству строк в таблицах ХД.
Метрика по занимаемому пространству (количество байтов), для каждой схемы. Данные в хранилище расположены в нескольких схемах СУБД Oracle.
Метрика для процесса загрузки данных (ETL).
Метрика по самой большой таблице фактов в ХД.

Метрики из каждой группы собирались на определенную дату и для каждого отдельного владельца с помощью PL/SQL процедур, запускаемых после каждого процесса загрузки.

Рассмотрим подробнее каждую группу.

Метрики количества строк

В данной группе метрик собирается информация о том, сколько строк по каждому владельцу находилось в схемах ХД на каждый момент времени, когда выполнялся расчет этих метрик.

Используя эти показатели также можно определить соотношение между разными слоями ХД, сколько таблиц составляют детальные данные, датамарты, область стейджа. Когда появилась новая таблица, или сколько, в среднем, справочников на одну таблицу фактов.

Также становится возможным количественный анализ для разных владельцев по содержимому каждой конкретной таблицы.

Расчет производился при помощи стандартного подхода — генерации и запуска SQL кода в цикла выборки всех таблиц из заданного перечня схем.

Метрики объема

Для метрик объема, занимаемого таблицами ХД, я решил высчитывать исключительно занимаемое данными место в схемах, рапортуемое СУБД:

SELECT owner, to_date(v_cur_date, 'RRRR-MM-DD'), sum(bytes) from dba_segments 
      where owner in ('список схем через запятую')
      group by owner

Кроме этой метрики, хочу добавить еще и расчет места, которое занимают данные на диске. Сопоставив оба показателя, можно получить, например, соотношение эфективности ETL с точки зрения использования дискового пространства.

Метрики ETL

ETL инструмент, который используется для загрузки — Oracle Warehouse Builder. Скрипт, показанный ниже, собирает статистику выполнения для заданного процесса загрузки, агрегируя количество обработанных строк из подчиненных маппингов.

select ae.top_level_execution_audit_id, ae.execution_name, ae.created_on, ae.elapse_time, dt.Selected, dt.Inserted, dt.Updated, dt.Deleted
from owbsys.All_Rt_Audit_Executions ae,
 (
  select sum(coalesce(au.number_records_selected, 0)) as Selected, sum(coalesce(au.number_records_inserted, 0)) as Inserted, sum(coalesce(au.number_records_updated, 0)) as Updated, sum(coalesce(au.number_records_deleted, 0)) as Deleted
from OWBSYS.ALL_RT_AUDIT_MAP_RUNS au where au.execution_audit_id in (select execution_audit_id from owbsys.All_Rt_Audit_Executions ae where ae.top_level_execution_audit_id = <ИД последнего запуска>)
) dt
where ae.top_level_execution_audit_id = <ИД последнего запуска>

При желании отслеживать производительность каждого отдельного маппинга можно набор собираемых данных расширить.

Метрики самой большой таблицы

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

select p_Customer_Num, t.month_start_date, count(distinct f.Person_Id) as uniq, count(f.Person_Id) as t_cnt  
from dm.time_dimension t left outer join dm.trans_fact f on f.time_id = t.id and f.customer = p_Customer_Num
where month_start_date between p_Start_Date and p_End_Date
group by month_start_date;

позволяет собрать данные о количестве строк по каждому владельцу данных за каждый месяц плюс количество уникальных сотрудников по каждому месяцу.

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

Средняя стоимость места (в ГБ) добавления нового владельца на каждую 1000 сотрудников.
Средняя стоимость времени (в часах) добавления нового владельца на каждую 1000 сотрудников.
Среднее стоимость для СУБД по обработке строк (в миллионах строк) добавления нового владельца на каждую 1000 сотрудников.

Так же можно создать кучу красивых графиков, показывающих, среднее время ежедневной загрузки, зависимость этого времени от количества обработанных владельцев и т.п. Сравнивать между собой данные метрики после изменений в ETL, улучшений или ухудшений в СУБД и прочее подобное, что поможет более объективно оценивать результаты улучшений в процессах построения ХД и ответить на вопросы, с которых я начинал эту статью.

Метрики Хранилища Данных