|
接着我们上次那篇《一步一步学习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());
}
（编辑：南平站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
