Выгрузка данных отчетов 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
5
.epf 1.0 13,05Kb 5 Скачать

Выгрузка данных отчетов из 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    11317    10    47    

23

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

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

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

84000 руб.

19.08.2020    21151    16    0    

18

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

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

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

84000 руб.

07.06.2021    12355    1    0    

2

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

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

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

24000 руб.

24.04.2017    47496    92    153    

84

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

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

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

60000 руб.

05.10.2022    8649    5    8    

8

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

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

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

24000 руб.

16.11.2018    29539    18    31    

19

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

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

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

9000 руб.

08.12.2011    80558    123    123    

143

Загрузка данных из F3 TAIL 3 (еФарма 2) в 1С: Бухгалтерия 3.0 (базовая, ПРОФ, КОРП)

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

Внешняя обработка загрузки данных из файла-выгрузки, сформированного в программе F3 TAIL версии 3.4 (и выше) или еФарма версии 2.1, в базу конфигурации 1С: Бухгалтерия предприятия 8, ред. 3.0 (базовая, ПРОФ, КОРП).

13200 руб.

19.12.2016    44382    81    101    

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