內行才能看懂的 PowerBI DAX 引擎重大更新來了
- 2019 年 10 月 6 日
- 筆記
2019年3月1日,在SqlBits大會上,微軟宣布DAX引入一項重大更新:Calculation Group(暫且不做翻譯)。這項更新將對PowerBI及SSAS均構成重要影響。為此,微軟SSAS團隊官方,SQLBI.com以及Chris Webb分別在各自部落格記錄這一內容。(後兩者為SSAS領域國際頂級專家部落格)
按照 SSAS 團隊官方的話說,這是一項DAX本該具有卻一致未有的能力。 按照 馬克·啰嗦 大師的說法,之所以《DAX聖經 第二版》一直未出版,就是在等微軟宣布這項DAX更新,這將寫入最後一章。 按照 Chris Webb 的觀點,這項更新是DAX歷史上最重要的更新。
那麼到底是什麼功能(特性),另各位專家如此興奮不已呢,本文將結合三方部落格來介紹這一特性。
問題背景
簡單直接地說,這個問題來自於 DAX度量值 的復用問題,舉例如下: 現有度量值:訂單銷售額,訂單利潤,訂單利潤率等10個指標,它們的計算邏輯非常簡單。不妨稱之為基礎度量值。 當我們需要這些基礎度量值與一些條件關聯時,就會產生新的度量值,如和時間有關的組合,包括: MTD,QTD,YTD,PY,MTD PY,QTD PY,YTD PY,PY FY,MTD YOY%,QTD YOY%,YTD YOY%等很容易就超過10個,因此,對於10個基礎度量值,每個基礎度量值都會與10個條件組合來派生度量值,迅速多達100個度量值。 而如果有50個度量值與20個條件來組合,就會達到1000個度量值,我們稱之為無法復用導致的度量值爆炸式增長。
在微軟的產品中,其實有一條隱形的準則,那就是:如果用戶在有規律地重複一項操作,那麼有兩個可能:要麼有一個一鍵解決的方法,只是你不知道;要麼就是微軟缺乏了一項重要的特性。而99%的可能性是前者。 也就是用戶不知道這個一鍵搞定的方法,例如:數據模型自動檢測是對N次VLOOKUP的一鍵解決方案。
順便提一句:VLOOKUP 在辦公中將兩個表合併為一個表,再透視基本是辦公階段Excel用法的鐵律,而在BI中,直接建立數據模型的方法直接將辦公的用法完全碾壓,辦公需要VLOOKUP,而BI卻是一鍵解決的,因此花費大量精力去搞清楚VLOOKUP的十大技巧完全不必。
而在今天這裡,卻是後者,確實是微軟缺失了一項在 DAX 中解決復用問題的特性。再重複一次:如何在DAX中復用複雜邏輯,不需要編寫新的度量值,卻可以從基本度量值派生新的度量值。怎麼破?
Calculation Group
為了解決類似上述的問題,現有的DAX是無法做到的(當然,現在可以了)。微軟稱這項特性叫:Calculation Group。這一個只含有一列的表,列中的每個值定義了可復用的計算邏輯,稱這些值叫:calculation items。
這裡不做中文翻譯,因為該特性尚在預覽階段,以未來微軟提供的中文翻譯名字為準。
calculation items 可以與度量值任意結合使用,達到改變度量值已有邏輯,更準確講,是從已有邏輯派生新計算邏輯。
一個典型的案例就是將時間維度的邏輯進行復用,形如:
時間維度案例
表名稱:Time Inteligence 列名稱:Time Calculation 優先順序:20
對其中 calculation item 的定義如下:
可以看到,這裡用到了 SELECTEDMEASURE 函數,請仔細觀察上述的效果示意圖,可以看出:當 calculation item 與 度量值 在一起使用時,它可以動態的修改度量值。準確地猜測(微軟尚未發布官方說明),calculation item 將動態地修改度量值進行計算的篩選上下文環境,而不去管這個度量值是什麼。
從 CALCULATE 的技術細節來講,應該是在 CALCULATE 計算的上下文堆棧中,壓入新的篩選器,再計算 CALCULATE 的第一個參數。(詳細原理已經在Excel120的《DAX基礎》中給出,不再贅述。)這裡很明顯會涉及到三個問題:
如何選出當前的度量值:SELECTEDMEASURE。 當前度量值的名字:SELECTEDMEASURENAME。 是否是特定的度量值:ISSELECTEDMEASURE。
上述三個新增函數就可以來解決這個問題。
還有一個問題就是如果一個基礎度量值與多個calculation item同時使用,到底哪個 calculation item 先算的問題,這個由上述的優先順序定義給出。
相關資訊
該DAX能力目前僅僅在預覽階段的 SQL Server 2019 CTP 2.3 中存在,而且尚無任何微軟官方編輯器可以編輯操作該特性,也就是說,你無法在 DAX Studio,Power BI Desktop 或 SSAS 的 SSDT 中操作或設置該特性。
該特性已經在 引擎版本1470中存在。目前我們使用的 2019年2月 版的PowerBI 引擎版本為 1465。如下:
也就是說,本文所述的特性目前看不見,摸不著,但它的確存在於最新的DAX引擎中了。
這是一個使用起來很簡單的特性,但SSAS團隊需要在背後做很多事情,才能確保該特性與其他DAX特性完好共存,例如:Excel是可以作為客戶端來使用的;RLS的兼容;動態格式字元串;AllSELECTED等函數;Detail Rows 表達式等。
動態格式字元串
你可以想像對於一個基礎度量值(如:銷售額)與N個 calculation item 的結合使用,會產生 去年銷售額 以及 銷售額增長率 這兩個不同指標,而它們一個是數字格式,一個是百分比格式,但卻只有一個基礎度量值,這便構成了一個矛盾。
在 超級複雜中國式複雜報表 中,我們給出了現有 PowerBI DAX 下的解決方案,但那並非一種原生特性,而是需要利用多種技巧。很明顯,動態格式字元串的發布將更好的解決這類問題,以及更容易地創建中國式複雜報表。
動態格式字元串 特性應該會在2019的迭代中發布,我們將持續關注。
提前嘗鮮
如果特別希望提前體驗 calculation group 特性,確實有一個方法,那就是使用:Tabular Editor。這是一個由社區開發的第三方工具。
給出嘗試思路如下:
- 下載最新版 SQL Server 2019 CTP 2.3 並建立一個 SSAS Tabular 項目。正如上所述,目前微軟尚未提供任何一種可以體驗 calculation group 特性的編輯工具。
- 使用 Tabular Editor 來打開上述項目模型文件,通過 Tabular Editor 的能力來創建 calculation group。
- 部署發布項目。
- PowerBI Desktop live connenction 模式連接SSAS體驗。
按照義大利大師的話來說,由於Calculation Group特性的到來,即將解鎖一大波DAX的新能力,具體能做到哪些,現在不好說,微軟還沒有對外明確公布在 Power BI 中發布該特性的時間,大師們表示需要數月甚至整年時間來研究由此帶來的新玩法。
而在微軟的官方部落格已經能預料到由於DAX引擎一波特性的發布,已經讓SSAS團隊處於很大負荷的狀態。這些特性包括:管理聚合(多對多);PowerBI 增量刷新;開放 PowerBI 可作為 SSAS 連接,以及今天提及的Calculation Group。
總之,盡情享用吧。
參考資料
微軟SSAS團隊部落格: https://blogs.msdn.microsoft.com/analysisservices/2019/03/01/whats-new-for-sql-server-2019-analysis-services-ctp-2-3/
SQLBI部落格: https://www.sqlbi.com/blog/marco/2019/03/01/calculation-groups-in-dax-first-impressions/
Chris Webb部落格: https://blog.crossjoin.co.uk/2019/03/01/ssas-tabular-2019-calculation-groups-and-migration-from-ssas-multidimensional/