Power BI中定时刷新Excel文件

Power BI把商务智能带到了云端,为企业创建一个Power BI实例也就是分分钟的事情,之后便可以上传Excel文件并在浏览器以及移动设备中互动,非常的高大上。然而,及时更新Excel文件中的数据对企业决策至关重要,拿着前天的数据规划明天,是需要很多勇气的。幸好,Power BI提供了基于数据管理网关的定时数据刷新功能,使得Excel文件青春永驻。

Power BI提供了借助数据管理网关(Data Management Gateway,简称DMG)刷新数据服务,不但可以帮助用户定期从企业内部和外部抓取最新的数据,还支持Power Query这样的数据聚合,很是方便。有了DMG的帮助,Power BI用户不但可以体会到云的便捷,同时还可以与企业内部现有的业务数据互操作,两全其美。下面让我们来看看如何进行数据刷新。

安装数据管理网关

DMG本质上这是一个安装在企业内部的反向代理软件,可以根据Power BI从云上发布的指令,从企业内部外部抓取数据上传到Power BI,从而更新云端的Excel文件。DMG不但支持数据压缩和并行上传,而且在网络中断以后会自动重试,保证稳定的数据传输。同时,新版本的DMG还支持Power Query聚合刷新,即使Excel中包含多个数据源的复杂聚合也可以被刷新。具体的数据源种类以及验证方法列表可以参考这里

安装DMG的过程很直观。首先从Power BI管理员中心创建一个DMG,下载MSI安装,然后输入对应的密钥即可。稍等一会儿就可以在管理员中心看到这个DMG已经成功注册上线了。 话说今年7月份的Power BI已经支持DMG多实例的特性,通过把多个DMG放在一个组里面来达到负载平衡并提高服务可用性。如下图,我们已经创建了三个实例,假设一个实例的可用性是90%,那么三个实例同时挂掉的可能性只有0.1%,从而达到了理论上99.9%的高可用性了:

创建数据源

要刷新Excel文件,我们还需要在DMG上定义数据源,从而允许云上的Excel文件通过这些数据源来获取数据。换句话说,你可以通过DMG在云端与企业内部打通一条安全的数据通道。 下面让我们以一个具体的例子看看如何定义数据源。假设在云端有个Excel文件,通过Power Query和Power Pivot统计了来自微软研究院提供的软件累计下载数据,并且按照年份归类显示总数:

要定义数据源,首先点击Data菜单、Connection按钮,选择那个Power Query,选择Properties、Definition、Connection String,复制出那个长长的字符串:

Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(19f1198f-981c-4675-bffc-7877e78ac579)$;Location="Microsoft Research";Extended Properties="UEsDBBQAAgAIAK…"

这个字符串其实是编码过了的M语言代码,刻画了刚才在Power Query中所有的用户操作。把这个字符串复制到管理员中心,就可以看到这个Power Query用到了一个OData数据源,提供密码后保存,就可以看到这个数据源已经被成功创建了:

需要注意的是,刚才输入的用户名和密码是用DMG上面私有的证书来加密的,即使某国国安局胁迫微软也没法弄到明文,因为证书是由我们自己版权所有翻录必究,棱镜门之后,不得不提高警惕啊。 下面只需要测试一下聚合,对相应用户授权即可。

设置定时刷新

定义好数据源之后,便可以点击Power BI中的Excel文件并选择Schedule Data Refresh了:

除了刷新频率、时间之外,我们还可以选择但凡刷新遇到错误之后可以自动通知某人,从而快速跟进排错。

此外,Power BI还提供了历史快照,方便用户了解Excel文件数据是何时刷新、花费多久等等:

更多内容,请参考在线帮助以及排错手册

顺便插一句,Microsoft Research提供的软件与其他的效率软件不一样,大多数是着眼未来的应用,2009和2011的下载量主要是由以下排名靠前的软件贡献的,有机会可以把玩一下,非常有意思:

至此,我们分享了Power BI中定时刷新Excel文件的体验,相信大家有了非常感性的了解。没错,在这个云计算的时代,许多传统的应用包括Microsoft Office都被搬上了云端,使得用户体验到云的即开即用、按需付费等便利;同时,DMG搭起了云与企业内部的桥梁,使得云计算与现有的业务数据无缝结合,相得益彰。听说DMG之前的code name是bifrost,北欧神话中的彩虹桥是也,真是很贴切。 文中提到的Excel文件可以从这里下载。

更新:An Inside look at Microsoft Data Management Gateway白皮书提供了许多关于数据管理网关的技术细节,推荐阅读。

 



张 琪