Import Azure Active Directory Users into Power BI; An Step toward Dynamic Row-Level Security

Import Azure Active Directory Users to Power BI using PowerShell

A question that I often get when I explain what the dynamic row-level security in Power BI is and how it works, is that; how can we get all the users in an Active Directory tenant imported into Power BI? There are other benefits also to import Azure Active Directory (AAD) users loaded into Power BI for reporting purposes. In this article, I’ll explain a method you can use simply to do that.

Video

Azure Active Directory PowerShell Cmdlets

There are two main methods to fetch the Azure Active Directory information; Microsoft Graph, or PowerShell Cmdlets. Both methods are very useful. However, explaining both in one article will be overwhelming. In this article, I’ll focus on how you can fetch the information using PowerShell Cmdlets. The method I explain here is manual. However, the PowerShell scripts can be automated to run as a scheduled process (I might explain that later in another article too). Let’s see how it works.

The method explained here is exporting the AAD users into a CSV file first, and then Power BI imports data from the CSV. You can use any other intermediate data sources such as Excel, SQL Server, etc if you want to. You just need to use their PowerShell cmdlets or parameters to do that.

Special thanks to Aaron Nelson for helping on preparing the demo for this article. Anytime I have a PowerShell question, he is the master who just finds a way to do it in a few seconds. Connect with him using his blog, Twitter, GitHub, or LinkedIn profile.

Aaron Nelson

Start with PowerShell

To run these commands you can open the Windows PowerShell, but remember to Run it as administrators, as some of the commands requires administrative privilege to do some module installation.

Then start with the script below (This script installs the Azure related modules in the local Powershell library);

Install-Module Az.Resources

Answer the question with “y” meaning that you would like to proceed with the installation and press Enter.

After the installation, the second command to run is to connect the Azure Account, which you can use the below command for it;

Connect-AzAccount

If you use the command just like above, then an authentication window will pop up and ask you to log in using an Azure Account. This should be an account that has administrative access on the Azure Active Directory because we want to fetch the list of users using this account.

However, if the account that you provide here has access to multiple AAD tenants, then the command automatically picks one, in order to specify in detail which tenant and environment, etc to use for this account, you can use more detailed input like below;

Connect-AzAccount
        [-Environment ]
        [-Tenant ]
        [-AccountId ]
        [-Subscription ]
        [-AuthScope ]
        [-ContextName ]
        [-SkipContextPopulation]
        [-MaxContextPopulation ]
        [-UseDeviceAuthentication]
        [-Force]
        [-Scope ]
        [-DefaultProfile ]
        [-WhatIf]
        [-Confirm]
        []

Learn more about the Connect-AzAccount cmdlet here.

After successfully connecting the account and tenant, there is just one more command to run, which is below;

Get-AzADUser -First 10

The command above returns the first 10 users in the tenant. I limited the result with the parameter named “First”, you can also use other parameters as mentioned here;

Get-AzADUser
    [-AppendSelected]
    [-ConsistencyLevel ]
    [-DefaultProfile ]
    [-Expand ]
    [-Filter ]
    [-First ]
    [-Orderby ]
    [-Search ]
    [-Select ]
    [-Skip ]
    []

Ideally, you want this data to be exported into a storage format so that you can use that as the Get Data Source in Power BI. The command below is exporting that as a CSV file;

Get-AzADUser | ConvertTo-CSV | Out-File C:\ADUsers.csv

You can choose the path of the output file as whatever you want.

This file now can be simply used in Power BI Desktop by Get Data from Text/CSV;

You then need to remove the top first row and then use the First Row as Headers, and that’s it. this is the data table with all Azure Active Directory Users;

This data then can be used for reporting, and most importantly, for the user table with all the emails for the Dynamic Row-Level Security configuration. The dataset has a column determining if the user is a guest or member.

Summary & Considerations

The method mentioned in this article is manual. It still needs to be automated so that the scheduled refresh of Power BI automatically picks up the updates. This can be done with a scheduled process to run the PowerShell script.

There is another way to populate the information from Azure Active Directory, and that is using Microsoft Graph. I’ll explain that method separately in another article.

In this article, you’ve learned how you can use PowerShell scripts to extract the Azure Active Directory users and export it as CSV, and then import that data into Power BI. This data can be extremely useful for a Dynamic row-level security setup.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply