PowerBi On-Premise Upload

<# https://github.com/microsoft/ReportingServicesTools
Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1 #>

# Code By SB 2019
$ReportServerURI = 'http://localhost:8080/Reports'          # Input Local path of powerbi file
$filePath = "C:\Users\Export_PBI_SSRS\Inventory.pbix"       # Input Local path of powerbi file
$PBIxfileName = "Inventory"                                 # INput your Powerbi File Name
$FolderName ='TestingByMax'                                 # Input PowerBI server Folder Name Where you wann to deploy
$Username ='Username'
$password ='Password'                          
$ReportServerName ='DBServerName'                           #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportServer'                      #input PowerBi Database Name

$ConnectionString ='data source=Client01\SQL2019;initial catalog=Client_SB_1'  # input New Connection String / Client ConnectionString

$FolderLocation = '/'
$FolderPath = $FolderLocation + $FolderName

write-host "Deployment Started ..." -ForeGroundColor Yellow 
$session = New-RsRestSession -ReportPortalUri $ReportServerURI
Write-RsRestCatalogItem -WebSession $session -Path $filePath.FullName -RsFolder $folderPath -Description $Description -Overwrite
$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.AuthType = ‘Windows'
$dataSources[0].DataModelDataSource.Username = $ConnectionString 
$dataSources[0].DataModelDataSource.Secret = $password

Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

$ID =  $dataSources[0].Id

#$Query = " Update [DataModelDataSource] SET ConnectionString = Username From [dbo].[DataModelDataSource] Where DataSourceID ='" + $ID  + "' "
#Invoke-Sqlcmd -Query $Query -ServerInstance CPMSUNRSQL17\CPMSRINST17 -Database ReportServerPowerBI

$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.Username = $Username
$dataSources[0].DataModelDataSource.Secret = $password
Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

write-host "Deployment Done . . ." -ForeGroundColor Green

Upload a whole Folder

<# https://github.com/microsoft/ReportingServicesTools
Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1 #>

$SourceDirectory = 'C:\Users\Export_PBI_SSRS'
# Code By SB 2019
$ReportServerURI = 'http://localhost/Reports' # Input Local path of powerbi file
#$filePath = "C:\Users\chenc89\Desktop\jobdata\Develop\Powerbi_Owner\Export_PBI_SSRS\Inventory.pbix"     # Input Local path of powerbi file
$FolderName ='TestingByMax/GReports'       # Input PowerBI server Folder Name Where you wann to deploy
$Username ='UserName'
$password ='Password'                          
$ReportServerName ='PBIDBServeName'                #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportDBName'           #input PowerBi Database Name 
$ConnectionString ='data source=Client01\SQL2019;initial catalog=Client_SB_1'  # input New Connection String / Client ConnectionString
$FolderLocation = '/'
$FolderPath = $FolderLocation + $FolderName


