一步一步學習sqlserver BI--應用開發

接著我們上次那篇《一步一步學習sqlserverBi--多維資料庫建立》，現在我們多維資料庫已經有了 ，並且裡面也已經有了資料，那麼趕快進入咱們程式員的主題吧。

今天我要在這個多維資料庫上 面開發兩個應用：

1。按天統計各個部門的交易量

2。按天統計各個部門和各個遊戲的交 易量

首先設計強型別的資料集,如下圖。

按部門統計資料集

按部門和遊戲交叉統 計資料集

設計MDX語句,在資料層執行MDX，並返回CellSet

/**//// <summary>

/// 按天統計各個部門的交易資料
/// </summary>
/// <param name="tradeDateKey">日期的索引值</param>
/// <returns></returns>
public CellSet Count(int tradeDateKey)
{
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])'");
mdxBuilder.Append (" SELECT　{ [Measures].[Total Orders Count], [Measures].[Total Amount Count], [Measures].[Total Money Count], [Measures].[Un Paid Cancel Amount Count], [Measures].[Un Paid Cancel Money Count], [Measures].[Paid Cancel Amount Count], [Measures].[Paid Cancel Money Count]} ON COLUMNS,");
mdxBuilder.Append(" {[Department].[Dep Code Alternate Key].Members} ON ROWS");
mdxBuilder.Append(" FROM [Data Center DW]");
mdxBuilder.Append(" WHERE ([Time].[TimeKey]. ["+tradeDateKey+"])");

return DBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString());
}

/**//// <summary>

/// 按天統計各個遊戲單個部門的交易資料
/// </summary>
/// <param name="tradeDateKey">日期的索引值 </param>
/// <returns></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])'");
mdxBuilder.Append(" SELECT　{ [Measures].[Total Orders Count], [Measures].[Total Amount Count], [Measures]. [Total Money Count], [Measures].[Un Paid Cancel Amount Count], [Measures].[Un Paid Cancel Money Count], [Measures].[Paid Cancel Amount Count], [Measures].[Paid Cancel Money Count]} ON COLUMNS,");
mdxBuilder.Append(" {[Game].[Game Code Alternate Key].Members} ON ROWS");
mdxBuilder.Append(" FROM [Data Center DW] ");
mdxBuilder.Append(" WHERE ([Time].[TimeKey].[" + tradeDateKey + "],[Department].[Dim Department].["+departmentKey.ToString()+"])");

return DBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString());
}