Турбопривод для витрин данных

Брайан Ла Планте
Oracle Magazine/Russian Edition 7/2000

Улучшить производительность витрины данных можно простым и дешевым способом: использованием таблиц предварительно вычисленных итогов.

Когда компания George’s Groceries только начинала создавать витрину данных, менеджеры этой компании не имели быстрого доступа ко всем своим историческим данным о продажах. А ведь располагая всеми этими детальными (low-level) данными (кто купил что, где и в какой день), они могли бы быстро произвести полезные анализы. Но когда же витрина данных была принята в эксплуатацию, пришла очередь им удивиться. Они обнаружили, что всего несколько аналитических запросов смогли так загрузить их небольшой сервер, что опять нужно было долго ждать ответы, которые им были необходимы.

К счастью, специалисты компании George’s Groceries быстро поняли, что они должны сделать, чтобы ускорить обработку данных без дополнительных затрат на аппаратуру и ПО: использовать таблицы предварительно вычисленных итогов (pre-aggregated summary tables). Итожа данные во время загрузки на более высоком уровне суммирования – например, продажи за месяц а не за день – эти таблицы резко сократили размер наборов данных, необходимых для ответов на многие запросы. Результат для менеджеров George’s Groceries – значительное улучшение производительности. И единственное, что для этого потребовалось, - это разработка скриптов сопровождения для создания и поддержки этих новых таблиц.

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

Обзор таблиц итогов

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

• Игнорированием некоторых измерений
  
• Суммированием данных измерения на более высоком уровне,согласно его иерархии (таком как месяц вместо дня)
  

Например, витрина данных компании George’s Groceries содержит данные продаж в разрезе по магазину, дню, клиенту и товару плюс соответствующие данные по клиенту и типу товара. Кроме того, некоторые потенциально полезные таблицы итогов могут включать следующие данные:

• Данные продаж по месяцам и товарам
  
• Данные продаж по месяцам и клиентам
  
• Данные продаж по магазинам, месяцам и товарам
  
• Данные продаж по клиентам, месяцам и товарам
  
• Данные продаж по типам клиентов, типам товаров и магазинам
  

Отметим, что таблица итогов – это не таблица, в которой некоторые из этих данных исключены. Например, таблица с данными продаж, которая содержит данные только Восточного района, не является настоящей таблицей итогов; наоборот, это секционированная (partitioned) таблица фактов.

