Want to push data directly to the cloud using the Power BI API but not sure where to start? Our handy guide will help you get the basics under your belt.
Power BI can connect to a wide variety of systems to collect data to drive reports and dashboards. But what if we want to consolidate data from multiple locations, such as a large number of dynamically created SharePoint lists, into a single dataset?
You might be tempted to first consolidate this data into a location such as Azure SQL, before connecting to that SQL database from Power BI. However, this isn’t actually necessary, as we can use Power BI’s own API to create a dataset into which we can push data directly, removing the need for a separate database.
This blog post outlines the steps involved in using the Power BI API to manage data. These are:
- Azure Active Directory (AD) app registration
- User consent
- Authentication
- Creating a dataset
- Writing data
- Creating a report
This first blog post covers app registration, user consent and authentication, while the second part will cover using the Power BI API.
Azure AD app registration
To get started with the Power BI API, you’ll first need to configure a new App Registration in the relevant Azure Active Directory (in this case, blogdemo.onmicrosoft.com). In the ‘required permissions’ section, we have to specify what permissions our application needs to access online services managed through Azure AD. In this case, we want to give full access to Power BI:
In practice you should give an application only the minimum permissions it requires to function, but we’ll ask for everything to keep this demo straightforward. Adding these permissions doesn’t actually give the application access to Power BI – it only indicates that the application wants these permissions. Users must consent to this application having this access – we’ll see how this happens shortly.
From the app registration we need to take a note of the application ID (0ee9fc79-861f-4d34-a439-2c3a4f88c171), and generate and take note of a key. The application ID is like a username for the application, while the key is like a password. We also need a reply URL (which is set during the initial creation of the app registration) – in this case we are using https://www.company-net.com
User consent
As I mentioned earlier, an app registration only lists the permissions requested by an application, rather than granting them. Before an application can access Power BI as a user, the user has to consent to this happening. This is done by the user following a special link to Azure AD that will look something like this:
https://login.microsoftonline.com/common/oauth2/authorize?response_type=code+id_token&redirect_uri=https://www.company-net.com&client_id=0ee9fc79-861f-4d34-a439-2c3a4f88c171
This URL contains the reply URL and application ID (passed in as the client_id) from our app registration. Once a user follows this link and authenticates by entering their username and password, they are asked to consent to the list of permissions we asked for when creating the app registration:
By clicking on ‘Accept’, the user (in this case Alice) consents to our application having those permissions on her content in Power BI. Now that we have our App registration and a user has granted consent, it’s time to actually create our application!
Application authentication
Before we can do anything with the Power BI API, we need to authenticate and acquire an access token that we can then pass into all our calls to the API. To authenticate we perform an HTTP POST to https://login.microsoftonline.com/blogdemo.onmicrosoft.com/oauth2/token, passing in the following values:
- tenant_id –
blogdemo.onmicrosoft.com - grant_type –
password - resource –
https://analysis.windows.net/powerbi/api - client_id –
0ee9fc79-861f-4d34-a439-2c3a4f88c171(our Application Id) - client_secret –
V3lk3h72iBe2gCZk50xFp9EUGCIbZWa1+qfHMAVW+9o= - username –
alice@blogdemo.onmicrosoft.com - password –
<hidden> - scope –
openid
If this POST succeeds, we get back a chunk of JSON that contains a large, impenetrable-looking access_token value. That value is actually a JSON web token (JWT) that we can decode, which can be invaluable in trying to work out why things haven’t worked:
Now that we have this token, we can actually start to use the Power BI API – which I’ll look at in the next blog post in this series.
Follow us on Twitter or LinkedIn to be the first to know when a new blog post is published.