在 Power BI Desktop 中使用 DirectQueryUse DirectQuery in Power BI Desktop

使用 Power BI Desktop 时,若已连接数据源,始终可以将数据副本导入 Power BI DesktopWith Power BI Desktop, when you connect to your data source, it is always possible to import a copy of the data into the Power BI Desktop. 对于某些数据源,还可使用另一种方法︰使用 DirectQuery 直接连接到数据源。For some data sources, an alternative approach is available: connect directly to the data source using DirectQuery.

受支持的数据源Supported Data Sources

有关支持 DirectQuery 的数据源的完整列表,请参阅 DirectQuery 支持的数据源For a full listing of data sources that support DirectQuery, see Data sources supported by DirectQuery.

如何使用 DirectQuery 建立连接How to Connect using DirectQuery

当使用获取数据连接到受 DirectQuery 支持的数据源时,连接窗口将让你选择连接方式。When you use Get Data to connect to a data source supported by DirectQuery, the connection window lets you select how you want to connect.

导入选项和 DirectQuery 选项之间的差异如下︰The differences between selecting Import and DirectQuery are the following:

导入 - 将选定的表和列导入 Power BI Desktop 中。Import – the selected tables and columns are imported into Power BI Desktop. 在你创建可视化效果或与之进行交互时,Power BI Desktop 需要使用导入的数据。As you create or interact with a visualization, Power BI Desktop uses the imported data. 你必须刷新数据(这将导入完整的数据集)才能查看自初始导入或最近一次刷新以来基础数据所发生的任何更改。You must refresh the data, which imports the full data set again, to see any changes that occurred to the underlying data since the initial import or the most recent refresh.

DirectQuery - 不会将任何数据导入或复制到 Power BI Desktop 中。DirectQuery – no data is imported or copied into Power BI Desktop. 对于关系源,选定的表和列显示在“字段”列表中。For relational sources, the selected tables and columns appear in the Fields list. 对于 SAP Business Warehouse 等多维源,所选多维数据集的维度和度量值会显示在“字段”列中。For multi-dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. 在你创建可视化效果或与之进行交互时,Power BI Desktop 会查询基础数据源。也就是说,你查看的始终都是最新数据。As you create or interact with a visualization, Power BI Desktop queries the underlying data source, which means you’re always viewing current data.

在使用 DirectQuery 时许多数据建模和数据转换功能可用,但存在一些限制。Many data modeling and data transformations are available when using DirectQuery, though with some limitations. 当创建可视化效果或与其互动时,必须查询基础数据源。刷新可视化效果所需的时间取决于基础数据源的性能。When creating or interacting with a visualization, the underlying source must be queried and the time necessary to refresh the visualization is dependent on the performance of the underlying data source. 当最近已请求为处理请求而必需的数据时,Power BI Desktop 将使用最近数据来减少显示可视化效果时所需的时间。When the data necessary to service the request has recently been requested, Power BI Desktop uses recent data to reduce the time required to display the visualization. 通过从主页功能区中选择刷新,将确保使用当前数据刷新所有可视化效果。Selecting Refresh from the Home ribbon will ensure all visualizations are refreshed with current data.

Power BI 和 DirectQuery 一文详细介绍了 DirectQuery。The Power BI and DirectQuery article describes DirectQuery in detail. 另请参阅以下各节,详细了解使用 DirectQuery 的好处、限制条件和重要注意事项。Also, see the following sections for more information about benefits, limitations, and important considerations when using DirectQuery.

通过使用 DirectQuery 带来的好处Benefits of using DirectQuery

使用 DirectQuery 带来的几个好处是:There are a few benefits to using DirectQuery:

  • DirectQuery 可使你在超大型数据集上生成可视化效果,除此之外将无法使用预聚合首先导入所有数据DirectQuery lets you build visualizations over very large datasets, where it otherwise would be unfeasible to first import all of the data with pre-aggregation
  • 对基础数据的更改可能需要刷新数据。对于某些报表,需显示当前数据,而这可能需要传输大量数据,使得重新导入的数据的操作变得不可行。Underlying data changes can require a refresh of data, and for some reports, the need to display current data can require large data transfers, making re-importing data unfeasible. 与此相反,DirectQuery 报表始终会使用当前数据By contrast, DirectQuery reports always use current data
  • 1 GB 的数据集限制适用于 DirectQueryThe 1 GB dataset limitation does not apply to DirectQuery

DirectQuery 的限制Limitations of DirectQuery

