Хитрость №3. Проектирование DWH и кубов на MS SQL/SSAS: оптимизация больших кубов

http://www.sql.ru/blogs/dwh/1232

Автор: Критик

Часто через 3-4-5 лет после начала работы ОЛАП-отчетности, система начинает работать несколько медленнее, что раньше. Вобщем-то понятно почему - объемы данных растут, а оборудование как правило остается тем же самым.

Некоторые метода борьбы за скорость:

·  Правильно спроектированная система, причем как на уровне хранилища, так и на уровне кубов ;)

·  Расчитываем все, что возможно, на уровне хранилища, mdx используем для легких расчетов ;))

·  Правильные и актуальные агрегаты, построенные на основе пользовательской статистики.
А почему бы не спроектировать их один раз и на всё время существования проекта? К сожалению, так не получится. В компанию приходят новые люди, уходят старые сотрудники, и каждый из них обычно использует какие-то свои отчеты. Если не актуализировать дизайн агрегатов, то можно получить ситуацию, когда они используются очень мало, т.к. нужных - просто нет. Таким образом, агрегаты желательно периодически перепроектировать.

·  Обязательно указываем свойство slice в секциях куба. Как правило, это позволит движку SSAS не сканировать все секции при получении запроса от пользователя, а прочитать только нужные.

·  Для очень старых данных весьма желательно держать в кубе не детальные значения, а "проекции".
Например, пусть в хранилище имеются данные о продажах с 2000 года по настоящий момент с уровнем гранулярности -час. Очевидно, что настолько подробные данные за (условно) 2000-2010 года пользователям не нужны в 99% случаях, а нужны лишь за 2011 и 2012 годы. Поэтому старые данные при загрузке в куб вполне можно отнести на 00 часов последнего дня месяца.
То есть даты при загрузке в куб старых секций на уровне запроса преобразуются примерно так:
2000-03-23 15:00:00.000 -> 2000-03-31
2000-03-25 12:00:00.000 -> 2000-03-31
2000-03-28 11:00:00.000 -> 2000-03-31
2000-03-29 23:00:00.000 -> 2000-03-31
Это позволит в разы уменьшить размер куба, а в некоторых случаях и в десятки раз.
Однако не забываем, что в самом хранилище следует иметь максимально детальные данные;

- Начинаем процессы обновления в хранилища сразу после окончания рабочего дня.
Обычно расчеты ведутся ночью, например стартуют в 01:00 или 02:00, чтобы быть уверенным, что все данные за прошлый день в исходную OLTP-систему введены. Но часто в больших системах 8-9 ночных часов на расчеты не хватает. Конечно, можно заменить оборудование, но в лучшем случае это займет пару-тройку месяцев ожидания. Что можно сделать в таком случае? Некоторые обязательные операции можно перенести на вечерний период (условно с 19:00 до 24:00). Например, пусть в хранилище ежедневно перезагружаются данные за последние 45 дней (чтобы отразить изменения задним числом). Причем данные за эти 45 дней нужно удалить перед загрузкой, а удаление не такая быстрая операция. Так почему бы ее не перенести на вечерний период?
Это позволит существенно ускорить ночные расчеты. В моей практике (в особо запущенных системах) одно только деление расчетов на два этапа в несколько раз ускоряло ночное заполнение хранилища. В результате пользователи получали готовые данные до начала рабочего дня, а не после обеда.