从 PowerBI 到 AI 的查询突然失败并显示 (502):Bad Gateway [英] Query from PowerBI to AI suddenly fails with (502): Bad Gateway

问题描述

在 Power BI(桌面)中，我们使用 Power BI 查询 (M) 从 Application Insights Analytics 获取数据.我们将 Power BI 报告发布到 Power BI Online，并配置了每日刷新.在 2017 年 1 月 25 日(UTC)停止工作之前，它一直运行良好.

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).

我们得到的错误是:

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

这是完整的错误:

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

有人知道怎么解决吗?

推荐答案

502 Bad Gateway Message 通常是由于 AA Query 返回的数据过多.网关限制为 8MB 数据，期间.

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.

您创建了一个仪表板，该仪表板在 2016 年 12 月有效，并在该月初向您提供了所有请求.现在是 2017 年 1 月，它失败了.您可以使用 PowerBI 仪表板使用如下查询从原始结果中计算一些指标.

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

另一个修复

假设您正在计算平均值和百分位数之类的东西，您也可以让 Analytics 为您执行此操作，而 PowerBI 只显示结果.

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.

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

按小时

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.

这篇关于从 PowerBI 到 AI 的查询突然失败并显示 (502):Bad Gateway的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！