目前,在使用 DirectQuery 时存在一些限制:There are currently a few limitations to using DirectQuery:

  • 所有表都必须来自单个数据库All tables must come from a single database
  • 如果“查询编辑器”查询过于复杂,将会出错。If the Query Editor query is overly complex, an error will occur. 要更正错误,必须在“查询编辑器”中删除有问题的步骤,或者导入数据,而不是使用 DirectQuery。To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. 对于 SAP Business Warehouse 等多维度源而言,没有查询编辑器For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor
  • 关系筛选操作仅限于在单方向上执行,而不能在两个方向上执行(尽管可能要在 DirectQuery 的两个方向上启用作为预览功能的交叉筛选)。Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). 对于 SAP Business Warehouse 等多维源而言,模型中未定义任何关系For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model
  • DirectQuery 不提供时间智能功能。Time intelligence capabilities are not available in DirectQuery. 例如,DirectQuery 模式不支持日期列(年、季度、月、日等)的特殊处理方式。For example, special treatment of date columns (year, quarter, month, day, so on) are not supported in DirectQuery mode.
  • 默认情况下,会限制允许在度量值中使用的 DAX 表达式;请参阅下一段落(在此项目符号列表后)了解详细信息By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information
  • 使用 DirectQuery 时,返回数据有 100 万行的限制。There is a 1 million row limit for returning data when using DirectQuery. 这不影响用于创建使用 DirectQuery 返回的数据集的聚合或计算,仅影响返回的行。This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. 例如,你可以使用在数据源上运行的查询聚合 1000 万行,并且只要返回到 Power BI 的数据不超过 100 万行,即可使用 DirectQuery 将该聚合的结果准确地返回到 Power BI。For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. 如果从 DirectQuery 返回的结果超过 100 万行,则 Power BI 返回错误。If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

为了确保发送到基础数据源的查询具有可接受的性能,默认情况下对度量值进行了限制。To ensure that queries sent to the underlying data source have acceptable performance, limitations are imposed on measures by default. 高级用户可选择绕过此限制,方法是依次选择“文件”>“选项”,然后选择“设置”>“选项和设置”>“DirectQuery”,然后选择选项“允许 DirectQuery 模式下的度量值不受限制”。Advanced users can choose to bypass this limitation by selecting File > Options and then Settings > Options and settings > DirectQuery, then selecting the option Allow unrestricted measures in DirectQuery mode. 选中该选项后,即可使用对度量值有效的任何 DAX 表达式。When that option is selected, any DAX expression that is valid for a measure can be used. 但是,用户必须知道,可在导入数据的情况下正常工作的某些表达式,在 DirectQuery 模式下则可能会导致针对后端数据源的查询速度缓慢。Users must be aware, however, that some expressions that perform very well when the data is imported may result in very slow queries to the backend source when in DirectQuery mode.

使用 DirectQuery 的重要注意事项Important considerations when using DirectQuery

