?文/HALI就職于汽車行業戰略部門
專注汽車市場信息情報收集和分析

因為工作需要，每月周期性的更新數據和撰寫PPT 報告成為繁重的勞動。結果是很多時間花費在數據處理上，真正的分析工作，往往只能草草收場。不能坐以待斃，就要想想有沒有更好的辦法。

最早，我想到并采取的是Excel圖表到PPT的鏈接。后來隨著各種BI工具的普及，嘗試學習并使用了Power BI。它實在強大，據說使用了SQL Server的數據引擎Vertipaq，數據處理速度飛一樣。另外，可以處理的數據可以做到海量，加上是專業數據庫，比較之下，Excel秒成渣。

于是嘗試將工作平臺轉移到PowerBI。但是，最終的測試發現，微軟不支持用戶能自動與PPT嵌入圖表（不知道為什么，但是看來微軟想直接在PowerBI內部制作類似報告，與PPT不同，PowerBI的報告將更傾向于數據）。

雖然有第三方工具或插件可以嵌入PowerBI圖表到PPT（如何在PPT中動態交互PowerBI報告？)，但是需要聯網等等，很麻煩。各位做報告的同學一定有經驗，你絕對不希望PPT報告有任何技術問題，不然董事會上各位大佬干瞪眼等著刷新數據，呵呵。后果慘過沒有報告。這就是報告一族面對的政治生態。簡單說就是絕不允許任何問題。

平心而論，目前PowerBI的報告能力，顏色和文字能力，圖表的定制能力，目前不及Excel和PPT。給分析人員看是非常夠用了，快捷又方便。但是給管理層做報告，呵呵。領導看到PowerBI的報告，意見是質量一般。我也是做過幾次才發現，PowerBI報告不能用其他字體，不能調整單個數據label，等等。有其他方法，太費勁。

這就尷尬了，PowerBI白學了。這么好的數據處理能力真的可惜了。曾經有一段時間嘗試學習Excel PowerPivot，但是發現差異挺大，而且不能使用PowerBI的便捷圖表方式和分組什么的真的是損失，貌似不能兼得了，怎么辦呢？

搜google吧，功夫不負有心人，還真讓我找到了解決方法-OLAP（OnlineAnalytical Processing）。應采總的邀約，把相關的內容匯總一下，在這里分享給大家。希望對需要經常更新PPT報告的人有幫助。

傳統方式：Excel 數據圖表和PPT的鏈接及自動數據更新

我的工作涉及數據分析圖表和定期報告（PPT）。之前采用的方式是Excel維護數據，然后使用多個pivot table數據支持，制作數據圖表。然后Excel內部的圖表用黏貼鏈接的方式，黏貼進PPT里面。每個月數據更新時，把新增數據手工黏貼到數據源表，然后refresh all pivot table。然后打開PPT，提示是否更新數據，點yes。在無數次的屏幕抽動（抽筋一樣閃動）之后，結束了圖表更新。

這樣做的好處是以透視表的方式固定了每月固定的工作流程，一定程度上實現了圖標數據更新的自動化，節省大量時間。

但如果可以享受PowerBI的高效，又可以享受Excel和PPT的強大定制功能，還可以固定下來所有的工作步驟，然后一鍵刷新是不是就完美了？

PowerBI實際上可以理解為一個小規模的SQL Server，精簡了很多東東，但是SSAS服務保留了。如果你運行PowerBI，通過任務管理器，你可以看到SSAS的服務一起運行了，SSAS服務就是留給兼容的外部程序訪問數據庫的，很幸運Excel里面提供了訪問SSAS的組件。

這就讓我們可以從PowerBI外部訪問，并利用Excel的圖表功能成為可能。

Excel鏈接PowerBI的方法有三種,下面一一介紹。

方法一：Excel手動連接PowerBI SSAS服務

打開pbix文件，然后打開DAX Studio（關于DAX Studio的使用請參考：DAX Studio：你遲早會用到的幾個功能)，復制pbix文件的ssas服務端口：

然后，打開Excel，新建一個文件。點擊data>from other sources>fromanalysis services：

把pbix的分析服務端口黏貼到里面：

然后下一步，下一步，結束。注意，這一步我們建立了一個鏈接文件，它的存放位置在這里：鏈接文件是以端口號為開頭的，容易區分。

