PowerBI 多種增量刷新方案最大支持100T數據存儲及單個模型50G+

2019 年 10 月 6 日
筆記

什麼是增量刷新

所謂增量刷新，是指增量刷新數據。一般情況下，在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次，但費用昂貴，屬於企業級解決方案。