Power BI报表服务器之数据自动刷新
天行
2周前，Power BI星球发布了《报表服务器极简安装指南》，截止今日，阅读人数已达近2000，之所以现在才开始写后续内容，一是想看看有多少人对报表服务器的什么主题感兴趣；二是想通过读者的反馈检验一下文章中的观点是否存在错误。
那么先给大家汇报一下两个问题的收获：
一是上一篇文章发布后，得到了很多朋友的鼓励，同时很多人表示要马上把报表服务器搭起来，在工作中进行使用推广，不知道2周后有多少人已经开始实施，但从随附资源(公众号回复"报表服务器"下载相关资源)的下载情况来看，实际没有超过10人。
当然，那些软件都可以公开下载，而且速度也可能比百度网盘快，不一定要从所附链接下载。但即使把这个人数乘以10倍，也不过是阅读人数的20分之一。
从正面意义上来说，现实中领先80%的人真不是件困难的事情，只要你开始实践，就成功了一半。
二是上文中确实隐含着“问题”，或者说是后续投入实战会遇到的坑。那就是当时为了保证应用环境的合法和免费，选择了SQL Server Express版本，而这个版本微软居然没有显著提示不支持Agent服务，而且正常安装了该项服务，只是启动时会报错。
感谢星友叶余提前进行了数据源更新的测试，发现了该问题，并及时提醒。
所以本文首先要修正这个问题，然后再进行报表服务器数据源自动刷新功能的演练。
下面开始吧！
要理解为什么要启动SQL Server的Agent服务，就必须了解Power BI关于数据源自动刷新的背后原理。
首先，大家在使用Power BI Desktop时，都是有现成的数据源，比如数据库（SQL Server、Oracle、Mysql等等）的接口、Excel、CSV等等，只要第一次用PowerQuery做好的数据规范，以后数据更新时只要点击一个【刷新】键就可以了。
但是，如果你的报表要发布给很多人查看使用时，总不能给每个人安装一个Desktop版本，给所有人设置数据源路径，分发更新的数据源文件，教所有人如何点击【刷新】键吧？
所以，只要投入实用，就面临两个选择：微软公共云和本地报表服务器。
微软的公共云无论是Pro账户还是Premium账户，都需要安装本地网关服务（Gateway），简单说就是在本地数据源和公共云之间建立一个入口，实现数据传送、更新和安全控制等功能。如需详细了解可以去官网继续学习。
https://docs.microsoft.com/zh-cn/power-bi/service-gateway-onprem
1、PBIX报表文件的解释器。
可以简单看作网站服务的前端，对报表的形式进行展示。一方面大家可以把PBIX文件的后缀改成ZIP，然后打开看看，就会知道报表文件的大致结构是什么样的。另一方面，其实报表服务器支持多种报表格式（正式的说法是支持很多种扩展模式），比如可以直接把Excel文件上传上去，报表服务器会自动归类为Excel报表。也就是说，报表服务器具备多种报表格式的解释能力。
2、报表数据的存储和计算。
这一部分可以看作是网站服务的后台，这是由SQL Server的数据库引擎来解决问题的。所有的报表数据都存储在reportserver和 reportservertempdb 数据库中。
所以，可以推断出两个结论：
（1）报表服务器必须在SQL Server的数据库引擎下才能正常运行。
这个和数据源从哪个数据库来是两个概念，无论什么数据源都要转化成SQL Server数据库的数据才能被报表服务器识别使用。当然，这个转化过程是无需人为干预的。
（2）报表服务器数据源的自动刷新功能，本质上就是SQL Server数据库的自动化控制功能。
这项功能就是由SQL Server Agent服务所提供的。而微软在一个隐藏得很深的地方进行了提示：Express版不支持Agent服务。
这就是，我们为什么首先要将SQLServer Express版本进行更新替换的原因。
（一）SQL Server Developers版本安装
按照上一篇文章的介绍，从试用角度来分析，唯一可以免费开发使用的全功能版本就是SQL Server Developers版本。
为了尽量减少版本升级的工作量，我花了整整一下午的时间查阅资料，动手测试，试图不用删除原有版本，直接升级或者手工修改实例名称。
最后的结果直接告诉大家：
删除Express版，重新安装Developers版本是最快，也是最安全的做法，没有之一。
微软的这个做法也许就是告诉用户，免费就等于折腾吧。
重装过程中还会遇到一个“坑”，就是可能会提示安装失败。如下图所示：
原因是安装SSMS 18.2（SQL Server ManagementStudio）时，系统组件的版本已经比SQL Server 2017安装盘中的版本高，安装程序不能用低版本去覆盖高版本的系统文件。
如果大家在重装过程中遇到该问题，直接进入win10的【设置】-【应用】中删除Microsoft C++2017 Redistrbutable组件，特别提示的是，将X64和X86两个版本都要删除。
执行两个应用删除后，重新进入SQL Server 2017的安装程序，完成Developers版本的安装。
（二）重装Power BI报表服务器
重装了SQL Server Developers版之后，Power BI报表服务器的设置程序依然打开，也可以识别新的数据库实例，但点击【连接】进入之后，虽然没有提示报错信息，但迎接你的将是无穷无尽的空转。
最后，只能用任务管理器关闭设置程序，老老实实地删除报表服务器，重新进行安装设置。
好在，前面已经实践过一次，再次设置报表服务器应该驾轻就熟了吧？
完成报表服务器【Web服务URL】、【数据库】、【Web门户URL】等三项核心内容的设置，输入报表服务器的默认网址：http://*.*.*.*/reports，
报表服务器熟悉的界面就可以再次呈现在眼前了。
三、规划测试环境
日常工作中的Power BI数据源一般包括两种类型：
业务数据结构：
（一） 基本信息：
使用XLSX格式存放，包括两类信息：
1、产品：
2、人员
（二） 销售业务明细：
考虑了四种最常见的方式：
1、单Excel工作簿单一sheet
2、单Excel工作簿多个sheet
3、单文件夹多个Excel工作簿
4、单文件夹多个CSV文件
最后通过Power BI呈现简单的几类分析：
1、按月汇总销售额
2、按人员汇总销售额
3、按日汇总销售额及销售件数
4、按产品名称查看销售额占比
5、按产品分类查看销售额占比
6、按销售地区查看销售额占比
分别根据四种销售明细数据不同的更新方式，设计了对应的4个PBIX报表，界面内容如上图保持一致，主题风格略有区别。
（一） 完成Desktop模式下四种不同方式的数据刷新
1、在D盘建立data目录，将附件中的内容放入其中。
（1）基础数据.xlsx：包括人员和产品信息
（2）业务明细_1：对应单文件单工作簿方式的销售明细数据
（3）业务明细_12：对应单文件多工作簿方式的销售明细数据
（4）Excel工作簿：内有12个sheet，格式一致，对应单文件夹多XLSX文件方式的销售明细数据
2、在4个报表文件中分别以绝对文件路径的方式，完成数据导入刷新的需求。
3、推荐使用参数管理，将文件/文件夹路径赋值给参数，并将M代码中的绝对文件路径替换为指定参数名称。好处是以后更改路径，就只用在参数管理中进行修改即可，不用再去修改M代码。比如，后面就要进行类似修改操作。
以CSV文件夹模式为例：
4、测试一下，参数化管理的M代码是否运行正常，报表文件是否能正常执行刷新任务。
尽管如何用Power Query实现四种方式的数据获取、整理并不是本文的重点，但建议大家可以去看看这四个文件中具体实现的M代码，足够精巧高效。
在此，要感谢施阳老师的PQFans网站一直以来提供的帮助。
个人对M的态度一直是拿来主义+够用就好，国内已经有先行者将M研究得足够透彻，网上现有资源，足以应对90%以上的日常数据清洗的技术需求，就没有必要花太多精力去钻研M。这四种方式的代码，就直接参考了施阳老师PQFans上的资料，具体的解释请直接去https://pqfans.com/1104.html进行了后续学习。
（二） 建立共享文件夹并上传数据文件
Power BI报表服务器的本地数据刷新不需要通过网关，但数据文件必须存放在报表服务器能够访问的共享文件位置上，即使用“/主机名/共享名/文件名（/目录名）”模式，或者用IP地址取代主机名。
从实用的角度看，因为报表服务器一般是24小时不停机运行，所以数据源文件共享目录建在报表服务器所在的电脑上更为合适。
1、在服务器上新建目录，设置共享名为“Data“，记得打开win10的”共享和发现“开关。
2、将D盘上的Data目录的测试数据传输至服务器的共享目录下。
（三） 在Desktop模式下更改文件路径
在Desktop模式下，将4个报表文件中的文件/文件夹路径参数新增共享资源参数，此时参数化路径的好处就呈现出来了，只用选择参数，不用逐个修改M代码。
1、单Excel工作簿多个sheet
2、单文件夹多Excel工作簿模式
3、单文件多CSV文件模式
再次测试刷新功能是否正常。
（四） 上传PBIX文件至报表服务器
分别将4个PBIX文件上传至报表服务器
此时注意，登录报表服务器的用户名在报表服务器所在Win10系统中，一定是administrators成员，即具有系统管理员权限。否则报表服务器将提示权限不够，没有修改、上传的权限。
（五） 在报表服务器上设置报表的数据源
1、点击每个报表上右上角的三个点，选择【管理】，点击【数据源】，进行报表数据源的设置。
2、分别输入登录报表服务器的用户名和密码，点击【测试连接】，验证无误后，选择【保存】。
如法炮制，完成所有4个报表的数据源设置。如果在Desktop模式下，对路径参数的修改没有问题，此处只需要点【测试连接】和【保存】即可。
（六） 在报表服务器上设置报表的计划刷新
这里给读者留个问题，如果要设置一个10分钟更新一次的计划，该如何实现呢？
将剩余三个报表的刷新计划设置好，就要进入“见证奇迹”的时刻了。
（七） 模拟测试工作数据更新场景
根据4种更新方式的不同，分别用不同的方法改变数据源，模拟测试数据源变化后，自动刷新的场景。
首先，有必要再次理解报表服务器的更新逻辑，即：
1、目前已有一个8月17日10点的刷新计划，这个计划的意思是SQL Server Agent服务在对应时间会执行一个数据库更新任务。
2、该更新任务只是在安排在具体的时点自动执行，实际上在任何时候，都可以指示执行这个更新任务。
所以，我们的模拟测试不用等到对应的时间，看系统执行的效果，可以手动要求系统立即执行该更新任务。
1、单Excel工作簿单一sheet模式
（1） 打开报表，可以看到当前的业务明细一共4091件。
（2） 打开服务器共享目录中的“业务明细_1.xlsx”文件，将明细增加到4105条。
（3） 在报表的【管理】-【计划的刷新】界面中，选中刚才设好的刷新计划，点击上方的【立即刷新】。
（4） 此时可以看到该计划的【状态】显示为“正在刷新“，注意这个状态不会自动更新。10秒钟后，点击浏览器工具栏的【刷新】后，会发现【状态】显示为"Completely Data Refreshed"，即数据刷新完成。
（5） 回到主页，点击打开该报表，发现统计件数变成了4105件，说明报表根据数据源文件的变化进行了刷新，目标完美达成！
2、单Excel工作簿多个sheet模式
（1）打开服务器共享目录中的“业务明细_12.xlsx”文件，删除201807-201812共6个工作表。
（2）同样，手动执行刷新计划，回到主页，打开报表可以看到按月统计销售额的图表中，只剩下1-6月的数据，报表完美实现了按数据源的更改自动刷新的需求。
3、单文件夹多Excel工作簿模式
（1）进入服务器共享目录的Excel文件夹，删除201801-201803三个文件。
（2）手动执行刷新计划，打开报表，可以发现统计月份少了2018年1-3月的数据，刷新要求按需达成。
4、单文件夹多CSV文件模式
（1）进入服务器共享目录的CSV文件夹，只保留201801-201806等6个CSV文件。
（2）手动执行报表的刷新计划，打开报表，发现按月统计销售额图表中只有2018年1-6月的数据，说明刷新任务正常执行。
至此，我们完成了关于报表服务器自动刷新数据源的全部测试流程，简单提炼一下该模式在实践中的实施模式：
1、IT部门负责搭建好报表服务器（含SQL Server的配置），并在服务器上建好共享文件夹。
2、业务管理部门负责开发业务报表，并在本文的提示下设置好报表的数据源路径和刷新计划。
3、业务一线岗位负责下载或录入业务明细数据，并上传至服务器的共享文件夹。
4、剩下的事情全部由Power BI报表服务器负责完成。
从此以后，各岗位只需关注各自的责任，保证在规定时间内完成自己的任务，再也不用加班做报表了！
本文技术要点回顾：
1、SQL Server不同版本的区别，以及重装升级的要点；
2、使用Power Query对四种常见业务明细方式的数据获取和整理；
3、Power Query的参数设置及运用；
4、Power BI报表服务器数据源的设置；
5、Power BI报表服务器刷新计划的设置及手动运用。
您掌握了吗？
彩蛋：
这样实用的彩蛋是不是值得您的打赏呢？
联系客服
