I’ve extolled the virtues of Application Insights previously on the blog, as I believe it is a nice solution that can provide valuable intelligence concerning your web applications. Whether you are looking to extend the solution to capture additional properties relating to your users, leverage the in-built availability testing features to receive alerts whenever your application is down or incorporate it as part of your existing Azure deployment templates, I am confident that Application Insights can fit multiple business needs. Indeed, it can offer comparable, if not superior, functionality when compared with tools such as Google Analytics. A large part of this comes down to the flexible data extraction options within the tool as standard, which allows you to export data for consumption as part of a Stream Analytics Job or to quickly generate M query code snippets for use within Power BI Desktop, to allow you to build out an engaging reporting solution. The first of these options is particularly appealing and a route I have been exploring with a keen interest in the past few weeks. Via the handy Export options available within the Application Insights Log Analytics window, it is possible to get this code easily after you have defined your query:
The example code that is generated can be found below:
/*
The exported Power Query Formula Language (M Language ) can be used with Power Query in Excel
and Power BI Desktop.
For Power BI Desktop follow the instructions below:
1) Download Power BI Desktop from https://powerbi.microsoft.com/desktop/
2) In Power BI Desktop select: 'Get Data' -> 'Blank Query'->'Advanced Query Editor'
3) Paste the M Language script into the Advanced Query Editor and select 'Done'
*/
let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/v1/apps/f07da591-19b0-4b0d-821a-908f0cc3d5ab/query",
[Query=[#"query"="union pageViews,customEvents
| where timestamp between(datetime(""2019-06-29T09:00:00.000Z"")..datetime(""2019-06-30T09:00:00.000Z""))
| summarize Users=dcount(user_Id) by bin(timestamp, 1h)
| order by timestamp asc
| render barchart
",#"x-ms-app"="AAPBI",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string", Text.Type },
{ "int", Int32.Type },
{ "long", Int64.Type },
{ "real", Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool", Logical.Type },
{ "guid", Text.Type },
{ "dynamic", Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuery
With clear instructions provided, this can be quickly added into Power BI Desktop…but you do have to make an important choice concerning your chosen authentication method to access Application Insights. For development/testing purposes, it is possible to use anĀ Organizational account for authentication, provided that the Azure Active Directory user in question has been granted the relevant permissions at the subscription/resource level to interact with Application Insights. However, this is not recommended as a solution beyond these realms, for the following reasons:
- If a user accounts password ever needs to change, due to enforced password policy, then you will need to update any credentials within Power BI Online manually. For multiple reports, this could take a considerable amount of time to do each month.
- If the user account is ever disabled or their permissions revoked, then likewise, you would need to go through and updateĀ every affected report.
- The user account in question will likely have access to other services or privileged access levels; therefore, using this does not conform to the “least privileged” account security principle.
With this in mind, I would advise that you take advantage of the API access capabilities within the tool and generate an API key for the service; then, you can modify the authentication method accordingly to use this API key. There is a detailed blog post from Phidiax that goes through each of the steps involved to get this working successfully within Power BI Desktop using Anonymous authentication, however, for some strange reason, this causes the following error when your report is deployed onto Power BI Online:
Perhaps Microsoft has changed something in the backend since 2017, which is why this no longer works. The way to get around this issue is twofold:
- First, leave your extracted M code query from Application Insights unaltered.
- For authentication methods, ensure that Basic is selected and populate theĀ User Name field with theĀ API Key value generated from Application Insights, as indicated in the screenshot below.
As part of this, you should also ensure that theĀ Privacy Settings for your data source are set appropriately.
It’s nice that a solution is available, to allow us to work with API Keys when connecting to Application Insights from Power BI. Unfortunately, I cannot take full credit for it, and must instead give all thanks to my esteemed developer colleague Andrew Bennett for figuring out this particular issue. Thanks, Andrew! š