Выгрузка данных отчетов из 1С в Power BI
Полный цикл выгрузки данных.
-
Создаем таблицу для хранения данных на SQL сервере.
-
Из обработки в 1С подключаемся к серверу и к таблицам.
-
Удаляем старые данные из таблицы на сервере для ввода новых. (Так происходит обновление данных).
-
Отправляем запросы удаления данных на SQL сервер.
-
Получаем данные из запроса 1С.
-
Формируем данные для отправки и отправляем.
-
В 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.
