PowerBIManager.psm1
|
$Script:LogVerbose = $false $Script:LogInformation = $true $Script:LogWarning = $true $Script:PBILogFilePath = $null #region Logging function Use-PBILog{ param( [string]$FilePath, [switch]$LogVerbose, [switch]$IgnoreInformation, [switch]$IgnoreWarning ) $Script:LogVerbose = $LogVerbose $Script:LogInformation = !$IgnoreInformation $Script:LogWarning = !$IgnoreInformation $Script:PBILogFilePath = $FilePath } function Write-PBILogVerbose{ param([string] $Message) Write-Verbose $Message if ($Script:LogVerbose) { if ($Script:PBILogFilePath){ $Message | Out-File FilePath $Script:PBILogFilePath -Append -Force } } } function Write-PBILogInformation{ param([string] $Message) Write-Information $Message if ($Script:LogInformation) { if ($Script:PBILogFilePath){ $Message | Out-File FilePath $Script:PBILogFilePath -Append -Force } } } function Write-PBILogWarning{ param([string] $Message) Write-Warning $Message if ($Script:LogWarning) { if ($Script:PBILogFilePath){ $Message | Out-File FilePath $Script:PBILogFilePath -Append -Force } } } function Write-PBILogError{ param([string] $Message) Write-Error $Message if ($Script:PBILogFilePath){ $Message | Out-File FilePath $Script:PBILogFilePath -Append -Force } } #endregion #region Exported Core Functions function Connect-PBI{ <# .SYNOPSIS Connect to the Power BI service .PARAMETER Credential PSCredential object. UserName should be email adress used to log on to the PowerBI service (Azure AD account). .PARAMETER ClientId Application Id for an AAD application that has authorization for the Power BI API rest service. .EXAMPLE Connect-PBI -ClientId "d57ac8af-1019-431d-8fed-5fd8db180388" .EXAMPLE Connect-PBI -ClientId "d57ac8af-1019-431d-8fed-5fd8db180388" -Credential $PbiCredential #> [CmdletBinding(DefaultParameterSetName = 'default')] param( [Parameter(Mandatory = $false)] [System.Management.Automation.CredentialAttribute()] [PSCredential] $Credential, [Parameter(Mandatory = $true)] [string] $ClientId ) if (!$Script:AuthenticationContext) { Write-PBILogVerbose -Message 'Initialize AuthenticationContext' $AuthorityAddress = "https://login.microsoftonline.com/common/oauth2/authorize" $script:AuthenticationContext = New-Object -TypeName Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext -ArgumentList ($AuthorityAddress) } Write-PBILogVerbose -Message 'Acquire Access Token' $TargetResourceIdentifier = "https://analysis.windows.net/powerbi/api" $AuthorizationRedirectUri = "https://login.live.com/oauth20_desktop.srf" if ($PSCmdlet.ParameterSetName -eq 'credential') { Write-PBILogVerbose -Message 'Using credential parameter' $UserCredential = New-Object Microsoft.IdentityModel.Clients.ActiveDirectory.UserPasswordCredential($Credential.UserName, $Credential.Password) $AuthenicationResult = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContextIntegratedAuthExtensions]::AcquireTokenAsync($script:AuthenticationContext , $TargetResourceIdentifier , $ClientId , $UserCredential).Result } else { Write-PBILogVerbose -Message 'Microsoftonline login' $ADPlatformParams= New-Object Microsoft.IdentityModel.Clients.ActiveDirectory.PlatformParameters([Microsoft.IdentityModel.Clients.ActiveDirectory.PromptBehavior]::Auto) $AuthenicationResult = $script:AuthenticationContext.AcquireTokenAsync($TargetResourceIdentifier , $ClientId, [Uri] $AuthorizationRedirectUri , $ADPlatformParams).Result } if(!$AuthenicationResult) { Write-Warning -Message "NOT Authenticated" } else { Write-PBILogInformation "Authenticated as $($AuthenicationResult.UserInfo.DisplayableId)" $script:TokenExpiresOn = $AuthenicationResult.ExpiresOn $script:AccessToken = $AuthenicationResult.AccessToken $script:AuthenticatedUser = $AuthenicationResult.UserInfo.DisplayableId } } function Invoke-PBIMethod{ <# .SYNOPSIS Invoke Method on the Power BI REST API .PARAMETER ApiOperation Operation part of the url .PARAMETER RequestMethod GET, POST, or DELETE. Defaults to GET. .PARAMETER RequestBody Body of posted message. .PARAMETER IgnoreGroup If set, do not inject current group id in URL. .EXAMPLE Invoke-PBIMethod -ApiOperation Groups -IgnoreGroup .EXAMPLE Invoke-PBIMethod -ApiOperation users .EXAMPLE Invoke-PBIMethod -ApiOperation users -RequestMethod POST -RequestBody ([PSCustomObject]@{"groupUserAccessRight"="Admin";"emailAddress"="john@contoso.com"}) #> [CmdletBinding(DefaultParameterSetName = 'default')] param( [Parameter(Mandatory=$true)] [string] $ApiOperation, [Parameter(Mandatory=$false)] [string] $RequestMethod="GET", [Parameter(Mandatory=$false)] [object] $RequestBody, [Parameter(Mandatory=$false)] [switch] $IgnoreGroup = $false ) $requestUrl = "https://api.powerbi.com/v1.0/myorg/" if (!$IgnoreGroup){ if(![string]::IsNullOrWhiteSpace($script:GroupId)) { $requestUrl = "$($requestUrl)groups/$($script:GroupId)/" } } $requestUrl = $requestUrl + $ApiOperation $requestHeaders = Get-PBIRequestHeader Write-PBILogVerbose "Invoke $($RequestMethod) $($requestUrl)" if ($RequestBody) { $jsonBody = ConvertTo-Json $RequestBody -Depth 10 Write-PBILogVerbose -Message "Body $($jsonBody)" $result=Invoke-RestMethod -Uri $requestUrl -Headers $requestHeaders -Method $RequestMethod -Body $jsonBody } else { $result=Invoke-RestMethod -Uri $requestUrl -Headers $requestHeaders -Method $RequestMethod } return $result } function Use-PBIGroup{ <# .SYNOPSIS Select a group (app workspace). The groupid will be used in all subsequent calls to the Power BI REST API. .PARAMETER Name Name of the app workspace to be used. .PARAMETER Id Id of the app workspace to be used. .PARAMETER Clear Clear the set app workspace id. Subsequent calls to Power BI REST API will target 'My Workspace'. .EXAMPLE Use-PBIGroup "CorporateFinance" .EXAMPLE Use-PBIGroup -Id "e7ba1c**-1315-****-****-fff5d4bf5b**" #> [CmdletBinding(DefaultParameterSetName = 'default')] param( [Parameter(Mandatory=$false)] [string] $Name, [Parameter(Mandatory=$false)] [string] $Id, [Parameter(Mandatory=$false)] [switch] $Clear = $false ) if (![string]::IsNullOrWhiteSpace($Id)) { $script:GroupId=$Id Write-PBILogInformation "Using Group $($script:GroupId)" } elseif (![string]::IsNullOrWhiteSpace($Name)) { Write-PBILogVerbose -Message "Get Group Id for $($Name)" $grps = Invoke-PBIMethod -ApiOperation groups -IgnoreGroup $groupId = $grps.value |Where-Object name -eq $Name | Select-Object -ExpandProperty id if ([string]::IsNullOrWhiteSpace($groupId)) { Write-Warning "Could not find group $($Name)" } else { $script:GroupId=$groupId Write-PBILogInformation "Using Group $($Name) ($($script:GroupId))" } } elseif ($Clear) { $script:GroupId=$null } else { Write-Warning "Use-PBIGroup requires either Id or Name parameter or Clear switch." } } #endregion #region Exported Upload Functions Function Import-PBIContent{ <# .SYNOPSIS upload PBIX or Excel File; optionally replacing connection strings. .PARAMETER FilePath Full path of PBIX or XLSX file. .PARAMETER Dataset Optional name of the dataset and report after uploading. By default the name of the file is used. .PARAMETER ConnectionReplacement Array of ConnectionReplacement objects. See Set-PBIDatasource. .EXAMPLE Get-ChildItem "C:\Source\PowerBI" -Filter "*.pbix" | Import-PBIContent .EXAMPLE Import-PBIContent -FilePath "C:\Source\PowerBI\Fin4498.pbix" -Dataset "Finance Key Figures" #> [CmdletBinding()] param( [Parameter(ValueFromPipelineByPropertyName,Mandatory=$true)][Alias('PSPath')] [string]$FilePath, [Parameter(Mandatory=$false)] [string] $Dataset, [Parameter(Mandatory=$false)] [object[]] $ConnectionReplacement ) Begin { if ($PSCmdlet.MyInvocation.ExpectingInput -and $PSBoundParameters.ContainsKey(‘Dataset’)) { Write-Warning "Dataset parameter will be ignored." } } Process { $File = Convert-Path $FilePath $requestUrl = "https://api.powerbi.com/v1.0/myorg/" if (!$IgnoreGroup){ if(![string]::IsNullOrWhiteSpace($script:GroupId)) { $requestUrl = "$($requestUrl)groups/$($script:GroupId)/" } } if ([string]::IsNullOrEmpty($Dataset) -or $PSCmdlet.MyInvocation.ExpectingInput) { $Dataset = [IO.Path]::GetFileNameWithoutExtension($File) } $requestUrl = "$($requestUrl)imports?datasetDisplayName=$($Dataset)" #Check if the dataset already exists, detemine nameConflict value $matchDatset = Get-PBIDataset | Where-Object name -eq $Dataset if (!$matchDatset){ $requestUrl = "$($requestUrl)&nameConflict=Abort" } else { $requestUrl = "$($requestUrl)&nameConflict=Overwrite" } $requestHeaders = Get-PBIRequestHeader Write-PBILogVerbose "Invoke POST $($requestUrl)" $fileName = [IO.Path]::GetFileName($File) $boundary = "--QQQwwQqqydfdsfhjdjdhsgRRfdfdfd545623529ZMNSJYTkkKKKgfddssereewrw5435345bnvv" $fileBin = [IO.File]::ReadAllBytes($File) $ansiEncoding = [System.Text.Encoding]::GetEncoding("iso-8859-1") $bodyLines = ( $boundary, "Content-Disposition: form-data; name=`"file0`"; filename=`"$($fileName)`"", "Content-Type: application/x-zip-compressed", "", $ansiEncoding.GetString($fileBin), "$($boundary)--", "" ) -join [System.Environment]::NewLine try { $result = Invoke-RestMethod -Uri $requestUrl -Headers $requestHeaders -Method Post -ContentType "multipart/form-data; boundary=$($boundary)" -Body $bodyLines Write-PBILogInformation "$($File) uploaded." if ($ConnectionReplacement) { #get the new Dataset id from the import id $WaitCount=0 $ImportState = "none" while (($WaitCount -lt 60) -and ($ImportState -ne "Succeeded")) { Start-Sleep -Seconds 1 $ImportedAssets = $null $ImportedAssets = Invoke-PBIMethod "imports/$($result.id)" $ImportState = $ImportedAssets.importState $WaitCount++ } Write-PBILogVerbose "Import state $($ImportState) waited $($WaitCount) * seconds" if ($ImportState -eq "Succeeded") { $ImportedDatasetId = $ImportedAssets.datasets[0].id Write-PBILogVerbose "Imported dataset id $($ImportedDatasetId)" #replace datasource connections Set-PBIDatasource -DatasetId $ImportedDatasetId -ConnectionReplacement $ConnectionReplacement } else { Write-Warning "Cannot replace connections Import $($result.id) State $($ImportState)" } } } catch [System.Exception] { Write-PBILogError $_.Exception.Message } } } #endregion #region Exported Wrapper Functions function Get-PBIGroup{ <# .SYNOPSIS Get all app workspaces available for the current user. .EXAMPLE Get-PBIGroup #> $result = Invoke-PBImethod "groups" -IgnoreGroup $result.Value } function Get-PBIReport{ <# .SYNOPSIS Get all reports in the current app workspace #> $result = Invoke-PBImethod "reports" $result.Value } function Get-PBIDataset{ <# .SYNOPSIS Get all datasets in the current app workspace .EXAMPLE Get-PBIDataset |? configuredBy -like "John*" | Format-Table #> $result = Invoke-PBImethod "datasets" $result.Value } function Get-PBIDashboard{ <# .SYNOPSIS Get all dashboards in the current app workspace #> $result = Invoke-PBImethod "dashboards" $result.Value } function Get-PBITile{ <# .SYNOPSIS Get all tiles for the specified dashboards. .PARAMETER DashboardId Dashboard id. .PARAMETER DashboardName Dashboard display name. .DESCRIPTION This function accepts output from Get-PBIDashboard as input; thus enabling getting tiles for multiple or all dashboards. .EXAMPLE Get-PBIDashboard | ? displayName -like "FIN*" | Get-PBITile .EXAMPLE Get-PBITile 40****2f-e88e-***-b624-39d6******79 #> [CmdletBinding(DefaultParameterSetName = 'default')] param( [Parameter(ValueFromPipelineByPropertyName,Mandatory=$true)][Alias('id')] [string] $DashboardId, [Parameter(ValueFromPipelineByPropertyName,Mandatory=$false)][Alias('displayName')] [string] $DashboardName ) process { $result = Invoke-PBImethod "dashboards/$($DashboardId)/tiles" $result.Value | Select-Object -Property *, @{Name="dashboardId"; Expression={$DashboardId}}, @{Name="dashboardName"; Expression={$DashboardName}}| Write-Output } } function Set-PBIDatasetOwner{ <# .SYNOPSIS Take over ownership of dataset. .PARAMETER DatasetId Dataset id. .PARAMETER DatasetName Optional Dataset name, used in informational message. .PARAMETER CurrentOwner Optional current owner of the dataset,used in informational message. .DESCRIPTION This function accepts output from Get-PBIDataset as input .EXAMPLE Get-PBIDataset | ? configuredBy -like 'john*' | Set-PBIDatasetOwner .EXAMPLE Set-PBIDatasetOwner be977***-6d33-****-9184-df******b67e #> [CmdletBinding(SupportsShouldProcess = $true ,ConfirmImpact='Medium',DefaultParameterSetName = 'default')] param( [Parameter(ValueFromPipelineByPropertyName,Mandatory=$true)][Alias('id')] [string] $DatasetId, [Parameter(ValueFromPipelineByPropertyName,Mandatory=$false)][Alias('Name')] [string] $DatasetName, [Parameter(ValueFromPipelineByPropertyName,Mandatory=$false)][Alias('configuredBy')] [string] $CurrentOwner ) Process { if (!$PSBoundParameters.ContainsKey("CurrentOwner") -or !$PSBoundParameters.ContainsKey("DatasetName")) { $dataset = Invoke-PBImethod "datasets/$($DatasetId)" $DatasetName = $dataset.name $CurrentOwner = $dataset.configuredBy } if ($CurrentOwner) { if ($CurrentOwner -ne $script:AuthenticatedUser) { if ($PSCmdlet.ShouldProcess("Dataset $($DatasetName)")) { Write-PBILogInformation "Dataset $($DatasetName) transfer ownership from $($CurrentOwner) to $($script:AuthenticatedUser)" $result = Invoke-PBImethod -ApiOperation "datasets/$($DatasetId)/takeover" -RequestMethod POST $result | Out-Null } } else { Write-PBILogVerbose -Message "Skipping dataset $($DatasetName) already owned by $($script:AuthenticatedUser)" } } } } function Sync-PBIDataset{ <# .SYNOPSIS Trigger on demand dataset refresh. .PARAMETER DatasetId Id of dataset to be refreshed. .EXAMPLE Sync-PBIDataset "3486e***-e0a6-****-b07f-cad6512aa***" .EXAMPLE Get-PBIDataset | ? name -like "*Finance*" | Sync-PBIDataset #> [CmdletBinding(DefaultParameterSetName = 'default')] param( [Parameter(ValueFromPipelineByPropertyName,Mandatory=$true)][Alias('id')] [string] $DatasetId ) Process { # get dataset check owner $dataset = Invoke-PBImethod "datasets/$($DatasetId)" if ($dataset.configuredBy -ne $script:AuthenticatedUser) { Write-PBILogInformation "Dataset $($DatasetId) transfer ownership from $($dataset.configuredBy) to $($script:AuthenticatedUser)" $result = Invoke-PBImethod -ApiOperation "datasets/$($DatasetId)/takeover" -RequestMethod POST } $result = Invoke-PBImethod "datasets/$($DatasetId)/refreshes" -RequestMethod POST Write-PBILogInformation "Triggered refresh on dataset $($dataset.name)" } } #endregion #region Datasource functions function Get-PBIDatasource{ <# .SYNOPSIS Get datasource information for a dataset. .DESCRIPTION The Power BI Rest API method can only be used for datasets owned by the current user. If the the dataset is not owned by the current user a take over will be issued. .PARAMETER Dataset id. #> [CmdletBinding(DefaultParameterSetName = 'default')] param( [Parameter(Mandatory=$true)] [string] $DatasetId ) # get dataset check owner $dataset = Invoke-PBImethod "datasets/$($DatasetId)" if ($dataset.configuredBy -ne $script:AuthenticatedUser) { Write-PBILogInformation "Dataset $($DatasetId) transfer ownership from $($dataset.configuredBy) to $($script:AuthenticatedUser)" $result = Invoke-PBImethod -ApiOperation "datasets/$($DatasetId)/takeover" -RequestMethod POST } $result = Invoke-PBImethod "datasets/$($DatasetId)/datasources" $result.value } <# .SYNOPSIS Modify connection details for dataset. .PARAMETER DatasetId Dataset id. ,PARAMETER ConnectionReplacement Object describing the connections details to be modified. #> function Set-PBIDatasource { [CmdletBinding(SupportsShouldProcess = $true ,ConfirmImpact='Medium',DefaultParameterSetName = 'default')] param( [Parameter(Mandatory=$true)] [string] $DatasetId, [Parameter(Mandatory=$true)] [object[]] $ConnectionReplacement ) if ($PSCmdlet.ShouldProcess("Dataset $($DatasetId)")) { $datasources = Get-PBIDatasource -DatasetId $DatasetId $ServerReplacement = $ConnectionReplacement | Where-Object {$_.CurrentServer -and !$_.CurrentDatabase} Foreach ($datasource in $datasources) { if ($datasource.connectionDetails) { if($datasource.connectionDetails.server) { $matchval = $null $matchval = $ServerReplacement | Where-Object CurrentServer -eq $datasource.connectionDetails.server if ($matchval) { Write-PBILogInformation "Replace dataset $($DatasetId) server $($datasource.connectionDetails.server) with $($ServerReplacement[0].NewServer)" $Newconnection = $datasource.connectionDetails.PSObject.Copy() $Newconnection.server = $ServerReplacement[0].NewServer $RequestBody = @{ updateDetails = @( @{connectionDetails=$Newconnection datasourceSelector=$datasource} ) } Invoke-PBIMethod -ApiOperation "datasets/$($DatasetId)/updatedatasources" -RequestMethod POST -RequestBody $RequestBody } } } } } } #endregion #region private functions function Get-PBIAccesstoken{ #todo check connected, expiry $script:AccessToken } Function Get-PBIRequestHeader { $accesToken = Get-PBIAccesstoken $requestHeaders = @{ 'Content-Type'='application/json' 'Authorization'= "Bearer $accesToken" } $requestHeaders } #endregion |