周期性的更新数据和撰周报月报成为繁重的劳动。很多时间花费在数据处理上,而真正的分析工作,往往只能草草收场,周报月报的质量也难以得到提高。
使用Power BI对图表进行可视化处理,效果和稳定性是杠杠的,但是总有一些表格是需要在Excel里完成的。可是微软不支持用户能自动与PPT嵌入图表(也许是微软想直接在PowerBI内部制作类似报告,但是不得不说做出高大上的演讲报告方面,PowerBI只是个弟弟)。目前PowerBI的报告能力,颜色和文字能力,图表的定制能力,目前不及Excel和PPT。给分析人员看是非常够用了,快捷又方便。但是给管理层做报告,呵呵。领导看到PowerBI的报告,意见是质量一般。我也是做过几次才发现,PowerBI报告不能用其他字体,不能调整单个数据label,等等。
虽然有第三方工具或插件可以嵌入PowerBI图表到PPT,但是需要联网等等,万一刷新不出来怎么办呢。各位做报告的同学一定有经验,你绝对不希望PPT报告有任何技术问题,不然董事会上各位大佬干瞪眼等着刷新数据,呵呵。后果惨过没有报告。这就是报告一族面对的政治生态。简单说就是绝不允许任何问题。
而光有Excel PowerPivot,可视化又不够?一套数据源两吃是多么迫切的需求!!
在微信公众号POWERBI星球 上终于找到了一篇解决方法,现把亲测的结果汇总在这里。希望对需要经常更新数据分析报告的同行有帮助。
从PowerBI外部访问,并利用Excel的图表功能成为可能。
Excel链接PowerBI的方法有三种,下我亲测了前两种:
tips:如果原始数据有更新,一定要先pbix文档更新完毕,再有很多图表的Excel文档更新,Excel文档里才会是最新的数据。
因为Excel是调用PowerBI的服务,因此这些在Excel里面图表报告的数据调用,其实都是在PowerBI里面完成的,超级高效。
至此,我们即可以享受PowerBI的高效,又可以享受Excel和PPT的强大定制功能,还可以固定下来所有的工作步骤,以后一键刷新就好。当然有些图表还是需要适当调整样式。
以上是最经典的OLAP链接方式。但是,这种数据更新方式真的有点麻烦。其他的方法先放在这里,有兴趣的小伙伴可以验证 了在评论里告诉我一下。
1.2.2利用宏文件自动连接PowerBI ssas服务
方法二:利用宏文件自动连接PowerBI ssas服务
Google上还找到一个澳大利亚的大神写的一个专门的Excel VB宏,来实现一键自动链接PowerBI文件。也就是说你不再需要链接文件,不需要DAX Studio查询端口,不需要每次都建立链接文件,一键连好。这个哥们儿叫Matt Allington。你可以在「PowerBI星球」后台回复“Excel连接PowerBI”获取这个Excel宏文件。
我这里把VBA的代码放一下:
SubUpdateUserPath()
'developed by Matt Allington from http://Exceleratorbi.com.au
user = Environ(“LOCALAPPDATA”)
UserPath = user & “**\Microsoft\Power BIDesktop\AnalysisServicesWorkspaces **”
Sheets(“Connection”).Range(“B2”) = UserPath
End Sub
SubRefreshSSASConnection()
’ developed byMatt Allington from http://Exceleratorbi.com.au
Dim myTable AsListObject
UpdateUserPath
Range(“SSAS_Data”).ListObject.QueryTable.RefreshBackgroundQuery:=False
Port =Range(“Port”)
Db =Range(“DB”)
If Len(Port) =5 Then
With ActiveWorkbook.Connections(“PowerBID”).OLEDBConnection
.CommandText = Array(“Model”)
.CommandType = xlCmdCube
.Connection = Array( _
“OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;PersistSecurity Info=True;Initial Catalog=” & Db & ";Data " _
, _
“Source=localhost:” & Port & “;MDXCompatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update IsolationLevel=2” _
)
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = “”
.MaxDrillthroughRecords = **1000 **
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
.RetrieveInOfficeUILang = True
End With
With ActiveWorkbook.Connections(“PowerBID”)
.Name = “PowerBID”
.Description = “”
End With
ActiveWorkbook.Connections(“PowerBID”).Refresh
Else
MsgBox “You must have exactly 1 instance of Power BI Desktop open”,vbCritical
End If
End Sub
大家注意黑体加删除线的部分可能需要调整。(第一个部分指定了PowerBI的SSAS端口文件位置,可能因为安装的位置不同而不同,只装一个PowerBI软件,且是一路yes的不需要调整。
第二个部分,最大打开drill数可以调到最大10,000)
除了上述两种OLAP链接方式,微软自己其实也发现了大家的需求,推出了一个插件。
2.pro版利用在线powerbi的“在Excel里分析”
亲测可用,但是需要开通pro版
因为相当于使用云SSAS功能,一方面是需要将pbix文档上载,并设置好各数据源的网关才能实现更新,
连接的每一步操作的响应速度较慢,增加了很多不确定性因素(比如加班的晚上,突然怎么也加载不了新数据,会不会很崩溃),从稳定性考虑还是推荐上面的本地方法。
另外很多的公司数据安全也不允许这样的数据上传操作