Тот, кто несет фонарь, спотыкается чаще,
чем тот, кто идет следом
Жан Поль

Предисловие

В данной статье я хочу рассказать о том, чем я занимаюсь последние 5 лет. Информации скопилось очень много и я попытался изложить её в простой и доступной форме. Схема системы:
Общая схема (vsd)

Начало

— Мы тебя берем -, сказал мой будущий руководитель, — но надо посетить ещё один кабинет.
Я стоял в душном коридоре, рядом шуршал принтер, а передо мной была закрытая дверь. За дверью кто-то громко говорил по телефону. Вот голос умолк, мы постучались. За дверью в кресле сидел мужчина, он посмотрел на меня, потом на руководителя и спросил:
— Подходит?
— Да, вполне. – Ответил мой будущий босс.
— Ок, берём.
Так незамысловато начался мой трудовой путь в Банке.

Первая, озвученная мне задача — Автоматизация сбора данных и предоставление, на их основе, управленческой отчетности. Я начал работу с Microsoft SharePoint 2010. Версия 2007 уже была развернута до меня. И первым моим подвигом была попытка перенести пользователей со старой версии SharePoint на новую, в ходе которой, я случайно всех этих пользователей удалил. В связи с этим, знакомство с администратором сервера для портала с отчетами проходило в процессе восстановления данных: нервно, печально, тихо. Я дрожащими руками и мокрым лбом, восстанавливал удаленных, а он смотрел, чтобы я ещё что-нибудь не выкинул.

Первая ласточка

Спустя какое-то время Microsoft SharePoint вместе со встроенным Microsoft Reporting Services был развернут. После XSLT преобразований и css вёрстки портал приобрел корпоративный вид. И начался процесс создания “Хранилища”.

“Хранилищем” называли Базу Данных на Oracle, в которой предполагалось строить “витрины”. А под “витринами” понимались данные для аналитики. Первый отчет я создавал в одиночку, так как штат расширялся медленно, а сделать надо было как обычно — “вчера”. На первых порах не было даже серверов под базу данных и под портал SharePoint. Использовали стандартный пользовательский компьютер, раздобыв к нему пароль администратора. Эксплуатация этого самого пароля администратора являлся строгим нарушением дисциплины. Так что с первых дней работы в Банке мне пришлось смириться с мыслью о том, что успеха тут можно достичь, только нарушая правила. Вторым нарушением был списанный компьютер, который почему-то забыли у нас забрать. Так как он не был настроен под использование в корпоративной сети банка, я воткнул в него Yota и пользовался им как точкой доступа в интернет. В общем, из подручных средств удалось построить рабочее место программиста.

Стоит отметить, что департамент, в который меня взяли на работу, не был связан с программированием и занимался исключительно экономическими задачами. А меня и моих коллег брали как-бы нелегально. В Банке не принято интересоваться, как человек будет решать задачу, она просто спускается сверху и если ты её решил, то молодец, если нет, то будут неприятности (как с реальными нелегалами). Отсюда моё непосредственное руководство, понимая, что автоматизировать управленческую отчетность могут только программисты, пошло на риск и под видом экономистов стало набирать нас.

Так как ресурсов изначально не было, то первой базой данных был Microsoft SQL Server, который использовался так же порталом SharePoint и Reporting Services. Данные я строил по схеме “Звезда”, в отчетах широко использовал контрол – Matrix, с его помощью группировал по строкам, и столбцам. Публике понравились мои отчеты и через пару дней нас: меня, администратора, двух начальников и двух аналитиков, пригласили в самую главную комнату Банка. Комната была обставлена старинной мебелью, за столом сидел Большой человек. В торжественной обстановке он рассказал нам про важность нашей работы и вдохновил на дальнейшие подвиги.

Что не устраивает в Microsoft Reporting Services.

Но то, что нравилось руководству не совсем удовлетворяло сотрудников Банка, которые должны были ежедневно работать с нашими отчетами. Работая с Microsoft Reporting Services, пользователи отмечали следующие неудобства:

