Выгрузка данных отчетов 1С в Power BI для аналитиков

01.08.22

Интеграция - Внешние источники данных

Как я решил проблемы выгрузки данных в Power BI для нашего аналитика. Запрос был на то, чтобы грузить результат уже готовых отчетов 1С и меньше вычислительных действий производить на стороне Power BI. Некоторые из отчетов имели в себе результирующие данные в количестве от 300 000 до 500 000 строк за каждый месяц. Часть таблиц должна была выгружаться по регламенту каждый день, вторая часть выгружается только по потребности. Обработка в настоящее время безошибочно работает на 1С:Предприятие 8.3 (8.3.20.1838), конфигурация Управление торговлей, редакция 11.1 (11.1.4.14).

Скачать файлы

Наименование Файл Версия Размер
Выгрузка данных отчетов 1С в Power BI для аналитиков.:
.epf 13,05Kb
4
.epf 1.0 13,05Kb 4 Скачать

Выгрузка данных отчетов из 1С в Power BI

       Полный цикл выгрузки данных. 

  1. Создаем таблицу для хранения данных на SQL сервере.
  2. Из обработки в 1С подключаемся к серверу и к таблицам. 
  3. Удаляем старые данные из таблицы на сервере для ввода новых. (Так происходит обновление данных).
  4. Отправляем запросы удаления данных на SQL сервер.
  5. Получаем данные из запроса 1С.
  6. Формируем данные для отправки и отправляем. 
  7. В Power BI настраиваем загрузку данных из SQL сервера и получаем готовые данные. 

 

  • Создаем таблицу для хранения данных на SQL сервере.

Самостоятельно или через системного администратора создаем базу данных "PowerBI" и создаем нужные нам таблицы. 
Правила и принципы создания таблиц на SQL сервере можно посмотреть тут:
Как создать таблицу в SQL.

Очень важно уделить внимание названию таблиц, так как потом названия таблиц будут отображаться у аналитика в программе PowerBI, и лучше если таблицы будут называться логично но латиницей. 

  • Из обработки в 1С подключаемся к серверу и к таблицам.
Функция ВыполнитьПодключениекСерверу()Экспорт

	Connection = Новый COMОбъект("ADODB.Connection");
	Провайдер = "SQLOLEDB"; // имя провайдера ODBC, чаще всего указывается именно такой
	СРВ = "srv1c-sql.*****.local";
	БД = "PowerBI";
	УИД = "PowerBI";
	ПВД = "111-111111";
	СтрокаПодключения = "Provider=" + Провайдер + "; Persist Security Info=False; Server=" + СРВ + ";Database=" + БД + ";UID=" + УИД + ";PWD=" + ПВД + ";";
	Connection.CommandTimeOut = 0;
		
Connection.Open(СокрЛП(СтрокаПодключения)); 
Возврат Connection;
	
КонецФункции
  • Удаляем старые данные из таблицы на сервере для ввода новых. (Так происходит обновление данных)

Удаление данных происходит двумя способами, а точнее по двум сценарием. 
Первый: если таблица содержит в себе до 50 000 записей (справочники партнеры, номенклатура, склады, регионы и так далее), то таблицу можно удалить полностью и полностью загрузить. 
Второй сценарий: удаление записей по определенному периоду. Такую процедуру можно использовать, когда таблица содержит от 50 000 записей и обновление требуется только текущего дня или недели или месяца (данные отчета продажи, остатки, обороты, выручка).
Приведу пример второго варианта работы с таблицей. 

  • Отправляем запросы удаления данных на SQL сервер.

Сначала проверяем, есть ли подключение к серверу.
Затем, если период не выбран, то устанавливаем в качестве границ удаления начало и конец текущего месяца.
Формируем текст запроса для SQL с указанием названия таблицы и параметрами периода.
Выполняем отправку нашего запроса. Ответ от сервера мы не получаем, потому что для проверки можно открыть SQL Server Management Studio и проверить таблицу. 

