In previous article, I introduced the solution to execute a MDX query by SQL store procedure. However, it has some limitation such linked server performance, user permission…

There are other approaches to overcome those disadvantages of the store procedure. In this article, I will introduce ADOM.NET library which also support executes a MDX file. It means that we save MDX query MDX  format and then execute it by using ADOM.NET

Create MDX file

In my example, I would like to build a Year-To-Date report of current year and Year-To-Date of last year for all product sub categories in Adventure Works. This step is to create MDX file that contains MDX query.

WITH MEMBER Measures.[CYTD Current]ASSUM(YTD([Date].[Calendar].CURRENTMEMBER)   ,[Measures].[Internet Sales Amount]),FORMAT_STRING =”Currency”MEMBER Measures.[CYTD Last Year]ASSUM(   YTD(      ParallelPeriod([Date].[Calendar].[Calendar Year]                 ,1 ,[Date].[Calendar].CURRENTMEMBER )   )   ,[Measures].[Internet Sales Amount]),FORMAT_STRING =”Currency”SELECT NON EMPTY({                  Measures.[CYTD Current]  ,Measures.[CYTD Last Year]  }                  ) ON COLUMNS,NON EMPTY([Product].[SubCategory].CHILDREN) ON ROWSFROM [Adventure Works]WHERE [Date].[Calendar].[Month].[September 2014]

Then  I saved this script as CYTDTotalAmountProductSubCategory.mdx

Building a window form application

In this step, I developed a window form that is used to execute CYTDTotalAmountProductSubCategory.mdx file and binding data to a Data Grid View.

Environment

SQL Server 2014 Developer Edition

Visual Studio 2015 Community version

Prerequisite Condition

You should download Adventure Works Multidimensional Database here then restore it on your environment.

Installing ADOMD libraries for working with OLAP cube (multidimensional database). Click here to read more detail

Create a window form application

This step is to create a window form to load data from the MDX file created above

  • Project name: Analytics.UserForms
  • Form Name: CYTD Product Sub Category
    • Load Data button
    • Data Grid View
    • Load Data Click event

Next step, we need to implement Click event for Load Data button. This event is following steps

  • Read content of MDX file
  • Establish a connection to Analysis Service Instance on server using AdomdConnection
  • Execute MDX query from the file using AdomdCommand
  • Fill returned data into a DataTable
  • Bind data to Data Grid View

private void btLoadData_Click(object sender, EventArgs e)
{
DataTable dt = GetCYTDTotalAmountProductSubCategory();
dgv_CYTDProductSubCategory.DataSource = dt;

}
private DataTable GetCYTDTotalAmountProductSubCategory()
{
string ConnectionOLAPCube = “Provider=MSOLAP;Data Source=DUNGDT\\SQLSERVER2014; Initial Catalog=AdventureWorksDW2014Multidimensional;”;
ConnectionOLAPCube.Replace(“\\\\”, “\\”);
var dt = new DataTable();
string query = System.IO.File.ReadAllText(“D:\\Projects\\Analytics\\Analytics.DAL\\MDX\\CYTDTotalAmountProductSubCategory.mdx”);
AdomdConnection conn = new AdomdConnection(ConnectionOLAPCube);
conn.Open();
var cmd = new AdomdCommand(query, conn);

var objDataAdapter = new AdomdDataAdapter(cmd);
objDataAdapter.Fill(dt);
conn.Close();
return dt;

}

Now, we run the application to see the result

Conclusion

ADOMD libraries provide us a lot of classes and components that we can use to work with OLAP cube such as querying data, schema,… This article is just very simple to start working with ADOMD libraries.

Advertisements

2 thoughts on “How to execute MDX query with ADOMD libraries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s