Отчеты типа Matrix выглядят как сводные таблицы, с группировками. Группировки могут разворачиваться, посредством нажатия на + у каждого члена группы. На Web-форме, это нормально работает, но при выгрузке в Microsoft Excel, при большом объеме данных всё очень сильно тормозит.
В некоторых отчетах, при определенном наборе значений, запрос, получающий данные из Базы Данных в Reporting выполнялся очень долго, и в итоге выскакивала ошибка превышения интервала ожидания. Попытки увеличения интервала не решили проблему, т.к. существует ограничение на время возврата данных от сервера. В случае с Internet Explorer это ReceiveTimeout. Его можно изменить, но только на машине клиента, а такой возможности у нас не было.
Объемы данных в отчете для клиента могли превысить все мыслимые значения. В первую же неделю работы я с этим столкнулся. Пользователь хотел выгрузить 1 миллион строк из базы, чтобы, как он выразился: “Просто сверить их хочу, вдруг, где ошибка!”. Выгружая такое количество информации в Excel, Reporting не мог завершить выгрузку, т.к. у него заканчивалась память на сервере. Сервера, первое время, были довольно слабые и такие ошибки заводили ситуацию в тупик.
Запросы, используемые в отчетах, MS Reporting сохраняет у себя на сервере. Но хотелось, чтобы они хранились в БД Oracle, в которой располагалось Хранилище. Та же проблема были и с проверкой доступа к отчетам, который хранится в Microsoft SharePoint. Пришлось писать утилиты, которые, ежедневно, копировали эти данные с исходных систем в хранилище БД Oracle.

Так же следует отметить, что в Банке пользователи работают с данными чаще всего в Microsoft Excel. Большинству из них отчеты на Web-форме не нужны. Делая какой-нибудь доклад, или презентацию, пользователь хочет как-то “поиграть” с цифрами. Добавить что-то своё, провести какую-то аналитику или сравнить с предыдущими отчетами. Только бухгалтерские и статистические данные формировались в стандартных формах. Но таких отчетов было меньшинство, большинство же предоставляло данные пользователю, в целях дальнейшей работы с ними.

Microsoft Reporting Services не может, Analysis Services поможет

Если гора не идет к Магомету,
Магомет идет к горе
рассказ о Ходже Насреддине

Перечисленные выше проблемы доставили немало хлопот. Пока я искал их решение, к нам пришли ребята из QlikView и стали предлагать свой продукт. Народ был колоритный, кто берцы с костюмом одел, кто пиджак поверх футболки. В общем, экономили они на продажниках, хотели своими силами всё сделать. Но сам продукт произвел впечатление и заставил задуматься над тем, как улучшить то, что делаем мы. Минусом этого продукта, в случае нашего хранилища было то, что он использовал память для построения своих отчетов. А в нашем случае предполагались огромные объёмы данных, и ограниченность памяти потенциально могла быть серьёзной проблемой. Вторым моментом было то, что непосредственное руководство хотело у себя иметь команду собственных персональных разработчиков, а не людей со стороны. В общем, несмотря на их шикарные презентации, им отказали. Но QlikView был похож на OLAP, а я в своё время был на курсах Microsoft Analysis Services. Это послужило толчком к тому, что я стал строить кубы поверх нашего хранилища. А с помощью ASPxPivotGrid от компании DexExpress я показывал их на Web-форме. Так же выяснилось, что Microsoft Excel может выступать клиентом Analysis Services, что оказалось очень удобно. Т.к. пользователь мог сам построить и проанализировать то, что ему нужно.

Трудности с Microsoft Analysis Services

Но не обошлось без ложки дёгтя. Microsoft Analysis Services не очень удобен для администрирования и у него есть ряд проблем:

Массовая раздача доступа — например, пользователям нужно дать доступ к 30-ти различным кубам. При решении этой задачи стандартными средствами надо давать в каждом кубе доступ по отдельности.
Автопартиционирование — нет штатного способа сделать так, чтобы новые партиции добавлялись автоматически.
Обновление кубов — когда кубов очень много, с множеством партиций в каждом, сложность администрирования многократно возрастает. Microsoft Integration Services удобен в случае одного куба, но при росте числа кубов удобство теряется.
При удалении пользователя из Active Directory, развернуть проект куба на сервер становится невозможно, если удаленный пользователь имел доступ к этому кубу. Приходится выяснять, какая учетная запись была удалена и так же удалять её из куба.
Сложность с разворачиванием измененного проекта.
Для решения этих проблем, Microsoft предоставил API, c помощью которого можно разработать утилиты, облегчающие работу. Так же проекты, в которых описывается создаваемый куб, хранят всю информацию в xml файлах. Это существенно облегчает разработку ПО, для удобного администрирования MS Analysis Services.

В общем, в своём стиле, дядя Билл, вместо того чтобы дать готовый дом вручил молоток доски и гвозди.

