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

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

• Настройка загрузки данных в PowerBI.

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