SQL Server BI 實戰：多維數據庫應用開發與 MDX 查詢實作

前言

延續上一篇《SQL Server BI 步步為營：多維數據庫建立》，我們已經成功建立多維數據庫並完成數據填充。現在，讓我們進入程式開發的核心環節，在這個多維數據庫之上開發兩個實用的分析應用：

1. 按天統計各部門的交易量
2. 按天統計各部門與各遊戲的交叉交易量

強類型數據集設計

首先設計兩個強類型數據集，分別對應上述兩種統計需求：

按部門統計數據集

按部門與遊戲交叉統計數據集

MDX 查詢實作：資料層核心邏輯

在資料存取層，我們撰寫 MDX 語句並在 Analysis Services 上執行，回傳 CellSet 物件供上層報表或應用程式使用。以下提供兩個核心查詢方法的完整程式碼：

方法一：按天統計各部門交易資料

/// <summary>
/// 按天統計各個部門的交易數據
/// </summary>
/// <param name="tradeDateKey">日期的鍵值</param>
/// <returns>CellSet 結果集</returns>
public CellSet Count(int tradeDateKey)
{
    StringBuilder mdxBuilder = new StringBuilder();
    
    // 定義計算成員：各項交易指標的 SUM 聚合
    mdxBuilder.Append("WITH MEMBER [Measures].[Total Orders Count] AS 'SUM([Measures].[Total Orders])' ");
    mdxBuilder.Append("MEMBER [Measures].[Total Amount Count] AS 'SUM([Measures].[Total Amount])' ");
    mdxBuilder.Append("MEMBER [Measures].[Total Money Count] AS 'SUM([Measures].[Total Money])' ");
    mdxBuilder.Append("MEMBER [Measures].[Un Paid Cancel Amount Count] AS 'SUM([Measures].[Un Paid Cancel Amount])' ");
    mdxBuilder.Append("MEMBER [Measures].[Un Paid Cancel Money Count] AS 'SUM([Measures].[Un Paid Cancel Money])' ");
    mdxBuilder.Append("MEMBER [Measures].[Paid Cancel Amount Count] AS 'SUM([Measures].[Paid Cancel Amount])' ");
    mdxBuilder.Append("MEMBER [Measures].[Paid Cancel Money Count] AS 'SUM([Measures].[Paid Cancel Money])' ");
    
    // SELECT 子句：指定欄位與列維度
    mdxBuilder.Append("SELECT { ");
    mdxBuilder.Append("[Measures].[Total Orders Count], ");
    mdxBuilder.Append("[Measures].[Total Amount Count], ");
    mdxBuilder.Append("[Measures].[Total Money Count], ");
    mdxBuilder.Append("[Measures].[Un Paid Cancel Amount Count], ");
    mdxBuilder.Append("[Measures].[Un Paid Cancel Money Count], ");
    mdxBuilder.Append("[Measures].[Paid Cancel Amount Count], ");
    mdxBuilder.Append("[Measures].[Paid Cancel Money Count] ");
    mdxBuilder.Append("} ON COLUMNS, ");
    
    // 列維度：所有部門成員
    mdxBuilder.Append("{ [Department].[Dep Code Alternate Key].Members } ON ROWS ");
    
    // 來源 Cube 與切片條件
    mdxBuilder.Append("FROM [Data Center DW] ");
    mdxBuilder.Append("WHERE ([Time].[TimeKey].[" + tradeDateKey + "])");
    
    return DBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString());
}

方法二：按天統計各部門各遊戲交易資料

/// <summary>
/// 按天統計各個遊戲單個部門的交易數據
/// </summary>
/// <param name="tradeDateKey">日期的鍵值</param>
/// <param name="departmentKey">部門鍵值</param>
/// <returns>CellSet 結果集</returns>
public CellSet Count(int tradeDateKey, int departmentKey)
{
    StringBuilder mdxBuilder = new StringBuilder();
    
    // 定義計算成員（同方法一）
    mdxBuilder.Append("WITH MEMBER [Measures].[Total Orders Count] AS 'SUM([Measures].[Total Orders])' ");
    mdxBuilder.Append("MEMBER [Measures].[Total Amount Count] AS 'SUM([Measures].[Total Amount])' ");
    mdxBuilder.Append("MEMBER [Measures].[Total Money Count] AS 'SUM([Measures].[Total Money])' ");
    mdxBuilder.Append("MEMBER [Measures].[Un Paid Cancel Amount Count] AS 'SUM([Measures].[Un Paid Cancel Amount])' ");
    mdxBuilder.Append("MEMBER [Measures].[Un Paid Cancel Money Count] AS 'SUM([Measures].[Un Paid Cancel Money])' ");
    mdxBuilder.Append("MEMBER [Measures].[Paid Cancel Amount Count] AS 'SUM([Measures].[Paid Cancel Amount])' ");
    mdxBuilder.Append("MEMBER [Measures].[Paid Cancel Money Count] AS 'SUM([Measures].[Paid Cancel Money])' ");
    
    // SELECT 子句
    mdxBuilder.Append("SELECT { ");
    mdxBuilder.Append("[Measures].[Total Orders Count], ");
    mdxBuilder.Append("[Measures].[Total Amount Count], ");
    mdxBuilder.Append("[Measures].[Total Money Count], ");
    mdxBuilder.Append("[Measures].[Un Paid Cancel Amount Count], ");
    mdxBuilder.Append("[Measures].[Un Paid Cancel Money Count], ");
    mdxBuilder.Append("[Measures].[Paid Cancel Amount Count], ");
    mdxBuilder.Append("[Measures].[Paid Cancel Money Count] ");
    mdxBuilder.Append("} ON COLUMNS, ");
    
    // 列維度：所有遊戲成員
    mdxBuilder.Append("{ [Game].[Game Code Alternate Key].Members } ON ROWS ");
    
    // 來源 Cube 與雙重切片條件：日期 + 部門
    mdxBuilder.Append("FROM [Data Center DW] ");
    mdxBuilder.Append("WHERE ([Time].[TimeKey].[" + tradeDateKey + "],");
    mdxBuilder.Append("[Department].[Dim Department].[" + departmentKey.ToString() + "])");
    
    return DBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString());
}

關鍵技術點解析

• WITH MEMBER 子句：在查詢範圍內動態定義計算成員，避免在 Cube 中永久建立大量計算度量，提升維護彈性。
• SUM 聚合函數：對基礎度量進行加總，確保多維查詢結果的正確性。
• WHERE 切片：使用 TimeKey 與 Department 維度鍵值進行精確切片，實現「按天、按部門」的多維切片分析。
• CellSet 回傳：Analysis Services 執行 MDX 後回傳 CellSet，適合進一步轉換為 DataTable 或直接繫結至報表控制項。

實務建議

1. 生產環境建議將 MDX 字串參數化，避免字串拼接導致的注入風險與效能損耗。
2. 對於高頻查詢，可考慮在 Cube 中預先定義計算成員，或使用快取機制減少重複計算。
3. 若需支援多日期範圍查詢，可將 WHERE 子句改為使用 STRTOSET 或 FILTER 函數處理日期區間。