Укрощение Microsoft Analysis Services

Опытность — это школа, в которой уроки стоят дорого,
но это — единственная школа, в которой можно научиться.
Франклин Б.

С помощью инструментария от Microsoft, я разработал и активно использую следующие утилиты:

Утилита массовой раздачи доступа, которая позволяет раздавать доступ одновременно во множество кубов, в определенные роли в каждом. Так же она автоматически удаляет пользователей, которых больше нет в Active Directory.
Утилита автообновления, которая определяет когда и какой куб надо обновить, и что необходимо в нем обновить (какую партицию и т.п.) и запускает обновление. У неё есть возможность сохранять ошибки, возникшие в процессе обновления, и так же, есть возможность добавления новых кубов, их администрирование, приостановка обновлений.

К сожалению, текущая рабочая нагрузка не позволяет заняться разработкой утилит, способных существенно облегчить работу с MS Analysis Services. Хотя мыслей и идей на этот счёт множество.

Стоит так же отметить ошибку, которую я сделал, когда только начал их строить. Она заключалась в том, что факты и измерения я строил с помощью сложных селектов. Когда настала пора резать куб на партиции, они перекочевали в каждую партицию. Что привело к их многократному дублированию, что есть очень плохо. С другой стороны, некоторые селекты выполнялись довольно долго и в случае, когда требовалось пересобрать весь куб заново, время его перестроения сильно возрастало. Спустя какое-то время, пришло понимание того, что прежде чем создать куб необходимо создать таблицы в базе, которые будут содержать все данные для него. А новые данные доливать в них, ежедневно, с помощью Job’ов. На основе этих таблиц, в итоге, строить куб. Так же стоит выработать правила наименования сущностей и решить в каких случаях необходимо создавать отдельные схемы в Базе Данных.

Всегда хочется большего.

OLAP кубы существенно облегчили работу пользователей, но не решили всех проблем. По-прежнему оставались отчеты, которые по различным причинам не могли быть загружены в Analysis Services. Определенные трудности были связанны с тем, что время обновления куба или его партиции может составлять от нескольких минут до нескольких часов. Когда это время есть в наличии, проблем нет, но бывают ситуации, когда данные надо предоставить ”чем раньше, тем лучше” и тут кубы не помощник. Решение было найдено в процессе работы с пользователями. В смежном со мной подразделении работала одна очень толковая сотрудница. Видя её высокий профессиональный уровень, было решено, предоставить ей доступ к базе через Web-форму. Задача не казалась мне слишком сложной, и я написал приложение, которое позволяло писать запросы к базе и возвращать результат в виде файла в формате csv. Это была Web-форма с полем для ввода текста и кнопкой, по нажатию на которую выполнялся запрос и возвращались данные.

Благодаря этому сотрудница написала множество шаблонов на MS Excel, использующих OLAP кубы и выгрузки. Последние она анализировала как с помощью формул, так и с помощью Power Pivot. Как результат, она стала делать хорошо и слишком много и… уволилась. Чуть позже таким же образом уволилась ещё одна сотрудница. Мыслями об этом я поделился с бывалым коллегой, пенсионного возраста, на что получил замысловатый ответ:

— Паровозик из Ромашково должен соответствовать Ромашкову, иначе рельсы не выдержат. (ну, что – то вроде, сотрудницы профессионально переросли свой отдел)

В итоге, я переработал программу, которая выгружала данные из базы. Теперь она может выгружать в файл формата Microsoft Excel. При её создании я использовал OpenXML SDK. Система состояла из трех Web-форм. Одна для удобного администрирования, вторая для того, чтобы пользователь мог видеть список существующих отчетов и третья для формирования отчета. По сравнению с Microsof Reporting Sevices, данная система требовала минимум оперативной памяти при своей работе, т.к. она использовала модель SAX для формирования Excel файла. Также она могла ждать выполнение запроса сколь угодно долго. Сделал я это с помощью отсылки проверочных запросов от клиента серверу. Другим преимуществом был существенно больший объём информации, который она могла выгрузить. Это я сделал, формируя определенное количество строк на одну закладку(Sheet) в файле Excel. Как только это количество достигалось, создавалась следующая закладка. Данное приложение, вначале, было спрятано за 7-мью замками, а доступ давался только особо попросившим. Но, постепенно, оно набрало популярность, и его вывели из тени.

Задач много, людей мало

