Наверняка каждому из вас знакома ситуация, когда организация начинала вести внутреннюю аналитику в Excel, потому что его возможностей было более чем достаточно. Но с ростом компании задачи усложняются, объёмы данных растут, и Excel начинает сначала кряхтеть, а затем превращается в «небесного тихохода», по вине которого ежедневно впустую теряется масса оплачиваемого рабочего времени.
Наш сегодняшний рассказ про то, как компания федерального уровня с большим документооборотом переходила с реестра в виде громоздких Excel-файлов на нормальный, вменяемый BI-инструмент. Ну хорошо – про наше видение, как компания должна переходить (клиент пока ещё не принял окончательного решения).
Проблематика вопроса
Чтобы была понятней суть проблемы: сейчас в компании подготовка и консолидация данных через Excel занимает порядка 80 % рабочего времени сотрудников, причем 79 % — это ожидание реакции самой программы. То есть человек выполнил какие-то манипуляции, написал формулу или значение, нажал клавишу Enter и идёт курить, пить кофе или обедать: некоторые операции консолидации и обновления данных занимают от 40 минут до 2 часов.
Главной проблемой является заложенный в стародавние времена механизм консолидации данных — куча сложных перекрёстных формул, в которых чёрт ногу сломит. Когда-то этот механизм придумал какой-то «гуру» Excel, но он явно не предполагал, что будет обрабатываться такой объём данных. Раньше всё работало без тормозов, и файлы с прописанными формулами просто копировались из проекта в проект. И когда через несколько лет начались проблемы, никто уже точно не знал, как работает консолидация. А найти специалиста, который смог бы её переделать, не удалось, что не удивительно: разработчики обычно не изучают формульный язык Excel да и не пользуются им.
Трудоёмкость и длительность расчётов в Excel привела к тому, что по мере увеличения объёмов данных приходилось постепенно нанимать в отдел всё больше сотрудников, так как уже имеющиеся работники переставали справляться с обработкой документов.
Сложилась эта ситуация далеко не сразу и не на днях. И в результате заказчик решил: пора мигрировать. Основным требованием было то, чтобы новая система оказывала как можно меньше влияния на технологический процесс. То есть заказчик, конечно, недоволен, что Excel тормозит, но и менять привычные операции из-за нового инструмента очень не хочет. Весь технологический процесс можно разделить на два основных этапа:
- подготовка данных из разных источников, в основном из бумажных документов,
- внесение в реестр в виде Excel-файлов и консолидация.
Технологический процесс у заказчика выглядит так:
Второй нюанс: при введении данных в реестр сотрудники проверяют, не вводился ли этот документ раньше. Для этого выполняют поиск по совокупности реквизитов. Мы предложили автоматизировать эту процедуру и выявлять дубликаты уже на этапе ввода. То есть приходит новый документ, его начинают вбивать без всяких фильтров. Если система находит дубликат по каким-либо параметрам (к примеру, по назначению и дате платежа), эта запись подсвечивается красным цветом, чтобы пользователь выяснил: нет ли ошибки во вносимых или уже внесённых данных. Таким образом реализуется контроль дублирования записей и корректности ввода средствами Excel.
Мы решили не создавать зоопарк из разных вендоров. Решение Microsoft было в данном случае лучше не потому, что эта компания разработала и Excel, и Power BI. И даже не потому, что они хорошо интегрированы друг с другом. Просто Power BI позволяет использовать в качестве источника данных целую папку, в которую пользователь может сложить разные файлы по своему усмотрению (в том числе и текстовые). И обработать их буквально одним щелчком мыши. Ну и добавить другие источники информации, если понадобится (1С, автоматизированные учётные системы других подразделений и т. п.).
Power BI и Power Query буквально в сотни раз ускоряют работу и позволяют обойти ограничение на количество данных, которыми оперирует Excel. Сначала это было 64 тыс. строк, теперь счёт идёт на миллионы. Так что если вы упёрлись в возможности Excel, то следующим логическим шагом может быть переход или на Power Pivot или на Power BI: они используют те же самые подходы и механизмы. Если для обработки данных вы применяли какие-то формулы на языке М (в Power Query) или DAX в Power Pivot, то всё это будет практически без изменений работать и в Power BI.
Power BI, как единый интегрированный продукт, позволяет соединить в себе все те технологии, которые годами «навешивались» на Excel, как игрушки на елку. Пусть в него нельзя вводить данные (ну почти), нельзя считать А + Б, как это делается в Excel, но зато его можно использовать в качестве обработчика больших объёмов данных. Например, если нужно произвести какие-то вычисления над колоссальным количеством столбцов или строк. Excel тоже справится, но за часы, а Power BI — за секунды.
Есть два–три конкурирующих продукта, которые, гипотетически, могут выполнять те же вычисления столь же быстро, но они будут не так хорошо интегрироваться с Excel и, что не менее важно, потребуется развернуть дополнительную ИТ-инфраструктуру.
Кроме того, нельзя сбрасывать со счётов и финансовую составляющую: Microsoft предоставляет Power Pivot Desktop бесплатно, если вы хотите использовать этот механизм только для интеграции своих собственных Excel-файлов и будете готовить отчеты или анализировать данные в одиночку или в составе небольшой группы.
При этом создаваемые Power BI файлы можно свободно перемещать между компьютерами, как Excel-отчёты. В результате пользователи получают в руки не просто какие-то рассчитываемые, фиксированные данные, Power BI предоставляет широкие возможности для построения различных интерактивных, моментально пересчитывающихся графиков и диаграмм.
После внедрения Power BI по самым пессимистичным оценкам длительность этапа расчётов уменьшилась в 200–300 раз (!). Учитывая, что в отделе на данный момент трудится 12 человек, которые тратят кучу времени на обработку документов, можно смело сокращать штат раза в 4. А это уже заметная экономия на ФОТ и налогах.
Но экономия рабочего времени и денег – не единственное преимущество этого решения. Раньше в компании никогда не анализировался весь объем данных – только для каждого бизнес-направления или проекта в отдельности. Excel просто не справился бы с такой нагрузкой. А теперь станет возможной не только перекрёстная аналитика по проектам. При дальнейшем расширении этой линейки до, например, Power BI Pro, Power BI RS или даже Power BI Premium, появится возможность готовить интерактивные отчёты для различных категорий пользователей, управлять уровнями доступа к конфиденциальным данным и предоставлять доступ через web или мобильные устройства.
То есть, практически не меняя рабочего процесса, обеспечить всех ЛПР в компании информацией, что называется, на кончиках пальцев.
Юрий Колмаков, эксперт Департамента систем консолидации и визуализации данных, «Инфосистемы Джет»
Комментарии (32)
reesly
23.08.2018 10:32Отличный пример автоматизации бардака.
Excel — отличный инструмент «последней мили» консолидации,
а вводить вторично в Excel документы с назначением платежа — это неправильно созданная система учета и консолидации.
McCow
23.08.2018 12:51Скажем так: есть маленькие компании, где Excel — единственный инструмент учёта (ну, может, не считая 1С). А есть компании, где есть и автоматизированные системы учёта и консолидации и вот такие, «ручные». Не обсуждая бизнес процессы заказчика мы же не удивляемя, когда кто-то заводит маленькую ручную собачку, имея сейфовую дверь на входе, сигнализацию, службу охраны периметра и личного телохранителя.
… но собачки со временем стареют и с ними очень жалко расставаться
AndyGray
23.08.2018 11:04Уже несколько месяцев внедряем Power BI. Результаты превышают любые ожидания!
a-tk
23.08.2018 17:53+2Так долго потому что не поддаётся никак?
AndyGray
23.08.2018 19:43Много бизнес-процессов, плюс нужно писать/адаптировать коннекторы, плюс разбираться в тонкостях, плюс невозможно уделить 100% времени только интеграции.
Asgat_Akhmetshin
24.08.2018 17:37когда в Power BI в диаграммах видишь P&L, Cash-flow, дебеторскую и кредиторкую задолженность из 1С в режиме реального времени, это конечно космос
astono0
23.08.2018 12:28+1«А найти специалиста, который смог бы её переделать, не удалось, что не удивительно: разработчики обычно не изучают формульный язык Excel да и не пользуются им.»
А вы пытались хоть где-то?
Я вот как раз специализируюсь на разработке решений для екселя и гугл таблиц, разработчиков в этой сфере хватает.JetHabr Автор
23.08.2018 12:53Думаю, это вопрос к HR-службе заказчика, так? Если кратко, то да, пытались. Только заказчику нужен не Excel-разработчик на фулл-тайм.
astono0
23.08.2018 12:59+1Как и большинству других компаний, поэтому это в основном проектная работа.
Жаль, конечно, что HR не в состоянии выполнять свою прямую функцию.
Я вообще не видел у нас таких вакансий, а вот в Швеции, к примеру, видел компанию с целым отделом под ексель разработку. :)
firedragon
23.08.2018 21:56Возможно заказчику не нужен этот костыль?
Сложная логика и аналитика выносится как в репорт системы, так и в кастомные портальные решения.
tim2018
23.08.2018 14:30о
обновления данных занимают от 40 минут до 2 часов
может вы просто не умеете их готовить? одинаковая структура, минимум вычислений в исходных файлах, их открытие на просмотр, range — array- range. очень быстроJetHabr Автор
23.08.2018 14:55Не… там декартово произведение из множества файлов. Безусловно, можно было ускорить средствами excel, даже затянуть всё в PowerPivot, но Power BI даёт в этой связке наилучшие показатели по производительности. Плюс — дополнительные возможности по расширению и дальнейшей модернизации системы. И если заказчик когда-нибудь всё же решиться отказаться от экселя, наработки в Power BI не пропадут.
phenik
23.08.2018 14:51Есть хорошая компонента spreadsheetgear, полностью совместимая с форматами файлов экселя, и на порядки ускоряющая работу с ними. Также можно дополнить функционал экселя собственными процедурами на языках программирования поддерживающих работу с компонентами, к примеру в С#, не используя медленный vba. Недостаток решения, компонент платный.
McCow
23.08.2018 19:11phenik, тут на вкус и цвет… Кому то подойдёт C# за деньги, кого-то устроит связка Python+R+M+DAX и бесплатно.
А вообще напишите статью про такой замечательный инструмент. А то на Хабре ничего толком не находится.phenik
24.08.2018 05:51Опыт использования компоненты на статью не янет, но прокомментировать могу)
Для C# есть бесплатная версия — express, использовал ее. Опыт использования spreadsheetgear свелся к тестированию триальной версии, но на реально работающем приложении эксель. Однако это приложение не бизнес-процесс, а исследовательский отчет. Очень объемистые исходные данные, много вычислений на vba, плюс масса сложных диаграмм для представления результатов. Впечатления от использования компоненты только положительные:
1. Ускорение работы приложения на порядок, для наиболее тяжелых данных было 10-15 мин, стало в районе 1 мин. Отмечу, не занимался специально оптимизацией кода, вполне возможно добиться большего ускорения.
2. Не обнаружено ни одной не совместимости с экселем в объеме использованного функционала.
3. Простота расширения пользовательского интерфейса приложения, в сравнении с убогими возможностями встроенного vba. В частности, использование диалоговых панелей в стиле самого экселя.
4. Относительная простота миграции, если конечно не придется осваивать язык программирования, тот же С#.
К сожалению, на этом опыт эксплуатации компоненты окончился, так как руководство сочло приобретение коммерческого варианта компоненты не целесообразным по нескольким причинам. Первая, не устроила цена, второе, нашлись менее эффективные способы некоторого ускорения приложения, и последнее, все же это просто исследовательский отчет, пользователи могут результаты и подождать, прибыли не упустят)
KEugene
23.08.2018 15:22обновления данных занимают от 40 минут до 2 часов
Даже не представляю, что бы это могло быть. Имею порядочный опыт в области «экселевских» консолидаций. Большая продолжительность обычно тогда, когда прямо в формулах ссылки на сетевые ресурсы, но два часа…
Но, в любом случае,Power BI позволяет использовать в качестве источника данных целую папку, в которую пользователь может сложить разные файлы по своему усмотрению
как-то, извините, стремно звучит. «Накидав, накидав та й борщ», а потом баланс не сходится.Stecenko
23.08.2018 19:49обновления данных занимают от 40 минут до 2 часов
Даже не представляю, что бы это могло быть. …
Например, миллион ячеек на листе, половина — формулы с зависимостями по цепочке, меняем 100 000 ячеек по одной — и после каждой весь лист пересчитывается.
Скорее всего, было что-то в таком духе.KEugene
24.08.2018 14:48Как это «меняем 100 000 ячеек по одной»? Эксель так не работает. Руками, что-ли?
Если без ссылок «наружу» и средний «офисный» компьютер (без запущенных ресурсоемких приложений) не старше трех лет, то пересчет миллиона ячеек займет до минуты (если среди формул только простая арифметика, то секунд 20-30). С таким регулярно имею дело.McCow
24.08.2018 16:54Проблема, очевидно, не в самом движке Excel, а в большом количестве файловых операций / обращений к диску, что и описано в комменте ниже.
KEugene
24.08.2018 18:06У экселя есть проблемы при обновлении данных по сети. Некоторое время назад для меня это было настолько большой проблемой, что пришлось обращаться за разъяснением в Майкрософт, благо компания, где все происходило, является «очень дорогим клиентом» и можно не бояться быть отфутболенным на общий форум. Если в двух словах, то даже в версии 2016 года сидит масса «хвостов» чуть ли не с первых версий программы. Например, мы легко можем видить один из таких атавизмов, получая данные из встроенного вэб браузера, который совсем даже не экземпляр IE. Так вот, модуль (или как там он называется), отвечающий за файловые операции, точнее за обновление связей в формулах, тоже разрабатывался в те далекие времена. Новые форматы и стандарты просто навешивались сверху. Думаю, многие разработчики так делают. В итоге, имеем то, что имеем: не важно, на сколько у вас крутая локальная сеть, обновляться данные будут везде одинаково медленно. Решение только одно — смириться и минимизировать получение информации из книг в сети, переходить на подключение к серверам данных по тем или иным протоколам.
Кстати, Эксель — еще тот «черный ящик». Если обновлять связи штатной командой имеем одну скорость. Если же проделать то же самое через VBA, то получим скорость в несколько раз выше. Имея ненормально здоровые таблицы, пришлось наделать себе самописные макросы-команды для поиска/замены, обновления связей, пересчета определенного диапазона ячеек и т.п. Кроме того, это позволило отображать по ходу «прогресс выполнения», что оказалось и психологически комфортнее :)
McCow
24.08.2018 10:55Думаю два часа — это время обеденного перерыва. Подождали минут 20 — недосчиталось. Пошли пообедали, покурили, кофе попили. Вернулись. О! Досчиталось! Сколько прошло? Два часа назад запускали. Значит столько и считалось…
JetHabr Автор
24.08.2018 11:53Большую часть времени, по нашим исследованиям данной ситуации, занимали файловые операции. Т.е. несколько тысяч записей небольшими блоками считывались из разрозненных, хранящихся в сети файлов, объединялись декартовым произведением и этот массив активно свопился, т.к. не использовалась новая табулярная модель, а использовался старый консервативный экселевский подход через формулы массива (Ctrl+Shift+Enter). В настоящее время это явный legacy-подход и его можно продолжать использовать только на небольшом объёме данных для сохранения совместимости с версиями старше Excel 2007. Владельцы Excel 95 и 2003, ау, откликнитесь!
Что касается исключения человеческого фактора — это мечта любого интегратора. Однако заказчики зачастую не готовы революционно менять годами устоявшиеся бизнес-процессы. Особенно, если эти бизнес-процессы позволили им заработать и свой первый миллион, и свой первый миллиард. Т.е. это вопрос не технический, а психологический.
И на наш взгляд, в подобных ситуациях Power BI — естественный логичный шаг для эволюционного подхода. Мостик между старыми Excel и новыми BI. Большой результат малой кровью.
servekon
23.08.2018 18:04Специально перезагрузился в Windows 10 x86_64. Установил. Пытаюсь подключиться к базе MS Access(.mdb), требует библиотеку, устанавливаю версию для 32 бит. Опять требует библиотеку, для 64 бит не даёт установить, т.к. офис стоит 32 бит и база Access в версии для x64 не работает. Короче, тупик. А так хотелось попробовать.
McCow
23.08.2018 19:06servekon, Посмотрите солюшн на официальном коммьюнити:
[https://community.powerbi.com/t5/Desktop/Power-BI-Desktop-Not-Connecting-to-Access/m-p/125940/highlight/true#M53372]
Если не поможет, поднимитесь по ветке выше, там много решений, но проблемы, если и возникают, то, в основном, со шлюзом.servekon
23.08.2018 21:33Спасибо, буду изучать.
P.S. Изучил. Советуют установить Microsoft Access Database Engine driver, так я его и установил. Проблема в том, что система Win 10 x86_64, а версия Microsoft Office — x86. Драйвер для x86_64 установить не позволяет.McCow
24.08.2018 11:02Да, с разрядностью в рамках одной системы у MS, к сожалению имеются такие проблемы совместимости. И, кстати, не только у MS/
Для использования BI в любом виде по опыту рекомендую использовать только 64-разрядные версии. Просто если принять это, как аксиому, жизнь в BI-средах станет заметно проще. Лимит в 2ГБ адресуемой памяти — очень существенный ограничитель. Как сапоги 35 размера, если у вас нога — 43.
Cassiopeya
24.08.2018 17:37Как раз сейчас переходим на Power BI из вот таких вот экселек, и самое сложное в этом переходе — люди.
Пользователи не хотят новые (удобные!) инструменты, боятся что ли. Им привычнее на папирусе.
AstarothAst
Теорема о полноте excel — любой бизнес-процесс может быть описан достаточно жирным excel файлом :)
OnelaW
С одним уточнением (из личной практики) способный открыться и уместиться в разумных пределах на экране.