Excel висит, куб летит: гонка отчётов на 400 тысячах строк

Звонок пришёл во вторник в половину одиннадцатого утра. Финдиректор клиента - торговая компания, около двухсот сотрудников - сообщил, что «Excel завис опять» и что ему нужен сводный отчёт по продажам через пятнадцать минут: совещание с генеральным. Привычная история: таблица с четырьмястами тысячами строк, группировка по четырём измерениям, Excel 2003 думает минуты три, потом зависает или выдаёт ошибку памяти. Иногда проходит, иногда нет - зависит от фазы луны.

У клиента к тому моменту уже стоял SQL Server 2005 - переходили в конце прошлого года, в том числе ради BI-инструментов. Analysis Services был установлен, но никем не тронут: просто руки не доходили. Мы решили воспользоваться ситуацией и сделать нормальный замер - не «ощущение», а цифры по трём сценариям.

Три сценария

Первый - прямой SQL. Аналитик открывает Management Studio или Excel с подключением к базе через ODBC, пишет агрегирующий запрос с GROUP BY по нужным измерениям. База - OLTP, индексы под транзакции, а не под аналитику. На 400 тыс. строк с четырьмя уровнями группировки запрос отрабатывает, но не мгновенно.

Второй - SSAS-куб. Куб собрали за два дня: источник - та же транзакционная база, обработка раз в ночь. Измерения: менеджер, товарная группа, регион, период. Сводная в Excel подключается через OLE DB for OLAP. Пользователь крутит срезы мышкой - никакого SQL не пишет.

Третий - Excel Services через SharePoint. Финансисты хотели отчёт прямо в браузере - без установки Excel у тех, кто смотрит, а не строит. Excel Services - функция готовящегося Office SharePoint Server 2007, тогда ещё в публичной бете Office 12, - публикует сводную таблицу как веб-страницу. Подключение - к тому же кубу.

Что намерили

Замеры делали три раза подряд, брали среднее. Сценарий один и тот же: отчёт продаж за год по регионам с детализацией до товарной группы.

Прямой SQL - около семи минут на холодном кеше, две с небольшим на горячем. При этом любой новый срез - новый запрос, новое ожидание. Для OLTP-базы нагрузка ощутимая: во время отчёта оперативная работа чуть притормаживала.

SSAS-куб - первый срез за 40-50 секунд (прогрев кеша Analysis Services), следующие срезы - от пяти до пятнадцати секунд. Итого отчёт со всеми группировками укладывается примерно в минуту. Восьмикратный выигрыш к прямому SQL на холодном кеше - и это без особой оптимизации куба. Плюс нагрузки на OLTP-базу нет вообще: куб читает из собственного хранилища.

Excel Services - задержка выше, чем при локальном Excel с кубом: браузерный рендеринг, лишний слой через SharePoint. Но для тех, кто просто смотрит готовый отчёт и не крутит срезы, вполне приемлемо. Главный плюс - не надо ставить Excel и настраивать подключение на каждой машине.

Что оказалось не очевидным

Excel сам по себе с OLAP-подключением работает заметно быстрее, чем с плоской выгрузкой - это многие не знают. Проблема «Excel виснет» в нашем случае была не в Excel как таком, а в том, что данные грузились в таблицу целиком, а потом Excel пытался их агрегировать локально. Сводная через OLE DB for OLAP так не делает: она отправляет MDX-запрос на сервер и получает уже агрегированный результат. Разница принципиальная.

Второе наблюдение: ночная обработка куба - узкое место при срочных запросах. Финдиректор хотел данные «на сейчас», а куб обновляется раз в ночь. Для большинства управленческих отчётов это нормально, но надо честно объяснять клиенту: это вчерашние данные к утру, не онлайн. В нашем конкретном случае устроило - большинство решений принимается по итогам дня, а не минуты.

Третье: SP1 для SQL Server 2005 ещё не вышел - ждём в апреле. По предварительным материалам Microsoft, там будут правки в Analysis Services по стабильности и часть улучшений в интеграции с Excel. Посмотрим, что реально изменится - пока работаем с тем, что есть, и результат уже вполне рабочий.

Итог по делу

Финдиректор в тот вторник отчёт получил - правда, сделали прямой SQL-запрос с нужными индексами и ждали две минуты на горячем кеше. Куб начали поднимать уже после совещания. Сейчас он работает, следующий квартальный отчёт пройдёт через него.

Если ваша аналитика живёт в Excel с плоскими выгрузками и таблицы перевалили за сотню тысяч строк - смотреть в сторону SSAS имеет смысл. Порог входа по настройке выше, чем кажется, зато потом пользователи крутят срезы сами и не звонят с просьбой «пересобрать таблицу». Подробнее о том, как мы строим такие решения, - на странице DWH и бизнес-аналитика.