一步一步学习sqlserver BI--应用开发(1)

接着我们上次那篇《一步一步学习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());
        }       

在业务逻辑层把CellSet组装成我们前台需要的数据集格式

 /**//// <summary>
        /// 填充按部门统计的数据
        /// </summary>
        /// <param name="model"></param>
        /// <param name="timeKey"></param> 
        public void Fill(DepartmentTotalModel model, DateTime time, int timeKey)
        {
            CellSet cellSet = new DepartmentTotalDac().Count(timeKey);

            DepartmentTotalModel.FactGameTradeDataRow newRow;

            for (int i = 1; i < cellSet.Axes[1].Positions.Count; i++)
            {
                newRow = model.FactGameTradeData.NewFactGameTradeDataRow();
                newRow.Department = cellSet.Axes[1].Positions[i].Members[0].Caption;
                newRow.TradeDate = time;

                for (int j = 0; j < cellSet.Axes[0].Positions.Count; j++)
                {
                    if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Orders Count]")
                    {
                        newRow.TotalOrders = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Amount Count]")
                    {
                        newRow.TotalAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Money Count]")
                    {
                        newRow.TotalMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Amount Count]")
                    {
                        newRow.UnPaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Money Count]")
                    {
                        newRow.UnPaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Cancel Amount Count]")
                    {
                        newRow.PaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Cancel Money Count]")
                    {
                        newRow.PaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);
                    }
                }

                model.FactGameTradeData.AddFactGameTradeDataRow(newRow);
            }

        }

 

 /**//// <summary>
        /// 填充按游戏和部门交叉统计的数据
        /// </summary>
        /// <param name="model"></param>
        /// <param name="timeKey"></param>
        /// <param name="depKey"></param>
        public void Fill(GameByDepartmentTotalModel model,DateTime time,int timeKey,string dep,int depKey)
        {
            CellSet cellSet = new GameByDepartmentTotalDac().Count(timeKey, depKey);

            GameByDepartmentTotalModel.FactGameTradeDataRow newRow;

            for (int i = 1; i < cellSet.Axes[1].Positions.Count; i++)
            {
                newRow = model.FactGameTradeData.NewFactGameTradeDataRow();
                newRow.Game = cellSet.Axes[1].Positions[i].Members[0].Caption; 
                newRow.Department = dep;
                newRow.TradeDate = time;               

                for (int j = 0; j < cellSet.Axes[0].Positions.Count; j++)
                {
                    if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Orders Count]")
                    {
                        newRow.TotalOrders = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Amount Count]")
                    {
                        newRow.TotalAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Money Count]")
                    {
                        newRow.TotalMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Amount Count]")
                    {
                        newRow.UnPaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Money Count]")
                    {
                        newRow.UnPaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Cancel Amount Count]")
                    {
                        newRow.PaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);
                    }
                    else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Cancel Money Count]")
                    {
                        newRow.PaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);
                    }
                }

                model.FactGameTradeData.AddFactGameTradeDataRow(newRow);
            }

        }

好了，目前为止，我们已经从多维数据库里面返回我们需要统计的数据了，爱怎么展现就怎么展现吧。数据列表，柱状图，曲线图，饼状图等等。在以后的章节中我会介绍一款开源的非常成熟的图形控件ZedGraph,我做展现的时候，有部分就是用的这个家伙，修改了它的一些代码，感觉非常实用。