Время прочтения: 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.