使用 Power BI Desktop 或 Power BI 服务时,可以连接各种类型的数据源,并且可以通过不同的方式连接这些数据。 可以将数据导入 Power BI,也可以在其原始源存储库中直接连接数据,前者是获取数据最常见的方法,后者称为 DirectQuery。 本文介绍 DirectQuery 及其功能,具体包括以下主题:

  • DirectQuery 的各种连接选项
  • 何时应考虑使用 DirectQuery 而非导入的相关指导
  • 使用 DirectQuery 时带来的弊端
  • 使用 DirectQuery 的最佳做法

简而言之,使用 DirectQuery 和导入的最佳做法是:

  • 如可能,应使用“导入”将数据导入 Power BI。 这样做可以充分利用 Power BI 的高性能查询引擎,并提供高度交互和功能完善的数据体验。

  • 如果通过导入数据无法实现目标,则可以考虑使用 DirectQuery。 例如,如果数据被频繁更改并且报表必须反映最新数据,DirectQuery 可能是最佳选择。 但是,通常只有当基础数据源可以为典型聚合查询提供交互式查询(少于 5 秒)并且能够处理生成的查询负载时,使用 DirectQuery 才可行。 此外,考虑结合使用 DirectQuery 的限制列表时应格外慎重,以确保仍然能够实现目标。

Power BI 为两种连接模式(导入和 DirectQuery)提供的功能集将随时间的推移不断改进。 这将包括在使用导入数据时提供更强的灵活性,以便可以在更多情况下使用导入模式,以及消除使用 DirectQuery 时带来的的一些弊端。 无论如何改进,使用 DirectQuery 时都会始终将基础数据源的性能作为主要考虑因素。 如果该基础数据源速度缓慢,则对该数据源使用 DirectQuery 将仍然不可行。

本主题介绍 Power BI(而不是 SQL Server Analysis Services)中的 DirectQuery。 DirectQuery 也是 SQL Server Analysis Services 的功能之一,下面描述的许多细节同样适用于 SQL Server Analysis Services,但还是存在重大区别。 若要详细了解如何使用 SQL Server Analysis Services 的 DirectQuery,请参阅 SQL Server Analysis Services 2016 中的 DirectQuery

本文重点介绍 DirectQuery 的建议工作流、在 Power BI Desktop 中创建报表的位置,以及如何在 Power BI 服务中直接连接数据。

Power BI 连接模式

Power BI 可连接大量不同类型的数据源,包括:

  • 联机服务(Salesforce、Dynamics 365 等)
  • 数据库(SQL Server、Access、Amazon Redshift 等)
  • 简单文件(Excel、JSON 等)
  • 其他数据源(Spark、网站、Microsoft Exchange 等)

这些数据源通常都可以使用导入方法将数据导入 Power BI。 其中某些数据源还可以使用 DirectQuery 进行连接。 有关支持 DirectQuery 的其他一组数据源的信息,请参阅 DirectQuery 支持的数据源一文。 将来会有更多数据源支持 DirectQuery,我们会主要关注可以提供良好的交互式查询性能的数据源。

SQL Server Analysis Services 是一个特例。 连接到 SQL Server Analysis Services 时,你可以选择导入数据,或使用实时连接。 使用实时连接与使用 DirectQuery 在以下方面类似:不导入任何数据,始终对基础数据源进行查询以刷新视觉对象;但在许多其他方面实时连接又有所不同,因此使用了不同的术语(“实时”和“DirectQuery”)。

以下各节详细说明了这三个数据连接选项 -“导入”、“DirectQuery”和“实时连接”。

导入连接

如果在 Power BI Desktop 中使用“获取数据”以连接数据源(如 SQL Server),并且选择“导入”,则该连接行为如下所示:

  • 在初始“获取数据”体验期间,所选的每个表定义一个将返回一组数据的查询(可以在加载数据之前对这些查询进行编辑,例如应用筛选器、聚合数据或联接不同的表)。
  • 加载时,这些查询定义的所有数据都将被导入 Power BI 高速缓存。
  • 在 Power BI Desktop 中生成视觉对象时,将查询导入的数据。 Power BI 存储可确保查询速度非常快,因此可立即反映视觉对象的所有更改。
  • 但对基础数据的任何更改都不会反映在任何视觉对象中。 必须先“刷新”,然后重新导入数据。
  • 报表(.pbix 文件)发布到 Power BI 服务时,就会创建一个数据集并上传到 Power BI 服务。 该数据集包含导入的数据。 随后可以对该数据设置按计划刷新,例如每天重新导入数据。 可能必须配置本地数据网关,具体取决于原始数据源的位置。
  • 在 Power BI 服务中打开现有报表或创作新报表时,将再次查询导入的数据,确保交互性。
  • 可以将视觉对象或整个报表页固定为仪表板磁贴。 每次刷新基础数据集时,将自动刷新磁贴。

DirectQuery 连接