Процедура ПроверитьЗаполненностьТаблицыПродажи(НачалоПериода,КонецПериода) Экспорт
	
	Connection = ВыполнитьПодключениекСерверу();

	Если НЕ ЗначениеЗаполнено(НачалоПериода) или НачалоПериода = Дата("00010101") Тогда
		НачалоПериода   	= НачалоМесяца(ТекущаяДата());
		КонецПериода 	    = КонецМесяца(ТекущаяДата());	
	КонецЕсли;
	
	Если Connection = Неопределено Тогда
	    Возврат;
	КонецЕсли;
	RecordSet                = Новый COMОбъект("ADODB.Recordset");
	Command                  = Новый COMОбъект ("ADODB.Command");
	Command.ActiveConnection = Connection;
	ЗапросНаСайте =
	"DELETE FROM dbo.total_sales1 WHERE date_sales >=CAST('"+Формат(НачалоПериода,"ДФ=гггг")+Формат(НачалоПериода,"ДФ=ММ")+Формат(НачалоДня(НачалоПериода),"ДФ=дд")+"' AS date) "
	"and date_sales <=CAST('"+Формат(КонецПериода,"ДФ=гггг")+Формат(КонецПериода,"ДФ=ММ")+Формат(НачалоДня(КонецПериода),"ДФ=дд")+"' AS date)";
	
	Command.CommandTimeout = 100;
	Command.CommandText    = ЗапросНаСайте;
	Command.CommandType    = 1;

	Connection.Close();
КонецПроцедуры
  • Получаем данные из запроса 1С.

После удаления данных таблицы переходим к её заполнению. Процедуру выгрузки данных поместил в "Попытку", что бы в случае возникновения проблем понять, что не так.

	Попытка
		RecordSet = Command.Execute();
		ВыгрузитьСписокПродаж(НачалоПериода,КонецПериода);	
		//ЗаписьЖурналаРегистрации(НСтр("ru = 'Выгрузка продажи Power'",),
		//УровеньЖурналаРегистрации.Информация, , ,НСтр("Продажи выгрузились'",));
		//Connection.Close();
		//Возврат;
	Исключение
		ЗаписьЖурналаРегистрации(НСтр("ru = 'Выгрузка продажи Power не прошла'", ),
		УровеньЖурналаРегистрации.Информация, , ,ЗапросНаСайте + " "+ОписаниеОшибки(),);
		Connection.Close();
		Возврат;
	КонецПопытки;

Получаем выгрузку из запроса и передаем его дальше для обработки каждой строки. Пример получения данных и выгрузки покажу на номенклатуре. 
В цикле по каждой номенклатуре начинаем формировать нашу строку запроса. Если данные еще не были отправлены и количество строк в запросе к SQL = 0, тогда в текст запроса добавляем шапку запроса и идем дальше.
В строку добавляем временные параметры по количеству строк таблицы.
Заменяем наши временные параметры значениями из запроса и добавляем к исходной строке запроса. 

Выгружать строки можно как по одной, так и по несколько строк в одном запросе. Для этого мы и считали, сколько строк уже обработано. Если количество обработанных строк достигло нужного количества, то мы обрабатываем запрос для безошибочного выполнения (удаляем лишнюю запятую в конце запроса) и отправляем запрос на сервер. И так до конца цикла.  