foreach($filePath in Get-ChildItem $SourceDirectory -Filter *.pbix)
{
$PBIxfileName = $filePath -replace '.pbix','' # INput your Powerbi File Name
Write-host ""

write-host $PBIxfileName -ForegroundColor Green 
try{
    write-host "Deployment Started ..." -ForeGroundColor Yellow 
    $session = New-RsRestSession -ReportPortalUri $ReportServerURI
    Write-RsRestCatalogItem -WebSession $session -Path $filePath.FullNane -RsFolder $folderPath -Description $Description -Overwrite
    $datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
    $dataSources[0].DataModelDataSource.AuthType = ‘Windows'
    $dataSources[0].DataModelDataSource.Username = $ConnectionString 
    $dataSources[0].DataModelDataSource.Secret = $password

    Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

    $ID =  $dataSources[0].Id

    #$Query = " Update [DataModelDataSource] SET ConnectionString = Username From [dbo].[DataModelDataSource] Where DataSourceID ='" + $ID  + "' "
    #Invoke-Sqlcmd -Query $Query -ServerInstance CPMSUNRSQL17\CPMSRINST17 -Database ReportServerPowerBI

    $datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
    $dataSources[0].DataModelDataSource.Username = $Username
    $dataSources[0].DataModelDataSource.Secret = $password
    Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

    write-host "Deployment Done . . ." -ForeGroundColor Green 
}catch [System.IO.IOException] {
     $msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
     Write-Error msg
}catch [System.Web.Services.Protocols.SoapException]{
 if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
            {
                Write-Error "Report: $reportName already exists." 
            }
            else
            {
                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
}

}

Power Bi Clouid Upload

import requests
from requests_toolbelt.multipart.encoder import MultipartEncoder

groupId = "your workspace ID"
reportName = "reportName that you choose to appear on the server"
accessToken = "xx"

url = 'https://api.powerbi.com/v1.0/myorg/groups/' + groupId + '/imports?datasetDisplayName=' + reportName
headers = {
    'Content-Type': 'multipart/form-data',
    'authorization': 'Bearer ' + accessToken
}
file_location = '/xx.pbix'
# you need this dictionary to convert a binary file into form-data format
# None here means we skip the filename and file content is important 
files = {'value': (None, open(file_location, 'rb'), 'multipart/form-data')}

mp_encoder = MultipartEncoder(fields=files)

r = requests.post(
    url=url,
    data=mp_encoder,  # The MultipartEncoder is posted as data, don't use files=...!
    # The MultipartEncoder provides the content-type header with the boundary:
    headers=headers
)

PublishPBIXFile.ps1

(Install不了可以直接執行)

# https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1

<#PSScriptInfo

.VERSION 1.0.0.1

.GUID 0b89f8fb-6de5-4f1b-8a0d-6172a89d4743

.AUTHOR Rob Sewell @sqldbawithbeard

.COMPANYNAME Sewells Consulting

.COPYRIGHT Rob Sewell @sqldbawithbeard

.TAGS Power Bi SSRS SQL Server Reporting Services PBIX deployment

.LICENSEURI https://github.com/SQLDBAWithABeard/Functions/blob/master/LICENSE

.PROJECTURI https://github.com/SQLDBAWithABeard/Functions

.ICONURI

.EXTERNALMODULEDEPENDENCIES

.REQUIREDSCRIPTS

.EXTERNALSCRIPTDEPENDENCIES

.RELEASENOTES


#>

#Requires -Module ReportingServicesTools

<#

.DESCRIPTION
 Uploads PBIX files to a PowerBi report server and sets the data source

#>

Param()


<#
.SYNOPSIS
Publishes a Power Bi File to a PowerBi Report Server. It will overwrite existing
files.

.DESCRIPTION
Publishes a Power Bi File to a Power Bi report Server and sets the connection
string for the data source and the credential. Only works with one data source
at present.It will overwrite existing files

.PARAMETER FolderName
The Name of the folder the report is to be placed. Will be created if it doesnt
exist - IE dbachecks-weekly or Finance

.PARAMETER ReportServerURI
The URI of the Report Server like http://Server01/ReportS

.PARAMETER FolderLocation
The Path to the folder - If it is in the root directory then / otherwise
/dbachecks

.PARAMETER PBIXFile
The full path to the pbix file

.PARAMETER Description
The Description of the report

.PARAMETER ConnectionString
The connection string for the data source

.PARAMETER ConnectionUserName
The User name for the credential for the datasource if required - Use a
creential object if possible

.PARAMETER Secret
The password for the user for the datasource - Use a credential object if
possible

.PARAMETER Credential
A credential object for the user for the data source

.EXAMPLE

$FolderName = 'TestFolder'
$ReportServerURI = 'http://localhost/Reports'
$FolderLocation = '/'
$PBIXFile = 'C:\Temp\test.pbix'
$Description = "Descriptions"
$ConnectionString = "Server001;dbachecks"

    $publishPBIXFileSplat = @{
        ReportServerURI = $ReportServerURI
        FolderLocation = $FolderLocation
        Description = $Description
        PBIXFile = $PBIXFile
        ConnectionString = $ConnectionString
        FolderName = $FolderName
      ConnectionUserName = $UserName1
      Secret = $Password1

    }
    Publish-PBIXFile @publishPBIXFileSplat

    Deploys a report from the PBIX file C:\Temp\test.pbix to the report server
    on the localhost into a folder called TestFolder located at the root of the
    server (which it will create if it doesnt exist) and sets the connection
    string to "Server001;dbachecks" with a user name and password stored in the
    variables

.NOTES
Rob Sewell 20/08/2018
#>

function Publish-PBIXFile {
    [CmdletBinding(DefaultParameterSetName = 'ByUserName', SupportsShouldProcess)]
    Param(
        [Parameter(Mandatory = $true)]
        [string]$FolderName,
        [Parameter(Mandatory = $true)]
        [string]$ReportServerURI,
        [Parameter(Mandatory = $true)]
        [string]$FolderLocation,
        [Parameter(Mandatory = $true)]
        [string]$PBIXFile,
        [Parameter()]
        [string]$Description = "Description of Your report Should go here",
        [Parameter()]
        [string]$ConnectionString,
        [Parameter(ParameterSetName = 'ByUserName')]
        [string]$ConnectionUserName, 
        [Parameter(ParameterSetName = 'ByUserName')]
        [string]$Secret,
        [Parameter(Mandatory = $true, ParameterSetName = 'ByCred')]
        [pscredential]$Credential
    )

    $FolderPath = $FolderLocation + $FolderName
    $PBIXName = $PBIXFile.Split('\')[-1].Replace('.pbix', '')

    try {
        Write-Verbose "Creating a session to the Report Server $ReportServerURI"
        # establish session w/ Report Server
        $session = New-RsRestSession -ReportPortalUri $ReportServerURI
        Write-Verbose "Created a session to the Report Server $ReportServerURI"
    }
    catch {
        Write-Warning "Failed to create a session to the report server $reportserveruri"
        Return
    }

    # create folder (optional)
    try {
        if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Creating a folder called $FolderName at $FolderLocation")) {
            $Null = New-RsRestFolder -WebSession $session -RsFolder $FolderLocation  -FolderName $FolderName -ErrorAction Stop
        }
    }
    catch [System.Exception] {
        If ($_.Exception.InnerException.Message -eq 'The remote server returned an error: (409) Conflict.') {
            Write-Warning "The folder already exists - moving on"
        }
    }
    catch {
        Write-Warning "Failed to create a folder called $FolderName at $FolderLocation report server $ReportServerURI but not because it already exists"
        Return
    }

    try {
        if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Uploading the pbix from $PBIXFile to the report server ")) {
            # upload copy of PBIX to new folder
            Write-RsRestCatalogItem -WebSession $session -Path $PBIXFile -RsFolder $folderPath -Description $Description -Overwrite
        }
    }
    catch {
        Write-Warning "Failed to upload the file $PBIXFile to report server $ReportServerURI"
        Return
    }

    try {
        Write-Verbose "Getting the datasources from the pbix file for updating"
        # get data source object
        $datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIXName"
        Write-Verbose "Got the datasources for updating"
    }
    catch {
        Write-Warning "Failed to get the datasources"
        Return
    }


    try {
        Write-Verbose "Updating Datasource"


        foreach ($dataSource in $datasources) {
            if ($ConnectionString) {
                # change connection string (to point at new source)
                $dataSource.ConnectionString = $ConnectionString
            }
            if ($Credential -or $UserName) {
                if ($Credential) {
                    $UserName = $Credential.UserName
                    $Password = $Credential.GetNetworkCredential().Password
                }
                else {
                    $UserName = $ConnectionUserName
                    $Password = $Secret
                }
                $dataSource.CredentialRetrieval = 'Store'
                $dataSource.DataModelDataSource.Username = $UserName 
                $dataSource.DataModelDataSource.Secret = $Password 
            }
            if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Updating the data source for the report $PBIXName")) {
                # update data source object on server
                Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIXName" -RsItemType PowerBIReport -DataSources $datasource
            }
        }
    }
    catch {
        Write-Warning "Failed to set the datasource"
        Return
    }
    Write-Verbose "Completed Successfully"
}

Many Power Bi On-Premise API on this website.

我只有成功使用"Get" Report List，"Post "的 Cred 文件並無詳細說明如何設置，可以自行研究研究。

Reference：

https://stackoverflow.com/questions/57986480/programmatically-deploying-power-bi-reports-to-power-bi-report-server-and-change

https://github.com/microsoft/ReportingServicesTools

https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1

https://community.powerbi.com/t5/Developer/Uploading-a-PBIX-file-using-the-import-API/m-p/357386

• ← Flask簡單完整應用 - Python
• Export all of Report on Power Bi Server - Power Bi →