使用 DirectQuery 时,应考虑以下三点:The following three points should be taken into consideration when using DirectQuery:

  • 性能和负载 - 所有 DirectQuery 请求都会发送到源数据库,因此刷新视觉对象所需的时间取决于后端源响应查询结果所需的时间。Performance and load - All DirectQuery requests are sent to the source database, so the time required to refresh a visual is dependent on how long that back-end source takes to respond with the results from the query (or queries). 使用 DirectQuery 的视觉对象的建议响应时间(包括返回请求数据)应为 5 秒或更短,建议的最长结果响应时间为 30 秒。The recommended response time (with requested data being returned) for using DirectQuery for visuals is five seconds or less, with a maximum recommended results response time of 30 seconds. 超过此时间会使报表的用户体验变得令人无法接受的差。Any longer, and the experience of a user consuming the report becomes unacceptably poor. 此外,将报表发布到 Power BI 服务后,超过几分钟时间的任何查询将会超时,且用户将收到错误。In addition, once a report is published to the Power BI service, any query that takes longer than a few minutes will timeout, and the user will receive an error.

    还应当根据使用发布报表的 Power BI 用户数量,考虑源数据库的负载。Load on the source database should also be considered, based on the number of Power BI users who will consume the published report. 使用行级安全性 (RLS) 也可能会产生显著的影响;多个用户共享的非 RLS 仪表板磁贴将导致对数据库的单个查询,但是,在仪表板磁贴上使用 RLS 意味着刷新一个磁贴需要每个用户一个查询,因而将显著增加源数据库负载,并可能会影响性能。Using Row Level Security (RLS) can have a significant impact as well; a non-RLS dashboard tile shared by multiple users results in a single query to the database, but using RLS on a dashboard tile usually means the refresh of a tile requires one query per user, thus significantly increasing load on the source database and potentially impacting performance.

    Power BI 将创建尽可能高效的查询。Power BI creates queries that are as efficient as possible. 但是在某些情况下,生成的查询可能不能高效到避免失败的刷新。Under certain situations however, the generated query may not be efficient enough to avoid refresh that would fail. 其中一个示例是生成的查询将从后端数据源检索大量行(超过 100 万),在这种情况下会发生以下错误:One example of this situation is when a generated query would retrieve an excessively large number of rows (more than 1 million) from the back-end data source, in which case the following error occurs:

    The resultset of a query to external data source has exceeded
    the maximum allowed size of '1000000' rows.
    

    生成包含非常高的基数列的简单图表,聚合选项设置为不汇总This situation can occur with a simple chart that includes a very high cardinality column, with the aggregation option set to Don’t Summarize. 视觉对象应只具有基数低于 100 万的列,或必须应用适当的筛选器。The visual needs to only have columns with a cardinality below 1 million, or must have appropriate filters applied.

  • 安全 -使用发布报表的所有用户都使用发布到 Power BI 服务后输入的凭据连接到后端数据源。Security - All users who consume a published report connect to the back-end data source using the credentials entered after publication to the Power BI service. 这与导入数据的情况相同:所有用户会看到相同的数据,而不考虑后端源中定义的任何安全规则。This is the same situation as data that is imported: all users see the same data, irrespective of any security rules defined in the backend source. 希望实现每用户安全性的客户使用 DirectQuery 源进行实现,并使用 RLS。Customers who want per-user security implement with DirectQuery sources and use RLS. 详细了解 RLSLearn more about RLS.
  • 支持的功能 - DirectQuery 模式不支持 Power BI Desktop 的所有功能,或对某些功能有限制。Supported features - Not all features in Power BI Desktop are supported in DirectQuery mode, or have some limitations. 此外,Power BI 服务中的某些功能(如快速见解)对使用 DirectQuery 的数据集不可用。In addition, there are some capabilities in the Power BI service (such as Quick Insights) that are not available for datasets using DirectQuery. 因此,决定是否使用 DirectQuery 时,应当考虑使用 DirectQuery 时这些功能的限制。As such, the limitation of such features when using DirectQuery should be taken into consideration when determining whether to use DirectQuery.

发布到 Power BI 服务Publish to the Power BI service

通过DirectQuery 创建的报表可发布到 Power BI 服务。Reports created using DirectQuery can be published to the Power BI Service.

如果使用的数据源不需要本地数据网关(Azure SQL 数据库、Azure SQL 数据仓库或 Redshift)则必须提供凭据,然后所发布的报表才能显示在 Power BI 服务中。If the data source used does not need the on-premises data gateway (Azure SQL Database, Azure SQL Data Warehouse, or Redshift), credentials must be provided before the published report will be displayed in the Power BI Service.

可以通过在 Power BI 中选择设置齿轮状图标然后选择设置来提供凭据。You can provide credentials by selecting the Settings gear icon in Power BI, then select Settings.

Power BI 将显示设置窗口。Power BI displays the Settings window. 在此窗口中,选择数据集选项卡,选择将使用 DirectQuery 的数据集,然后选择编辑凭据From there, select the Datasets tab and choose the dataset that uses DirectQuery, and select Edit credentials.

在提供凭据之前,如果打开已发布的报表或浏览通过与此类数据源的 DirectQuery 连接创建的数据集,会导致出错。Until credentials are supplied, opening a published report or exploring a dataset created with a DirectQuery connection to such data sources results in an error.

对于除 Azure SQL 数据库、Azure SQL 数据仓库和 Redshift 外的使用 DirectQuery 的数据源,必须安装本地数据网关,并且必须注册数据源才能建立数据连接。For data sources other than Azure SQL Database, Azure SQL Data Warehouse and Redshift that use DirectQuery, an on-premises data gateway must be installed and the data source must be registered to establish a data connection. 可以了解有关本地数据网关的详细信息You can learn more about on-premises data gateway.

后续步骤Next steps

有关 DirectQuery 的详细信息,请查看以下资源:For more information about DirectQuery, check out the following resources: