PublishPBIXFile.ps1
|
<#PSScriptInfo .VERSION 1.0.0.0 .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 $dataSource = 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" # 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" } |