Команда разработчиков потихоньку расширялась. Взяли ещё одного, мне в помощь и человек 5 программистов БД Oracle. Моего помощника тут же нагрузили дизайном SharePoint и системой премирования, так что помощь была лишь в том, что c этого момента его делал не я.

Задачей программистов, работающих с базой, было наполнение её данными. Отчеты же строил я, в одиночку. Параллельно, я администрировал OLAP Кубы и все программы, которые написал. Тестированием всего этого тоже занимался я. Так же мне надо было, постоянно, всем желающим, по заявкам, раздавать доступ ко всем нашим ресурсам. Ещё необходимо было поддерживать ряд отчетов, которые отправлялись в центр. В отделе работал принцип – тот, кто делает, на том и едут. Попытки передать часть своего функционала кому-нибудь ещё встречали непонимание со стороны руководства. Чтобы скинуть с себя хотя бы отчеты пришлось уйти в отпуск на месяц. Мне повезло, так как после этого случая, на такой срок в отпуск уходил только руководитель. Пока я отдыхал, руководство было вне себя от гнева, так как некому было делать отчеты. В итоге, программисты БД разобрались с Microsoft Reporting Services и научились с ним работать. Вернувшись из отпуска, я почувствовал некоторое облегчение. Теперь можно было уходить домой не в 8 вечера, а немного пораньше.

Чем пользовались

Система отчетности стремительно разрасталась и набирала популярность. Помимо неё приходилось делать и другие, полезные приложения. Была разработана система оценки рентабельности клиентов, премирования сотрудников, сопоставления комиссий по клиентам, и премий по страхованию. Разрабатывались программы на .Net Framework, для контроля за ошибками использовали Elmah. В качестве ORM системы лучше всего подошёл Dapper. Так же применялся logfornet, на клиентской стороне использовали JQuery. Т.к. сроки разработки ставились по принципу “надо сделать вчера”, то широко применялись контролы DevExpress и ASP.NET. Для передачи информации между клиентом и сервером использовали JSON. Контроль за выполнением задач велся с помощью Redmine.

Ох уж эти файлы!

Для передачи информации между системами в Банке широко используются файлы. Причём чаще всего это файлы формата Excel. Откуда идёт эта традиция мне не известно, но грузить их необходимо, и файлов очень много. Загрузку делали, кто как мог. Кто-то использовал средства Oracle, я писал утилитки на C#, один из коллег использовал Microsoft Integration Service, другой Informatica.

И вот, как то, придя из отпуска, я увидел длинный список требований-пожеланий, которые написали коллеги в моё отсутствие. Это был универсальный загрузчик из файлов в базу. Договорились, что разбирать он будет Excel файлы старого и нового форматов, а так же любые текстовые файлы. Для Excel он будет конвертировать всё в string и писать колонку за колонкой в staging таблицу, для обычных же текстовых файлов, всё писать в другую таблицу, по принципу – одна строчка — одна запись в колонке. Интерфейсом ввода должна стать Web-форма. А само приложение должно быть единым и работать на Web-сервере. Что осложняло его разработку, так как у пользователей, с одной стороны, должна быть возможность загрузить данные в реальном времени через браузер, с другой – загрузка должна запускаться в автономном режиме, по расписанию.

Как-то утром зовёт меня к себе босс и ставит перед фактом: “Сделать надо за неделю, не прекращая администрировать всё то, что ты написал раньше.” В попытках увеличить сроки выполнения задачи, руководитель, с видом профессионала, по пунктам перечислил, что я должен делать и сколько на это тратить времени. В общем, удалось договориться на 3 недели. Но реально работа была закончена через 3 месяца. Это, наверное, менеджерская традиция — ставить короткие сроки.

Чтобы не разгневать руководство, сотрудники, в течение отведенного на разработку времени, спешно делают прототип, который как-то работает. Демонстрируют его начальству, оно ставит галочку – “сделано” и бежит всем показывать и рассказывать, что за чудо штуку написали их подчинённые и как это всё работает. Причём, вскользь упоминается, о том, что проект находится на стадии тестирования и в нём есть ошибки. После того как руководство обо всем отчиталось, все забывают о проекте и ты, не спеша, можешь его доделать. Были случаи, когда к моменту реальной готовности приложения, оно было уже никому не нужно.

Спустя три месяца загрузчик был готов. При его разработке использовались контролы jQWidgets. Приложение оказалось очень полезным, т.к. пользователи видели все загрузки в одном месте и могли легко и удобно управлять загрузками из Web браузера. Исчезла проблема – “Что и откуда мы грузим, почему оно не загружается и как с этим разобраться”.

