专栏首页PowerBI战友联盟PowerBI 多种增量刷新方案最大支持100T数据存储及单个模型50G+

PowerBI 多种增量刷新方案最大支持100T数据存储及单个模型50G+

什么是增量刷新

所谓增量刷新,是指增量刷新数据。一般情况下,在PowerBI或PowerBI Desktop中点击【刷新】按钮,会将数据源的数据全部刷新一遍,如果数据源数据很多,而每次变化的很少,例如只有最近一日发生变化,那这种不问青红皂白就直接全部刷新的方法显然会耗时耗力。很可惜在默认情况下,PowerBI就只支持这种数据刷新方式。

随着2018年5月PowerBI的升级,Premium开始支持真增量刷新,本文来说明目前可以用来在PowerBI中实现增量数据刷新的技巧。

关于 Power BI Premium,可以阅读:Power BI Premium 来了

在PowerBI中实现增量刷新,目前有三种方法:

  • 用 PowerQuery M 实现增量刷新,该方法有一定限制,属于假增量刷新。
  • 用 DAX 实现增量刷新,该方法也有一定限制,属于假增量刷新。
  • 用 Power BI Premium 实现增量刷新,该方法也有一定限制,但属于真增量刷新

数据场景假设

假设这里有不断在更新的订单表,首先加载2011年到2013年数据,而每次数据刷新只刷新2014年数据,以下的方案应该如何实现呢。

用 PowerQuery M 实现增量刷新

用 PowerQuery M 实现增量刷新的核心技巧在于:查询的纵向合并。这里可以节省的时间在于网络传输的时间,方案如下:

  • 首先将远程历史数据通过任何查询形式保存在本机。
  • 查询本机历史数据再纵向合并远程的增量数据。

这样一来,每次历史数据的获取只是读取本机磁盘的时间,而不再产生网络传输的时间消耗,进而在一定程度上缓解了数据刷新的时间瓶颈问题。

再使用PowerQuery M的纵向合并表查询把几个查询合并成最终结果,完成增量刷新的效果。如下:

其中订单是由其他的订单数据合并而来,对应的PowerQuery M查询如下:

