Learn SQL Server bi--application development step by step

Then we last the "step-by-step Learning sqlserverbi--Multidimensional Database Building", now we have a multidimensional database, and there are already data, then quickly enter the topic of our programmers.

Today I want to develop two applications on this multidimensional database:

1. To count the transactions of each department on a per-day basis

2. Count the volume of each department and game on a per-day basis

First design a strongly typed DataSet, as shown in the following figure.

Statistics by department data set

Cross-statistical datasets by department and game

Design MDX statements, execute MDX at the data tier, and return to Cellset

/**////<summary>

///Statistics The transaction data for each department by day
///</summary>
///<param name= "Tradedatekey" > Date key value </param>
<returns></returns>
Public CellSet Count (int tradedatekey)
{
StringBuilder mdxbuilder = new S Tringbuilder ();
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) ");
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>

///Statistics The transaction data for each game's individual department by day
///</summary>
///<param name= "Tradedatekey" > Date key Values </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 $ 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 ());
}