А кому-нибудь из разработчиков нравится SharePoint?

…После чего тщательно
обработать напильником!
Из Анекдота

Много сложностей при разработке доставили приложения под SharePoint. Сама по себе, разработка, как бы, кардинально, не отличается от создания обычных Web-сайтов. Но на сервер все это развертывается очень долго.

В итоге решили: серьёзные приложения, требующие большого объёма кодирования, делать отдельно, со ссылками на SharePoint. Они имели похожий дизайн, использовали ту же систему доступа, что и портал.

В процессе администрирования портала появилась необходимость более гибкой работы с учетными записями пользователей. Проблема была в том, что часть информации хранилась в Active Directory, часть в Oracle. Был разработан поисковик, который работал с обеими системами одновременно. Он оказался настолько удачным, что пользоваться им стали очень многие сотрудники в Банке. Особенно, после того как я к нему прикрутил фотографии сотрудников.

В поисках волшебной кнопки

Нет мастерства без волшебства.
Валентин Борисов

Если обычных сотрудников Банка мы как-то смогли удовлетворить, то руководство хотело от нас нечто большего. Им виделась Web-форма с большим светофором и кнопкой с надписью “Нажми меня и всё станет лучше”. Этот светофор пытались сделать с момента старта проекта. Но серьёзная работа началась только спустя 3 года.

Были взяты все плановые показатели и сопоставлены с фактами. Из этого всего сделали дерево, обходя которое открывались графики с табличками по каждому показателю. В проекте предусмотрели возможность загружать и отражать действия по улучшению ситуации с выполнением плана. Решение оказалось очень интересным и полезным, но оно требовало очень много человеческих ресурсов, т.к. по каждому показателю надо было подгружать прогнозы на то, как будет он выполняться и в случае невыполнения, подгружать объяснения и изменения, для улучшения ситуации. В этом был и свой большой плюс. С этим приложением позиции нашего отдела в Банке очень сильно укрепились. Руководство обязало работать с данной программой сотрудников различных подразделений.
Параллельно с ней, я, по своей инициативе, разработал программу, которая объединила все аналитические кубы. Помимо сводных таблиц она, так же, отображала информацию в графическом виде. Результат этой работы оказался косвенно полезен. Приложением пользовались больше в ознакомительных целях, т. к. оно наглядно показывало удобство работы с кубами. В итоге, возросло число сотрудников активно работающих с ними. Другим плюсом этого решения оказалась возможность быстро обнаружить ошибки в системе, т.к. на графиках, построенных с помощью аналитических кубов, видны аномальные всплески, либо провалы в те дни, которые содержат ошибочные данные. А поиск и исправление ошибок было второй частью работы.

Доверяй, но проверяй

Данные могли меняться задним числом, загрузиться частично, либо задвоиться. Доверие к продуктам очень сильно зависело от качества данных. Не зря говорят — “Качество вызывает уважение!”. Со временем пришло понимание того, что необходимо разработать единую систему контроля качества. В итоге, разработали Web приложение, в котором заводились селекты, используемые в отчетах, и проверки к ним. Система ежедневно запускала эти проверки и сообщала об ошибках. В данный момент я пытаюсь подключить к ней проверку кубов. Так же к ней подключили проверку расчетных данных. В результате доверие к продуктам выросло, хотя всё ещё есть над чем работать. Недаром ведь шутят:

“ Если бы программист был доктором:
— Доктор, у меня нога болит.
— Странно. У меня такая же и не болит! “

Место для шага вперед (Виктор Цой)

На данный момент, разработка, системы автоматизации управленческой отчетности практически остановилась. Часть разработчиков, по уши, увязла в администрировании, а другая была подряжена на различные проекты смежных подразделений. Так же часть сил отнял проект руководства по автоматизированному контролю нашей деятельности. Хотя, несмотря на это, есть много вещей, которые можно сделать значительно лучше и удобнее. И если набраться сил и суметь не только их реализовать, но и придать товарный вид, то я думаю, они могли бы принести хорошие дивиденды. Как говорил Онорэ Бальзак: “Чтобы дойти до цели, надо прежде всего идти.”

Хотя система автоматизированной отчетности не превратилась в крупную компанию, конкурирующую с фирмой Microsoft, но, на мой взгляд, она вполне способна на это.

Автоматизация финансовой отчетности