订单 = Table.Combine( { #"订单2011", #"订单2012", #"订单2013", #"订单2014" } )

可以把订单2011~2013的数据置于本地服务器或PC,在刷新时,虽然会全部刷新,但由于数据可以预先置于本地,则可以有效降低数据的网络传输时间。

另外,如果该模型发布到云端,显然都会有网络传输时间,但可以节省的是,数据在PQ中ETL的时间,可以预先将数据用PQ(或其他ETL工具)进行ETL后进行保存。在实际查询时会节省ETL的时间。

当然,这种方法并不是真正意义上的增量刷新,属于利用了PowerQuery或者说ETL可以缓冲数据存储的思想。

用 DAX 实现增量刷新

由于使用PowerQuery的方式实现假增量刷新是数据进入数据模型前,所以仍然要刷新所有数据,因此并没有降低模型对数据的吞吐量。在数据加载进入数据模型之后,我们也可以实现增量刷新的效果,使用DAX的UNION函数将多个表纵向合并即可。对此,我们设计的结构如下:

这里需要将订单2011~2014全部加载进入模型,但可以设置只有订单2014包含在报表刷新中,如下:

这样每次刷新数据的时候,只有最后一组订单数据(订单2014)会被刷新,如下:

用DAX实现合并的函数表达式如下:

订单 =
VAR Orders2011 = '订单2011'
VAR Orders2012 = '订单2012'
VAR Orders2013 = '订单2013'
VAR Orders2014 = '订单2014'
RETURN
    UNION (
        Orders2011,
        Orders2012,
        Orders2013,
        Orders2014,
        FILTER ( Orders2011, FALSE () )
    )

这里面最后一行的FILTER一定会让人困惑不解。没错,不要这句也是没问题的,但它是为了解决下面这个问题了准备的。

有伙伴问到:如果一开始订单是正常加载的,那就无法被UNION了。没错,因为订单这个表名已经存在,以后再想UNION是没办法的。这时候可以尝试:

  • 将原有订单拆分成增量加载形式。
  • 删除加载的订单,此时数据模型会出现大量报错,因为没有了订单这个表。
  • 用UNION的方式合成新表并命名为订单,并补足关系,理论上可以修复所有的错误。但在实际实践中,可能并不能修复所有错误,仍然有大量错误存在,这视具体实际情况而定。

因此,这种删除后重新用DAX计算表的方式弥补订单存在风险,务必备份数据

一种未雨绸缪的做法是,在建模初期就预料到某些表会很大,可能需要增量加载,那可以直接使用计算表,但此时如果只有一个表怎么办,也就是说,必须实现一个表的UNION作为占位符。由于DAX的UNION函数必须至少有两个参数,且这两个参数必须都是列数一样的表,所以使用FILTER的这句技巧实际返回一个空表与前面的表合并,以起到占位符的作用,待有真正需要合并的表的时候,再做替换。

? 提示 当然,在只有一个表的时候使用计算表可以不用UNION以更加简单。

这样,就使用DAX的UNION实现了增量刷新的效果,并且该效果确实起到了增量加载数据,但根据DAX引擎的原理,整个数据模型会重建,仍需花费时间,这里省去了数据加载的时间,但无法省去模型重建的时间。另外的一个缺陷是,同样的数据在模型中保留了两份。

经过实际测试,该方法确实可以显著降低模型生成的时间,具体情况以实际为准。

用 Power BI Premium 实现增量刷新

Power BI 5月更新的预览功能中,已经给出了Power BI正统实现增量刷新的方法,但就是在Power BI Desktop只是进行设置,实际到Power BI Service的Premium专有容量中再进行实际阶段的增量加载,由于Power BI Premium允许单个数据模型可超过50G且数据存储达到100T(参考:Microsoft Power BI Premium 白皮书),这个量级的数据在本地PC或Power BI Desktop是无法进行的,而Power BI Premium却提供了这样的能力。如果在Power BI Desktop设置好增量刷新,效果如下:

其含义为:

  • 将订单表增量刷新
  • 存储最后3年的数据行
  • 刷新最后1日的数据行
  • 在检测到数据更改时触发刷新动作

在Power BI Desktop设计好后,发布的时候会看到:

由于设置了增量刷新,必须发布到含有Premium容量的工作区,否则是无法发布的。

很多伙伴会问,在你自己设置增量刷新的时候,不会成功,会提示这样的错误:

你会发现,无论你怎么设置参数都不对,这里有几个非常重要的细节需要指出:

  • 必须在查询编辑中设置参数,且参数的数据类型必须是【日期/时间】,日期型也是不对的。
  • 设置的参数必须起名为RangeStart和RangeEnd分别表示时间的开始和结束。参数名用别的也是不对的。
  • 对需要增量更新的表,如:订单,让订单日期被上述的两个参数所筛选。

如下:

只有满足了以上设置,增量刷新才会乖乖出来给你用。

这种方法当然是真增量刷新,而且可以应对多达100T的数据存储以及单个100G的数据模型,这个能力非常强大,当然,为此付出的代价就是必须购买5K美金/月起步(注意:是起步,实际以定价估算计算器为准)的Power BI Premium服务。

但如果仔细算一笔账,其实这也并非绝不合理,5K美金/月大致为30000RMB/月,相当于一个IT工程师的月薪,而有了Premium,可以不再需要IT工程师来搭建和维护数据环境,这其实是相当的。而这个环境可以支撑多达100T的数据量级以及单个100G的数据模型,足以应付企业级数据分析需求。

总结

本文整体分析并实际演示了在Power BI中实现数据增量刷新的各种方法以及各自优缺点:

  • PQ M 方法:简单且便于维护,但并非真正增量刷新,只能节省网络传输和部分ETL的时间。
  • DAX 方法:可以在Power BI模型限制范围内部分降低数据加载时间,但要注意在项目开始引入以便避免后期问题。
  • PowerBI Premium方法:终极的数据增量刷新方法,支持多达100T的数据存储以及单个数据模型可以超过50G且每天可以刷新48次,但费用昂贵,属于企业级解决方案。

本文分享自微信公众号 - PowerBI战友联盟(powerbichina),作者:BI佐罗

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-05-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如何配合流处理让 PowerBI 在整点完成自动刷新

    这里有个问题是:如果你和老板(看报告的重要人物)约定了说每天 9:00 刷新数据,而他 9:00 来看的时候,发现报告并没有刷新呢,因为这时候数据还没有刷新完成...

    BI佐罗
  • 【高级应用】PowerBI增量刷新原理揭秘及实战详解

    随着 PowerBI 在 2020年2月的更新,增量刷新应该被投票投成了大众货,现在所有小伙伴都可以使用这个功能了。当然,其前提是 PowerBI Pro 帐号...

    BI佐罗
  • PowerBI 2018 8月更新 一键导出PDF报告集合

    每月一次的 Power BI 更新如期而至,本月更新个人认为是很有意义的。本文将详细描述这些内容。

    BI佐罗
  • 解密区块链生态的技术信任

    区块链大本营
  • LinkedIn 2017新兴职业报告:面向未来的所有新兴工作都需要软件工程师

    来源:LinkedIn 编译:刘小芹 常佩琦 【新智元导读】 根据美国劳工统计局的数据,今天进入小学的孩子们中,预估有65%最终将会从事那些尚未出现的工作。L...

    新智元
  • 3-9 读写缓存流 ——Buffered

    类BufferedStream就是给另一流上的读写操作添加一个缓冲区。缓冲区是内存中的字节块,用于缓存数据,从而减少对操作系统的调用次数。因此,缓冲区可提高读取...

    py3study
  • 『为金融数据打标签』「2. 元标签方法」

    在〖三隔栏方法〗一贴里,我们已经解决了第一个问题,即根据止损止盈来给数据打标签。本帖则关注第二个问题,即如果下单,该下多少。

    用户5753894
  • AI泡沫之上,创业公司的护城河和边界扩张

    很早,VC 们就已放出人工智能将在 2018 年泡沫破灭的预言,现在的他们正作壁上观,盯着那张写满已投公司的图纸,以尽力确认自己抓住的是泡沫背后的真实。

    AI科技大本营
  • 写给供应链产品经理:浅谈订单系统的设计

    订单管理是一个常见的管理问题,包含在企业的订单处理流程中。由于客户/用户下订单的方式多种多样、订单执行路径千变万化、产品和服务不断变化、发票开具难以协调,这些情...

    物流IT圈
  • 深度学习实战 mnist数据集预处理技术分析

    mnist数据集可以从https://s3.amazonaws.com/img-datasets/mnist.npz 这个网址进行下载,下载的文件是一种称为np...

    算法与编程之美

扫码关注云+社区

领取腾讯云代金券