Система управленческого учёта – служит для сбора, систематизации, атрибуции, хранения и анализа данных с целью использования при принятии управленческих решений. Проще говоря, она нужна прежде всего, чтобы в любую секунду руководитель мог открыть Power BI/Excel и увидеть, что происходит в компании прямо сейчас: по деньгам, по людям, по KPI, по показателям качества и так далее. Описанная ниже система является работающей моделью того, что мы хотели бы видеть «в идеале», причём в рамках только одного департамента Digital-рекламы. Решение для всего агентства уже создаётся профессиональными разработчиками на гораздо более серьёзной платформе о которой возможно будет написано в дальнейшем.
Возможно для кого-то написанное ниже будет очевидным и банальным. Однако, в нашей компании это было практически инновацией, которая помогла решить задачи, казавшиеся ранее не решаемыми без массивных финансовых вливаний. Также, сразу хочу представить себя не как профессионального разработчика, которым не являюсь. Основная моя обязанность – управление проектами, координация работы команды. Есть понимание бизнес-процесса в digital-рекламе, поскольку ранее сам трудился в байинге. За счёт этого было проще так-как я примерно знал, что именно должно получиться на выходе. Вместе с командой, в которую входили байеры и специалисты по контролю закупок, мы дорабатывали образ результата. Реализацию я осуществлял в основном самостоятельно, при активной поддержке директора ИТ и некоторых других сотрудников. Недостаток знаний технических нюансов программирования восполнялся по месту, чтением форумов/блогов/просмотром видео-гайдов. В нашем случае этого вполне хватило для создания работающего решения без дополнительных капиталовложений. Да, ещё было несколько активных проектов помимо этого. То есть работа над данным проектом занимала примерно от 10% до 30% моего рабочего времени.
Исторически так сложилось, что у нас в компании не было единой системы управленческого учёта. Нет, были конечно всякие 1С и Террасофт, решающие какие-то отдельные задачи бухучёта, финансового учёта, документооборота. Однако, каждый раз, когда руководителю срочно требовался отчёт о деятельности нашего подразделения, приходилось в почте кидать клич на сбор данных по куче аккаунтов рекламных платформ. Всё это сводилось в некую большую эксельку, которая выверялась руководителем вручную и пересылалась дальше. Как вы понимаете, каждый раз такой «пожар» отнимал массу времени и сил, а точность цифр была, скажем честно, не самой высокой.
Именно тогда первый раз возникла идея сделать некую базу. Чтобы выгрузка отчёта о текущих оборотах в разрезе каждой площадки (формата, устройства и так далее) занимала бы секунды и не требовала вовлечения всей команды. Итак, начали с «чистого» MS Access. Сделали кучу таблиц, некоторые запросы, обобщающие данные и выводящие их в удобоваримом виде.
Заполнение первой версии базы Access делалось следующим образом:
Использовались табличные формы ввода данных, которые создавались одним кликом на основе самих таблиц. Менеджеры делали эксельки в определённом формате, а я потом методом Copy-Paste переносил эту простыню в табличную форму, состоящую из обычных текстовых полей и комбо-боксов. При специальной вставке, комбо-боксы сразу подхватывали значение, если оно имелось в подвязанной к комбо-боксу таблице. Отчёты, в форме обычных SQL запросов, формировались за доли секунд. Результирующие данные копировались назад в Excel методом Copy-Paste. Всех такая схема вполне устраивала примерно года полтора.
Но отдел вырос, как и поток входящих задач. Если старая база Access начиналась с примерно 50 проектов в месяц, то через 2 года это было уже более 100 проектов или 1200 в год. Файловая база начала тормозить. Кроме того, усложнились требования к отчётам. Было необходимо формировать их чаще, чем раньше и с гораздо большим количеством данных. В какой-то момент руководство поставило задачу сделать нормальную систему учёта, которую могли бы использовать другие отделы в нашем департаменте, а директор сам бы мог получить все необходимые отчёты.
Итак, у нас появилась задача сделать нечто гораздо более гибкое, чем файловая база Access с кучей табличных форм. В то же время решение должно было быть достаточно простым в реализации, поскольку лишнего бюджета в компании не было. Дедлайн был разумеется «ещё вчера». Задачу нужно было реализовать быстро и наверняка.
Ничего кроме, MS Access и VBA сразу на ум не пришло. Но очень хотелось уйти от громоздких файлов с базой, которые нужно постоянно бэкапить, восстанавливать, сжимать. Файловые базы довольно часто падают и просто глючат. Скорость формирования сложных отчётов на больших выборках (более 5000 записей о 40 столбцах, скажем) уже совсем не устраивала: формирование таких выгрузок занимает иногда по 5-7 минут. Хотелось гибкости интерфейса Access и скорости MSSQL. Кроме того, были специфические требования связанные с раздельным доступом к данным и при этом нежелательностью паролей тк. они сразу стали бы всем известны. База предполагалась только для внутреннего использования на работе, без возможности удалённого доступа.
Поискав по форумам всё же приняли решение делать на базе Access, но с добавлением функциональности автологина через ActiveDirectory, раздельным доступом к формам и данным, хранением данных в базе MSSQL. Благо у нас под другой проект сервер MSSQL уже был поднят и админы сразу согласились предоставить необходимые ресурсы. Архитектурно приложение выглядело так, что Access в нём выполнял функции интерфейса и разделения доступа. Все манипуляции с данными проводились в MSSQL. Таблицы подвязывались методом no-DSN написанным на VBA же скриптом.
Распространение новых версий осуществлялось при помощи следующей схемы:
На файловом сервере Windows был создан каталог в который выгружались бинарные версии сборок базы. О безопасности написано чуть ниже. Итак, при помощи инструментов встроенных в VBA база сама себя компилировала и выкладывала в каталог. При этом старая версия бинарника сохранялась в виде переименованного файла, на случай необходимости срочного отката версии. Можно было реализовать это всё при помощи Git, но решили пойти проще. Вся автоматика была реализована на скриптах CMD. С помощью этих же скриптов запускалась база на клиентской машине. Пользователь кликал CMD файл, бинарник скачивался с сервера в локальный временный каталог и запускался оттуда же. Необходимость скачивания была продиктована ограничениями Access на одновременный запуск базы несколькими пользователями.
Ниже код CMD-скрипта запускающего базу со стороны пользователя:
Set SERVER_PATH=\storageDepShareDatabase
xcopy "%SERVER_PATH%DB_UU.accde" "%USERPROFILE%" /Y
%USERPROFILE%DB_UU.accde
Для запуска бинарника не было необходимости покупать всем MS Access. Полноценная версия нужна только разработчику решения. Пользователям устанавливалась 32-х битная версия Access 2013 Runtime, доступная бесплатно. При установке всех необходимых расширений Access (в полной версии) такая схема прекрасно работала на Windows 7.
Система доменной авторизации и безопасности Access изначально выстраивалась тщательно и даже немного параноидально. Это было связано с требованием руководства к безопасности данных и контроле доступа к ним. Байинговая информация имеет большую ценность на рынке, поскольку может быть источником конкурентных преимуществ для агентства и улучшить переговорные позиции при диалоге с площадками. Было необходимо жёстко ограничить доступ к формам на просмотр информации, в зависимости от должности, подразделения и конкретной группы в которую входит пользователь. Также, требовалось автоматически лимитировать данные выгружаемые в форме отчётов. За основу мы взяли прекрасную статью Tom Van Stiphout «Securing Access databases using Active Directory». Вообще, в русскоязычном сегменте Сети глубокой информации о возможностях MS Access крайне мало и она редко выходит за пределы функциональности демо-базы «Борей». Поэтому при разработке на 90% использовались англоязычные статьи и посты на различных тематических форумах. Итак, был выбран доменный способ авторизации. Пользователь добавлялся в определённую группу. Да, приходилось каждый раз писать заявку, но безопасность такого решения оправдывала издержки. На уровне всех форм были выставлены чекеры, проверяющие к какой группе относится пользователь. Если приемлемые группы не были найдены, то база завершала свою работу с ошибкой. На случай непредвиденных ситуаций был предусмотрен бэк-дор в виде текстового файла с определёнными данными, который можно положить в одном каталоге с бинарником. Если база обнаруживала данный файл, то все формы открывались уже без проверок. Информацией о такой возможности обладал только разработчик системы, поэтому риск несанкционированного доступа сводился к минимуму.
Чуть позже, помимо «зашитой» в формы проверки принадлежности пользователя к определённой группе, появилась более простая и гибкая надстройка в виде стандартной проверки в базе: «пользователь-роль-права». Это конечно же гораздо более уязвимая схема. Она используется только для уточнения прав пользователей при работе с расширением Google Chrome о котором будет кратко сказано ниже.
Опыта работы с MSSQL особо не было, поэтому база проектировалась достаточно сумбурно. Была создана простая иерархия таблиц:
- Клиент
- Проект
- Основная таблица, составляющая 90% базы: Закупаемые позиции по месяцам, с привязкой к проекту через уникальный номер
- Вспомогательные таблицы сущностей, такие, как: бренды, форматы рекламы, типы услуг
- Развязочные таблицы между указанными выше
Также, были созданы некоторые процедуры, выводящие информацию из базы в различной форме:
- Стандартный отчёт по позициям за определённый период (с возможностью ограничения списка только теми проектами, которые может видеть пользователь)
- Список проектов (для гибкого вывода списка, в зависимости от пользователя)
Как не трудно догадаться, основной способ ограничения доступа к данным был на уровне MSSQL. Access перед выводом списка проектов делал запрос к процедуре “Список проектов” с указанием id пользователя. Процедура сличала список доступных этому юзеру клиентов и выводила проекты, которые либо созданы этим пользователем, либо принадлежат к его клиентам.
Стандартный отчёт также, был привязан к выводу проектов, поэтому пользователь не мог увидеть позиции не «своих» клиентов. Проблему двойного вызова EXEC в MSSQL обошли с помощью вывода позиций через функцию:
SELECT * FROM dbo.getPositionsFiltered(@month_start, @year_start, @month_end, @year_end, @position_id) n_1
INNER JOIN #all_projects_src ON #all_projects_src.project_id = n_1.project_id
То есть для вывода Стандартного отчёта, запрашивалась процедура (с параметрами), которая уже сама делала запрос к вспомогательной функции и фильтровала доступный список проектов через INNER JOIN. Топорно просто, но работает как часы.
Связь между MS Access и MSSQL была, как уже писалось выше, на основе noDSN соединения ODBC.
Для присоединения таблицы вызывался такой код:
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & “Server” & ";DATABASE=" & “Database” & ";UID=" & “Username” & ";PWD=" & “Password”
Set td = CurrentDb.CreateTableDef(«LocalTableName”, dbAttachSavePWD, “RemoteTableName”, stConnect)
Для работы с базой использовались два метода. Первый заключался в вызове процедур и выгрузке результата через временный запрос:
Sub runSQL(sqlStr As String, Optional return_values As Boolean = False, Optional rs As DAO.Recordset = Null)
Dim dbs As DAO.DATABASE
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
If (isQueryExists("Temp_query")) Then
dbs.QueryDefs.Delete ("Temp_query")
End If
Set qdf = dbs.CreateQueryDef("Temp_query")
qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & SQL_SERVER & ";DATABASE=" & DATABASE & ";UID=" & SQL_LOGIN & ";PWD=" & SQL_PASS
qdf.sql = sqlStr
qdf.ReturnsRecords = return_values
If return_values Then
'Если помещаем результат в rs
Set rs = qdf.OpenRecordset()
Dim count_rows As Integer
count_rows = myRecordCount(rs)
Else
'Если не помещаем результат в rs, то просто отрабатываем запрос
qdf.Execute
End If
dbs.QueryDefs.Delete ("Temp_query")
End Sub
Второй способ в большей степени использует возможности самого Access, а именно рекордсеты. Я не буду подробно описывать этот мощнейший инструмент работы с данными. Более подробно вы можете ознакомиться с ним в роликах на YouTube.
В какой-то момент число пользователей базы существенно выросло. При этом, значительной части операторов базы уже не требовалось ничего, кроме инструментов ввода информации. Вкусы и привычки у всех достаточно отличаются. Необходимость поддерживать относительно сложную инфраструктуру Access и постоянно дополнять её под требования новых пользователей, вынудили искать ещё более простое решение. А именно требовалось сделать легко поддерживаемый терминал для заполнения базы, без необходимости устновки Access, но достаточно мощный. Была ещё и другая проблема. Помимо описываемой мной системы управленческого учёта в компании уже работала достаточно громоздкая система документооборота. Часть данных пересекалась, поэтому требовалось устранить дублирование работы. Наиболее сложная вводная состояла в том, что упомянутую систему документооборота по разным причинам было невозможно никак изменять: ни фронт-энд, ни бэкэнд. Доступ к базе этой системы из соображений сохранности данных и безопасности было решено не предоставлять.
Для решения данной задачи мы воспользовались возможностями Расширений Google Chrome. Благо, это браузер, который установлен по умолчанию у всех сотрудников нашей компании. В основу системы легла связка: Chrome Extensions+Angular+PHP+MSSQL (та же база, что и для Access). Более подробно об этом решении я напишу отдельно, поскольку кратко описать её вряд ли получится.
Итак, была создана инфраструктура ввода информации. Вывод, то есть собственно управленческие отчёты, был построен на двух платформах: MS Excel и MS Power BI. С первым всё очень просто. Access использовал стандартный модуль подключения к файлу Excel, уже сделанному в определённом шаблоне. Создавалась копия этого файла в локальном временном каталоге пользователя. Далее делался вызов в MSSQL уже упомянутой мной процедуры «Стандартный отчёт». Вывод шёл в временный Recordset (DAO), который распарсивался уже соответственно структуре файла Excel. В какой то момент пользователи столкнулись с длительным временем формирования отчёта, поскольку в него входит 41 колонка, а число выгружаемых записей пошло уже на десятки тысяч. Планируется в обозримом будущем переписать модуль вывода отчёта Excel с использованием подключаемого DLL на C++.
Гораздо быстрее отрабатывал отчёт Power BI. В начале использовалась только десктопная версия. Разработчик делал модель, которая распространялась среди пользователей (руководителей ключевых подразделений и директора департамента Digital) при помощи примерно такого же CMD файла, какой был описан выше для запуска бинарников Access. Схема была не очень удачной из-за постоянных обновлений платформы Power BI. При этом модель созданная на новой версии теряла совместимость со старыми и приходилось постоянно обновлять платформу для 8-10 пользователей. Решением явилась облачная версия Power BI, которая в платной версии поддерживает корпоративные шлюзы к базе MSSQL и расписание обновлений. Если раньше пользователю приходилось нажимать на кнопку «Обновить» и ждать по 2-3 минуты пока модель подгрузит данные и их обработает, то теперь он сразу видит актуальную информацию в момент открытия дашборда. О тонкостях использования Power BI для выгрузки корпоративной отчётности будет написана отдельная статья.
Методология разработки решения была и остаётся гибкой. Встроенные в Access формы позволили быстро создать прототип системы и уже в течение первой недели начать обсуждение его с пользователями. Все решения принимались «на лету». Не было такого, что разработчик «уходил думать» на 2-3 недели. Результат выдавался сразу. Если что-то не нравилось – меняли сразу же, прямо в процессе демонстрации после очередного однодневного спринта. Все замечания учитывались в течение максимум пары дней, особенно в первые недели разработки т.к. именно в это время закладывалась наиболее важная часть функциональности системы. Непосредственно техническая разработка занимала примерно 40-50% времени. Остальное время уходило на многочисленные расспросы пользователей по нюансам, координацию рабочей группы проекта, координацию с департаментом ИТ и менеджером по бизнес-процессам. Всё делалось внутри агентства, без привлечения профессиональных программистов. Срок создания системы на Access составил 4 месяца. Расширения Google Chrome – 3 месяца. Отчёты Power BI заняли примерно 1 неделю. Примерно год ушёл на доводку системы точно под требования процесса и пользователей.
Таким образом, при помощи связки MS Access и MSSQL удалось в кратчайшие сроки с нуля создать самописную модель системы управленческого учёта. С её помощью срок выгрузки самых распространённых отчётов и сводных сократился до нескольких минут или даже секунд. Некоторым руководителям оказалось достаточно отчётов Power BI, которые они анализируют самостоятельно. Вся разработка работающей модели заняла в сумме около 8 месяцев. Результатом явилось не только решение тактической проблемы ускорения выгрузки управленческой отчётности, но и гораздо более глубокое понимание того, как должна быть выстроена система управленческого учёта на уровне всего агентства.
Автор: nsuvorov
