Sql-Server
按多對多表過濾
我很難理解如何過濾掉多維表達式的結果。
這是我的數據庫模式。
這是我的數據。
Manufacturer ╔════╦═══════════════════╗ ║ Id ║ Name ║ ╠════╬═══════════════════╣ ║ 1 ║ Awesome Computers ║ ║ 2 ║ TailSpin Toys ║ ╚════╩═══════════════════╝ Item ╔════╦═════════╦════════════════╦═══════╗ ║ Id ║ Name ║ ManufacturerId ║ Stock ║ ╠════╬═════════╬════════════════╬═══════╣ ║ 1 ║ PC ║ 1 ║ 40 ║ ║ 2 ║ Server ║ 1 ║ 10 ║ ║ 3 ║ STB ║ 2 ║ 80 ║ ║ 4 ║ Console ║ 2 ║ 50 ║ ╚════╩═════════╩════════════════╩═══════╝ Part ╔════╦══════════════════╦════════╦══════════╦═══════╗ ║ Id ║ Name ║ ItemId ║ StatusId ║ Stock ║ ╠════╬══════════════════╬════════╬══════════╬═══════╣ ║ 1 ║ MBO ║ 1 ║ 1 ║ 100 ║ ║ 2 ║ Processor ║ 1 ║ 1 ║ 100 ║ ║ 3 ║ Server MBO ║ 2 ║ 2 ║ 20 ║ ║ 4 ║ Server processor ║ 2 ║ 2 ║ 20 ║ ║ 5 ║ Main box ║ 3 ║ 2 ║ 40 ║ ║ 7 ║ Adapter ║ 3 ║ 3 ║ 30 ║ ║ 8 ║ Controller ║ 4 ║ 2 ║ 40 ║ ║ 10 ║ Adapter ║ 4 ║ 1 ║ 60 ║ ║ 11 ║ Memory card ║ 4 ║ 2 ║ 80 ║ ╚════╩══════════════════╩════════╩══════════╩═══════╝ Status ╔════╦═════════════╗ ║ Id ║ Name ║ ╠════╬═════════════╣ ║ 1 ║ No data ║ ║ 2 ║ Available ║ ║ 3 ║ Unavailable ║ ╚════╩═════════════╝
我將所有內容都導入了表格模型解決方案。在此之後,我創建了兩個度量:
- 表項:ItemStock:=SUM($$ Stock $$)
- 表部分:PartStock:=SUM($$ Stock $$)
然後我將多維數據集部署到伺服器。
通過執行以下 MDX 查詢…
SELECT NON EMPTY { [Part].[Name].CHILDREN } ON ROWS, { [Measures].[PartStock] } ON COLUMNS FROM [Model] WHERE ( { [Status].[Id].&[1] } )
…我得到這個結果集…
╔═══════════╦═══════════╗ ║ ║ PartStock ║ ╠═══════════╬═══════════╣ ║ Adapter ║ 60 ║ ║ MBO ║ 100 ║ ║ Processor ║ 100 ║ ╚═══════════╩═══════════╝
…沒關係。
但是,在執行此 MDX 查詢時…
SELECT NON EMPTY { [Item].[Name].CHILDREN } ON ROWS, { [Measures].[ItemStock] } ON COLUMNS FROM [Model] WHERE ( { [Status].[Id].&[1] } )
…我得到這個結果集…
╔═════════╦═══════════╗ ║ ║ ItemStock ║ ╠═════════╬═══════════╣ ║ Console ║ 50 ║ ║ PC ║ 40 ║ ║ Server ║ 10 ║ ║ STB ║ 80 ║ ╚═════════╩═══════════╝
我期望 ItemStock 表中的項目會被Part表過濾為多對多關係。例如,MBO、Processor 和 Adapter 引用了第 1 項和第 4 項,因此結果將被限制為它們,結果應該是這樣的:
╔═════════╦═══════════╗ ║ ║ ItemStock ║ ╠═════════╬═══════════╣ ║ Console ║ 50 ║ ║ PC ║ 40 ║ ╚═════════╩═══════════╝
我究竟做錯了什麼?
假設我們正在討論 SSAS 2012 或 2014 中的表格模型(因為 2016 具有圍繞雙向交叉過濾的新功能)交叉過濾僅在一個方向上流過關係。所以過濾
Status
將交叉過濾Part
,但不是Item
orManufacturer
。過濾Manufacturer
將交叉過濾Item
,Part
但不會Status
。您嵌入表格模型中的任何計算都必須用 DAX 編寫。因此,如果您想過濾
Status
並影響ItemStock
您需要編寫以下程式碼的度量:ItemStock := IF( ISCROSSFILTERED('Status'[Id]), CALCULATE( SUM( 'Item'[Stock] ), SUMMARIZE( 'Part', 'Item'[Id] ) ), SUM( 'Item'[Stock] ) )
關於 DAX 中多對多公式的更多理論:
- 通過 Marco Russo的 SUMMARIZE 和交叉表過濾優化 DAX 中的多對多計算
- 白皮書: Marco Russo 和 Alberto Ferrari的多對多革命 2.0