然后Excel會提示你打算把用什么方式調用外部ssas服務的數據。當然PivotTable Report了：

注意看下右側的可選數據表和所有列，看看是不是都在了？

這里有個技巧。你會發現一些數字列不能被拖入value。怎么辦呢？查閱了微軟的官方文檔，方法是在PowerBI里面給每個數字列做一個度量值。然后你會看到這些度量值（帶西格瑪∑符號）如下圖：然后他們就可以被拖入value合并計算了。

然后根據需要做各種透視表吧。

這里建議大家再單獨建立一個新的Excel文件，該文件專門用來制作最終的圖表（管理層喜歡的那種各種定制圖表，包含貴公司的各種文化和領導喜好）。

下一步是把OLAP文件里的數據鏈接黏貼到最終圖表文件里面。（其實業務順序可能是根據業務需要先做最終圖表Excel文件，然后根據圖表文件的數據需求再做OLAP pivot數據文件），最后把最終圖表黏貼鏈接到PPT。

這里提醒一句，一定要在同時打開PowerBI和OLAP文件的情況下才能修改PowerBI文件里面數據表和列的名稱。否則，OLAP文件找不到變更后的列和表名稱。最好留一個專門做報告的PowerBI文件，不要隨便修改。我做過實驗，調整列的位置或增加列不會影響OLAP文件，所以可以隨后增加分組等工作，不需要一開始都分好。

最后，還有個問題，每月數據更新。當pbix文檔關閉并重新打開后數據端口會發生變化，怎么辦呢？

用DAX studio查詢新端口。然后打開一個新Excel嘗試鏈接ssas端口。然后關閉Excel文件。下一步是關鍵，打開我們保留了很多透視表的OLAP Excel文檔，選擇pivot tools>connection properties：

選擇definition選項卡，選擇connection file，Browse然后選擇新端口號開頭的鏈接文件。

然后refreshall，屏幕抽動無數次，結束。

其他的更新步驟不多說了，Excel就是全部刷新，PPT在打開時更新數據就ok了。至于pbix文件的數據更新，請各位參考采總的星球文章，推薦同一文件夾里的新增更新方式。

因為Excel是調用PowerBI的服務，因此這些在Excel里面圖表報告的數據調用，其實都是在PowerBI里面完成的，超級高效。

至此，我們即可以享受PowerBI的高效，又可以享受Excel和PPT的強大定制功能，還可以固定下來所有的工作步驟，以后一鍵刷新就好。當然有些圖表還是需要適當調整樣式。

以上是最經典的OLAP鏈接方式。但是，這種數據更新方式真的有點麻煩。有沒有更快捷的方法呢？google告訴我有的。

方法二：利用宏文件自動連接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 & "MicrosoftPower BIDesktopAnalysisServicesWorkspaces" Sheets("Connection").Range("B2") = UserPathEnd SubSubRefreshSSASConnection()' developed byMatt Allington from http://Exceleratorbi.com.auDim myTable AsListObjectUpdateUserPathRange("SSAS_Data").ListObject.QueryTable.RefreshBackgroundQuery:=FalsePort =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鏈接方式，還有別的辦法嗎？在星球貼出本文后，一個迪拜的星友 分享了另外一個實現方法。

方法三：利用Power BI Publisher

Power BIPublisher是微軟自己做的一個Excel插件。看來微軟也覺得讓各位數據分析員每次更新數據都去重新建立鏈接文件太費力了。因此寫了一個插件方便大家。

據她說：“可以從Excel界面登陸連接PowerBI的報告和數據。然后加載成透視表，刷新就跟Excel刷新一樣也可以設置每次打開自動刷新。我們財務系統的所有數據都推到了dataflow里這樣就不用從ERP里下載了。”

我去下載了測試。發現使用該插件的前提是PowerBI必須是Pro賬戶。所以，不給錢，官方是不會給你這個方便的。不過購買Pro的同學可以下載該插件，享受這一便利。

本文至此結束。所有操作，親測可靠，各位有需要的親可以嘗試。

答應了采總寫一篇文章，拖了一段時間，今天交差，請各位斧正，如果各位有新的方法和心得，也請分享，希望對大家的工作有所幫助，謝謝。

-精彩推薦-

喜歡了別忘了點贊哦~