Query from PowerBI to AI suddenly fails with (502): Bad Gateway

Question

In Power BI (Desktop) we use a Power BI Query (M) to get data from Application Insights Analytics. We published the Power BI Report to Power BI online configured with a daily refresh. It worked fine until it stopped working on 25-1-2017 (UTC).

The error we get is:

DataSource.Error: Web.Contents failed to get contents from '.....' (502): Bad Gateway

This is the complete error:

DataSource.Error: Web.Contents failed to get contents from 'https://management.azure.com/subscriptions/<subscriptionId>/resourcegroups/fps.fsa/providers/microsoft.insights/components/4PS%20Field%20Service%20iOS%20-%20iOS/api/query?api-version=2014-12-01-preview&csl=customEvents%0A%7C%20where%20timestamp%20%3E%20ago%2830d%29%0A%7C%20order%20by%20timestamp%20desc%0A%7C%20extend%20dimensionUserId%20%3D%20tostring%28customDimensions.%5B%27userId%27%5D%29%0A%7C%20extend%20dimensionHost%20%3D%20tostring%28customDimensions.%5B%27url%27%5D%29%0A%7C%20extend%20measurementQuantity%20%3D%20iff%28%20isnotempty%28customMeasurements.%5B%27value%27%5D%29%2C%20todouble%28customMeasurements.%5B%27value%27%5D%29%2C%200.0%29%0A%7C%20extend%20measurementKey%20%3D%20tostring%28customDimensions.%5B%27key%27%5D%29%0A%7C%20extend%20platform%20%3D%20%27iOS%27%0A&x-ms-app=AAPBI' (502): Bad Gateway
Details:
    DataSourceKind=Web
    DataSourcePath=https://management.azure.com/subscriptions/<subscriptionId>/resourcegroups/fps.fsa/providers/microsoft.insights/components/4PS%20Field%20Service%20iOS%20-%20iOS/api/query
    Url=https://management.azure.com/subscriptions/<subscriptionId>/resourcegroups/fps.fsa/providers/microsoft.insights/components/4PS%20Field%20Service%20iOS%20-%20iOS/api/query?api-version=2014-12-01-preview&amp;csl=customEvents%0A%7C%20where%20timestamp%20%3E%20ago%2830d%29%0A%7C%20order%20by%20timestamp%20desc%0A%7C%20extend%20dimensionUserId%20%3D%20tostring%28customDimensions.%5B%27userId%27%5D%29%0A%7C%20extend%20dimensionHost%20%3D%20tostring%28customDimensions.%5B%27url%27%5D%29%0A%7C%20extend%20measurementQuantity%20%3D%20iff%28%20isnotempty%28customMeasurements.%5B%27value%27%5D%29%2C%20todouble%28customMeasurements.%5B%27value%27%5D%29%2C%200.0%29%0A%7C%20extend%20measurementKey%20%3D%20tostring%28customDimensions.%5B%27key%27%5D%29%0A%7C%20extend%20platform%20%3D%20%27iOS%27%0A&amp;x-ms-app=AAPBI

Does anyone know how to solve this?

Answer 1

The 502 Bad Gateway Message is usually due to the AA Query returning too much data. The gateway is limited to 8MB of data, period.

Example

You created a dashboard that worked in December 2016 and gave you all requests from the start of the month. Now it's January 2017 and it's failing. You use the PowerBI Dashboard to calculate some metrics from the raw results using a query like the one below.

requests | where timestamp > datetime(2016-12-01)

The fix

Determine how many days you really care about. If your intention was to get all requests and the timing from the first of the month you can cut out a lot of extra data by limiting the time range to this month AND by only projecting the columns you need

requests | where timestamp > startofmonth(now()) | project name, duration

Another fix

Assuming you are calculating things like averages and percentiles you could also just have Analytics do this for you and PowerBI just display the results.

requests | where timestamp > startofmonth(now()) | summarize count(), avg(duration), min(duration), max(duration), stdev(duration), percentiles(duration, 50, 75, 90, 95, 99) by name

More Examples

You might want a meaningful graph, so you could split up the aggregations by a time period. This would give you a lot of data in return, but severely less than you'd get if you queried the raw data points.

By Day

requests | where timestamp > startofmonth(now()) | summarize count(), avg(duration), min(duration), max(duration), stdev(duration), percentiles(duration, 50, 75, 90, 95, 99) by bin(timestamp, 1d), name

By Hour

requests | where timestamp > startofmonth(now()) | summarize count(), avg(duration), min(duration), max(duration), stdev(duration), percentiles(duration, 50, 75, 90, 95, 99) by bin(timestamp, 1h), name

I've only given a few examples and you'd have to make sure they fit in with the intention of your dashboard.

Answer 2

The recommendation as James suggested is to limit the result size, yet if you still need to return a larger data set you can work directly with AI API instead of ARM.

1) You will need to create an API Key, see https://dev.applicationinsights.io/documentation/Authorization/API-key-and-App-ID

2) Next you will need to update the Power BI M script that you exported from Analytics by replacing the ARM URL with AI API:

Replace ARM call:

.....
Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourcegroups/<RESOURCE GROUP>/providers/microsoft.insights/components/<APP NAME>/api/query?api-version=2014-12-01-preview", [Query=[#"csl"="requests",#"x-ms-app"="AAPBI"],Timeout=#duration(0,0,4,0)])),
.....

With AI API call:

....
Source = Json.Document(Web.Contents("https://api.applicationinsights.io/beta/apps/<APPLICATION_ID>/query?api-version=2014-12-01-preview", [Query=[#"csl"="requests",#"x-ms-app"="AAPBI"],Timeout=#duration(0,0,4,0)])),
.....

3) Finally, update credentials to basic, and use your API Key

enter image description here