pbiTools.psm1
|
# Power BI Tools and Library # (v) Bob, Prodata Sept 2019 # History # 24/09/2019 First Version # # Install-Module MicrosoftPowerBIMgmt function New-pbiReport { param( [Parameter(Mandatory=$True)]$Path, #File OR Folder. Can be Array [Parameter(Mandatory=$True)]$Workspace, #Single Workspace [Parameter(Mandatory=$False)]$SqlServer, #Name of SQL Servr to Map Model to (If a Model ) [Parameter(Mandatory=$False)]$UserName #Name of Current User. If specified will take over dataset if needed ) ) $ErrorActionPreference = "Stop" $WorkspaceID = Get-PowerBIWorkspace -Name $Workspace foreach ($p in $Path) { Get-ChildItem -Path $p| Where {$_.extension -like ".pbix"}| foreach { Write-Verbose -Message $_ $Id= New-PowerBIReport -Path $_.FullName -Name $_.BaseName -Workspace $WorkspaceID -ConflictAction:"CreateOrOverwrite" | Select -ExpandProperty "Id" $count =@(Get-PowerBIReport -Workspace $WorkspaceID -Name $_.BaseName ).Count # if ($count -gt 1) { # Invoke-pbiReportBindFix -ReportId $Id # } if ($SqlServer) { Set-pbiSqlServer -Workspace $Workspace -Name $_.BaseName -SqlServer $SqlServer -UserName $UserName } else { Set-pbiDatasetGateway -Workspace $Workspace -Dataset $_.BaseName } } } } #Check if Linked Report has Duplicate and fix by # # function Invoke-pbiReportBindFix { } function Check-pbiModules { Load-Module "SqlServer" Load-Module "MicrosoftPowerBIMgmt" } function Remove-pbiReport { param( [Parameter(Mandatory=$True)]$Workspace #Single Workspace ) Get-PowerBIReport -Workspace (Get-PowerBIWorkspace -Name $Workspace) | foreach { $Id =$_.Id $Url = "reports/$Id" Invoke-PowerBIRestMethod -Url $Url -Method Delete } Get-PowerBIDataSet -Workspace (Get-PowerBIWorkspace -Name $Workspace) | foreach { $Id =$_.Id $Url = "datasets/$Id" Invoke-PowerBIRestMethod -Url $Url -Method Delete } } function Set-pbiRebindReport { param( [Parameter(Mandatory=$True)][object]$Workspace , #Single Workspace [Parameter(Mandatory=$False)][String]$Report="*", #Name of Report. Can be * [Parameter(Mandatory=$False)][String]$ForceDatasetId #DataSetId to force a Bind To ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIReport -WorkspaceId $WorkspaceId | Where {$_.Name -like $Report} | foreach { $DatasetId=$_.DatasetId $reportId=$_.Id $Url ="groups/$WorkspaceId/datasets/$DatasetId" $Id=Get-PowerBIDataset -WorkspaceId $WorkspaceId -Id $DatasetId | Select -ExpandProperty "Id" if (!$Id) { Write-Verbose -Message ("Rebind Report: " + $_.Name) #Get Name of Dataset in Remote Workspace $Url ="datasets/$DatasetId" $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $DatasetName =$data.name if ($ForceDatasetId) { $newDatasetId=$ForceDatasetId } else { #Get Dataset ID for Local Workspace Dataset $newDatasetId=Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -eq $DatasetName } | Select -ExpandProperty "Id" } #Rebind Report to Dataset specified $url="groups/$WorkspaceId/reports/$reportId/Rebind" $Body=@" {datasetId: "$newDatasetId"} "@ Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body } } } function Set-pbiDatasetTakeOver { param( [Parameter(Mandatory=$True)]$Workspace , #Single Workspace [Parameter(Mandatory=$False)]$Dataset="*", #Name of Dataset. Can by Array [Parameter(Mandatory=$False)]$UserName="" #Username of USer to Change Ownership to ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset} |foreach { $Id =$_.Id $Url ="datasets/$Id/Default.TakeOver" Invoke-PowerBIRestMethod -Url $Url -Method Post -Body "" } } function Set-pbiDatasetGateway { param( [Parameter(Mandatory=$True)]$Workspace , #Single Workspace [Parameter(Mandatory=$False)]$Dataset="*" #Name of Dataset. Can by Array ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset} |foreach { $Id =$_.Id $DataSet = $_.Name $IsOnPremGatewayRequired =$_.IsOnPremGatewayRequired if ($IsOnPremGatewayRequired) { $Url ="datasets/$Id/DiscoverGateways" $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $GatewayId=$data.value.Id $Body=@" {"gatewayObjectId": "$GatewayId"} "@ $url="datasets/$Id/BindToGateway" Write-Verbose -Message $url try { Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body } catch [System.Net.Http.HttpRequestException],[AggregateException] { write-Error ("Could Not Bind Gateway on DataSet $DataSet . Check SqlServer Name and Gateway Configuration.") write-Error (Resolve-PowerBIError -Last | Out-String) } catch { #write-Error ( $_.Exception) write-Error (Resolve-PowerBIError -Last | Out-String) } } } } function Get-pbiStatus { param( [Parameter(Mandatory=$True)] [object] $Workspace , #WorkSpaces. Can be Array [Parameter(Mandatory=$False)][string] $Dataset="*",#Name of Dataset. Can by WildCard [Parameter(Mandatory=$False)][hashtable] $Errors =@{}, #Hash Table of Errors During Processing to tag them on Status returned [Parameter(Mandatory=$False)][hashtable] $StartTimes =@{} #Hash Table of Start Times if Processing Failures not logged ) # $ErrorActionPreference = "Stop" $DataSetList=@() ForEach ($w in $Workspace ) { $WorkspaceId=(Get-PowerBIWorkspace -Name $w) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset -and $_.IsRefreshable -eq $True} |foreach { $Id=$_.Id $obj=$null $obj = New-Object System.Object $obj | Add-Member -type NoteProperty -Name WorkSpace -Value $w $obj | Add-Member -type NoteProperty -Name DataSetId -Value $_.id $obj | Add-Member -type NoteProperty -Name DataSet -Value $_.Name $obj | Add-Member -type NoteProperty -Name ConfiguredBy $_.ConfiguredBy $obj | Add-Member -type NoteProperty -Name IsOnPremGatewayRequired $_.IsOnPremGatewayRequired #Get last Refresh History $url="datasets/$Id/refreshes?" + '$top=1' $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $status =(&{if ($data.value.status -eq "Unknown") {"InProgress"} else {$data.value.status}}) if ($Errors.item($Id).length >0) {$status = "Failed" } if ($Errors.item($Id).length >0) { $startTime = $StartTimes.item($Id) $endTime =$startTime } else { if ($data.value.startTime) {$startTime = [datetime]$data.value.startTime} if ($data.value.EndTime){$endTime =[datetime]$data.value.EndTime} } $obj | Add-Member -type NoteProperty -Name refreshStatus $status $obj | Add-Member -type NoteProperty -Name refreshType $data.value.refreshType $obj | Add-Member -type NoteProperty -Name startTime $startTime $obj | Add-Member -type NoteProperty -Name EndTime $endTime $obj | Add-Member -type NoteProperty -Name Error ($Errors.item($Id) + $data.value.serviceExceptionJson ) $DataSetList+=$obj } } $DataSetList } function Invoke-pbiRefresh { param( [Parameter(Mandatory=$True)] [object]$Workspace , #Array of Workspaces [Parameter(Mandatory=$False)] [String]$Dataset="*", #Name of Dataset. Can by Array [parameter(Mandatory=$false)] [switch]$MailOnFailure=$False, #Email on Failure [parameter(Mandatory=$false)] [switch]$Wait=$False, #Wait for Failure (Synchronous) [parameter(Mandatory=$false)] [int]$WaitTime=10, #Time to wait for checking Processing Progress [parameter(Mandatory=$false)] [String]$SqlServer ="localhost" ,#Sql Server to Log Results. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlDatabaseName , #Sql Server to Log Results to. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlSchemaName ="dbo" , #Sql Server to Log Schema to. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlTableName ="ProcessView", #Sql Table to Log Results to [parameter(Mandatory=$false)] [switch]$IgnoreProcessError, #Raise Error if any Process Errors. Default is to Fail on any Error [parameter(Mandatory=$false)] [switch]$NoOutput #Output Status When Finished ) $ErrorActionPreference = "Stop" $errormessage="" Check-pbiModules $Datasets =@() $Errors =@{} $StartTimes =@{} $Failed =$false ForEach ($w in $Workspace) { $wDatasets=@() $WorkspaceId=(Get-PowerBIWorkspace -Name $w) | Select -ExpandProperty "Id" $wDatasets=Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset -and $_.IsRefreshable -eq $True } forEach ($d in $wDatasets) { $Id =$d.Id $Datasets+=$Id $DatasetName=$d.Name if ($MailOnFailure.IsPresent) {$notifyOption="MailOnFailure"} else { $notifyOption="NoNotification" } $Body='{"notifyOption": "' + $notifyOption+ '"}' $url="datasets/$Id/refreshes" try { Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body } catch [AggregateException] { $failed=$True $error=$_.Exception.innerexception.message if ($error.Contains("Bad Request")) {$error ="Refresh Limit Reached" } write-Warning ("`r`nRefresh $w\$DatasetName. $error " ) Write-Verbose -Message $_.Exception $Errors.Add($id,$error) $StartTimes.Add($id,(Get-Date)) continue } catch { $failed=$True write-Warning ("`r`nRefresh $w\$DatasetName. $_.Exception.message" ) Write-Verbose -Message $_.Exception $Errors.Add($id,$_.Exception ) $StartTimes.Add($id,(Get-Date)) continue } } #Synchrous Processing if Required. By Default is Asyncronous if ($Wait.IsPresent) { While (!($Complete)) { $InProgress=$false Write-Verbose -Message "Checking Last Refresh Status..." ForEach ($d in $wDataSets) { $id=$d.Id $url="datasets/$Id/refreshes?" + '$top=1' $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $status=$data.value.status if ($status -eq "Unknown") {$InProgress=$true } if ($status -eq "InProgress") {$InProgress =$true } if ($status -eq "Failed") {$failed=$true } } if ($InProgress -eq $false) {$Complete=$true } else {Start-Sleep -Seconds $WaitTime} } } } #Log Results to Table if ((($SqlServer) -and ($SqlDatabaseName)) -or (!($NoOutput ))) { $status=Get-pbiStatus -Workspace $Workspace -Dataset $Dataset -Errors $Errors -StartTimes $StartTimes } if (!($NoOutput )) { $Status | Select } if (($SqlServer) -and ($SqlDatabaseName)) { Write-Verbose -Message "Logging to $SqlServer.$SqlDatabaseName.$SqlSchemaName.$SqlTableName" $Status | Select-Object -Property * | Write-SqlTableData -ServerInstance $SqlServer -DatabaseName $SqlDatabaseName -TableName $SqlTableName -SchemaName $SqlSchemaName $Status | ForEach-Object { if (!($_.Error)) { $s="Workspace: " + $_.WorkSpace + ",Dataset:" + $_.DataSet + ",Error:" + $_.Error if ($_.refreshStatus -eq "Failed") { Write-Error -Message $s } } } } if ($failed) { EXIT 1 } } function Set-pbiSqlServer { param( [Parameter(Mandatory=$True)]$Workspace , #Single Workspace [Parameter(Mandatory=$False)]$Name="*", #Name of Dataset. Can by Array [Parameter(Mandatory=$True)]$SqlServer="", #New SqlServer Name [Parameter(Mandatory=$False)]$UserName="" #Username of validated user. If this is set then updaet will TakeOver Dataset if required ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Name} |foreach { $ConfiguredBy=$_.ConfiguredBy $Id =$_.Id $Dataset = $_.Name $Url = "datasets/$Id/datasources" $ds=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $ds $datasourceType=$data.value.datasourceType $oldServer =$data.value.connectionDetails.server $Database =$data.value.connectionDetails.database if ($Username) { if ($ConfiguredBy -ne $UserName) { Set-pbiDatasetTakeOver -Workspace $Workspace -Dataset $Dataset $ConfiguredBy=(Get-PowerBIDataset -WorkspaceId $WorkspaceId -Id $Id) | Select -ExpandProperty "ConfiguredBy" if ($ConfiguredBy -ne $UserName) { Write-Error "Cannot Updatedatasource. Owner=$ConfiguredBy,Current UserName=$UserName" } } } if ($datasourceType -eq "sql") { $Url = "datasets/$Id/updatedatasources" $Body=@" {"updateDetails":[ {"connectionDetails": { "server": "$SqlServer", "database": "$Database" }, "datasourceSelector": { "datasourceType": "Sql", "connectionDetails": { "server": "$OldServer", "database": "$Database" } } } ] } "@ Write-Verbose -Message $Url Write-Verbose -Message $Body Write-Host "Updating Datasource for $Workspace\$Dataset. Server $oldServer=>$SqlServer..." try { Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body } catch [System.Net.Http.HttpRequestException],[AggregateException] { write-Error ("Update for $Workspace\$Dataset.PBIX Failed. Check PowerQuery has no MERGE operations. See https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources") write-Error (Resolve-PowerBIError -Last | Out-String) } catch { write-Error ( $_.Exception) } } else { Write-Host "datasourceType=$datasourceType. Not Updated as not Sql" } #Set PBI Gateway (not Required as it now Auto Sets) Set-pbiDatasetGateway -Workspace $Workspace -Dataset $Dataset } } function Load-Module ($m) { # If module is imported say that and do nothing if (Get-Module | Where-Object {$_.Name -eq $m}) { return } else { # If module is not imported, but available on disk then import if (Get-Module -ListAvailable | Where-Object {$_.Name -eq $m}) { Import-Module $m } else { # If module is not imported, not available on disk, but is in online gallery then install and import if (Find-Module -Name $m | Where-Object {$_.Name -eq $m}) { Install-Module -Name $m -Force -Verbose -Scope CurrentUser -AllowClobber Import-Module $m } else { # If module is not imported, not available and not in online gallery then abort write-host "Module $m not imported, not available and not in online gallery, exiting." Exit 1 } } } } export-modulemember -function New-pbiReport export-modulemember -function Remove-pbiReport export-modulemember -function Set-pbiRebindReport export-modulemember -function Set-pbiDatasetTakeOver export-modulemember -function Set-pbiDatasetGateway export-modulemember -function Get-pbiStatus export-modulemember -function Invoke-pbiRefresh export-modulemember -function Set-pbiSqlServer |