PowerBI中避免远程数据表重复查询的最优方案问询

问题场景 #

远程数据库服务器my_db_server下的my_db库包含表my_source_data_table，现有两个子查询（Query A、Query B）依赖该表，当前查询代码如下：

初始查询（MySourceDataTable） #

let
    Source = Sql.Databases("<my_db_server>"),
    Database = Source{[Name="<my_db>"]}[Data],
    MySourceDataTable = Database {[Schema="dbo",Item="<my_source_datatable>"]}[Data],
in
    MySourceDataTable 

依赖查询 #

Query A（按Foo、Bar列排序）：

let
    Source = MySourceDataTable,
    ReorderColumnsFooBar = Table.ReorderColumns(Source ,{"Foo", "Bar"})
in
    ReorderColumnsFooBar

Query B（按Bar、Foo列排序）：

let
    Source = MySourceDataTable,
    ReorderColumnsBarFoo = Table.ReorderColumns(Source ,{"Bar", "Foo"})
in
    ReorderColumnsBarFoo 

当前整体刷新耗时约90秒，其中远程查询占80%，需优化以避免重复远程请求。

执行逻辑分析 #

默认情况下，PowerBI会对每个依赖查询单独触发远程请求。原因是Table.ReorderColumns操作支持查询折叠，PowerBI会将列重排逻辑转换为不同的SQL语句（分别按Foo, Bar和Bar, Foo的顺序查询），因此两个子查询会各自发起一次远程数据库请求，导致两次耗时的远程查询。

优化方案：显式缓存远程数据 #

在初始查询的末尾添加Table.Buffer()函数，可以将远程表的数据一次性加载到PowerBI的内存缓存中，后续两个子查询直接复用缓存数据，不再触发远程请求。

修改后的初始查询代码：

let
    Source = Sql.Databases("<my_db_server>"),
    Database = Source{[Name="<my_db>"]}[Data],
    MySourceDataTable = Database {[Schema="dbo",Item="<my_source_datatable>"]}[Data],
in
    Table.Buffer(MySourceDataTable)

原理说明 #

• Table.Buffer()会强制将表数据加载到内存，中断查询折叠，阻止PowerBI将后续子查询的操作推送到远程数据库。
• 所有依赖该初始查询的子查询都会直接读取内存中的缓存数据，远程查询仅执行一次，整体耗时会大幅降低（仅保留一次远程查询的80秒，加上本地处理的10秒左右）。

注意事项 #

• 确保my_source_data_table的数据量在PowerBI的内存承载范围内，避免因数据过大导致内存不足。
• 如果后续需要对初始查询添加过滤、筛选等可折叠操作，建议先完成这些操作后再使用Table.Buffer()，避免缓存不必要的数据。

内容的提问来源于stack exchange，提问作者HeXor