SQL Server Analysis services offers a powerful query language on top of your datawarehouse called MDX. But how do you use these MDX queries inside your .NET application?
You cannot use your normal ADO.NET classes, instead you have to use the Microsoft.AnalysisServices.AdomdClient.dll.
You can download the file from here: http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
The code itself is somewhat similar:
using (AdomdConnection conn = new AdomdConnection("Data Source=tfsDB;Initial Catalog=Tfs_Analysis; MDX Compatibility=1;"))
{
conn.Open();
var mdxQuery = new StringBuilder();
mdxQuery.Append("WITH ");
mdxQuery.Append("SET [Last 4 weeks] as Filter([Date].[Date].[Date], [Date].[Date].CurrentMember.Member_Value < Now() AND [Date].[Date].CurrentMember.Member_Value >= DateAdd(\"d\", - 28, Now())) ");
mdxQuery.Append("SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Work Item].[System_WorkItemType].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY {Hierarchize(Distinct({[Last 4 weeks]}))} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS ");
mdxQuery.Append("FROM (SELECT ({[Work Item].[System_WorkItemType].&[Requirement], [Work Item].[System_WorkItemType].&[Change Request]}) ");
mdxQuery.Append("ON COLUMNS FROM [Team System]) WHERE ([Work Item].[Iteration Hierarchy].[All],[Test Case].[System_WorkItemType].[All],[Work Item].[System_State].&[Active],[Measures].[Work Item Count]) ");
using (AdomdCommand cmd = new AdomdCommand(mdxQuery.ToString(), conn))
{
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
ds.Tables.Add();
DataTable dt = ds.Tables[0];
dt.Load(cmd.ExecuteReader());
return dt;
}
}