Процедура ВыгрузитьСписокНоменклатур()
    Connection = ВыполнитьПодключениекСерверу(); // одна из трех вышеописанных функций
    Если Connection = Неопределено Тогда
        Возврат;
    КонецЕсли;
    RecordSet = Новый COMОбъект("ADODB.Recordset");
    Command = Новый COMОбъект ("ADODB.Command");
    Command.ActiveConnection = Connection;
	
	СписокНоменклатур = ПолучитьСписокНоменклатур();
	
	СчетчикСтрок = 0;
	ДанныеОтправлены = Ложь;
	Для Каждого Номенк из СписокНоменклатур Цикл
		Если ДанныеОтправлены Тогда
			СчетчикСтрок = 0;
			ДанныеОтправлены = Ложь;
		КонецЕсли;
		Если СчетчикСтрок = 0 Тогда
			ЗапросДобавленияОстатков = 
			"INSERT
			|INTO dbo.nomenclature_table (GUID,nomenclature_name,group_1)
			|Values ";
		КонецЕсли;
		
		ЗапросДобавленияОстатков = ЗапросДобавленияОстатков + "('@1param','@2param','@3param'),";
		
		ЗапросДобавленияОстатков = СтрЗаменить(ЗапросДобавленияОстатков,"@1param",Номенк.Ссылка.ГУИД);
		ЗапросДобавленияОстатков = СтрЗаменить(ЗапросДобавленияОстатков,"@2param",СтрЗаменить(Номенк.Ссылка,"'"," "));
		ЗапросДобавленияОстатков = СтрЗаменить(ЗапросДобавленияОстатков,"@3param",Номенк.Поле1);
		
		СчетчикСтрок = СчетчикСтрок + 1;
		
		Если СчетчикСтрок = 5 Тогда
			ЗапросКSQL = Сред(ЗапросДобавленияОстатков,0,СтрДлина(ЗапросДобавленияОстатков)-1);
			Command.CommandText = ЗапросКSQL;
			Command.CommandType = 1;
			Попытка
				RecordSet = Command.Execute();
				ЗапросДобавленияОстатков = "";		
				//ЗаписьЖурналаРегистрации(НСтр("ru = 'Выгрузка Power'", ),
				//УровеньЖурналаРегистрации.Информация, , ,НСтр("ru = 'Выгрузка Прошла'", ));
				ДанныеОтправлены = Истина;
			Исключение
				//Сообщить ("Не удалось экспортировать остатки товара: " + Документ.Номенклатура +" по партнеру "+Документ.Партнер);
				//Сообщить (ОписаниеОшибки());
				//Сообщить(Документ.Количество);
				ЗаписьЖурналаРегистрации(НСтр("ru = 'Выгрузка Power номенклатура Ошибка'", ),
				УровеньЖурналаРегистрации.Информация, , ,ОписаниеОшибки()+" "+ЗапросКSQL,);
				Connection.Close();
				Возврат;
			КонецПопытки;
		КонецЕсли;
	КонецЦикла;
	Connection.Close();
	ЗаписьЖурналаРегистрации(НСтр("ru = 'Выгрузка Power Номенклатура завершена'", ),
	УровеньЖурналаРегистрации.Информация, , ,ТекущаяДата(),);

КонецПроцедуры 

Если цикл завершился, но количество сформированных строк меньше, чем необходимо для отправки, можно после цикла добавить дополнительную проверку с отправкой запроса. 

Если НЕ ДанныеОтправлены Тогда
		ЗапросКSQL = Сред(ЗапросДобавленияОстатков,0,СтрДлина(ЗапросДобавленияОстатков)-1);
		Command.CommandText = ЗапросКSQL;
		Command.CommandType = 1;
		Попытка
			RecordSet = Command.Execute();
			ЗапросДобавленияОстатков = "";		
			//ЗаписьЖурналаРегистрации(НСтр("ru = 'Выгрузка Power'", ),
			//УровеньЖурналаРегистрации.Информация, , ,НСтр("ru = 'Выгрузка Прошла'", ));
			ДанныеОтправлены = Истина;
		Исключение
			//Сообщить ("Не удалось экспортировать остатки товара: " + Документ.Номенклатура +" по партнеру "+Документ.Партнер);
			//Сообщить (ОписаниеОшибки());
			//Сообщить(Документ.Количество);
			ЗаписьЖурналаРегистрации(НСтр("ru = 'Выгрузка Power номенклатура Ошибка'", ),
			УровеньЖурналаРегистрации.Информация, , ,ОписаниеОшибки()+" "+ЗапросКSQL,);
			Connection.Close();
			Возврат;
		КонецПопытки;
КонецЕсли;

  Все. Данные отправлены на SQL Server. Это можно проверить через SQL Server Management Studio. Осталось только получить данные в Power BI. 

А дальше уже работа аналитика по настройке и связям таблиц в Power BI.

Обработка Power BI выгрузка данных из загрузка в power bi интеграция с

См. также

Автоматическая многопоточная выгрузка данных 1С 8.3 в БД Clickhouse (для работы с данными 1С в BI-системах)

Внешние источники данных Платформа 1С v8.3 Управляемые формы Анализ и прогнозирование Конфигурации 1cv8 Узбекистан Беларусь Кыргызстан Молдова Россия Казахстан Платные (руб)

