Всем привет! Меня зовут Сергей Коньков - я работаю архитектором в компании CloudReports. Сегодня я расскажу, как мы создали продукт, который помогает пользователям работать с данными и в какой-то мере соединяет два мира аналитики: Excel и облачные хранилища данных.

Задача

BigQuery и другие аналитические хранилища в сочетании с современными BI инструментами перевернули работу с данными за последние годы. Возможность обрабатывать терабайты информации за секунды, интерактивные дашборды в DataStudio и PowerBI, сделали работу очень комфортной.

Однако если посмотреть глубже, можно увидеть - выиграли от этих изменений в основном профессионалы, владеющие SQL и Python и бизнес пользователи на руководящих позициях, для которых разрабатываются дашборды.
А как быть с сотнями миллионов сотрудников, для которых главным инструментом анализа был и остается Microsoft Excel? Они в каком-то смысле, остались за бортом новых изменений. Это менеджеры по продажам, владельцы малого бизнеса, руководители небольших отделов. Освоить PowerBI у них нет времени. Все что им остается это экспортировать данные из отчетов в свой любимый Excel и продолжить работу там, но это не очень удобно, занимает время и есть ограничения по объему данных.

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

Вспоминаем OLAP

Да, сегодня Excel по-прежнему самый популярный инструмент для работы с информацией в мире. А Сводная таблица, это то что используют миллионы пользователей каждый день. А раньше было еще больше. Если вы работали с данными в крупной компании десять лет назад вы наверняка слышали про технологию OLAP кубов от Microsoft и других вендоров, которые создаются поверх реляционных SQL баз, и позволяют получать результаты обработки миллионов строк данных за секунды. Самым популярным способом работы с OLAP кубами была и есть сводная таблица Excel. К слову OLAP по прежнему очень распространен в корпоративном мире, это все так же часть Microsoft SQL Server, однако имеет ряд ограничений по объемам и скорости обработки и все больше уступает рынок облачным аналитическим хранилищам.

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

Kогда Microsoft выводил на рынок данную технологию был опубликован открытый протокол для работы с OLAP базами - XMLA (XML для аналитики). Именно этот протокол и использует Excel когда подключается к OLAP серверу. Все работает примерно так:

Решение

Идея проста - вместо OLAP сервера мы сделаем Python приложение , которое будет делать следующее:

  • принимать XMLA запросы от Excel

  • конвертировать логику XMLA запроса в SQL код

  • отправлять SQL запрос в BigQiery

  • полученный от BigQuery ответ конвертировать в XMLA и отправлять обратно в Excel

Данное приложение (App) можем опубликовать в облаке, так как Excel имеет возможность отправлять запросы XMLA запросы по протоколу HTTPS. Все будет работать примерно так:

Использование

После того как мы разработали и опубликовали приложение, администратору BigQuery для начала использования достаточно просто создать таблицу и определить для соответсnвующих полей типы агрегации (сумма, минимум, максимум и т.д.). Далее пользователь в Excel используя подключение к службам аналитики (OLAP) соединяется с нашим сервисом:

После этого мы получаем доступ к таблице BigQuery непосредственно из сводной таблицы. И можем легко "играть" с данными.

Кроме того, мы реализовали в данном сервисе слой кэширования данных для ускорения запросов и экономии затрат на BigQuery.

Что дальше

Сейчас мы активно тестируем сервис на своих клиентах и думаем над добавлением нового функционала.

Например, SQL запросы наряду с BigQuery поддерживают и другие облачные хранилища данных. Добавив один класс в наше приложение мы реализовали аналогичный механизм для ClickHouse. Скоро будет готова версия для Snowflake и Amazon Redshift.

Будем рады услышать вопросы и мнение коллег в комментариях.

Комментарии (8)


  1. kosiakk
    27.11.2022 13:52
    +1

    Классная идея! Хотя Pivot в Excel считается уже очень продвинутой технологией, всё равно база пользователей больше, чем у любого другого инструмента.

    На мой взгляд, такой адаптер будет полезнее даже не BigData, а для обычных SQL движков. Если в фирме уже есть ClickHouse, это скорее всего означает что там есть необходимость, и возможность (профессионалы, которые установили и заполняют).

    Но ведь есть несравнимо больше компаний, где данные прекрасно умещаются в Postgres! И там как раз есть потребность в понятном GUI.


    1. mongohtotech Автор
      27.11.2022 14:25
      -1

      Тут еще важна скорость. OLAP обеспечивает получение ответов за секунды. BigQuery аналогично.

      Поэтому работать с данными в сводной таблице удобно: переставляем поля - быстро получаем результат.

      Postrges думаю не всегда сможет дать аналогичную скорость.

      Но идея понятна, нужно подумать. Спасибо!


      1. kosiakk
        27.11.2022 16:52

        Я как раз недавно замерял - на localhost выдаёт 30 отдельных Select в одном потоке. Если запустить 32 потока по числу процессоров, то получается 400 отдельных запрос-ответов. Каждую миллисекунду. Т.е. 400'000 запросов в секунду на базе 0.3 ТБ. Если послать большие аналитические запросы, то будет ещё быстрее - один поток агрегирует 9 миллионов строк в секунду. Не ClickHouse, конечно, но для бизнес-данных этого достаточно с лихвой.

        Я бы купил такую обёртку вокруг PG, просто чтобы предоставить привычный GUI Экселя для бизнес-аналитиков. Кстати, возможно, MS Access может интегрироваться с внешними БД?


        1. aborouhin
          27.11.2022 18:20

          Кстати, возможно, MS Access может интегрироваться с внешними БД?

          Может, через ODBC. А для Postgres есть OBDC драйвер, так что Access в качестве фротнэнда для Postgres использовать вполне можно. Но это всё-таки другие задачи немного, Access больше ориентирован на то, чтобы один раз потратить время на создание набора формочек/отчётов, которые потом использовать. А Excel - чтобы на лету разные варианты пробовать.

          Но вообще Excel из Postgres данные же брать умеет и так, вроде... как минимум двумя способами, через тот же ODBC и через Npgsql.


  1. mentin
    28.11.2022 06:12
    +2

    Это получилось примерно как если MS Analysis Services подключить в ROLAP режиме (возможно HOLAP в зависимости от уровня кеширования) к BigQuery?


    1. mongohtotech Автор
      28.11.2022 07:29

      Да, именно так


      1. mentin
        28.11.2022 08:03
        +1

        А в чем преимущество, кроме конечно стоимости SSAS?


        1. mongohtotech Автор
          28.11.2022 08:52

          SSAS:

          • приобрести MS Windows Server

          • приобрести SQL Server

          • нужен специалист по OLAP

          • нужно разработать OLAP куб

          Наше решение:

          • создать таблицу в BigQuery