一步一步学习sqlserver BI--应用开发

一步一步学习sqlserver BI--应用开发2010-01-01 李梦蛟"s blog 接着我们上次那篇《一步一步学习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（））;
}