Страницы: 1
RSS
Пригоден Access под мои цели или что посоветуете в качестве БД в моем случае?, БД
 
Здравствуйте форумчане!
Задумался о построении БД в своем отделе.
В отделе 7 человек, из этих 7-и сотрудников знания эксель только у двоих отличные.
По этой причине ни о каком уходе из файлов эксель в другие СУБД не может быть и речи.
Из 7-и человек работать в этом смогут только двое да и не работать учиться)))
Остальные сотрудники кроме пользовательского экселя ничему не обучаемые.
Это я к тому что прежде чем предлагать всякие MySQL, PostgreSQL и Power BI вы представляли реалии кто будет с этим работать.
Естественно, делать так чтобы в отделе работало только два человека в мои планы не входит.
По этой причине необходимо создать БД отдела максимально дружественную к пользователям Excel.
Уход в Access тоже не подойдет по тем же соображениям и плюс много сложной отчетности, которая должна быть построена на БД.
Осложняющим фактором является то, что нет устроявшейся формы отчетности, приходится то так сделать, то эдак.
По этой причине строить отчетность в Access нет никакого желания.

Имея такие вводные пришла идея выкручиваться следующим образом.
1. В отдельных xlsx файлах вести файлы-справочники. Один файл - один справочник. Например, в одном файле перечень договоров по подрядчикам, во втором файле реквизиты субподрядчиков, в третьем файле данные о ходе сдачи документации заказчику, в четвертом файле единичные расценки по видам работ, в пятом классификатор, присваеваемый виду работ и т.д.
В общем будет как-бы множество столбцов, но не в одной раздутой книге Эксель, а в некоем множестве книг. Эдакий конструктор, из которого 2-е сотрудников будут строить сложную отчетность по общему идентификатору (разную, в т.ч. по новым формам)
2. Всей файлы-справочники (вместо того чтобы использовать ВПР) связать через Access, но при этом не загружать их в сам Access, а только создать связанные таблицы.
4. На основании запросов power query к Access 2-е сотрудников смогут строить сложную отчетность для отдела (отчетность будет опят же строится в файлах эксель). При этом файлы отчетности будут не такие тормознутые как было ранее (не будет множества ВПР-формул), а самое главное в них легче наводить порядок если изменилось что-то в справочнике (не придется переделывать все отчеты). Кроме того наводить порядок смогут низкоквалифицированные сотрудники отдела - они смогут разобраться в файле-справочнике.

При такой схеме  сотрудники, которые кроме экселя ничего не хотят знать, не останутся без дела - им я смогу вовлечь  в наполнение файлов-справочников. Они смогут их подкорректировать при необходимости, так как это же знакомый им эксель!

В общем получается схема:
База эксель - Access для построения связей между справочниками в Excel - Запрос power query к Access - отчетность в Excel

Отдельно хочу пояснить почему не рассматриваю power pivot .
Строить модель данных в power pivot не подходит так как 1. Мне не нужна сводная таблица в отчетности. А выводить данных из pivota во что-либо другое, отличающееся от сводной таблицы, как я понял, не выйдет.
2. Проблематично подключиться через power query к модели данных power pivot. Получается система нипель туда закачать данные не вопрос, а обратно танцы с бубном.

Что посоветуете мне? Правильная ли моя идея идти к решению проблемы БД через Access?
Может есть какой-то другой подходящий инструментарий.
 
Каковы объемы данных? У Вас столько звеньев, что возможно Excel +vba сам бы с этим справился, но все зависит от объема данных.
 
40 справочников, 135 000 строк.
20 справочников с общим идентификатором и 20 без
 
Сергей М.,  Вы сперва решите для себя, какой основной вопрос хотите решить. По описанию я лично не понял.
Что вы подразумеваете под
Цитата
Сергей М. написал:
2. Всей файлы-справочники (вместо того чтобы использовать ВПР) связать через Access, но при этом не загружать их в сам Access, а только создать связанные таблицы.
?

Если не хранить данные в базе Access, то  выигрыш минимальный
1. Централизовано можно перелинковать таблицы.
2. Используя SQLQuery, можно готовить готовые вьюхи, которые как таблицы можно использовать для дальнейшей обработки, правда при этом база может расти, из-за промежуточных результатов.
но при этом, если бездумно внести изменений в исходные таблицы, то упадет все и это нужно блокировать, и судя по всему на уровне доступов к файлам.

А вот хранение там позволит работать и быстрее и более продуктивно работать совместно. не всегда только объем данных  толкает на уход от Excel. Возьмите простой случай слияния Word. Сослались на Excel - одновременно менять исходные данные или дополнять нельзя, и не важно сколько там записей. 1 или тысячи.
Изменено: БМВ - 22.11.2020 09:37:53
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал: Централизовано можно перелинковать таблицы.
Ничего не понял. Что это за решение? Говорите со мной как с новичком в программировании, я не понимаю.

