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"
}