Viewing posts for the category PowerBI

Script like below:

<# .SYNOPSIS Export of all SSRS reports datasources and images .DESCRIPTION This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -. Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension. Please change the "Configuration data" below to your enviroment. Works with SQL Server 2005 and higher versions in all editions. Requires SELECT permission on the ReportServer database. .NOTES Author : Olaf Helper Requires: PowerShell Version 1.0, Ado.Net assembly .LINK GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx #>

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不了可以直接執行)

PowerBI需要的License，