Цитата
БМВ написал: 2. Используя SQLQuery, можно готовить готовые вьюхи, которые как таблицы можно использовать для дальнейшей обработки, правда при этом база может расти, из-за промежуточных результатов.
Что такое готовые вьюхи? Запросы в режиме "только подключение"? Вы предлагаете отчетность конструировать объединением запросов PQ?
Например, один запрос вытащил столбец номеров договора, второй запрос вытащил реквизиты подрядчика, третий запрос вытащил % выполнения объемов
И объединением запросов делать отчет?
Я хотел обойти это путем связей между файлами в Access. Т.е. вместо того чтобы создавать десятки разных запросов к разрозненным файлам-справочникам создать один единственный PQ-запрос к Access и далее отобрать нужные столбцы для отчета.
 
Про программирование я даже не начинал.

Если источники данных это набор таблиц Excel, то их можно линковать в базе Access. Там будет только ссылка и каждый раз данные будут тянутся из excel файлов. Если требуется сменить файл, то просто меняется линк на другой файл. Таблица остается с тем же именем но данные будут браться из другого файла.  

При обращении к базе access любое Query созданное и сохраненное в ней при подключении не отличается от таблици. А сам запрос может быть очень сложным и сам делать львиную долю обработки не говоря о связи таблиц по идентификаторам. это можно назвать вьюхой от View. Они сделаны, стабильны и используются для прямых отчетов или для формирования промежуточных таблиц.
Например, один запрос вытащил столбец номеров договора, второй запрос вытащил реквизиты подрядчика, третий запрос вытащил % выполнения объемов - может быть сразу сделан через SQL

Я ничего не предлагаю ибо предлагать вслепую - не мое правило.  Пока вы не ответили на вопрос заданный мной выше, обсуждать бесполезно.
По вопросам из тем форума, личку не читаю.
 
Сергей М., почти уверен, что можно обойтись только Power Query
 
Цитата
БМВ написал:
Пока вы не ответили на вопрос заданный мной выше, обсуждать бесполезно.
Под
" Все файлы-справочники (вместо того чтобы использовать ВПР) связать через Access, но при этом не загружать их в сам Access, а только создать связанные таблицы."
я имел в виду файлы-справочники в xlsx-формате подключить как связанные таблицы в Access, затем открыть "Схема данных" и соединить все файлы через общий идентификатор. После чего закрыть Access и никогда не использовать, пока не потребуется произвести изменения в Схеме данных.
К файлу Access делать запросы Power query из файла эксель где нужно будет построить отчетность.
Таким образом Access как бы заменит мне множество формул "ВПР" - он свяжет все файлы по общему идентификатору и в принципе больше ничем заниматься не будет.

Возможно это вы и имели в виду под "перелинкованием".
Т.е. сама БД не будет находиться в Access. Access нужен только расставить связи между файлам, тогда, по моей задумке, запрос PQ я смогу направить централизованно через файл Access.

Т.е.  по сути мне нужна модель данных, построенная на основе файлов ексель к которой можно подключаться через PQ запросы каким-нибудь простым способом (в идеале без утилит-посредников).
Модель данных Pover pivot, как я понял, для моих целей не сгодится, так как мало того что к Pover pivot нельзя конектится через PQ запросы, так еще и отчетность на его основе строить можно с ограничениями - только в виде сводных таблиц, а мне отчетность нужно строить в виде множества склеенных столбцов из разных справочников (нужна не просто сводные таблицы, а более изощренные варианты таблиц).
Изменено: Сергей М. - 22.11.2020 21:17:17
 
Цитата
Сергей М. написал:
затем открыть "Схема данных" и соеденить все файлы через общий идентификатор.
И что это вам даст? Схема данных нужна для настройки ограничений, а не как в DAX определения связей. Join в Power Query в любом случае придётся делать, он сам не догадается. А раз так, то каков смысл в настройке схемы данных?
Изменено: Андрей VG - 22.11.2020 21:17:34
 
Я вот чувствовал что есть подвох, но не думал что такой очевидный. Эта схема помогает при построении запросов в самом Access, но не внешним инструментам.
По вопросам из тем форума, личку не читаю.
 
Цитата
Андрей VG написал: И что это вам даст? Схема данных нужна для настройки ограничений, а не как в DAX определения связей, Join в Power Query в любом случае придётся делать, он сам не догадается. А раз так, то каков смысл в настройке схемы данных?
Я говорю вот про эту схему данных. И сдается мне в access он не для настройки ограничений,  а для настройки полей соответсвия двух и более связанных таблиц

По вашему выходит файлы эксель нет смысла связывать таким образом?
Я надеялся сделать таким образом что-то на подобии модели данных к которой буду обращаться запросами, модель данных на основе файлов эксель, которые смогут редактировать сотрудники без познаний в области БД.
Если я закачаю ексель базу в access, то из 7-и человек с ней смогут работать только 2-е. Один из которых я. Вот вам и смысл - не хочу чтобы двое сотрудникова работали за 7-х. Эдакое компромиссное решение хотел изобрести, дружественное к обычному пользователю без глубоких познаний и уж тем более в СУБД.
 
