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 $Path | Where {$_.extension -like ".pbix"}| foreach { Write-Verbose -Message $_ $id= New-PowerBIReport -Path $_.FullName -Name $_.BaseName -Workspace $WorkspaceID -ConflictAction:"CreateOrOverwrite" | Select -ExpandProperty "Id" if ($SqlServer) { Set-pbiSqlServer -Workspace $Workspace -Name $_.BaseName -SqlServer $SqlServer -UserName $UserName } else { Set-pbiDatasetGateway -Workspace $Workspace -Dataset $_.BaseName } } } } 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 * ) $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/4d4d4b25-43da-4071-8c58-add3c0bddaa0" $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $DatasetName =$data.name #Get Dataset ID for Local Workspace Dataset $newDatasetId=Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -eq $DatasetName} | Select -ExpandProperty "Id" #Rebind Report to Dataset in Workspace, not remote Workspace $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) } } } } function Get-pbiStatus { param( [Parameter(Mandatory=$True)]$Workspace , #WorkSpaces. Can be Array [Parameter(Mandatory=$False)]$Dataset="*" #Name of Dataset. Can by WildCard ) # $ErrorActionPreference = "Stop" $DataSetList=@() $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset} |foreach { $Id=$_.Id $obj=$null $obj = New-Object System.Object $obj | Add-Member -type NoteProperty -Name WorkSpace -Value $Workspace $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 $obj | Add-Member -type NoteProperty -Name refreshStatus (&{if ($data.value.status -eq "Unknown") {"InProgress"} else {$data.value.status}}) $obj | Add-Member -type NoteProperty -Name refreshType $data.value.refreshType $obj | Add-Member -type NoteProperty -Name startTime $data.value.startTime $obj | Add-Member -type NoteProperty -Name EndTime $data.value.EndTime $obj | Add-Member -type NoteProperty -Name Error $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)] [int32]$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" Check-pbiModules $Datasets =@() ForEach ($w in $Workspace) { $WorkspaceId=(Get-PowerBIWorkspace -Name $w) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset} |foreach { $Id =$_.Id $Datasets +=$Id $Dataset=$_.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 [System.Net.Http.HttpRequestException],[AggregateException] { Resolve-PowerBIError -Last write-Error ("Workspace: $w. Could Not Refresh $Dataset") write-Error (Resolve-PowerBIError -Last | Out-String) } catch { write-Error ( $_.Exception) } } } #Synchrous Processing if Required. By Default is Asyncronous if ($Wait.IsPresent) { $Failed=$False While (!($Complete)) { $Unknown=$false Write-Verbose -Message "Checking Last Refresh Status..." ForEach ($Id in $DataSets) { $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") {$Unknown =$true } if ($status -eq "Failed") {$Failes=$true } } if ($Unknown -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 } 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 (!($IgnoreProcessError)) { $s="Workspace: " + $_.WorkSpace + ",Dataset:" + $_.DataSet + ",Error:" + $_.Error if ($_.refreshStatus="Failed") { Write-Error -Message $s} } } } if (!($NoOutput )) { $Status | Select Property * } } 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 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 |