Comments 32
Попробуйте ознакомится с R. Может под Вашу задачу не сильно подойдет. Но ознакомится лишним не будет.
Есть смутное ощущение, что Metabase может закрыть все потребности. Логику вынести в экшены, отображение данных на витрины.
А Microsoft Access или 1С не пробовали? Зачем сразу из пушки по воробьям?
Я для себя сделал вывод, что если компания живёт в экселе - то у неё очень плохо с разработчиками.
Размерами никого не удивить, сотни тысяч строк и десятки таблиц - это небольшая эскюэльненькая базочка. Да, постгрес, да и mysql ворочают и побольше и побыстрее и на скромном железе. В принципе, это логичный путь для развития внутренней системы.
Только есть одно но.
RDBMS не имеют интерфейса. В отличие от экселя. Мало данные загнать туда, надо ещё для них сделать UI, который их вносит, редактирует, показывает. И это не так просто, как кажется.
Возможно, что в компании их нет совсем.
P.S. в который раз вспоминается Г. Кузнецов из Компьютеров, который мечтал о гибриде электронных таблиц и rdbms на персоналках.
Для этого предназначался MS Access. Правда, я никогда не слышал, чтобы им кто-то пользовался.
Очень даже часто приходилось видеть MS Access в работе. Ещё в конце девяностых переводил бумажную "систему" работы с зерном (поставщики, элеваторы, разнарядки, потребители\продажа) в MS Access и убедился, что далеко не я один занимаюсь подобным. Уровень визуализации - вполне. MS Office стоит на 99% ПК. Так что работало и работает.
Многие университетские знакомые начинали какие-то проекты и работы выполнять в MS Access.
Тут на Хабре который год пытаются продвигать lsFusion, чтобы "интерфейс создавался сам". Но что-то не слышно отзывов от реальных пользователей...
"Троллейбус из буханки.JPG", но Эксель умеет работать с ODBC и можно привычный пользователям экселевский отчёт заполнять прямо запросом к базе, или селективной хранимкой.
Более того, к Экселю можно писать SQL запросы, он и сам предоставляет родной ODBC драйвер.
Зря. Такая дичь была, надо было её дальше растить и мечтать о книге гиннеса. А теперь сделали как положено - кому это интересно? Ну что мы, БД на 20 таблиц не видели?
Не знаю какую конкретно задачу решаете вы, но я по долгу службы много раз решал задачу больших экселей. Гораздо эффективнее решил бы эту задачу power pivot. Во первых данные грузятся сразу в модель реляционных таблиц. Во вторых остаётся возможность после обновления пользоваться этим файлом локально, без конекта и доп настройки клиента и питонов, в третьих есть VBA и многие формулы можно реализовать прям в нем (хотя это крайне редко нужно), в четвёртых есть формулы поддерживающие контекст (аналог scope из mdx). Плюсом идёт независимость наличия надстройки на клиенте, модель реализованная в power pivot работает в файле, где она не установлена из коробки.
А если вам надо переехать, то тогда простой вопрос - зачем вы оставляете Эксель "мордой" для субд, раз уж сервер и питон в деле, почему сразу тонкий клиенте на вебе не написать?
Как уже отметили, у Excel есть большой плюс: GUI и привычность пользователю. И обычно шаг от наколеночного решения до опромышливания - гигантский. Если хочется убрать вырвиглазный ВПР (индекс поискпоз куда гибче, кстати), то в качестве первого приближения можно было бы использовать стандартный Power Query:
Всё остаётся в том же файле с тем же интерфейсом.
Можно разделить большой файл сырых данных на отдельные файлы а-ля горячее/холодное хранение и обновлять только горячие данные без ожидания обновления всех формул.
Гибкость не хуже SQL. Есть много настроек через кликание мышкой.
Никакого питона, который неоткуда взять случайному пользователю в компании.
Ноль дополнительной инфраструктуры (ведь Postgres и планируемый Airflow мы не в воздухе запускаем)
Никаких дополнительных проблем с доступами и полномочиями: все на уровне того же файла.
Можно по-прежнему собирать данные из разных источников и локально крутить-вертеть независимо от технологий и интеграций: экспорт поддерживается в том или ином виде почти везде.
Если когда-то наступят светлые времена, можно прозрачно для пользователя подменить некоторые таблицы на таблицы СУБД: для пользователя ничего не изменится, есть pushdown.
Питоны - это классно, но только для упрощения жизни себе. Для того, чтобы такое решение можно было распространить в компании, надо провести очень немало организационных мероприятий и приложить ему усилий, а все это помимо никуда не девающейся сложность настройки и последующей актуализации/доработки.
Здравствуйте. А откуда в целом берутся данные в экселе? Если есть возможность, нужно переходить на нормальные источники данных. Эксель может служить в качестве визуализации итогового отчета, или как источник каких-то рукотворных справочников. Но основную инфу лучше брать из учетных систем.
Для себя выбрал такие критерии выбора системы.
До 100 тыс строк - excel. Если много формул и виснет на просчете - оптимизировать формулы или выносить их в VBA (что не желательно, тк надо менять расширение).
Больше 100 тыс и до 5 млн. Хорошо справляется Access. К нему так же замечательно коннектится excel через надстройку power pivot. PP вообще достаточно эффективно обрабатывает и хранит данные, но его нельзя использовать как источник данных.
Под специфический задачи: простенькие etl процессы - power query; визуализация/аналитика - power bi.
Если больше данных или нужны дополнительные свойства, в тч совместная работа. То лучше уже выбирать полноценные субд, но это кратно другие расходы
Можете объяснить, что значит "PP нельзя использовать как источник данных"?.
PP - так сократил power pivot. В нем можно сделать подключения к внешним источникам данных. Но подключиться к power pivot как к источнику данных нельзя.
Я понял, что PP это power pivot, не понял, что значит нельзя использовать как источник данных. PP это дата моделлер, который позволяет собрать данные от нескольких провайдеров в одну модель данных внутри файла. Оперировать сущностями этой модели и видоизменять их. По сути встроенный ETL.
Эту модель можно далее использовать для визуального отображения в виде, например, сводных или таблично на листе. То есть для сводной модель данных power pivot уже является источником. Поэтому тезис "нельзя подключиться" у меня и не клеится.
Для кого модель данных не может выступать источником? Вы хотите чтобы модель данных одного файла использовалась в других ресурсах (например использовать эту модель в других файлах)?
По поводу pp как etl я бы не согласился. У майкрософта в общем-то все продукты немного-то того, немного другого, а что-то как на зло отрезано. Но под задачу etl, считаю, больше заточен power query, он для этой же задачи встроен в power bi.
И вот после etl от power query или простого подключения power pivot/power bi кладут данные в свою базу, где под капотом довольно шустрая, как по мне, система. Собственно Ексель всегда и для обычных сводных таблиц загружал данные в эту "сводную", но в моднлях PP научился делать намного лучше.
И порой было бы здорово, если бы эти модели данных можно было использовать как источник данных для других подключений.
Подобные задачи хорошо реализуются в экосистеме python (все модные штучки лезут отсюда), без платных или рискованных решений типа PowerBI. Объем данных помещается в RAM 16GB, а значит можно использовать Python+Pandas или SQL+DuckDB, все это в блокноте Jupyter или MS VScode в режиме блокнота. Плюсы - нет сервера БД, скорость выше, простой язык запросов df.query(). Утка дает партицирование и очень быструю работу, быстрее самой быстрой SQLite. Оцениваю трудоемкость Pandas/DuckDB vs SQL/PG как 1:4.
ETL пишется на Pandas в pipe(), а вот запускать ETL-скрипты лучше не в монструозном Airflow, а в легком Dagster (проще стартовать, встроенная валидация данных вполне хороша). Трудоемкость AirFlow vs Dagster 3:1.
ETL-cкрипты сейчас принято не писать, а экспортировать из блокнотов, где они создаются естественным образом, вперемешку с документацией, графиками, описанием данных, доктестами и весь CI/CD сводится к выполнению ячейки с единственной строкой вида exp_py('excel'). Работа в блокноте vs classic IDE 1:5. И да, настоящая совместная работа аналитиков - это блокнот в JupyterLab/Hub в режиме одновременной правки, когда много курсоров, как в GoogleDocs, только еще и с исполнимым кодом. Писать исследование в 4-е руки оказалось не в 2, а в 3 раза быстрее, за счет более полного заимствования кода из "прежних" наработок, которые, в случае блокнотной работы, все находятся в одном открытом блокноте. И да, это иногда падает, но не сильно. Насчет сложности деплоя всей python-среды в офисе - его нет. Блокнот работает в браузере, любом.
Дэшборды тоже делаются нынче просто: Streamlit. И его код app.py также получается через CI/CD из блокнота с exp_py('dashboard'). Трудоемкость Streamlit vs Superset/Metabase 1:2, хотя от 50+ активных листателей дэшборда лучше Superset с аналитической БД.
MS Access + MS SQL сервер легко решают такие задачи. И активно используются. Потому, что: есть образцы, база Борей, например. И документация. И репутация. В Visual Basic есть пошаговая отладка, и компиляция, точки останова и много чего ещё.
Если задача решается слишком сложно, как вы описали, значит ее можно упростить и довольно значительно. Можно применить такой вариант- создать нужную инфраструктуру в sql server и настроить передачу данных напрямую из 1C в таблицы sql server и там же в sql server сделать нужные процедуры и функции обработки данных для отображения в exel файлах в качестве дашбордов через sql запросы в самих exel файлах. Но тут надо хорошо знать. SQL и уметь работать с 1С по связи с sql server напрямую(в документации 1с все описано). Зато никаких питонов и прочей обвязки и можно оптимизировать sql запросы по скорости работы. Если база данных будет расти то оптимизация ее настоятельно рекомендуется.
То есть не надо будет подключаться к MS SQL, на котором хранятся данные с 1с?
В документации 1С описано что работа с 1С-данными в БД на любом движке напрямую (не средствами платформы 1С) является нарушением лицензионных условий. Никаких DBeaver итп. Разложено много граблей на этом пути - 1С обеспечивает агрессивную конкурентную защиту экосистемы и 1С-сообщества ростом сложности и искусственными ограничениями. Монопольное положение (95%+ рынка учетных решений в РФ) позволяет творить и не такую дичь. Поэтому "никаких питонов и Excel-ей" лишь усугубит эту нездоровую для всего бизнеса ситуацию. Но есть и повод для оптимизма: монстр начал поедать себя. 10000 таблиц в современной 1С базе данных оказались неподъемными, и вместо одного 1С-специалиста нужно держать пять, а это неизбежный разброд и шатания.
Да, передача данных на ваш сервер делается средствами 1C
В случае IN-MEMORY или быстрых файловых БД на основе указателей (DuckDB, SQLite) сложная оптимизация SQL-запросов и нетривиальное создание БД не понадобятся, т.к. данные и так будут выдаваться мгновенно. Скидывая огромные регистры 1С (например, таблицу всех проводок с начала года на неск млн строк и сотни столбцов) в банальный TXT-файл - мы на Python его сериализуем и загружаем в RAM или читаем файлы партиций в сжатом типизированном виде. Это будет самый быстрый доступ к данными из возможного.
Запись нескольких миллионов строк в тхт не будет быстрой. В моем варианте можно настроить в 1С запись только новый появившихся данных и не каждый раз весь требуемый набор.
Да, это будет оптимально. Но на практике новые данные часто оказываются "старыми", и выгружать приходится гораздо больше, чем ожидалось. Пример: РЖД дослало накладную мая в октябре (в порядке вещей). А значит все что пришло вагонами с мая по октябрь и возможно и позже - в учете было занижено по цене (а если продавали по себестоимости - окажется продано в убыток). Бухгалтерия не будет перепроводить документы из-за одной накладной в бухучете, но в нормальном упр. учете ее заставят, и выгрузить придется 3 квартала вместо одного месяца. Нашкодить обходной пайплайн для таких случаев было бы правильнее, чем забить.
1С довольно быстро работает с TXT, главное не допускать детских ошибок вроде конкатенации строк, ненужной обработки исключений и проверки существования итд. Но в целом 1С сильно своеобразно - как макроязыком, так и структурами данных, схемой таблиц, сложностью.
Работал с файлом Эксель на 5,5 млн строк (были разбиты на 12 листов - по количеству месяцев). Вес - 1 Гб. В том же файлы сводные, дэшборд. Все весьма живенько крутилось на ноутбуке HP Omen не самой мощной конфигурации.
Тяжёлые формулы можно попробовать оптимизировать или загнать в VBA и запускать расчет тогда, когда это реально нужно.
Из Excel в SQL. Имеет место быть?