Power BI Pro 增量刷新效率对比——DateTime,Int,Varchar沐见南

最近Power BI Pro也可以启用增量刷新了，这对Pro用户来说实在是一个好消息。  
简单阅读了高飞老师对增量刷新的[中文介绍](https://www.powerbigeek.com/power-bi-service-incremental-refresh/)之后，我开始尝试对手头一个项目进行增量化改造，但改造过程中遇到了一个问题：甲方提供的数据库中，日期键数据类型是varchar(8)！  

一开始我尝试在pq中先将varchar(8)转为int，然后配置增量筛选，但可以很明显地看到，完全符合高飞老师文章中说的“先通过网络把全量数据接过来，然后本地增量，还不如直接全量”。于是我又换了一种思路，直接在pq里面对字符串进行筛选（具体方法可以下载我稍后提供的示例pbix文件）。经过本地测试后，直觉告诉我这种方案是可行的。  

为了进一步研究日期列采用DateTime、Int、Varchar(8)三种数据类型时的增量刷新效率，我利用随机数据制作了报表，进行详细测试。  

# 一、 数据源  
我使用MS Sql Server， 创建了三张表：
1. PbiIncrementalRefresh_DateTime  
创建表：
        Create Table PbiIncrementalRefresh_DateTime(
            Id Int Not Null,
            Value Decimal(18,2) Not Null,
            CreatedAt DateTime Not Null,
            ModifiedAt DateTime Not Null
            );
实际生产场景下，对于中等规模数据表，建议向ModifiedAt添加索引。小型数据表（适配硬件，典型如不超过100万行）没有加索引的必要，大型数据表添加索引拖累写入性能。具体情况视生产场景而定，例如从业务系统抽数，加索引属于对业务系统的侵入行为。本文只以ModifiedAt列未加索引的小型数据表为用例。  
生成百万行数据：  
		Declare @Seq Int,@n Int;
		Set @Seq=1;
		Set @n=1000000
		While @Seq<=@n
			Begin
				Insert Into [dbo].[PbiIncrementalRefresh_DateTime]
				(Id,[Value],CreatedAt,ModifiedAt)
				Values(@Seq,@n*Rand(),DateAdd(Day,(@Seq-@n)/100,GetDate()),DateAdd(Day,(@Seq-@n)/100*Rand(),GetDate()));
				Set @Seq=@Seq+1;
			End
1. PbiIncrementalRefresh_Int  
创建表：
        Create Table PbiIncrementalRefresh_Int(
            Id Int Not Null,
            Value Decimal(18,2) Not Null,
            CreatedAt Int Not Null,
            ModifiedAt Int Not Null
            );
从PbiIncrementalRefresh_DateTime复制数据：  
        Insert Into PbiIncrementalRefresh_Int
            Select Id
                ,[Value]
                ,Format(CreatedAt,'yyyyMMdd') As CreatedAt
                ,Format(CreatedAt,'yyyyMMdd') As ModifiedAt
            From PbiIncrementalRefresh_DateTime
1. PbiIncrementalRefresh_Varchar  
创建表：
        Create Table PbiIncrementalRefresh_Varchar(
            Id Int Not Null,
            Value Decimal(18,2) Not Null,
            CreatedAt Int Not Null,
            ModifiedAt Int Not Null
            );
从PbiIncrementalRefresh_DateTime复制数据：
        Insert Into PbiIncrementalRefresh_Varchar
            Select Id
                ,[Value]
                ,Format(CreatedAt,'yyyyMMdd') As CreatedAt
                ,Format(CreatedAt,'yyyyMMdd') As ModifiedAt
            From PbiIncrementalRefresh_DateTime

# 二、制作增量刷新报表
我制作了一份母报表[PbiIncrementalRefresh](http://public.mujiannan.me/oss/PowerBi/Examples/TestIncrementalRefresh/PbiIncrementalRefresh.pbix)，查询上述三张表的数据，并对三张表配置了完全相同的增量刷新策略（保留最近二十年、刷新最近五年）。然后通过分别删除其余表，生成了三张测试报表，我想这三张报表可能还兼具增量刷新教程的作用：[PbiIncrementalRefresh_DateTime](http://public.mujiannan.me/oss/PowerBi/Examples/TestIncrementalRefresh/PbiIncrementalRefresh_DateTime.pbix)、[PbiIncrementalRefresh_Int](http://public.mujiannan.me/oss/PowerBi/Examples/TestIncrementalRefresh/PbiIncrementalRefresh_Int.pbix)、[PbiIncrementalRefresh_Varchar](http://public.mujiannan.me/oss/PowerBi/Examples/TestIncrementalRefresh/PbiIncrementalRefresh_Varchar.pbix)。

# 三、服务端刷新测试
将上述三张报表全部发布到工作区，配置好刷新网关。虽然有点不科学，但我的网关确实跟数据库服务器安装在同一台机器上——这台机器就在我的家里，而且现在就在我的手边。因此，接下来的测试，结果可能会有比较大的误差。不过，令人宽心的是，我的这台机器带宽限制为2m，后台没有其它明显占用带宽的服务，可以提供相对还算可靠的结果。如果需要更加可信的结果，把测试完全放到azure上是一个更好的选择。  
我对三张报表分别进行了一次全量刷新和三次增量刷新，我不确定刷新的前后顺序是否会影响速度，但我至少保证了每一次刷新时没有其它刷新任务在进行。下表列出了最终结果：  
![Result](http://public.mujiannan.me/oss/PowerBi/Examples/TestIncrementalRefresh/Result.png)
# 四、测试结果分析  
从表格中可以看到，三种数据类型下的全量、增量刷新耗时稍有差异，按速度Int>DateTime>Varchar(8)。  

另外，我不太确信当我设置了增量刷新最近五年数据时服务端的行为，因此我把PbiIncrementalRefresh_DateTime修改为增量刷新最近十年，重新发布并测试了刷新的耗时，结果为全量耗时1分47秒，增量耗时50秒。所以现在我确信，增量刷新的行为恰好是我预想的那样：删除最近的数据，然后重新拉取。  

通过这一次测试，我的结论是：至少在从MS Sql Server获取数据时，不管表中日期采用的是DateTime、Int还是Varchar，都可以放心地配置增量刷新。其中Int是最高效的，DateTime是最方便设置且最灵活的，而在迫不得已时，即便是Varchar也可以做增量刷新，毕竟增量总比全量好。

Power BI Pro 增量刷新效率对比——DateTime,Int,Varchar沐见南

留言板：