Tag: Azure

Adding a Non-Email Enabled Object to Power BI Gateway Cluster Users

November 25, 2021 / jpries

The Power BI On-premise Enterprise Gateway allows for users to be added as administrators of the gateway cluster via the Powerbi.com portal.

One annoying quirk about this, however, is that at least as of this writing, it is impossible to add users (or groups) which do not have an email address via the user interface.

Attempting to do so will give the error message “These email addresses are invalid or duplicate.” Pretty annoying!

It turns out that it is actually possible to add objects which do not have email addresses associated with them (groups, users, even application SPNs), however it must be done with PowerShell.

Authenticating to the Power BI REST API with C# via SPN (Part 2 – C# Application)

November 18, 2021 / jpries

This post will be a quick primer on authenticating to the Power BI REST API utilizing a C# console application with SPN authentication (Application ID / Client Secret) and utilizing the MSAL library.

For anybody that has followed my previous posts this method is going to be largely similar with only minor differences in the key details. SPN authentication is preferred over Username / Password authentication as it allows the console application to truly authenticate as an application and not “as a user.” This is a much more secure and better method (though it is hasn’t always been available and some API functions don’t yet support this method, sadly). Authenticating via the MSAL library instead of the ADAL library is preferred as Microsoft has announced the termination of support for the ADAL library, so best to keep with the times.

Authenticating to the Power BI REST API with C# via SPN (Part 1 – Cloud Setup)

November 18, 2021 / jpries

A few years ago, I explored connecting to the Power BI REST API with a simple C# console application to retrieve metadata (such as a report inventory and usage information) in my post here.

In order to utilize the Power BI API, you must authenticate to it. In my post, I explored authenticating with a standard username and password. So long as you are not using multi-factor authentication (MFA), this is functional (but not necessarily the best security). If you have MFA enabled, then you cannot use a stored username and password in this manner, so we need another solution. Enter SPN authentication.

The Power BI API initially didn’t have great support for SPN authentication (basic functionality entered Preview in February of 2019), but it has improved greatly over time and is now at the point where it is very usable by custom applications that wish to read data from the API. There are a few extra configuration steps which are necessary in order to get this setup which I’ll cover below.

Usage Monitoring with the Power BI API – A Power BI Report for Tenant Usage Data

February 7, 2020 / jpries

Over the past few weeks, I’ve been putting together a series of posts on how to connect to the Power BI Rest API programmatically to extract inventory and usage data from the service.

Most of those posts (listed at the bottom of this page) are fairly long and technical — as, initially getting started using a program to read Power BI data via the API can be a bit much.

This post is going to be a bit different. Short and sweet. The payoff for all that hard work authenticating to Power BI, requesting data, downloading that data, and storing it in an easy to use SQL table.

With all of the hard work out of the way, its time to build a Power BI report to explore that great Activity Log usage data.

