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

问题描述

在Power BI(桌面)中，我们使用Power BI查询(M)从Application Insights Analytics中获取数据.我们将配置为每日刷新的Power BI报告发布到Power BI在线.在直到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

有人知道如何解决这个问题吗?

Does anyone know how to solve this?

推荐答案

"502错误的网关消息"通常是由于AA查询返回的数据过多所致.网关的周期限制为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):错误的网关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！