Цитата
БМВ написал: Эта схема помогает при построении запросов в самом Access, но не внешним инструментам.
И где же правда, а вот мне удалось построить запрос PQ к простенькой модели данных Access, что на картинке выше, причем все три таблицы хранятся исключительно в эксель-файлах и из Access их даже не удается редактировать.
Конкретно мне удалось к таблице 180601 добавить столбец "номер КС" из таблицы "Спр_ID_KS" - для этого в пошаговом мастере PQ пришлось в содержании крайнего столбца который автоматически присоединился к таблице 180601 (столбец "Спр_СПП_элемент")  отметить галочкой таблицу "Спр_ID_KS", затем, на следующем шаге, отметить галочкой нужный мне столбец в этой таблице).
Мне кажется такой способ не лишен преимуществ - не пришлось таблицы муторно конектить друг с другом по общему столбцу, нужно лишь галочками отмечать нужные столбцы из всех таблиц (к сожалению в несколько шагов).  Хорошо будет так работать, наверное, когда у меня будет много объединенных справочников.
По этой причине не могу согласиться, что способ не помогает внешним инструментам. Приятная работа с БД разве это не помощь?
И в качестве бонуса - я хочу строить БД по принципу один файл хранит один столбец данных и один столбец с идентификатором - такая структура не подвержена удалению столбцов пользователями. Так как удалять единственные столбцы (также как и переименовывать) никто в здравом уме в отделе не станет, так как залетный пользователь не воспринимает файлы-столбцы как полноценные таблицы, в общем наверняка не захочет там что-то улучшать.

Наверное, я что-то не учитываю. Не может же быть все так просто в таком сложном деле. В чем может быть подвох?
 
Цитата
Сергей М. написал:
И сдается мне в access он не для настройки ограничений,  а для настройки полей соответсвия двух и более связанных таблиц
Вы путаете это со структурой отношений в Power BI и Power Pivot, мягкое с тёплым. Достаточно было посмотреть свойства связи, чтобы убедиться - это именно про ограничения.
Лучше прислушайтесь к медведю. Схема данных позволяет строить запросы с автоматическим назначением связей в среде разработки Access (движком можно пользоваться и без неё, достаточно скачать Access Runtime или Engine). А Power Query без разницы откуда брать данные из таблиц или запросов. В общем - почитайте книгу по Access любую для начала. Не вижу смысла в теме устраивать пересказ того, что уже многократно описано в существующей литературе.
 
Андрей VG, при запросе PQ к файлу Access таблицы не пришлось коннектить по общему столбцу. Это говорит о том, что запрос PQ учитывает связь по идентификатору даже если access использует внешние Excel источники. Проверьте. Ну либо я не понимаю, что вы хотите до меня донести. Ну не могут же таблицы сами связаться по себе в PQ, значит есть что-то общее в Access со связями в модели данных в Power BI.
Изменено: Сергей М. - 22.11.2020 23:24:05
 
Цитата
Сергей М. написал:
что вы хотите до меня донести.
Power Query не будет создавать что-либо только на основании того, что вы в базе данных Access настроите схему данных - всего лишь это пытаюсь до вас донести. С точки зрения Power Query - нет никакой разницы - есть схема данных или её нет. Table.Join или Table.NestedJoin в Power Query вы будете делать в любом случае при запросе к БД.
 
Андрей VG, такую возможность PQ я описал тут  стоит только подключиться к файлу Access и отметить галочками нужные таблицы как PQ сам автоматически создает крайний столбец из которого настройками извлекаются столбцы других таблиц. При этом не приходится коннектить таблицы через общий столбец. Проверьте сами. Самое примечательное в этом то, что не обязательно чтобы таблицы были в самом Access, все работает даже при внешнем подключении Access к внешним Excel.
Изменено: Сергей М. - 22.11.2020 23:44:59
 
Дошло о чём вы. Вы действительно считаете это удобным? Хотя - на вкус и цвет... Я бы лучше запросы в базе сразу наделал, а уж потом только в Power Query...
 
Андрей VG, стыковка столбцов одной таблицы в 2000 строк с другой таблицей из тех же 2000 строк по общему идентификатору (а точнее сказать "развертывание" одного отмеченного галочкой столбца из второй таблицы справа от первой таблицы) происходит описанным методом ну очень долго, если вообще происходит.
Положительный результат сегодня я смог достичь только для данных, необходимых для предварительного просмотра внутри мастера шагов PQ, а при  попытке загрузить все тоже самое в таблицу в эксель начинается бесконечное фоновое обновление запроса (ждал завершения минут 10, но так и не дождался, пришлось прервать процесс, это же не возможно долго). Сдается мне, что описанная мною идея в лучшем случае теория, не практика. Либо я не правильно что-то сделал из-за чего инициировался какой-то долгий процесс в работе. Мне не удалось разобраться в причинах неудачи.
Теперь передо мной задача все та же. Нужно понять какая она, моя идеальная БД.
Смотрю теперь на Power BI с его богатой возможностью аналитики и возможностью делать связи в модели данных.
Изменено: Сергей М. - 23.11.2020 11:11:53
Страницы: 1
Наверх