如果在 Power BI Desktop 中使用“获取数据”连接数据源,并且选择“DirectQuery”,则该连接行为如下所示:

  • 在初始“获取数据”体验期间,会选择数据源。 对于关系数据源,这意味着选择一组表,每个表仍定义一个查询,该查询在逻辑上返回一组数据。 对于多维数据源(如 SAP BW),将仅选择数据源。
  • 但在加载时,实际上不会将数据导入 Power BI 存储。 相反,在 Power BI Desktop 中生成视觉对象时,会向基础数据源发送查询以检索所需数据。 刷新视觉对象所花费的时间取决于基础数据源的性能。
  • 对基础数据的任何更改都不会立即反映在任何现有视觉对象中。 仍然需要先“刷新”,因此将针对每个视觉对象重新发送必需的查询,并根据需要对视觉对象进行更新。
  • 将报表发布到 Power BI 服务时,将再次在 Power BI 服务中生成数据集(和导入模式相同)。 但是,该数据集不包含任何数据。
  • 在 Power BI 服务中打开现有报表或创作新报表时,将再次查询基础数据源以检索所需数据。 可能必须配置本地数据网关,具体取决于原始数据源的位置(和导入模式中刷新数据所需步骤相同)。
  • 可以将视觉对象或整个报表页固定为仪表板磁贴。 为了确保迅速打开仪表板,磁贴会按计划(例如每小时)自动刷新。 可以控制此刷新频率,以反映数据更改频率和查看最新数据的重要性。 因此,打开仪表板时,磁贴将反映自上次刷新后的数据,而不一定反映对基础源所做的最新更改。 可以反复刷新打开的仪表板,使其保持最新。

实时连接

如果连接到 SQL Server Analysis Services (SSAS),可选择从所选数据模型导入数据,或实时连接到所选数据模型。 如果选择“导入”,则需要针对该外部 SSAS 数据源定义查询,以便正常导入数据。 如果选择“实时连接”,则不需要定义查询,字段列表中会显示整个外部模型。 如果选择 DirectQuery,生成视觉对象时,将向外部 SSAS 源发送查询。 与 DirectQuery 不同的是,实时连接在创建新模型方面没有意义,即不能定义新的计算列、层次结构、关系等。 相反,只需直接连接到外部 SSAS 模型即可。

上段中所述的情况也适用于连接以下数据源(没有导入数据选项的情况除外):

  • Power BI 数据集(例如,连接到以前已创建并发布到服务的 Power BI 数据集以创作新报表将其覆盖)
  • Common Data Services

SSAS 报表发布到 Power BI 服务时,其行为在以下方面与 DirectQuery 报表类似:

  • 在 Power BI 服务中打开现有报表或创作新报表时,查询基础 SSAS 数据源(可能需要一个本地数据网关)

  • 仪表板磁贴按计划(例如每小时,或定义的任何频率)自动刷新

但两者也有重大区别:对于实时连接,打开报表的用户标识将始终传递到基础 SSAS 数据源。

介绍这些比较后,本文剩余部分将着重介绍 DirectQuery。

DirectQuery 在什么情况下有用?

下表描述了使用 DirectQuery 连接特别有用的场景,包括将数据保留在原始源中被认为是有益的情况。 该描述内容包括对特定场景是否适用于 Power BI 的讨论。

限制 说明
数据频繁变化,需要几乎“实时”的报表 导入数据模型最快可以每小时刷新一次。 因此,如果数据不断改变,并且报表必须显示最新数据,则使用按计划刷新的导入可能无法满足需求。 请注意,也可以将数据直接流式传输到 Power BI 中(尽管这种情况下数据量有限制)。

相比之下,使用 DirectQuery 意味着打开或刷新报表或仪表板将始终显示源中最新的数据。 此外,可以更频繁地(每 15 分钟)更新仪表板磁贴。
数据量非常大 如果数据量非常大,直接全部导入肯定不可行。 相比之下,DirectQuery 不需要大量数据传输,因为可以进行就地查询。