Usage Monitoring with the Power BI API – Tenant Usage Data with Power BI Activity Log (C# and PowerShell)

February 2, 2020 / jpries

A common task for a report developer when learning SSRS and managing your first Reporting Services environment is to access the Report Catalog and ExecutionLog tables in the Report Server SQL database to extract report inventory and usage information.

For a long time, I’ve wanted to do something similar in Power BI — list all of my reports and which ones are used the most and least (and by whom) across all of my workspaces, but due to the nature of the Power BI Service (specifically not having a local and easily accessible juicy SQL database to mine for data) it’s been a much tougher nut to crack.

Recently, I posted a blog on my experiences learning how to query the Power BI REST API in a programmatic way using a C# console application. Expanding on this I could answer a number of report and dataset inventory questions, but now I needed usage data to go along with it.

For some time now, Microsoft has made Power BI Usage data available through the Office 365 Audit Log, but this required special Office 365 permissions outside of Power BI to use. Just recently, however, Microsoft released the Power BI Activity Log, which is a Power BI specific interface into the Office 365 Audit Log data which pertains to Power BI. This means that only Power BI (Service Admin) privileges are needed and the data can be accessed via the Power BI PowerShell cmdlets or via the Power BI Rest API!

Usage Monitoring with the Power BI API – Activity Log (Audit Log) Activity Event Data

January 26, 2020 / jpries

When using the cloud-based Power BI Service, powerbi.com, every action that is taken while logged into the portal — whether it is viewing or publishing a report, creating a new workspace, or even signing up for a pro trial license, that activity is logged within the Microsoft servers as part of the Office 365 audit logs.

Accessing these logs can be accomplished via a couple of different methods (either through the Office 365 Audit Log functionality using the Office 365 Admin Center or PowerShell cmdlets; or through the new Power BI Activity Log (Power BI Get Activity Events) functionality accessible via a PowerShell cmdlet (Get-PowerBIActivityEvent) and an API). There are a few examples out there already on how to use these commands to access the data (and I have a post on accessing the data using the Power BI API and C# coming out in a few week), but there doesn’t seem to be a lot out there about the data itself, which is what I plan to focus on here.

Power BI – Granting Permission to a Custom Application in Azure Active Directory (App Registration)

January 25, 2020 / jpries

The Power BI Service, available at powerbi.com, utilizes Azure Active Directory to handle its authentication. This is important to know for creating a custom Power BI application (in any language — C#, Python, etc.) that reads and write from the Power BI REST API. The one exception to this would be using the Power BI PowerShell cmdlets, as they internally, use their own registration mechanism.

This post rehashes and elaborates on the information I touched on in this post, but I wanted to have a dedicated post on just this topic that I could link back to.

So, what is an App Registration? An App Registration is an entry in Azure Active Directory that lets Azure Active Directory know that you will be using a custom application and associates permissions to that application. Additionally, with the addition of permissions, it associates the application to the Azure Active Directory Resource (aka https://analysis.windows.net/powerbi/api). When your application connects to Azure Active Directory, it provides an Application ID (a GUID generated during the registration process) and some form of authentication. Depending on the type of application, the authentication might be user credentials (Delegated Permissions) or a Client Secret or Certificate (Application Permissions).

Power BI On-premises Gateway Cluster Monitoring (Power Platform API)

January 10, 2020 / jpries

Failover clustering and load balancing was introduced to the Power BI On-premises (aka Enterprise Gateway) almost two years ago in the November, 2017 update of the software.

Recently having the need to add redundancy to a configuration of the gateway, I eagerly began researching how to best implement this configuration.

Upon successfully installing a second member to the cluster, it’s interesting to note that there is absolutely no indication in the web interface that the cluster is now two members strong instead of one. Even more interesting is that this has been this way since it was implemented in 2017.

Pop Quiz: How many Gateway Agents are installed in this Cluster? (Did you guess 2? No? Why would you?)

So, how do we see how many agents are installed in our Gateway Cluster and which version of the software is installed on each? PowerShell to the rescue once again!

Getting Started with the Power BI API – Querying the Power BI REST API Directly (with C#)

January 3, 2020 / jpries

In my previous post I explored the idea of accessing the Power BI API via PowerShell for the purpose of reading report inventory and other object and configuration information and then loading that exported data into SQL Server tables to query to answer various questions about an organization’s configuration.

In this post, I’m going to do the same thing, but taken to the next level (and going a bit overboard) by accessing the Power BI REST API directly with a C# application I wrote.

This post is targeted toward those already comfortable writing basic programs (C#, Python, or anything capable of making a web request and parsing a JSON response). If this isn’t you, there is no harm in sticking with the PowerShell cmdlets, they’re very capable and fast to get started with. For anyone else, let’s dive in!

Atlanta Azure DataFest #1 Recap

August 20, 2018 / jpries

I just recently had the pleasure of attending the inaugural Atlanta Azure DataFest #1 ,which took place Thursday, August 16th and Friday, August 17th at the Alpharetta Microsoft office. This two-day conference, primarily organized by Stuart Ainsworth, was similar in structure and content to SQL Saturday events, but with some interesting changes. The biggest of which was that the conference was held over two days instead of one (and on week days no less.) This allowed for more diversity in the structure of the offered sessions.

Day 1 included multiple sessions which were more “keynote” in nature, in which the entire conference attended the same sessions in one big room. Day 2 included a choice of 3-hour workshops for some in depth lab work, more similar to a traditional pre-con. Both days additionally had the three tracks of sessions allowing attendees to pick and choose the topics of interest. While all sessions were Azure themed, there was enough diversity that anybody should have been able to find something of interest. About my only critique of the sessions would be that too many of them were 3rd-party vendor-centric, spending the entire time going through the details of a non-free piece of 3rd party software rather than only using free or native tools. That likely speaks more to the gaps in the Azure ecosystem (and not being able to do everything efficiently without 3rd party tools) than anything else.

At $50 for the two days, both of which included an excellent hot lunch, the event was a bargain. Everything was very well run and hopefully this new event continues on to future years.

Organizer Stuart Ainsworth starting things off with some morning announcements and Trey Johnson presenting his “Whistle Stop Tour of SQL Server and Azure BI” on Day 1 of Atlanta Azure DataFest.