Mondrian是一個開源項目。一個用Java寫成的OLAP（在線分析性處理）引擎。它用MDX語言實現查詢，從關係數據庫(RDBMS)中讀取數據。然後經過Java API用多維的方式對結果進行展示。Mondrian如何將MDX轉換成相應的SQL去底層數據源查詢數據的？這個就是本文要剖析的問題。
模型結構
<code><schema><dimension> <hierarchy> <table> <level> <level> <level> /<hierarchy>/<dimension><dimension> <hierarchy> <table> <level> <level> <level> /<hierarchy>/<dimension><cube> <table> <dimensionusage> <dimensionusage> <measure> <measure> <measure> <measure> <calculatedmember> <formula>[Measures].[銷售額] - [Measures].[成本]/<formula> <calculatedmemberproperty> /<calculatedmember> /<cube>/<schema>/<code>
表結構
數據庫：derby
dim_time
dim_store
fact_sales
查詢解析
1、單軸+單指標：查詢銷售額
<code>select {[Measures].[銷售額]} ON COLUMNS from [銷售情況]/<code>翻譯成的SQL:
<code>select sum("fact_sales"."sales") as "m0" from "fact_sales" as "fact_sales"/<code>2、單軸+單維度和指標：查詢日期和銷售額
<code> select {Crossjoin([時間].[年].Members, {[Measures].[銷售額]})} ON COLUMNS from [銷售情況]/<code>一個軸上出現不同的維度需要使用 Crossjoin
翻譯成的SQL:
<code>-- 獲取所有的年份select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC-- 求所有年份select count(distinct "time_year") from "dim_time"-- 獲取所有年份和求和數據select "dim_time"."time_year" as "c0", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales" where "fact_sales"."time_id" = "dim_time"."time_id" group by "dim_time"."time_year"/<code>3、單軸+雙維度和單指標：查詢日期和銷售額
<code>select { Crossjoin( Crossjoin([時間].[年].Members, [銷售點].[省].Members), {[Measures].[銷售額]} ) } ON COLUMNSfrom [銷售情況]/<code>翻譯成的SQL:
<code>// 獲取年select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC // 獲取省份 select "dim_store"."store_province" from "dim_store" as "dim_store" group by "dim_store"."store_province" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASC select count(distinct "time_year") from "dim_time" select count(distinct "store_province") from "dim_store" select "dim_time"."time_year" as "c0", "dim_store"."store_province" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store" where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_store"."store_province" /<code>4、雙軸+單維度和指標：查詢日期和銷售額
<code>select {Crossjoin([時間].[年].Members, {[Measures].[銷售額]})} ON COLUMNS, {[銷售點].[省].Members} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>// 獲取年select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC // 獲取省份 select "dim_store"."store_province" from "dim_store" as "dim_store" group by "dim_store"."store_province" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASC // 獲取省份個數 select count(distinct "store_province") from "dim_store" // 獲取日期個數 select count(distinct "time_year") from "dim_time" // 獲取中間數據 select "dim_time"."time_year" as "c0", "dim_store"."store_province" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store" where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_store"."store_province" /<code>5、雙軸+雙維度和指標：查詢日期和銷售額
<code>select {Crossjoin([時間].[年].Members, {[Measures].[銷售額]})} ON COLUMNS, {[銷售點].[市].Members} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASCselect "dim_store"."store_province", "dim_store"."store_city" from "dim_store" as "dim_store" group by "dim_store"."store_province", "dim_store"."store_city" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASC, CASE WHEN "dim_store"."store_city" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_city" ASC // 獲取城市個數 select count(distinct "store_city") from "dim_store" // 獲取日期個數 select count(distinct "time_year") from "dim_time" select "dim_time"."time_year" as "c0", "dim_store"."store_city" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store" where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_store"."store_city" /<code>6、分頁雙軸+雙維度和指標：查詢日期和銷售額
分頁的效果可以對比查看上圖。
<code>select {Crossjoin([時間].[年].Members, {[Measures].[銷售額]})} ON COLUMNS, {Subset([銷售點].[市].Members, 0, 3)} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASCselect "dim_store"."store_province", "dim_store"."store_city" from "dim_store" as "dim_store" group by "dim_store"."store_province", "dim_store"."store_city" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASC, CASE WHEN "dim_store"."store_city" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_city" ASC // 獲取城市個數 select count(distinct "store_city") from "dim_store" // 獲取日期個數 select count(distinct "time_year") from "dim_time" select "dim_time"."time_year" as "c0", "dim_store"."store_city" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store" where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_store"."store_city" /<code>從上面的SQL可以看出分頁其實是一個內存的分頁。
7、單字段排序
<code>select {Crossjoin([時間].[年].Members, {[Measures].[銷售額]})} ON COLUMNS, {Order([銷售點].[省].Members, [Measures].[銷售額], DESC)} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC select "dim_store"."store_province" from "dim_store" as "dim_store" group by "dim_store"."store_province" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASC select count(distinct "time_year") from "dim_time" select count(distinct "store_province") from "dim_store" // 這條SQL在沒有Order的情況是沒有的 select sum("fact_sales"."sales") as "m0" from "fact_sales" as "fact_sales" // 這條SQL在沒有Order的情況是沒有的 select "dim_store"."store_province" as "c0", sum("fact_sales"."sales") as "m0" from "dim_store" as "dim_store", "fact_sales" as "fact_sales" where "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_store"."store_province" select "dim_time"."time_year" as "c0", "dim_store"."store_province" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store" where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_store"."store_province" /<code> 8、過濾
<code>with member [Measures].[銷售額 環比上季] as 'ParallelPeriod([時間].[季度], (- 1))'select {[Measures].[銷售額], [Measures].[銷售額 環比上季]} ON COLUMNS, {Filter([時間].[季度].Members, ([Measures].[銷售額] > 4210000))} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>// 獲取明細select "dim_time"."time_year", "dim_time"."time_quarter" from "dim_time" as "dim_time", "fact_sales" as "fact_sales" where "fact_sales"."time_id" = "dim_time"."time_id" group by "dim_time"."time_year", "dim_time"."time_quarter" having (sum("fact_sales"."sales") > 4210000) order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC, CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC select "dim_time"."time_quarter" from "dim_time" as "dim_time" where ("dim_time"."time_year" = '2012') group by "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC select "dim_time"."time_quarter" from "dim_time" as "dim_time" where ("dim_time"."time_year" = '2013') group by "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC // 獲取年 select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC select count(distinct "time_year") from "dim_time" select count(distinct "time_quarter") from "dim_time" // 獲取日期明細數據 select "dim_time"."time_year" as "c0", "dim_time"."time_quarter" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales" where "fact_sales"."time_id" = "dim_time"."time_id" group by "dim_time"."time_year", "dim_time"."time_quarter" /<code>高級計算解析
1、累計分析(YTD/MTD/QTD)
<code>with member [Measures].[銷售額 YTD] as 'Sum(Ytd([時間].[季度].CurrentMember), [Measures].[銷售額])'select {[Measures].[銷售額], [Measures].[銷售額 YTD]} ON COLUMNS, {[時間].[季度].Members} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>// 獲取年-季度select "dim_time"."time_year", "dim_time"."time_quarter" from "dim_time" as "dim_time" group by "dim_time"."time_year", "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC, CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC // 獲取年 select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC // 獲取季度 select "dim_time"."time_quarter" from "dim_time" as "dim_time" where ("dim_time"."time_year" = '2013') group by "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC select count(distinct "time_year") from "dim_time" select count(distinct "time_quarter") from "dim_time" //獲取明細數據 select "dim_time"."time_year" as "c0", "dim_time"."time_quarter" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales" where "fact_sales"."time_id" = "dim_time"."time_id" group by "dim_time"."time_year", "dim_time"."time_quarter"/<code>2、單維度對比
<code>with member [Measures].[銷售額 環比上季] as 'ParallelPeriod([時間].[季度], (- 1))'select {[Measures].[銷售額], [Measures].[銷售額 環比上季]} ON COLUMNS, {[時間].[季度].Members} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>// 獲取年-季度select "dim_time"."time_year", "dim_time"."time_quarter" from "dim_time" as "dim_time" group by "dim_time"."time_year", "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC, CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC // 獲取年select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC // 獲取季度select "dim_time"."time_quarter" from "dim_time" as "dim_time" where ("dim_time"."time_year" = '2013') group by "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC select count(distinct "time_year") from "dim_time" select count(distinct "time_quarter") from "dim_time" //獲取明細數據 select "dim_time"."time_year" as "c0", "dim_time"."time_quarter" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales" where "fact_sales"."time_id" = "dim_time"."time_id" group by "dim_time"."time_year", "dim_time"."time_quarter"/<code>3、維度交叉對比
<code>with member [Measures].[銷售額 環比上季] as 'ParallelPeriod([時間].[季度], (- 1))'select {Crossjoin([銷售點].[省].Members, {[Measures].[銷售額], [Measures].[銷售額 環比上季]})} ON COLUMNS, {[時間].[季度].Members} ON ROWSfrom [銷售情況]/<code> 翻譯成的SQL:
<code>// 獲取省份select "dim_store"."store_province" from "dim_store" as "dim_store" group by "dim_store"."store_province" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASC // 獲取季度select "dim_time"."time_year", "dim_time"."time_quarter" from "dim_time" as "dim_time" group by "dim_time"."time_year", "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC, CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC // 獲取年select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC //select "dim_time"."time_quarter" from "dim_time" as "dim_time" where ("dim_time"."time_year" = '2013') group by "dim_time"."time_quarter" order by CASE WHEN "dim_time"."time_quarter" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_quarter" ASC select count(distinct "time_year") from "dim_time" select count(distinct "time_quarter") from "dim_time" select count(distinct "store_province") from "dim_store" //獲取明細數據select "dim_time"."time_year" as "c0", "dim_time"."time_quarter" as "c1", "dim_store"."store_province" as "c2", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store" where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_time"."time_quarter", "dim_store"."store_province"/<code>基於內存的對比
3、總計/小計
<code>select {Crossjoin([時間].[年].Members, {[Measures].[銷售額]})} ON COLUMNS, {Hierarchize({[銷售點].[市].Members, [銷售點].[省].Members})} ON ROWSfrom [銷售情況]/<code>翻譯成的SQL:
<code>// 獲取年份select "dim_time"."time_year" from "dim_time" as "dim_time" group by "dim_time"."time_year" order by CASE WHEN "dim_time"."time_year" IS NULL THEN 1 ELSE 0 END, "dim_time"."time_year" ASC // 獲取省份和城市select "dim_store"."store_province", "dim_store"."store_city" from "dim_store" as "dim_store" group by "dim_store"."store_province", "dim_store"."store_city" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASC, CASE WHEN "dim_store"."store_city" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_city" ASC // 獲取省份select "dim_store"."store_province" from "dim_store" as "dim_store" group by "dim_store"."store_province" order by CASE WHEN "dim_store"."store_province" IS NULL THEN 1 ELSE 0 END, "dim_store"."store_province" ASCselect count(distinct "time_year") from "dim_time"select count(distinct "store_city") from "dim_store" //獲取城市明細數據select "dim_time"."time_year" as "c0", "dim_store"."store_city" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store"where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_store"."store_city"// 省份明細數據select "dim_time"."time_year" as "c0", "dim_store"."store_province" as "c1", sum("fact_sales"."sales") as "m0" from "dim_time" as "dim_time", "fact_sales" as "fact_sales", "dim_store" as "dim_store" where "fact_sales"."time_id" = "dim_time"."time_id" and "fact_sales"."store_id" = "dim_store"."store_id" group by "dim_time"."time_year", "dim_store"."store_province"/<code> 
分享到:
閱讀更多 IT那些年那些事 的文章