Готовое решение для автоматизированной выгрузки данных из 1С 8.3, а также MS Excel в базу данных ClickHouse для работы с данными 1С в Yandex Datalens, Visiology, Apache Superset (и не только) - "Экстрактор данных 1С в BI". Решение отлично работает со всеми типовыми (и не только) конфигурациями 1С 8.3 для управляемых форм. Gозволяет автоматизировать работу бизнес-аналитика по ежедневной выгрузке данных из 1С в БД ClickHouse для последующей работы с этой БД в Yandex Datalens/ Система полностью автоматизирует работу с хранилищем данных в БД Clickhouse. Не надо быть программистом, чтобы одной кнопкой получать любые данные из 1С в Yandex Datalens

160000 руб.

15.11.2022    9907    6    23    

18

Перенос данных из Парус 10 в ЗГУ ред.3

Внешние источники данных Кадровый учет Файловый обмен (TXT, XML, DBF), FTP Обмен между базами 1C Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 10 учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

60000 руб.

05.10.2022    8160    4    8    

7

Перенос начальных остатков из Парус 7.71 в БГУ

Внешние источники данных Взаиморасчеты Учет ОС и НМА Логистика, склад и ТМЦ Бюджетный учет Платформа 1С v8.3 Бухгалтерский учет 1С:Бухгалтерия государственного учреждения Государственные, бюджетные структуры Россия Бюджетный учет Платные (руб)

Перенос словарей и начальных остатков из ПП Парус-Бухгалтерия Бюджет 7.71 в 1Сv8 БГУ2. Заполнение словарей и документов по вводу начальных остатков. Не требуется установка ПП Парус7. Возможна дозагрузка. Внешняя обработка с открытым кодом.

9000 руб.

08.12.2011    79804    119    121    

139

Перенос данных из Парус 7.хх в ЗГУ ред.3

Внешние источники данных Зарплата Бюджетный учет Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 7.хх учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

24000 руб.

24.04.2017    46882    89    149    

82

Обмен с СУФД (Федеральным казначейством) для Бухгалтерии предприятия 3.0

Внешние источники данных Платформа 1С v8.3 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Платные (руб)

Комплект обработок для обмена СУФД и Бухгалтерии 3.0. Содержит две обработки: для переноса платежных поручений из БП 3.0 в СУФД Федерального казначейства и загрузки выписок из СУФД в БП 3.0.

2400 руб.

30.03.2023    5492    14    2    

15

Импорт документов из розничной программы учета (ТрейдФарм, Манускрипт Солюшн, Е-Фарма и др.) в Бухгалтерию 3.0 с формированием книги учета доходов и расходов при УСН и бухгалтерского и налогового учета при ОСНО

Розничная торговля Внешние источники данных Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 Бухгалтерский учет 1С:Бухгалтерия 3.0 Фармацевтика, аптеки Россия Бухгалтерский учет Налоговый учет Платные (руб)

Представлена обработка для импорта документов из розничной программы учета товара в торговых точках и аптеках, необходимых для ведения бухгалтерского и налогового учёта, в Бухгалтерию 3.0. При загрузке данных в бухгалтерию переносятся документы поступлений, реализации, возвратов и перемещений в торговых точках и формируются все бухгалтерские и налоговые отчеты с учетом этих документов для любой системы налогообложения. Обработка позволяет загрузить данные из ПО ТрейдФарм. Имеются варианты для других розничных программ (ПО Manuscript Solution, Е-Фарма и S-Market).

18000 руб.

09.04.2020    17311    10    8    

6

Загрузка спецификаций в УНФ из системы Базис-мебельщик

Производство готовой продукции (работ, услуг) Внешние источники данных Платформа 1С v8.3 1С:Управление нашей фирмой 1.6 Лесное и деревообрабатывающее хозяйство Россия Управленческий учет Платные (руб)

Обработка предназначена для загрузки файлов, выгруженных из системы Базис-мебельщик, в справочник "Спецификации" для последующих процессов учета и диспетчирования полуфабрикатов и изделий.

6000 руб.

24.06.2021    17671    45    41    

22
Оставьте свое сообщение