用于SQL源的Power BI增量刷新

2022-08-23 17:23:07

问题

现在，Power BI中的Pro和Premium容量均可使用增量刷新。在数据集上实现增量刷新的三个主要好处包括更快的刷新，更可靠的刷新以及减少资源消耗。此外，由于增量刷新通常在具有数百万行的潜在大型数据集并且数据集有可能随着时间的推移显着增长的情况下更为有用，因此将分区过滤器推送到源非常重要提交查询以进行刷新操作的系统。

但是，如果在Power BI中开发报表时使用的数据源不支持所谓的“ 查询折叠”，则无法获得这些好处。大多数支持SQL查询的数据源（例如Azure SQL数据库，SQL Server，Oracle和Teradata）通常都支持查询折叠。但是需要适当地实现这些以确保增量刷新正常工作。

解

在本文中，我将演示如何以正确的方式在SQL Server数据源上实现增量刷新。这将通过一系列步骤来完成。

我将使用AdventureWorks2014数据库中的SalesOrderHeader表作为此技巧。在您的情况下，您可能有多个表作为源数据，您只需要弄清楚实现增量刷新所需要的表。这通常是事实表，通常随着时间的增长比维度表的增长更多。

步骤1：将数据源与Power BI Desktop连接

如下图所示，SQL Server数据库需要连接到Power BI桌面。

输入服务器名称，数据库名称，还可以选择写出您想使用的SQL查询。稍后我将解释写出SQL查询（而非视图）如何影响增量刷新的实现。

步骤2：配置Power Query datetime参数

应当注意，要配置增量刷新，将使用保留的且区分大小写的日期时间参数名称RangeStart和RangeEnd过滤数据集。下图显示了如何定义它们。

通常应将“类型”输入为“日期/时间”，“建议值”通常应为“任何值”，“当前值”应为任何日期时间值，因为Power BI服务将在以后自动检测到该值。但是，我只是在RangeEnd中使用了数据集的“ Modifieddate”最大值，而在RangeStart中使用了“ Modifieddate”的最小值。

步骤3：配置参数以将过滤器应用于数据集

在此步骤中，我们将尝试在数据集中找到该列，尝试在其上应用增量刷新过滤器并将其映射到使用自定义过滤器创建的参数，如下图所示。

单击“日期/时间过滤器”，然后导航到“自定义过滤器”。在这种情况下，我们使用基本配置，如下所示。

然后完全按照以下所示输入值。单击确定，然后关闭并应用查询编辑器窗口。

步骤4：在数据集上激活增量刷新

完成初始配置后，现在就可以激活数据集上的增量刷新设置，如下图所示。

接下来，您会看到在下一张图中，我们收到警告，表明我们的数据集无法折叠或不支持查询折叠。但是请记住，您可能会在本技巧中使用一个支持SQL查询的数据源。我们可以返回到Power Query编辑器并通过右键单击数据集中的源步骤或“应用步骤”中的任何步骤，然后检查数据集“ View Native Query”是否突出显示，来检查数据源是否支持查询折叠或变灰。如果显示为灰色，则表示数据集不支持查询折叠，因此，即使我们设法设置增量刷新，它也会非常缓慢，并且可能无法按计划工作。

解决此问题的方法是返回Power Query Editor，单击源数据的属性图标，然后完全删除SQL语句查询。然后仅导入所有表并选择所需的表。如下图所示。

之后，再次执行步骤3和步骤4。现在，您应该能够看到没有警告的增量刷新设置。您可以按照第4步中提到的步骤来验证是否正在执行查询折叠（“查看本机查询”现在已突出显示并且没有变灰）。

现在，您可以在此增量刷新后查看本机查询，其外观应类似于下图。单击关闭并应用。

select [_].[RevisionNumber],
    [_].[OrderDate],
    [_].[DueDate],
    [_].[ShipDate],
    [_].[Status],
    [_].[OnlineOrderFlag],
    [_].[SalesOrderNumber],
    [_].[PurchaseOrderNumber],
    [_].[AccountNumber],
    [_].[CustomerID],
    [_].[SalesPersonID],
    [_].[TerritoryID],
    [_].[BillToAddressID],
    [_].[ShipToAddressID],
    [_].[ShipMethodID],
    [_].[CreditCardID],
    [_].[CreditCardApprovalCode],
    [_].[CurrencyRateID],
    [_].[SubTotal],
    [_].[TaxAmt],
    [_].[Freight],
    [_].[TotalDue],
    [_].[Comment],
    [_].[rowguid],
    [_].[ModifiedDate]
from 
(
    select [RevisionNumber],
        [OrderDate],
        [DueDate],
        [ShipDate],
        [Status],
        [OnlineOrderFlag],
        [SalesOrderNumber],
        [PurchaseOrderNumber],
        [AccountNumber],
        [CustomerID],
        [SalesPersonID],
        [TerritoryID],
        [BillToAddressID],
        [ShipToAddressID],
        [ShipMethodID],
        [CreditCardID],
        [CreditCardApprovalCode],
        [CurrencyRateID],
        [SubTotal],
        [TaxAmt],
        [Freight],
        [TotalDue],
        [Comment],
        [rowguid],
        [ModifiedDate]
    from [Sales].[SalesOrderHeader] as [$Table]
) as [_]
where [_].[ModifiedDate] >= convert(datetime2, ‘2011-05-31 00:00:00‘) and [_].[ModifiedDate] < convert(datetime2, ‘2014-06-30 00:00:00‘)