С точки зрения АБД таблица предварительно вычисленных итогов – это таблица, созданная из таблицы фактов посредством применения операции "group by" (отсюда определение "предварительно вычисленная”). Например, следующие SQL- предложения должны создать итоги в разрезе магазин-месяц-товар для компании George’s Groceries из таблицы фактов:

Create table sum_store_month_prod as
Select store_id, month_id, product_id,
sum(qty) as qty, sum(ext_price) as ext_price
From sales s, lookup_day d
Where s.invoice_day = d.invoice_day
Group by store_id, month_id, product_id;

Создав однажды таблицу итогов, вы можете легко использовать ее впоследствии, так как современные популярные ROLAP (relational online analytical processing, оперативный анализ данных реляционных БД) средства поддерживают таблицы итогов “прозрачно”. Пользователь выбирает нужные ему атрибуты для отчета, и ROLAP-средство автоматически определяет, может ли какая-либо доступная таблица предварительно вычисленных итогов удовлетворить этот запрос. Oracle Discoverer, DSS Suite компании MicroStrategy и Business Objects (от одноименной компании) – все эти средства могут “прозрачно”, самостоятельно выбирать нужные таблицы итогов.

Как таблицы итогов улучшают производительность

Степень улучшения производительности, которое вы получаете, применяя таблицу предварительно вычисленных итогов, зависит, естественно, от того, в какой степени эта таблица итогов сокращает размер набора данных, который нужен для удовлетворения пользовательских запросов. Например, рассмотрим разницу между таблицей с данными продаж корпорации Oracle по клиентам, дням и товарам и другой таблицей с данными продаж по клиентам, месяцам и товарам. Единственное сокращение при переходе от таблицы фактов к таблице итогов в этом случае возможно, только если какой-либо клиент заказал один и тот же товар дважды в течение одного и того же месяца. Поскольку только немногие клиенты, скорей всего, так поступят, размер таблицы итогов может составить 98% размера таблицы фактов. С другой стороны, если измерение "клиент" будет исключено из процесса суммирования, то результатом будет таблица продаж по месяцам и товарам и размер этой таблицы может быть равен 1/100 исходной таблицы фактов. Запрос от отдела маркетинга товаров для получения данных о продажах Discoverer за февраль будет выполняться примерно в 100 раз быстрее на таблице итогов, чем на таблице фактов.

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

Определение таблиц итогов, которые необходимы

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

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

• Использование для получения нескольких отчетов
  
• Сходство часто используемых запросов
  
• Сходство запросов, необходимых для важных лиц,принимающих критически важные решения
  

Из первых трех позиций следует, что время, потраченное на создание и сопровождение таблиц итогов, должно быть меньше, чем экономия времени при использовании этих таблиц для получения ответов на запросы пользователей. Четвертая характеристика отражает необходимость рассмотрения того, как и кем будет использована полученная информация. В некоторых случаях, высокопоставленные лица, принимающие решения, требуют, чтобы витрина или хранилище данных доставляли некоторую информацию по запросу очень быстро, и неважно, какой расход ресурсов вызовет процесс загрузки. В случае EIS (Executive Information Service, информационная система руководителя) предварительно вычисленные итоги должны использоваться системой EIS для выполнения каждого отчета, так как пользователи EIS не будут ждать и нескольких минут для получения отчета.

Стратегии сопровождения таблиц итогов

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

Ваш метод перевычисления должен быть:

• Быстрым
  
• Надежным (bullet-proof), это означает таблицы итогов и фактов остаются синхронизированными даже в случае сбоя системы или задания
  
• Обладать возможностью повторного старта (то есть процесс загрузки может быть повторно запущен и возобновит свою работу корректно, с нужной точки, если произошел сбой во время генерации таблицы)
  

При выборе метода перевычисления имеются две основных возможности: полное или инкрементальное, нарастающим итогом, перевычисление. Если вы выбираете инкрементальное перевычисление, тогда надо также решить, будете ли вы проводить периодические перестроения таблиц или использовать метод "скользящего окна" (a rolling rebuild window), чтобы предотвратить таблицу итогов от “разбухания”.

Метод полного перевычисления:

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

Drop table sum_cust_month_old;
Create table sum_cust_month_new
As select customer_id, invoice_month,
sum(qty) as qty, sum(ext_price) as ext_price
From sales s, lookup_day d
Where s.invoice_day = d.invoice_day
Group by customer_id, invoice_month;

/* create appropriate indexes here */

Rename sum_cust_month to sum_cust_month_old;

Rename sum_cust_month_new to sum_cust_month;

Метод инкрементального перевычисления:

Но для большого объема данных перестроение таблицы итогов от нуля каждый раз уже не будет приемлемым решением. В этом случае имеет смысл рассмотреть добавление каждый день только записей текущего дня, как показано в следующем примере (который предполагает, что сегодняшние данные хранятся в таблице "daily_stage"):

Insert into sum_cust_month (
Customer_id, invoice_month, qty, ext_price
)
select customer_id, invoice_month,
sum(qty) as qty, sum(ext_price) as ext_price
From daily_stage s, lookup_day d
Where s.invoice_day = d.invoice_day
Group by customer_id, invoice_month;

Commit;

Этот метод быстр, так как вычисляется только сегодняшняя порция данных таблицы итогов. Однако, он не сжимает данные так, как это возможно; даже если суммирование производится за месяц, а не за день, новая строка вставляется для каждого клиента за каждый день этого месяца когда этот клиент что-то купил. В худшем случае эта таблица итогов станет раз в 30 больше, чем до такого перевычисления. Вряд ли в большинстве случаев один и тот же клиент будет выписывать чеки каждый день месяца. Тем не менее, чтобы удерживать размер таблицы итогов в разумных пределах, вы должны либо полностью периодически перестраивать таблицу (используя метод описанный ранее в секции "Метод полного перевычисления” (Complete Recalculation) либо использовать метод "скользящего окна" (rolling rebuild window).

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

Delete from sum_cust_month
Where invoice_month = $current_month;

Insert into sum_cust_month (
Customer_id, invoice_month, qty, ext_price
)
select customer_id, invoice_month,
sum(qty) as qty, sum(ext_price) as ext_price
From sales s, lookup_day d
Where s.invoice_day = d.invoice_day
And invoice_month = $current_month;

Commit;

Хотя метод "скользящего окна" немного сложнее чем подход периодического перестроения, он решает проблему генерации лишних записей и не требует много компьютерного времени (поскольку перевычисляются данные только за один месяц).

Гарантии безопасности

Помимо выбора наиболее оптимального метода перевычисления данных своей организации, вы, наверное, захотите внедрить в процесс создания таблиц итогов средства защиты (key safeguards). В частности, вы захотите защититься от проблем восстановления и ошибок суммирования.

Как избежать проблем с восстановлением

Чтобы быть уверенным, что вы можете легко восстановить свою таблицу итогов после сбоя во время ее построения, используйте единственную операцию commit для всего процесса, как мы это делали в наших примерах. Сегменты отката (rollback) должны быть сконфигурированы так, чтобы “покрывать” процесс загрузки, такой подход предоставляет легкий путь к восстановлению, так как с единственной операцией commit результат процесса загрузки будет зафиксирован полностью или совсем не будет зафиксирован. Этот подход более предпочтителен, чем применение скрипта с периодической выдачей commit, так как последний подход требует излишнего размышления, чтобы определить как запустить процесс восстановления после выполнения частичного суммирования.

Как избежать проблем с суммированием

Не забудьте, что начав использовать таблицы итогов в витрине данных, вы должны будете использовать новые скрипты для того, чтобы быть уверенными в качестве данных. Вы должны убедиться, что таблица итогов содержит правильно просуммированные данные и что не было ошибок во время вычисления. Хорошей проверкой для некоторого показателя может служить суммирование его значений в базовой таблице и сравнение результата с аналогичной суммой, полученной в результате суммирования его значений в таблице итогов. В нашем примере, мы могли бы вычислить sum(qty) и sum(ext_price) для базовой таблицы продаж за все время, затем вычислить те же суммы для каждой таблицы итогов за все время. Если эти результаты равны, значит все в порядке.

Агрегирование лучше ухудшения (Aggregation Beats Aggravation)

Использование таблиц предварительно вычисленных итогов часто приводит к сокращению времени выполнения отчетов – от многих часов к нескольким минутам. В одной компании высшее руководство захотело определить 100 наиболее крупных клиентов компании за 1998 год. Чтобы это сделать, нужно суммировать данные по продажам каждому клиенту за весь 1998 год, отсортировать всех клиентов по сумме продаж каждому и затем отобрать первых 100 — а это означает обработку каждой записи о продажах за весь год. Когда эта компания попробовала получить этот отчет в рамках своей ERP-системы, попытка оказалась неудачной – процесс получения отчета не удалось довести до конца. Попытка получить этот же отчет на витрине данных без использования таблиц итогов заняла более трех часов и тоже завершилась неудачно. С использованием таблицы итогов отчет был получен менее, чем за 20 минут. Это улучшение в соотношении более чем 10 к 1 не является исключением при использовании предварительных вычислений.

Если средства, которые вы используете для работы с витриной данных, работают с таблицами итогов “прозрачно”, как это имеет место в компании George, ваше решение использовать таблицу итогов можно легко реализовать. С советами, изложенными в этой статье, и богатым набором средств отбора итоговых данных в популярных пакетах, используемых на стороне клиента для работы с витринами данных, вы имеете все, что нужно для легкого, насколько это возможно, внедрения таблиц итогов. Так что, впрягайтесь (go ahead) и потратьте немного времени – вы увидите, как возрастет производительность вашей витрины данных.

Брайан ЛаПланте (bryan.laplante@pragmatek.com), старший консультант PRAGMATEK Consulting Group Ltd., в Миннеаполисе, штат Миннесота, где он специализируется в разработке витрин и хранилищ данных. Он участвовал в разработке тестов для СППР TPC-D и разрабатывал, настраивал и внедрял витрины и хранилища данных в течение более пяти лет.