Integrating PowerShell with PowerBI: Automating in Azure (Part 3)

by AlexHolmeset Frequent Visitor on ‎12-06-2017 03:29 PM

In this blog post we will take a look at how to automate the process of updating the data in the Power BI report we created in part 2, without needing a computer or server. We will do this by scheduling a Runbook in Azure Automation.

 

I assume that you have already have signed up for an Azure subscription. If not, there is a 30-day trial you can use for testing.

 

Four things you need to do:

- Create automation account.

- Create credential asset.

- Import needed modules.

- Setup runbook with script and schedule it. 

 

Create Automation Account

  1. Go to portal.azure.com.
  2. Select All Services, search for Automation Accounts, and then select it.
    azure1.jpg
  3. Press + Add.
  4. Set the Name, Subscription, etc., and then click the Create button.azure2.jpg

Create credential asset

Next, we need to create an asset where we can store the username and password needed for the script.

  1. Go to Automation Accounts and select your newly created account.
  2. Select Credentials, then click + Add to add a credential.
  3. Fill in the form and click Create.
    azure4.jpg

Import modules

Now, we need to import the needed modules into Azure.

  1. Go to Automation Accounts and select your newly created account.
  2. Select Modules gallery, search for Microsoft.ADAL.PowerShell, and select the module.
    azure5.jpg
  3. Click Import.
    azure6.jpg
  4. Search for and import Microsoft.PowerBI.PowerShell.
  5. Our script needs the Exchange Online module, but this is not found in the Modules Gallery. We need to copy that from a computer where its installed. To do this, open a PowerShell window and enter: (Get-Module -ListAvailable *).path
  6. Go to the path of the Exchange module and compress the content to a zip file.
  7. Go to Modules under your Automation Account and click + Add a module.
    azure7.jpg
  8. Select the path to where you stored the zip file and click OK.
    azure9.jpgazure8

Setup Runbook with script

  1. Go to Automation Accounts and select your newly created account.
  2. Select Runbooks and click + Add a runbook.
    azure9
  3. Create a new runbook.
    azure10.jpg
  4. Select your runbook.
  5. Click Edit.
    azure11.jpg
  6. Select your runbook and enter the modified script belowr.
    azure12

    Script:

    #If running as a automated task, remember to store credentials.
     
    $myCredential = Get-AutomationPSCredential -Name 'o365'
    $userName = $myCredential.UserName
    $securePassword = $myCredential.Password
    $password = $myCredential.GetNetworkCredential().Password
     
    $cred = New-Object System.Management.Automation.PSCredential ($userName, $securePassword)
    Set-ModuleCredential -Cred $cred -ConnectionUri "https://ps.outlook.com/PowerShell-LiveID?PSVersion=5.1.14393.187"
     
    #Define $info array
    $info = @()
     
    #Get all groups
    $Groups = Get-UnifiedGroup | Select-Object Alias,Accesstype,ManagedBy,PrimarySmtpAddress,Displayname,Notes,GroupMemberCount,GroupExternalMemberCount,WhenChanged
     
    foreach($Group in $Groups) {
     
        Write-Host -Object "Number of Groups left to process $GroupsCount" -ForegroundColor Green
        $Members = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType members
        $Owners = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType owners
        $MembersCount = $Members.count
        $OwnerCount = $Group.ManagedBy
     
        foreach($Owner in $Owners){
            $Object=[PSCustomObject]@{
                Name = $Group.Displayname
                Group = $Group.Alias
                Email = $Group.PrimarySmtpAddress
                UserName = $Owner.name
                NumberOfMembers = $Group.GroupMemberCount
                MemberOrOwner = 'Owner'
                NumberOfOwners = $OwnerCount.count
                GroupType = $Group.AccessType
                ExternalMemberCount = $Group.GroupExternalMemberCount
                WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
                Description = $Group.Notes
                }#EndPSCustomObject
            $info+=$object
        }
     
        foreach($Member in $Members){
            $Object=[PSCustomObject]@{
                Name = $Group.Displayname
                Group = $Group.Alias
                Email = $Group.PrimarySmtpAddress
                UserName = $Member.name
                NumberOfMembers = $Group.GroupMemberCount
                MemberOrOwner = 'Member'
                NumberOfOwners = $OwnerCount.count
                GroupType = $Group.AccessType
                ExternalMemberCount = $Group.GroupExternalMemberCount
                WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
                Description = $Group.Notes
                }#EndPSCustomObject
            $info+=$object
        }
     
        $GroupsCount--
     
    }
     
    #Connects to PowerBI
    Connect-PowerBI -AuthorityName m365x992073.onmicrosoft.com -ClientId 'fa7af8a7-56ad-429f-8f57-76b1bd2087e1'  -UserName $username -Password $password
     
    #Removes old data before updating with new data.
    Remove-PowerBIRows -DataSetId '025bc397-b8a2-4a3e-bca1-7502b4e50e33' -TableName 'GroupReport'
     
    #Adds new data.
    Add-PowerBIRows -DataSetId '025bc397-b8a2-4a3e-bca1-7502b4e50e33' -TableName 'GroupReport' -Rows $info

    7. Click Save and then Publish.
    azure13.jpg
    8. Go to overview and select Schedule.
    azure14.jpg

    9. Select Link a schedule to your runbook, Create a new Schedule, fill out the details and then select Create.
    azure15.jpg

    10. You are now good to go. You can get some statistics about the job if you go back to Overview.

Missing a step?  View part 1 and part 2 of this post series. 

 

This article was first published on my blog alexholmeset.blog.

Comments
by arunsubash111 New Member
on ‎05-10-2018 12:03 AM

hi all,

 

we have a requirement, pbix files are present locally in my desktop and i need a scripted method of publishing the files in pbix service.
please help.