Время прочтения: 4 мин.
Power BI – удобный инструмент аналитики данных. Расскажу о способе выгрузки отчётов в файлы с сервера на сетевой/локальный диск для их архивации или передачи. С помощью PowerShell и учётной записи в репозитории Power BI с доступом на чтение БД [PowerBI_CFG] эту задачку решить довольно легко.
Какие бывают типы отчётов Power BI?
Основной вид отчётов Power BI – это файлы *.pbix. Эти отчёты открываются и правятся в приложении «Power BI Desktop», а файлы можно загрузить на сервер Power BI через веб-интерфейс. Файлы хранят загруженные наборы данных, запросы, параметры подключений к источникам данных.
Второй тип отчётов – RDL. Создаются при помощи MS Report Builder, входящий в пакет MS Office.
Как хранятся отчёты на сервере Power BI?
Все данные системы Power BI хранятся в СУБД MS SQL Server, являющейся репозиторием Power BI, данные файлов отчётов содержатся в полях системных таблиц. В таблице [PowerBI_CFG].[dbo].[Catalog].[Content] находятся файлы отчётов типа PBIX. В таблице [PowerBI_CFG].[dbo].[CatalogItemExtendedContent].[Content] находятся файлы отчётов типа RDL, RDS, RSD.
Соединение с сервером MS SQL
Получить данные отчётов в файлы можно скриптом на PowerShell. Для выполнения таких скриптов на компьютере должна быть установлена консоль PowerShell.
Соединение с СУБД репозитория Power BI устанавливается командами:
$conn.ConnectionString = "Persist Security Info=False;User ID= $user;Password= $pass;Server= $SQL_Reportserver;Initial Catalog= $PBIRS_DB;";
$conn.Open();
Переменные должны содержать логин и пароль в БД [PowerBI_CFG], а также переменной download_folder необходимо присвоить сетевой или локальный путь для скачивания отчётов:
# Set the variables
[string] $SQL_Reportserver = "V-MIS-12R2-09.CA.SBRF.RU";
[string] $PBIRS_DB = "PowerBI_CFG";
[string] $download_folder = "";
Получение данных отчётов
Данные отчётов можно получить SQL запросом, а затем в цикле по каждой строке сохранять данные в файлы, составляя расширение на основе типа отчёта. Для получения отчётов из конкретной папки можно использовать фильтр по полю Path SQL запрос:
$sql = "SELECT
c.ItemID
, c.Name
, c.Path
, c.Type
, TypeDescription =
CASE c.Type
WHEN 2 THEN 'SSRS Report'
When 13 Then 'Power BI Report'
-- Not tested ! Code works
WHEN 1 THEN 'Folder'
WHEN 3 THEN 'Resources'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
-- 11 ... KPI
ELSE 'Other Type (check/revisit)'
END
, ISNULL(cc.ContentType,'SSRS') as ContentType
, RDL_BinaryContent = IIF(c.Type <> 13, CONVERT(varbinary(max), c.[Content]), null)
, PBIX_BinaryContent = IIF(c.Type = 13, CONVERT(varbinary(max), cc.[Content]), null)
, cc.ContentType
FROM dbo.[Catalog] c
LEFT JOIN dbo.[CatalogItemExtendedContent] cc ON c.ItemID = cc.ItemId AND cc.ContentType = 'CatalogItem'
WHERE c.TYPE IN (2, 13)
)
";
В рамках задачи интересны типы объектов PBIX и RDL, им соответствуют типы TYPE: 2, 13, что установлено опытным путём (официальная документация по типам объектов Power BI отсутствует).
Запись отчёта Power BI в файл
Далее я покажу, как выглядит запись данных в файлы скриптом PowerShell. Для решения задачи достаточно скопировать SQL-запрос, установить соединение и выполнить данный цикл:
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $conn;
$rdr = $cmd.ExecuteReader();
# $invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Loop
While ($rdr.Read())
{
Try
{
Write-Output "------------------------------------------"
$ItemName = $rdr.GetString(1);
Write-Output "Reading $ItemName"
# foreach ($invalid in $invalids)
# { $name = $name.Replace($invalid, "-"); }
# $rdr.GetInt32(3)
$ItemFileName = ""
If ($rdr.GetInt32(3) -eq 2) { $ItemFileName = $ItemName + ".rdl"; }
ElseIf ($rdr.GetInt32(3) -eq 13) { $ItemFileName = $ItemName + ".pbix"; }
if ($ItemFileName -ne "")
{
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0} ... " -f $ItemFileName);
$ItemName_Path = [System.IO.Path]::Combine($download_folder, $ItemFileName);
#$ItemName_Path
$fs = New-Object System.IO.FileStream ($ItemName_Path), Create, Write;
$BinaryWriter = New-Object System.IO.BinaryWriter($fs);
# Read Content
if ($rdr.GetString(4) -eq "Power BI Report") {
$BinaryContent = $rdr.GetSqlBinary(7).Value;
}
else
{
$BinaryContent = $rdr.GetSqlBinary(6).Value;
}
$BinaryWriter.Write($BinaryContent, 0, $BinaryContent.Length);
$BinaryWriter.Flush();
$BinaryWriter.Close();
$fs.Close();
}
else
{
Write-Output $ItemFileName "... !!! NOT exported"
Write-Output $rdr.GetInt32(3)
Write-Output $rdr.GetString(4)
}
}
Catch
{
Write-Output ($_.Exception.Message)
}
Finally
{
$fs.Dispose();
}
}
Вывод
На этом все. Я показал способ, который позволяет массово скачать отчёты Power BI. Хотелось бы обратить внимание на возможность проблемы скачивания отчётов, где данные занимают более 500 МБ. Однако, такие файлы не являются BI-отчётами, так как противоречат BI-подходам. Желаю удачи в работе с Power BI.