但是,大量数据可能也意味着对该基础源查询的速度很慢(如本文后面部分的“使用 DirectQuery 的影响”中所述。 当然,并不总是需要导入全部详细数据。 相反,数据可以在导入过程中预先聚合(通过“查询编辑器”可以轻松实现此操作)。 在极端情况下,可以只导入每个视觉对象所需的聚合数据。 所以 DirectQuery 是处理大量数据的最简单方法,请始终记住,如果基础数据源太慢,导入聚合数据也许是一个解决方案。
基础数据源中定义的安全规则 导入数据时,Power BI 将使用当前用户凭据(从 Power BI Desktop),或作为配置计划刷新(从 Power BI 服务)的一部分定义的凭据,连接到数据源。 因此,在发布和共享此类报表时,必须注意只与允许查看相同数据的用户共享,或将行级别安全性定义为数据集的一部分。

理想情况下,由于 DirectQuery 始终查询基础数据源,这将允许应用该基础数据源中的任何安全规则。 但是,目前 Power BI 将始终使用与采用“导入”方法时使用的相同凭据来连接基础数据源。

因此,除非 Power BI 允许将报表使用者的标识传递给基础数据源,否则 DirectQuery 在数据源安全性方面没有任何优势。
数据主权限制应用 某些组织对数据主权制定有相应策略,这意味着数据不能离开组织规定的前提。 基于导入的解决方案很显然会存在问题。 相比之下,如果使用 DirectQuery,数据将保留在基础数据源中。

但应注意,由于磁贴按计划刷新,即使使用 DirectQuery,某些视觉对象级别的数据缓存也会保留在 Power BI 服务中。
基础数据源是包含度量值的 OLAP 数据源 如果基础数据源包含度量值(如 SAP HANA 或 SAP Business Warehouse),则导入数据将引发其他问题。 这意味着导入的数据处于由查询定义的特定聚合级别。 例如,按 Class、Year 和 City 衡量总销售额。 如果构建的视觉对象要求较高级别聚合数据(如按 Year 的总销售额),它会进一步聚合总值。 这对于附加式度量(如 Sum、Min)没有问题,但对非附加式(如 Average、DistinctCount),会产生问题。

为了轻松从源中直接获取正确的聚合数据(根据特定视觉对象的需要),必须像 DirectQuery 一样按视觉对象发送查询。

连接到 SAP Business Warehouse (BW) 时,可以选择 DirectQuery 进行这种度量。 DirectQuery 和 SAP BW 中进一步介绍了对 SAP BW 的支持。

但是,当前 SAP HANA 上的 DirectQuery 将其视为关系数据源对待,因此提供的行为类似于导入。 这在 DirectQuery 和 SAP HANA 中有进一步介绍。

因此,综上所述,鉴于 Power BI 中 DirectQuery 的当前功能,其优势体现在以下方面:

  • 数据频繁变化,需要几乎“实时”的报表
  • 处理非常大的数据量,而无需预先聚合
  • 数据主权限制应用
  • 源是包含度量值(如 SAP BW)的多维度源

请注意,上一列表中的详细信息仅涉及 Power BI 的使用。 另外,始终可以选择使用外部 SQL Server Analysis Services(或 Azure Analysis Services)模型导入数据,然后使用 Power BI 连接该模型。 虽然这种方法需要其他技能,但它可以提供更强的灵活性。 例如,可以导入更多大容量数据,并且刷新数据的频率不受限制。

使用 DirectQuery 的影响

如本节内容所述,使用 DirectQuery 确实存在潜在的负面影响。 其中一些限制因使用的具体数据源不同而略有不同。 这在适当情况下会被提及,差异显著的数据源将由单独的主题介绍。

基础源的性能和负载

使用 DirectQuery 时,整体体验很大程度取决于基础数据源的性能。 如果刷新每个视觉对象(如更改切片器值后)都需要几秒钟时间(小于 5 秒),则等待体验是合理的,但与经常使用的将数据导入到 Power BI 的即时响应相比,可能仍感觉有些缓慢。 相反,如果数据源缓慢意味着单个视觉对象花费的时间更长(几十秒),甚至可能超出查询的时间,则体验会非常差。

除基础数据源的性能外,还应仔细考虑置于其上的负载(这通常会影响性能)。 正如下文所进一步讨论的,打开共享报表的每个用户以及定期刷新的每个仪表板磁贴都会向基础数据源发送查询(每个视觉对象至少发送一个查询)。 这种情况需要数据源能够处理此类查询负载,同时仍保持良好的性能。

单个数据源限制

导入数据时,可以将来自多个源的数据组合到单个模型中,例如,可以轻松联接公司 SQL Server 数据库中的数据和保存在 Excel 文件中的本地数据。 使用 DirectQuery 时,无法实现该操作。 如果对源选择 DirectQuery,则只能使用来自单个源(如单个 SQL Server 数据库)中的数据。

数据转换限制

同样,在“查询编辑器”中应用数据转换也存在限制。 如果使用导入数据,可以轻松应用一组复杂的变换来清理和重新整理数据,然后再使用数据创建视觉对象(例如解析 JSON 文档或将数据从列形式转换为行形式)。 这些转换在 DirectQuery 中受到更多限制。 首先,连接到 SAP Business Warehouse 等 OLAP 数据源时,不能定义任何转换,整个外部“模型”都来自该数据源。 对于类似 SQL Server 的关系数据源,每个查询仍可以定义一组转换,但是出于性能原因,这些转换将受到限制。 任何此类转换需要应用于基础数据源的每个查询,而不是在数据刷新时应用一次,因此它们仅限于可以合理地转换为单个本机查询的转换。 如果使用的转换过于复杂,将收到错误警报,必须删除它,或将模型切换到导入模式。

此外,“获取数据”对话框或“查询编辑器”产生的查询将用于生成和发送的查询的子选择中,以检索视觉对象所需的数据。 因此,在“查询编辑器”中定义的查询必须在此上下文中有效。 具体而言,这意味着不能使用包含公用表表达式或调用存储过程的查询。

建模限制

术语“建模”在此上下文中表示完善和丰富原始数据(作为创建使用它的报表的一部分)。 示例包括:

  • 定义表之间的关系
  • 添加新计算(计算列和度量值)
  • 重命名和隐藏列和度量值
  • 定义层次结构
  • 定义列的格式设置、默认汇总以及排序顺序
  • 分组或聚类值

如果使用 DirectQuery,仍然可以丰富大多数这些模型,当然还有正被丰富的原始数据的原则,以便改进后续使用。 但是,使用 DirectQuery 时,有些建模功能将不可用或受到限制。 通常,应用限制的目的是避免性能问题。 下列项目符号列表列出了所有 DirectQuery 数据源通用的限制。 如本文接近末尾处的“数据源详细信息”中所述,可能有其他限制适用于特定数据源。

  • 无内置日期层次结构:导入数据时,默认情况下,每个日期/日期时间列都将具有默认的内置日期层次结构。 例如,如果导入的销售订单表包含 OrderDate 列,则在视觉对象中使用 OrderDate 时,可以选择要使用的适当级别(年、月、日)。 使用 DirectQuery 模式时,此内置日期层次结构不可用。 但是请注意,如果基础数据源中存在“日期”表(如许多数据仓库中常见的那样),则可以照常使用 DAX 时间智能函数。

  • 在计算列中的限制:计算列仅限于行内,因为它们只能引用同一表中其他列的值,不能使用任何聚合函数。 此外,可用的 DAX 标量函数(如 LEFT())被限制为只能推送到基础数据源,因此从很大程度上取决于数据源的具体功能。 创建计算列的 DAX 时,不支持的功能不会在自动完成中列出,如果使用则会导致错误。

  • 不支持父-子 DAX 函数:在 DirectQuery 模型中,不能使用 DAX PATH() 系列函数,这类函数通常处理父-子结构,如帐户图表或员工层次结构图表。

  • 度量值的(默认)限制:默认情况下,可在度量值中使用的 DAX 函数和表达式受限。 同样,如果使用无效的函数或表达式,自动完成将不会列出该函数,并且会出错。 可以确定的原因是,默认情况下,度量值被限制为简单的度量值,这些度量值本身不太可能导致任何性能问题。 高级用户可以选择绕过此限制,方法是依次选择“文件”>“选项”和“设置”>“选项”>“DirectQuery”,然后选择选项“允许 DirectQuery 模式下的度量值不受限制”。 选中该选项后,即可使用对度量值有效的任何 DAX 表达式。 但是,用户必须知道,可在导入数据的情况下正常工作的某些表达式,在 DirectQuery 模式下则可能会导致针对后端数据源的查询速度缓慢。

    • 例如,默认情况下:

      • 可以创建一个简单的销售金额相加的度量值: SalesAmount = SUMX(Web_Sales, [ws_sales_price]* [ws_quantity])
      • 不能创建一个度量值,然后在所有项上平均销售额:AverageItemSalesAmount = AVERAGEX('Item', [SalesAmount])

      原因是,如果有大量项,此类度量值可能导致性能不佳。

  • 不支持计算表:DirectQuery 模式不支持使用 DAX 表达式定义计算表。

  • 关系筛选仅限于单方向:使用 DirectQuery 时,不能将交叉筛选关系方向设置为“双向”。 例如,以下三个表格不能生成显示每个客户[性别]和每个客户购买的产品[类别]数量的视觉对象。 本白皮书中详细说明了这类双向筛选的使用(本文介绍了 SQL Server Analysis Services 上下文中的示例,但基本要点也同样适用于 Power BI)。

    同样,施加此限制也是出于性能影响的原因。 此方面的一个特别重要的应用是,将行级别安全性定义为报表的一部分时,由于常见模式是在用户和其允许访问的实体之间建立多对多关系,因此必须使用双向筛选才能执行此操作。 但是,对 DirectQuery 模型使用双向筛选应十分谨慎,并注意性能方面产生的任何不利影响。

  • 无聚类分析:使用 DirectQuery 时,不能使用聚类分析功能自动查找组

报表限制

几乎所有的报表功能都支持 DirectQuery 模型。 在这种情况下,只要基础数据源提供了合适的性能水平,就可以使用相同的可视化组件集。 但在发布报表后,Power BI 服务中提供的一些其他功能会受到一些重要限制,如以下项目列表所述:

  • 不支持快速见解:Power BI 快速见解功能可快速搜索数据集的不同子集,同时应用一组复杂的算法来发现潜在相关的见解。 此功能对查询的性能要求非常高,因此在使用 DirectQuery 的数据集上不可用。

  • 不支持问答:使用 Power BI 中的“问答”可利用直观、自然的语言功能浏览数据并接收图表和图形形式的答案。 但是,当前使用 DirectQuery 的数据集不支持此功能。

  • 使用“在 Excel 中浏览”可能会导致性能不佳:可以在数据集上使用“在 Excel 中浏览”功能浏览数据。 这将允许在 Excel 中创建数据透视表和数据透视图。 尽管使用 DirectQuery 的数据集支持此功能,但性能通常比在 Power BI 中创建视觉对象的性能更慢,因此如果使用 Excel 对你的方案非常重要,则决定使用 DirectQuery 时请考虑这一点。

安全性

如本文前面所述,使用 DirectQuery 的报表在发布到 Power BI 服务后,将始终使用相同的固定凭据连接到基础数据源。 同时请注意,此处专指 DirectQuery,而不是 SQL Server Analysis Services 的实时连接,两者在这方面不同。 因此,发布 DirectQuery 报表后,必须立即配置用户要使用的凭据。 在完成此操作之前,打开 Power BI 服务报表将导致错误。

提供用户凭据后,所有打开报表的用户都可使用这些凭据。 这方面与导入数据完全相同,即除非行级别安全性已定义为报表的一部分,否则每个用户都将看到相同的数据。 因此,如果在基础数据源中定义了任何安全规则,共享报表时也必须注意这一点。

Power BI 服务中的行为

本部分介绍 Power BI 服务中 DirectQuery 报表的行为,主要是为了使读者了解将置于后端数据源的负载程度、需要考虑的共享报表和仪表板的用户数、报表的复杂性以及行级别安全性是否已在报表中定义。

报表 – 打开、与之交互、编辑

打开报表时,将刷新当前可见页面上的所有视觉对象。 每个视觉对象通常需要至少一个查询以对基础数据源进行查询。 某些视觉对象可能需要多个查询,例如,显示来自两个不同事实数据表的聚合值、包含更复杂的度量值或包含非附加式度量值的总和(如 Count Distinct)。 移动到新页面将导致刷新这些视觉对象,从而生成一组针对基础数据源的新查询。

报表上的所有用户交互都可能导致刷新视觉对象。 例如,选择切片器上的不同值需要发送一组新查询以刷新所有受影响的视觉对象。 单击视觉对象以交叉突出显示其他视觉对象或更改筛选器也是如此。

当然,类似情况还包括编辑新报表,该报表需要为每个步骤发送查询以产生所需的最终视觉对象。

存在一些包含结果的缓存,如果获得完全相同的最新结果,将即时刷新视觉对象。 如果将任何行级别安全性定义为报表的一部分,此类缓存不会在所有用户中共享。

仪表板刷新

单个视觉对象或整个页面可以作为磁贴固定到仪表板。 基于 DirectQuery 数据集的磁贴会按计划自动刷新,并将查询发送到后端数据源。 默认情况下,每小时刷新一次,但作为数据集设置的一部分,可以将其配置为每周和每 15 分钟刷新一次。

如果模型中未定义行级别安全性,这意味着每个磁贴将刷新一次,并在所有用户之间共享结果。 如果定义了行级别安全性,则会有很大的乘数效应 – 每个磁贴需要按用户单独向基础源发送查询。

因此,如果某个仪表板有 10 个磁贴、与 100 个用户共享、是在使用具有行级别安全性的 DirectQuery 数据集创建的,并且配置为每 15 分钟刷新一次,则将导致每 15 分钟向后端数据源发送至少 1000 个查询。

因此必须慎重考虑使用行级别安全性和计划刷新配置。

超时

在 Power BI 服务中,4 分钟超时应用于单个查询,超过该时间的查询将失败。 如前所述,建议对提供近似交互式查询性能的数据源使用 DirectQuery,因此此限制旨在防止执行时间过长的问题。

其他影响

使用 DirectQuery 的其他一般性影响如下:

  • 如果更改数据,则必须刷新以确保显示最新数据:由于给定使用缓存,无法确保视觉对象始终显示最新数据。 例如,视觉对象可能显示最后一天的事务。 然后由于切片器被更改,它可能会刷新显示最近两天的事务,包括一些最新的事务。 将切片器返回到其原始值将导致再次显示之前获得的缓存值,不包括之前看到的新到达的事务。

    选择刷新将清除所有缓存,并刷新页面上的所有视觉对象以显示最新数据。

  • 如果更改数据,不能保证视觉对象之间的一致性:不同的视觉对象(无论是在相同页面还是在不同页面上),可能会在不同的时间刷新。 因此如果基础数据源中的数据已更改,则不能保证每个视觉对象都在完全相同的时间点显示数据。 事实上,鉴于有时单个视觉对象需要多个查询(例如,获取详细信息和总计),因此,即使在单个视觉对象中,一致性也得不得保证。 若要确保一致性,需要刷新任何视觉时带来的刷新所有视觉对象的开销,同时使用昂贵的功能,如基础数据源中的“快照隔离”。

    再次选择“刷新”(刷新页面中所有视觉对象)可以很大程度上缓解此问题。 而且应注意的是,即便使用导入模式,如果从多个表中导入数据,也存在类似的保证一致性的问题。

  • 需要 Power BI Desktop 中的刷新以反映元数据的任何更改:报表发布后,使用“刷新”将只需刷新报表中的视觉对象。 如果基础数据源的架构已更改,这些更改将不会自动应用于更改字段列表中可用的字段。 因此如果已从基础数据源中删除表或列,则可能导致刷新时查询失败。 请在 Power BI Desktop 中打开报表并选择“刷新”,更新模型中的字段以反映更改。

  • 对任何查询可以返回的行数限制为一百万行:对于基础数据源的任何单个查询,可以返回的行数限制为固定的一百万行。 这通常没有实际意义,视觉对象本身不会显示那么多行。 但如果 Power BI 未完全优化发送的查询,并且有一些中间请求结果超出此限制,则可能会受此限制。 在生成达到更合理最终状态的视觉对象过程中,也可能会受此限制。 例如,如果有超过 1 百万的客户,如果不应用某些筛选器,则 Customer 和 TotalSalesQuantity 将达到此限制。

    返回的错误将是“外部数据源的查询结果集超过了允许的最大行数 1000000 行。”

  • 无法将导入模式更改为 DirectQuery 模式:请注意,通常可以将模型从 DirectQuery 模式切换到导入模式,这意味着必须导入所有需要的数据。 它无法切换回来(主要是由于 DirectQuery 模式下不支持此功能集)。 由于外部度量值的处理方式完全不同,多维数据源(如 SAP BW)的 DirectQuery 模型也不能从 DirectQuery 切换到导入。

Power BI 服务中的 DirectQuery

Power BI Desktop 支持所有数据源。 某些数据源还可直接在 Power BI 服务中使用。 例如,企业用户可以使用 Power BI 连接其 Salesforce 中的数据并立即获得仪表板,而无需使用 Power BI Desktop。

启用了 DirectQuery 的数据源中,只有两个数据源可以直接在服务中使用:

  • Spark
  • Azure SQL 数据仓库

但是,强烈建议在 Power BI Desktop 中对这两个数据源使用 DirectQuery。 原因是,当最初在 Power BI 服务中进行连接时,会应用许多关键限制,这意味着虽然开始时很容易(从Power BI 服务开始),但是会进一步限制生成报表的功能(例如,不能创建任何计算或使用许多分析功能,甚至不能刷新元数据以反映对基础架构的任何更改)。

成功使用 DirectQuery 的指南

如果要使用 DirectQuery,此节提供一些有关如何确保使用成功的高级指导。 此部分中的指导源自本文所述的使用 DirectQuery 的影响。

后端数据源性能

强烈建议验证简单的视觉对象是否可以在合理的时间内刷新。 要提供合理的交互体验,刷新时间应在 5 秒内。 当然,如果视觉对象刷新时间超过 30 秒,发布报表后很可能会出现进一步的问题,这会使解决方案无效。

如果查询速度慢,则最先检查发送到基础数据源的查询,以及所观测到的查询性能不佳的原因。 本主题不涉及各种在完整的一组潜在基础数据源中优化数据库的最佳做法,但却适用于适合大多数情况的标准数据库实践:

  • 基于整数列的关系通常比其他数据类型的列的联接更好
  • 应创建相应的索引,这通常意味着在支持列存储索引的数据源(如 SQL Server)中使用它们。
  • 应更新数据源中的任何必要的统计信息

模型设计指南

定义模型时,请考虑执行以下操作:

  • 避免在查询编辑器中定义复杂的查询。 在查询编辑器中定义的查询将转换为单个 SQL 查询,然后包含在发送到该表的每个查询的子选择中。 如果查询很复杂,则可能导致所发送的查询出现性能问题。 可以通过选择查询编辑器中的最后一步,并从上下文菜单中选择“查看本地查询”来获取一组步骤的实际 SQL 查询。

  • 简化度量值。 至少在开始时,建议将度量值限制为简单聚合。 如果这些度量值的执行令人满意,可以定义更复杂的度量值,但要注意每个度量值的性能。

  • 避免定义计算列上的关系。 这与需要执行多列联接的数据库尤其相关。 Power BI 目前不允许基于多列作为 FK/PK 的关系。 常见的解决方法是使用计算列来连接列,然后在其上创建联接。 尽管此解决方法对于导入数据是合理的,但在 DirectQuery 情况下,会导致表达式联接,通常会阻止使用任何索引,并导致性能不佳。 唯一的解决方法是,在基础数据库中将多列具体化为单列。

  • 避免定义 uniqueidentifier 列上的关系。 Power BI 在本机上不支持 uniqueidentifier 数据类型。 因此,定义 uniqueidentifier 列之间的关系将导致一个包含涉及 Cast 的联接的查询。 这通常也会导致性能不佳。 在进行特别优化之前,唯一的解决方法是在基础数据库中具体化替代类型的列。

  • 隐藏关系中的 to 列 应隐藏关系中的 to 列(通常是 to 表中的主键),使其不出现在字段列表中,从而不在视觉对象中使用。 通常,关系所在的列实际上是系统列(例如,数据仓库中的代理键),隐藏这些列总是很好的做法。 如果该列确实有意义,则引入一个可见并且具有等于主键的简单表达式的计算列。 例如:

    ProductKey_PK   (Destination of a relationship, hidden)
    ProductKey (= [ProductKey_PK],   visible)
    ProductName
    ...
    

    这样做只是为了避免在视觉对象包括主键列时可能发生的性能问题。

  • 检查所有计算列和数据类型更改的使用。 使用这些功能并不一定产生不利影响,它们会将查询发送到包含表达式的基础数据源,而不是对列简单引用的基础数据源,这可能会导致索引未被使用。

  • 避免对关系使用双向交叉筛选(预览)。

  • 设置“假设引用完整性”实验 关系的“假设引用完整性”设置使查询能够使用 INNER JOIN 语句,而不是 OUTER JOIN 语句。 这通常可以提高查询性能,但具体取决于数据源的详细情况。

  • 在查询编辑器中不使用相对数据筛选。 在查询编辑器中可以定义相对日期筛选。 例如,假设筛选日期是过去 14 天内的行。

    但是,编写查询,它将被转换为基于固定日期的筛选器。 这可以通过查看本机查询看到。

    这种转换几乎肯定是不需要的。 为了确保根据执行报表时的日期应用筛选器,请将筛选器作为报表筛选器应用于报表中。 目前的实现方法是创建计算列计算天数(使用 DAX DATE() 函数),然后在筛选器中使用该计算列。

报表设计指南

创建使用 DirectQuery 连接的报表时,请遵循以下指南:

  • 先应用筛选器:始终在生成视觉对象开始时应用任何适用的筛选器。 例如,一开始就应用筛选器 Year,而不是拖动 TotalSalesAmount 和 ProductName,然后筛选特定年份。 这是因为生成视觉对象的每个步骤都将发送查询,而在第一个查询完成之前可能会进行其他更改,这仍然会对基础数据源造成不必要的负担。 尽早应用筛选器通常会降低中间查询成本。 此外,不尽早应用筛选器可能导致达到 1 百万行的限制。

  • 限制页面上的视觉对象的数目:打开页面(或某些页级别的切片器或筛选器更改)后,将刷新页面上所有的视觉对象。 由于并行发送的查询数量有限制,随着视觉对象数量的增加,一些视觉对象将以串行方式刷新,从而增加刷新整个页面所需的时间。 出于此原因,建议限制单个页面中的视觉对象数量,改为包含更多更简单的页面。

  • 考虑关闭视觉对象之间的交互:默认情况下,报表页上的可视化组件可用于交叉筛选和交叉突出显示页面上的其他可视化组件。 例如,选择了饼图上的“1999”之后,柱形图交叉突出显示“1999”类别的销售额。

    但是,可以按此文所述控制交互。 在 DirectQuery 中,交叉筛选和交叉突出显示需要向基础数据源发送查询,所以如果响应用户选择所花费的时间太长,应关闭交互。

  • 考虑仅共享报表:发布到 Power BI 服务后,可以通过不同的方式共享内容。 对于 DirectQuery,建议只考虑共享已完成的报表,而不允许其他用户创作新报表(其生成的特定视觉对象可能会遇到性能问题)。

除了上述列表中的建议,请注意,以下每一种报表功能都会导致性能问题:

  • 度量值筛选器:包含度量值(或列聚合)的视觉对象可以包含这些度量值中的筛选器。 例如,下面的视觉对象按类别显示 SalesAmount,但仅包括超过 2 千万的类别。

    这会导致两个查询被发送到基础数据源:

    • 第一个查询检索符合条件 (Sales > 20M) 的类别
    • 第二个查询检索视觉对象所需的数据,包括满足 WHJERE 子句中的条件的类别。

    如果有数百或数千个类别(如此示例所示),这通常可以正常执行。 如果类别的数量大得多,则可能会降低性能(事实上,如果符合条件的类别超过 1 百万,由于前面所述的 1 百万行限制,查询将失败)。

  • TopN 筛选器:可以定义高级筛选器以筛选根据某些度量值排列的前(或后)N 个值,例如,仅在上述视觉对象中包含前 10 个类别。 这会导致两个查询被发送到基础数据源。 但是,第一个查询会从基础数据源返回所有类别,然后 TopN 基于返回的结果进行筛选。 具体取决于涉及的列的基数,这可能会导致性能问题(或由于 1 百万行限制引起的查询失败)。

  • 中值:通常情况下,所有聚合(Sum、Count、Distinct 等)都会被推送到基础数据源。 但这并不适用于中值,因为基础数据源通常不支持此聚合。 在这种情况下,会先从基础数据源中检索详细数据,然后从返回的结果中计算中值。 当中值在相对较少的结果中计算时,等待时间是合理的,但是如果基数很大,则会发生性能问题(或由于 1 百万行限制引起的查询失败)。 例如,国家/地区人口的中值可能是合理的,但销售价格的中值可能不合理。

  • 高级文本筛选器(“contains”和类似筛选器):当对文本列进行筛选时,高级筛选功能允许使用筛选器“contains”、“begins with”等筛选器。 对于某些数据源,这些筛选器肯定会导致性能下降。 特别是在真正需要完全匹配(“is”或“is not”)的情况下,不应使用默认的“contains”筛选器。 尽管结果可能相同(具体取决于实际数据),但由于使用索引不同,性能可能也会完全不同。

  • 多选切片器:默认情况下,切片器仅允许单选。 允许多选筛选器可能会导致一些性能问题,因为用户在切片器中选择一组项时(例如,10 个感兴趣的产品),每个新选择都将导致向后端数据源发送查询。 同时用户可以在查询完成之前选择下一项,这会导致基础源上的额外负载。

诊断性能问题

本部分介绍如何诊断性能问题,或如何获取更详细的信息以优化报表。

强烈建议在 Power BI Desktop 而不是 Power BI 服务中诊断任何性能问题。 通常情况下,性能问题只取决于基础数据源的性能水平,因此,在 Power BI Desktop 更加独立的环境中就更容易识别和诊断这些问题,并且可以一开始就消除某些组件(如 Power BI 网关)。 只有在 Power BI Desktop 中未发现性能问题时,调查才应关注 Power BI 服务中的报表细节。

同样,建议先尝试隔离单个视觉对象的任何问题,而不是调查页面上多个视觉对象的问题。

因此,假设已完成这些步骤(如本部分中前面段落所述)- 现在 Power BI Desktop 页面中单个视觉对象仍然缓慢。 为了确定 Power BI Desktop 发送到基础数据源的查询,可以查看可能由该数据源发出的跟踪/诊断信息。 此类跟踪还可能包含关于如何执行查询的详细信息以及如何改进的有用信息。

此外,即使数据源中没有这类跟踪,也可以查看 Power BI 发送的查询及其执行时间,如下所述。

确定通过 Power BI Desktop 发送的查询

默认情况下,Power BI Desktop 会在给定会话期间将事件记录到名为 FlightRecorderCurrent.trc 的跟踪文件中。

对于某些 DirectQuery 数据源,此日志文件包含发送到基础数据源的所有查询(将来会包括其余的 DirectQuery 数据源)。 将查询发送到日志的源如下所示:

  • SQL Server
  • Azure SQL 数据库
  • Azure SQL 数据仓库
  • Oracle
  • Teradata
  • SAP HANA

当前用户的 AppData 文件夹中可以找到的跟踪文件:

\<User>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

下面是获取对此文件夹的简单办法:在“Power BI Desktop”中,选择“文件”>“选项和设置”>“选项”,然后选择“诊断”。 将显示以下对话框窗口:

在“诊断选项”下,选择“打开跟踪文件夹”链接,此时会打开以下文件夹:

\<User>\AppData\Local\Microsoft\Power BI Desktop\Traces

导航到该文件夹的父文件夹将显示包含 AnalysisServicesWorkspaces 的文件夹,该文件包含每个打开的 Power BI Desktop 实例的工作区子文件夹。 这些子文件夹名称中带有整数后缀,例如 AnalysisServicesWorkspace2058279583。

该文件夹内是一个 \Data 子文件夹,其中包含当前 Power BI 会话的跟踪文件 FlightRecorderCurrent.trc。 相关联的 Power BI Desktop 会话结束时,将删除相应的工作区文件夹。

跟踪文件可以使用 SQL Server Profiler 工具读取,该工具作为 SQL Server Management Studio 的一部分可以免费下载。 可以从此处获取。

下载并安装 SQL Server Management Studio 后,运行 SQL Server Profiler。

若要打开跟踪文件,请执行以下步骤:

  1. 在 SQL Server Profiler 中,选择“文件”>“打开”>“跟踪文件”
  2. 输入当前打开的 Power BI 会话的跟踪文件路径,如:

        C:\Users\<user>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspace2058279583\Data
    
  3. 打开 FilghtRecorderCurrent.trc

将显示当前会话的所有事件。 注释示例如下所示,其中突出显示了事件组。 每个组具有以下内容:

  • 一个 Query Begin 和一个 Query End 事件,分别表示 UI(例如,从视觉对象,或从在筛选器 UI 中填充值的列表)生成的 DAX 查询的开始和结束

  • 一对或多对 DirectQuery Begin 和 DirectQuery End 事件,表示发送到基础数据源的查询(作为评估 DAX 查询的一部分)。

请注意,可以并行执行多个 DAX 查询,因此来自不同组的事件可能互相交错。 ActivityID 值可以用于确定属于同一个组的具体事件。

其他较重要的列如下所示:

  • TextData:事件的文本详细信息。 对于“Query Begin/End”事件,该内容会是 DAX 查询。 对于“DirectQuery Begin/End”事件,该内容会是发送到基础数据源的 SQL 查询。 当前选中事件的 TextData 也显示在底部区域中。

  • EndTime:事件完成的时间。

  • Duration:执行 DAX 或 SQL 查询的持续时间,以毫秒为单位。

  • Error:指示是否发生了错误(发生错误时,该事件显示为红色)。

请注意,在上图中,缩小了重要性较低的列,以便更容易看到重要性较高的列。

建议使用以下方法捕获跟踪以帮助诊断潜在性能问题:

  • 打开单个 Power BI Desktop 会话(避免多个工作区的文件夹产生混淆)

  • 在 Power BI Desktop 执行一组意向操作。 再执行一些额外的操作,确保将意向操作事件刷新到跟踪文件中。

  • 打开 SQL Server Profiler 并检查跟踪,如前面所述。 请记住,关闭 Power BI Desktop 时将删除跟踪文件。 此外,在 Power BI Desktop 中的进一步操作将不会立刻显示 – 应关闭跟踪文件并重新打开以查看新事件。

  • 保持较短的单个会话时间(10 秒的操作时间,而不是数百秒的操作时间),使跟踪文件更容易解释(并且因为跟踪文件的大小有限制,因此在时间很长的会话中可能会丢弃早期事件)。

了解 Power BI Desktop 发送的查询的形式

Power BI Desktop 创建和发送的查询的一般格式会对每个引用的表使用子选项,其中子查询由“查询编辑器”中定义的查询定义。 例如,假设 SQL Server 中有以下 TPC-DS 表:

请考虑运行以下查询:

该查询将生成下面的视觉对象:

刷新该视觉对象将生成下一段所示的 SQL 查询。 可以看出,Web Sales、Item 和 Date_dim 各有三个子选项,每个子选项都返回相应表上的所有列,即使实际只有四列被视觉对象引用。 子选项中的这些查询(颜色较深部分)完全是“查询编辑器”中定义的查询的结果。 对于目前 DirectQuery 支持的数据源,尚未发现以这种方式使用子选项会影响性能。 SQL Server 等数据源简化了对其他列的引用。

Power BI 使用此模式的原因之一是,所使用的 SQL 查询可直接由分析师提供,因此可根据“提供”的模式使用,而无需尝试重写。

详细信息

本文介绍了所有数据源中常见的 DirectQuery 的各个方面。 某些细节特定于某些数据源。 请参阅以下涵盖特定数据源的主题:

有关 DirectQuery 的详细信息,请查看以下资源: