Что такое ad hoc запросы
Когда пора задуматься о внедерении BI-системы?
В этой статье хочу поделиться личными наблюдениями вот за каким процессом. Как компании проходят путь от пункта «Нам достаточно стандартных отчетов в корпоративной учетной системе » до «Подготовка отчетности требует много времени и ресурсов. Пора все автоматизировать!». Надеюсь, что ниже иложенное поможет кому-то избежать некоторых ошибок и правильно выбрать решение Business Intelligence (BI-платформу).
Стадия первая. Прелюдия.
У руководства компании возникает потребность в регулярной отчетности (продажи, план-факт, финансовое состояние и прочее). Отчетность готовят специалисты соответствующих бизнес-подразделений (финансовый блок, коммерческая служба, логистика и т.д.). В этих подразделениях люди вынуждены совмещать свои основные обязанности (ведение учета, сопровождение договоров и т.п.) и подготовку отчетности. Из инструментов у них типовые отчеты в учетной системе и Excel. Причем с использованием Excel у них обычно не все в порядке.
Стадия вторая. Возбуждение.
Затем у руководителей растут аппетиты (растет компания, растет количество управленцев, приходят руководители с новым взглядом на бизнес и т.д.), и они начинают запрашивать все больше разовых (ad-hoc) отчетов, чтобы взглянуть на бизнес под разными углами. С ростом компании таких отчетов все больше, часть из них переходит в разряд регулярных, и у специалистов от бизнеса возникают проблемы с подготовкой всего этого многообразия в срок. В поисках спасения они начинают требовать от ИТ взять часть работы на себя, а именно, просят различные выгрузки из базы учетной системы (УС) и все чаще обращаются с требованиями разработать в УС новые отчеты.
Стадия третья. Зачатие (Эмбрион).
После всех этих процессов в компании начинает образовываться направление, именуемое бизнесом «аналитики». Так, в компании может появиться SQL-разработчик (с этого я начал путь) и специалист/ты, владеющие Excel и прочими программами из пакета Office. Развитие на этой стадии может проходить по-разному. Я лично видел, что со временем количество аналитиков может стать довольно большим (каждое подразделение обзаводится 1-2 специалистами или же в компании образуется отдельное подразделение). Я, кстати, мог оказаться и в этой роли, но мне повезло, что в университете меня не учили Excel’ю и на первом собеседовании тетенька из отдела HR сказала «ай-ай-ай». Мои уверения её в том, что я быстро (за пару недель) освою сей продукт, скорее всего, породили в ней мысль: «Явно передо мной самоуверенный болван, ибо у нас тут другие тетеньки годами работают на компутере и все еще боятся этого зверя, а этот наглый шкет такое заявляет». Лично мне быстро наскучило создавать разные выгрузки, и я начал интересоваться, а что же есть подходящего на рынке. Но в 2006 году я еще даже не знал термина BI, поэтому поиски были недолгими. Остановился я в результате на технологии OLAP.
Стадия четвертая. Избавление ИТ от ad-hoc или рождение OLAP. Начало проекта BI.
Как мне кажется, OLAP — уже довольно распространенная вещь, и с высокой долей вероятности в компании появляются люди, работавшие с OLAP-кубами как пользователи или разработчики. Они-то и сеят мысль о том, что внедрение кубов станет избавлением от многих проблем и облегчит жизнь большого количества сотрудников. Или же этот человек имел опыт с некой BI-системой. Поскольку сейчас речь скорее не о самых крупных компаниях, то людей, предлагающих что-то из SAP Business Objects, IBM Cognos, Oracle BI перестанут слушать, когда увидят ценник. В крупных же компаниях уже давно что-нибудь да есть, как минимум Microsoft BI (SQL Analysis Services и Reporting Services). «Как минимум» здесь не пренебрежение, просто это довольно распространенные решения, так как поставляются в комплекте в сервером БД, что часто приводит к выбору именно этой платформы.
Поскольку мы все хотим, чтобы наш проект вырос хорошим, добрым, сильным и здоровым, важно не совершить ряд типичных ошибок.
Ошибка первая. Спонтанный выбор системы.
Ошибка вторая. DWH или его отсутствие.
Бывает, что перед внедрением собственно системы подготовки автоматизированной отчетности, никто не озаботился тем, что все-таки было бы неплохо иметь хранилище данных (DWH). В результате, это приводит к тому, что OLAP или отчеты обращаются к большому числу источников данных, различным витринам, которые сформировались на 3-ей стадии. Из этого рождается хаос. Развивать и поддерживать систему после этого становится крайне затруднительно, и может оказаться так, что все придется строить заново.
Выбору системы для DWH тоже нужно уделить отдельное время, но зачастую это та же СУБД, на которой работает учетная система (УС). Такой подход вполне оправдан, так как в компании уже есть специалисты по продукту и может получиться экономия на лицензиях. Конечно, это не относится к случаю, когда в УС копится очень много данных (большое количество транзакций и СУБД выбиралась именно для этого) и лучше поискать другую СУБД, более подходящую для задач аналитики (есть механизмы колоночного хранения, размещение таблиц в оперативной памяти и т.п.).
Еще возможен вариант интеграции учетной системы с неким BI инструментом (видел предложения 1С + QlikView, от некоторых интеграторов), но тут я не совсем в курсе как всё устроено. Буду рад, если кто-то напишет об этом в комментариях или в личку.
Ошибка третья. Игнорирование того факта, что проект уже давно надо было начать.
Часто компании застревают надолго во второй стадии и это приводит к тому, что появляется множество различных витрин данных, кучи Excel-файлов с макросами для обработки данных и, конечно же, никто это никак не систематизирует и не описывает. Начиная в итоге проект BI, вы потратите намного больше времени на систематизацию требований, и придется столкнуться с некоторым сопротивлением со стороны пользователей, которые уже давно привыкли к тому, что есть. Например, они не захотят самостоятельно работать с кубами, потому как Вася из ИТ всегда сам делал им нужные выгрузки, тем самым сильно их избаловав. Все должно быть вовремя!
В общем, будьте готовы к тому, что ответственность за выбор и дальнейшую судьбу проекта будете нести именно Вы — читатели этой статьи, которая, надеюсь, вам хоть как-то поможет.
Ad hoc
Из Википедии — свободной энциклопедии
Ad hoc — латинская фраза, означающая «специально для этого», «по особому случаю». Как правило, фраза обозначает способ решения специфической проблемы или задачи, который невозможно приспособить для решения других задач и который не вписывается в общую стратегию решений, составляет некоторое исключение. Например, закон ad hoc — это закон, принятый в связи с каким-то конкретным инцидентом или для решения какой-то особой задачи, который не вписывается в законодательную практику и не решает других схожих проблем; отдел ad hoc — это подразделение в организации, созданное для решения какой-то узкой задачи, не попадающей в сферу компетенции ни одной из постоянных структур. В некоторых случаях выражение ad hoc может иметь негативный подтекст, предполагая отсутствие стратегического планирования и реакционные непродуманные действия.
В менеджменте управление ad hoc — это ситуационное управление (в противовес, или как дополнение к стратегическому управлению).
В международном праве термин ad hoc также используется для обозначения формы международно-правового признания при необходимости установления разовых контактов между сторонами, которые категорически не желают признавать друг друга.
В науке и философии имеется понятие гипотезы ad hoc — гипотезы, выдвинутой для объяснения какого-то особого явления или результатов конкретного эксперимента, не объясняющая при этом другие явления или результаты других экспериментов. При этом ученые зачастую скептически относятся к научным теориям, которые опираются на гипотезы ad hoc. Специальные гипотезы часто характерны для псевдонаучных предметов, таких как гомеопатия.
В армии специальные подразделения создаются в непредсказуемых ситуациях, когда сотрудничество между различными подразделениями внезапно необходимо для быстрых действий или из остатков предыдущих подразделений, которые были захвачены или иным образом сокращены.
В компьютерной технике имеется понятие беспроводные ad-hoc-сети — это сети, не имеющие постоянной структуры, в которых клиентские устройства соединяются «на лету», образуя собой сеть.
Как настроить режим Ad Hoc беспроводной сети с помощью утилиты, встроенной в ОС Windows(Windows XP)?
Примечание: До начала настройки убедитесь в том, что служба Windows Zero Configuration ( WZC ) запущена. Если вы не знаете, запущена она или нет, нажмите здесь, чтобы проверить настройки.
1. Создайте профиль сети Ad Hoc на компьютере A
Шаг 2: В закладке Беспроводные сети нажмите кнопку Добавить.
Шаг 5: Нажмите правой кнопкой мыши по Беспроводное сетевое соединение и выберите Свойства.
Шаг 6: В закладке Общие нажмите два раза Протокол Интернета ( TCP / IP ).
3. Выполните поиск сети Ad Hoc на компьютере B
Шаг 9: Нажмите правой кнопкой по Беспроводное сетевое подключение, выберите Просмотр доступных беспроводных сетей.
Шаг 10: Найдите беспроводную сеть adhoctest (которая была установлена на компьютере A ) в окне поиска. Затем два раза нажмите по ней и нажмите Подключиться в любом случае.
Базовые настройки по построению сети Ad Hoc были завершены. Если мы снова открываем окно поиска сети, то мы видим, что сеть adhoctest подключена.
Я занимаюсь глубоким погружением в SQL Server Query Store, и я часто вижу ссылки на «ad-hoc» запросы. Тем не менее, я не видел, что Query Store определяет ad-hoc-запрос. Я видел места, где можно было бы предположить, что это запрос без параметров или запрос, выполненный всего один раз. Существует ли для этого формальное определение? Я не имею в виду в целом. Я имею в виду, что это относится к хранилищу запросов.
3 ответа
После небольшого поиска я не смог найти конкретный и удовлетворяющий источник документации Microsoft, чтобы ответить на этот вопрос. Существует много хороших сторонних описаний и определений adhoc /ad-hoc /ad hoc, но для специфики этого вопроса я думаю, что один из близких к источнику идеален.
Перемещение прошлых общих (но все же точных) определений, таких как это сообщение SO (спасибо SqlWorldWide), если мы посмотрим, что говорит документация по этой теме, оно согласуется с тем, что вы упомянули об определении, основанном на количестве исполнений, я думаю, что мы можем считать это фактом.
Таким образом, похоже, что параметр конфигурации сервера специальных запросов также использует определение одного исполнения как определение ad hoc. Если запрос продолжает выполняться и генерирует тот же план, он больше не будет рассматриваться как таковой.
В статье Best Practices for Query Store также соответствует этому,
Сравните количество различных значений query_hash с общим числом записей в sys.query_store_query. Если отношение близко к 1 ad-hoc рабочая нагрузка генерирует разные запросы.
Это, конечно, относится к запросам, которые еще не используются в качестве хранимых процедур, параметризованных и т. д., потому что они могут быть распознаны и обработаны соответственно с места в карьер.
Итак, на основе всего этого можно сказать, что запрос обрабатывается как ad hoc, если:
Для специальных запросов столбец object_id в DMX sys.query_store_query будет равен 0, как указано в sys.query_store_query :
object_id :
Идентификатор объекта базы данных, частью которого является запрос (хранимая процедура, триггер, CLR UDF /UDAgg и т. д.). 0, если запрос не выполняется как часть объекта базы данных (ad-hoc-запрос).
Вы можете определить специальные запросы на основе этого значения, даже если это явно не указано как «это определение специальных запросов «. 🙂
Термин ad-hoc используется для обращения к запросам, которые были выполнены только один раз. Это согласуется с определением, используемым для настройки базы данных «Оптимизация для специальных рабочих нагрузок».
Ссылка, которую вы ссылаетесь на удаление специальных запросов, включает это конкретное определение «Удалить ad-hoc-запросы: это удаляет запросы, которые были выполнены только один раз и старше 24 часов».
Студия готового дизайна GS studio
Login
Часто в повседневной работе продуктовой компании в отделе Business Intelligence (или его зародыше) могут встречаться ad hoc задачи по выгрузкам из БД. Если их много, то они могут занимать почти все рабочее время без перерывов на более инновационную и результативную работу. В результате страдает как бизнес, так и сотрудники компании.
В статье мы рассмотрим, как взять под контроль поток задач, используя идею CRM-системы, в которой есть функции по автоматизации как легких запросов (обновления задач по cron), так и достаточно тяжелых (обновление по запросу клиента) с возможностью управлять входящими параметрами.
Зачем это нужно
В отделе Business Intelligence OLX мы работаем с отделами монетизации, маркетинга, продукта, безопасности, поддержки пользователей в странах — Украина, Казахстан, Узбекистан. Иногда прилетают задачи и из других стран. Отдельно стоит выделить работу в B2B Unit, где аналитики нашего отдела создали инновационную CRM-систему для работы колл-центров разных стран. Но об этом чуть позже. А сейчас рассмотрим проблему простых, коротких задач, которые могут отнимать много времени, так называемые ad hoc задачи, которые аналитикам стоит обходить стороной или уметь их держать под контролем, чтобы в них целиком не погрязнуть.
Каждый день приходят разнообразные задачи на выгрузку данных, автоматизации отчетности, которые делятся на быстрые ad hoc задачи и трудоемкие, которые требуют больше времени для анализа.
Количество обращений может достигать десятки в день, и к каждой задаче необходим индивидуальный подход. Однако встречаются однотипные задачи, которые можно автоматизировать, дав возможность менеджерам не стоять в очереди для получения данных, а самим управлять операционным процессом.
Кроме того, автоматизация дала бы возможность прозрачно вести учет обращений от каждого менеджера, делать историю обращений и времени обработки запросов в БД. До этого под каждую задачу писались скрипты на Python, R, PHP. Все скрипты были в разных местах на сервере, что создавало определенные неудобства и многочисленные дубли кода. Стоит отметить, что в компании используются разные инструменты под разные типы задач. Я же сторонник использования BI CRM.
За первый месяц внедрения BI CRM обработала около 500 запросов менеджеров, не считая автоматических выгрузок по cron. Что позволило значительно снизить нагрузку на BI отдел, увеличить скорость получения данных для менеджеров, построить аналитику по частотности SQL-запросов. С точки зрения разработчика, это кажется вполне логичным и обоснованным решением — создать единую контролируемую точку входа и сохранять различные метрики по использованию. Однако к этой идее мы пришли не сразу. Итак, начнем с истории.
Каталог SQL-запросов
Вначале было создано нечто вроде каталога SQL-запросов. Принцип довольно прост. Есть таблица с SQL-скриптами, есть PHP-скрипт, который по cron выполняет каждый запрос SQL. У такого решения есть недостатки. Нет нормального фронтенд-интерфейса для работы, нет статистики по запросам, отсутствует интерактивность и т. п. Мы все еще продолжали часто пользоваться отдельными скриптами, которые запускались по cron.
API для работы с БД в Excel
Когда стало приходить много задач типа «выгрузи мне данные по такому-то параметру», появилась идея API для работы с БД в Excel. Что уже лучше, впервые менеджер сам мог использовать API, не обращаясь к аналитику. Можно контролировать обращения менеджеров, нагрузку на БД. API состоял из двух типов SQL-запросов и представлял собой изнутри, по сути, все тот же каталог SQL-запросов. Первый тип запроса взял символ «R» из CRUD и позволял прочитать все, что находилось в базе по какому-то ID. Второй тип — предопределенные запросы, составленные аналитиком. Например, аналитик создает функцию посчитать количество объявлений пользователя и называет ее «get_count_ads». После этого к этой функции можно обращаться по GET-запросу.
У такого подхода был существенный минус — отсутствовала возможность пакетной обработки данных, что серьезно нагружало базу данных. Например, менеджер не мог добавить в функции сразу десятки тысяч пользователей и приходилось делать много маленьких запросов. Таким образом, информацию можно было получить только по одному объявлению или пользователю. Также менеджер должен разбираться с API интерфейсом, что было очень неудобно, и не у всех версия Excel поддерживала функцию webservice.
Идея BI CRM
И вот пришла идея сделать это цивилизованно, через CRM. По сути, отдел аналитики является интеллектуальной поддержкой для других отделов. Менеджеры — наши клиенты, с которыми можно работать по аналогии с технической поддержкой, но на более высоком уровне.
CRM дала возможность вести полную аналитику обращений менеджеров, SQL-запросов, хранить все запросы в одном месте, делать анализ самих запросов, их истории, контролировать нагрузки на базы.
Инструменты создания: PHP, MySQL
Базы, с которыми работает система: Amazon Redshift (PostgreSQL), MS SQL, Amazon MySQL.
Архитектура интерфейса
Все задачи подразделяются на автоматически обновляемые по cron или в ручном режиме.
Если первые очень похожи на старый, добрый автообновляемый каталог запросов SQL, то вторые — это новое custom-решение. Здесь задача называется «Шаблон». Аналитик создает шаблон SQL-запроса и задает плейсхолдеры. Выглядит шаблон таким образом:
Здесь #1 — это плейсхолдер для подстановки набора user_id, #2 и #3 — интервал дат.
Существует несколько типов плейсхолдеров, которые может добавлять аналитик:
Примеры: Text — textarea для ввода списка чисел или списка слов. Input — небольшое текстовое поле, Category — предопределенный набор данных в виде списка, который хранится в системе.
В результате сохранения шаблона, CRM создаст «Представление» — View этого шаблона. Это интерфейс для менеджера, в который он будет вводить данные, в нашем случае — это список user_id и даты.
После ввода входящих данных менеджер получает файл с исходящими данными.
Что под капотом
Внутренняя часть основывается на самописной CRM (техническое название движка — crud crm), которая уже использовалась в OLX ранее и хорошо себя зарекомендовала. Система представляет собой конструктор CRM для быстрого развертывания (в течение часа) CRM разных видов, будь то для техподдержки пользователей или колл-центра. Именно на этой CRM обрабатываются вопросы и баг-репорты клиентов по OLX-доставке. Внутри это CRUD-система (PHP, Twig, MySQL), ядро которой состоит из соответственно добавления, чтения, обновления, удаления любых типов данных. Есть предустановленный шаблон, который автоматически генерирует визуальные элементы на основе последовательности, заданной в конфигурации. Есть возможность использовать свои custom-шаблоны, что мы и делали, так как менеджеры в основном хотят видеть только то, что им нужно.
Вдохновением послужил PHP CrudKit (к сожалению, сейчас он уже не поддерживается). Мы даже использовали его на некоторых своих проектах, но столкнулись впоследствии со сложностями кастомизации интерфейса, так как он был немного недоработанным. Однако его простота и скорость развертывания вдохновила создать свою CRUD CRM.
Пример обновления столбцов в ядре:
Пример инициализации CRUD CRM в index.php:
Таким образом можно создавать различные типы полей и управлять ими.
Внутренняя архитектура BI CRM
Обновление запросов организовано следующим образом:
По cron запускается scheduler, который ищет задачи для выполнения по условиям:
По каждой задаче вычисляется интервал между временем последнего запуска в минутах. Если он больше заданной частоты обновления в интерфейсе или равен ей, то выполняется метод startTask, который в свою очередь запускает процесс по GET-запросу.
Задачи запускаются по URL, чтобы не нарушать целостность всего процесса. Если какая-то из задач выведет ошибки, то на другие задачи это не повлияет.