Recently, I wanted to connect my web application build with C# to PowerBI to give my users a unique experience across my website instead of jump to PowerBI site to view the reports. For the integration, I’m going to use the PowerBI APIs.
I found quite difficult and confusing the PowerBI documentation about register an app as service principal and how to integrate PowerBI APIs in your apps.
Getting started with PowerBI
First thing to do is to create an account on PowerBI website. You can use only company emails, that means you can’t use your personal account created for example with Live or Hotmail.
Second, it is highly recommended to download PowerBI Desktop. A link is provided on the PowerBI site but it doesn’t work at all. This link is the best link form the Microsoft site.
In the PowerBI site, you can create your Workspace where you can save your dashboards, reports and datasets.
If you click on an empty workspace, you can see in the address bar the URL of the workspace. This is useful to have same information for your integration. For example, this is a link of a workspace:
https://app.powerbi.com/groups/ed91a91f-0f63-4543-8578-4b13c6563XXX/list/dashboards?ctid=0306b564-e855-4e8a-ad57-2cf9b2b3XXXX
In this URL you find 2 important IDs:
- GroupId is basically the ID (GUID) of your workspace
- TenantId is the ID (GUID) of your organization. In the Azure Portal you see this ID also with the name Directory ID.
To connect a web application to PowerBI we need a couple of other values:
- AuthorityUrl: there are two possibilities here (the 2nd is recommended):
- https://login.windows.net/common/
- https://login.windows.net/<TenantId>
- ApiUrl: https://api.powerbi.com/
- EmbedUrlBase: https://app.powerbi.com/
- ResourceUrl: https://analysis.windows.net/powerbi/api
Using PowerBI Desktop
Power BI Desktop is a free application you install on your local computer that lets you connect to, transform, and visualize your data. With Power BI Desktop, you can connect to multiple different sources of data, and combine them (often called modeling) into a data model.
Also, this data model lets you build visuals, and collections of visuals you can share as reports, with other people inside your organization. Most users who work on business intelligence projects use Power BI Desktop to create reports, and then use the Power BI service to share their reports with others.
The most common uses for Power BI Desktop are as follows:
- Connect to data
- Transform and clean that data, to create a data model
- Create visuals, such as charts or graphs, that provide visual representations of the data
- Create reports that are collections of visuals, on one or more report pages
- Share reports with others by using the Power BI service
So, if you need help or more information how to use PowerBI, there is a very active community on Microsoft.
Finally, when you are ready on your PowerBI Desktop to publish your reports on your workspace, publish them with the button Publish on the top of the screen.
Setup your project
Now, we can create a new web application or use an existing one. The important thing is to add same packages for PowerBI. The packages we need are:
- Microsoft.PowerBI.Api is .NET Client library for Microsoft Power BI public REST endpoints providing access to your Workspaces, Reports, Datasets and more.
- Microsoft.PowerBI.JavaScript is a suite of JavaScript web components for integrating Power BI into your app
So, what we need now is to create a valid token for consuming the PowerBI APIs. There are two different ways to get this token using
- PowerBIClient
- Microsoft.Identity.Client
Get token with PowerBIClient
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.PowerBI.Api;
using Microsoft.PowerBI.Api.Models;
using Microsoft.Rest;
using PowerBIPortal.Data;
using PowerBIPortal.Enums;
using PowerBIPortal.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace PSC.Helpers
{
public class TileHelpers
{
PowerBIClient client;
/// <summary>
/// Creates the power bi client.
/// </summary>
/// <returns>Task<System.Boolean>.</returns>
public async Task<bool> CreatePowerBIClient()
{
bool rtn = false;
if (client == null)
{
var authenticationContext = new AuthenticationContext(SettingsModels.AuthorityUrl);
var credential = new ClientCredential(SettingsModels.ClientId, SettingsModels.ClientSecret);
var authenticationResult = await authenticationContext.AcquireTokenAsync(SettingsModels.ResourceUrl, credential);
if (authenticationResult != null)
{
var tokenCredentials = new TokenCredentials(authenticationResult.AccessToken, "Bearer");
client = new PowerBIClient(new Uri(SettingsModels.ApiUrl), tokenCredentials);
rtn = true;
}
}
else
rtn = true;
return rtn;
}
}
}
Get token with Microsoft.Identity.Client
namespace PSC.Service
{
using DotNetCorePaaS.Models;
using Microsoft.Extensions.Options;
using Microsoft.Identity.Client;
using System;
using System.Linq;
using System.Security;
public class AadService
{
/// <summary>
/// Generates and returns Access token
/// </summary>
/// <param name="appSettings">Contains appsettings.json configuration values</param>
/// <returns></returns>
public static string GetAccessToken(IOptions<ConfigurationModel> appSettings)
{
AuthenticationResult authenticationResult = null;
if (appSettings.Value.AuthenticationMode.Equals("masteruser", StringComparison.InvariantCultureIgnoreCase))
{
// Create a public client to authorize the app with the AAD app
IPublicClientApplication clientApp = PublicClientApplicationBuilder.Create(appSettings.Value.ClientId).WithAuthority(appSettings.Value.AuthorityUri).Build();
var userAccounts = clientApp.GetAccountsAsync().Result;
try
{
// Retrieve Access token from cache if available
authenticationResult = clientApp.AcquireTokenSilent(appSettings.Value.Scope, userAccounts.FirstOrDefault()).ExecuteAsync().Result;
}
catch (MsalUiRequiredException)
{
try
{
SecureString password = new SecureString();
foreach (var key in appSettings.Value.PbiPassword)
{
password.AppendChar(key);
}
authenticationResult = clientApp.AcquireTokenByUsernamePassword(appSettings.Value.Scope, appSettings.Value.PbiUsername, password).ExecuteAsync().Result;
}
catch (MsalException)
{
throw;
}
}
}
else if (appSettings.Value.AuthenticationMode.Equals("serviceprincipal", StringComparison.InvariantCultureIgnoreCase))
{
// For app only authentication, we need the specific tenant id in the authority url
var tenantSpecificUrl = appSettings.Value.AuthorityUri.Replace("organizations", appSettings.Value.TenantId);
// Create a confidetial client to authorize the app with the AAD app
IConfidentialClientApplication clientApp = ConfidentialClientApplicationBuilder
.Create(appSettings.Value.ClientId)
.WithClientSecret(appSettings.Value.ClientSecret)
.WithAuthority(tenantSpecificUrl)
.Build();
try
{
// Make a client call if Access token is not available in cache
authenticationResult = clientApp.AcquireTokenForClient(appSettings.Value.Scope).ExecuteAsync().Result;
}
catch (MsalException)
{
throw;
}
}
try
{
return authenticationResult.AccessToken;
}
catch (Exception)
{
throw;
}
}
}
}
One thought on “Connect web application to PowerBI”