Click here to Skip to main content
15,396,032 members
Articles / Web Development / ASP.NET
Article
Posted 4 May 2015

Stats

8.9K views
6 bookmarked

How to check whether a measure group is mapped with the dimension group

Rate me:
Please Sign up or sign in to vote.
4.79/5 (8 votes)
4 May 2015CPOL3 min read
This article shows how to check whether a Measure Group is mapped with the Dimension Group.

Introduction

For the past few months I am working on a dashboard application where ADOMD is the data source. Our application contains lots of charts, grids, maps and some other UI controls. We are using MDX queries for generating the widgets used in our application. As you all know the data must be correct and accurate in every dashboard application then only we will get correct visual in the chart, grid, maps etc. So it is must to check for the proper data and we must avoid the unwanted calls to the server for fetching the data. 

Background

Few days back I was working in Bubble Map in High Map. According to a map, we need to specify the latitude and longitude without any faults in data. Here we are going to use a mapping function to check the dimension group and measure group is mapped or not. This will ensure the data is proper, and without this check, our MDX query may keep on running. This will definitely make the end users to close our application and search for another one. So my idea is to give a message if the measure group and dimension groups are not mapped, to avoid running the queries for a long time.

What we are going to do?

We are going to do the following steps.

  1. An ajax call to pass the information need to check the process
  2. A controller function where we create a adomd connection object and pass the information to the model class
  3. A model class where we build and run the query with the needed parameters

The last step will give you an information which says whether the given measure group is mapped with the dimension group.

Using the code

So let us start the coding now.

As we discussed earlier, first of all we need an ajax call right?

1.An Ajax call

The following is our ajax call implementation.

C#
var cubeName = '';
var measureGroupName = '';
var dimensionGroupName = '';
var serverName = 'My server name';
var databaseName = 'My database name';
cubeName = 'My cube name';
measureGroupName = sessionStorage.getItem("measureGroupName");
dimensionGroupName = sessionStorage.getItem("dimensionGroupName");
var checkMeasuresMapped = {
cubeName: cubeName,
measureGroupName: measureGroupName,
dimensionGroupName: dimensionGroupName,
serverName: serverName,
databaseName: databaseName
};
$.ajax({
async: ajaxAsync,
url: '../CheckMeasureGroupMapping/',
type: 'POST',
dataType: 'json',
data: JSON.stringify(checkMeasuresMapped),
contentType: "application/json; charset=utf-8",
success: function(data) {
if (data.indexOf("Error") > -1) {
$('#btnCreate').css('enabled', 'false');
$('#Preview').html('
<p style="color:red;">Error : ' + data.replace('Error', '') + '</p>
');           
} else if (data == "Mapped") {
//Condition satisfied, write your codes here
ajaxAsync = true;
} else {          
$('#Preview').html('
<p style="color:red;">Warning : The given measure is not mapped with the dimension. Please check.</p>
');
}
},
error: function(xhrequest, ErrorText, thrownError) {
console.log(ErrorText + "," + thrownError);
$('#Preview').html('
<p style="color:red;">Error : ' + ErrorText + '</p>
');
}
});

Please be noted that I have passed all the needed information’s. We will list down what are all they.

I.Cube Name

II.Measure Group Name

III.Dimension Group Name

IV.Server Name

V.Database Name

Once you are done with the ajax implementation, you will get the information in our controller right? That is how the ajax call worksJ. If you are new to the ajax calls, please refer the following links.

  1. http://www.w3schools.com/ajax/
  2. http://www.w3schools.com/jquery/ajax_ajax.asp

 

2.Controller function

I hope you all are done with the ajax implementation, now we will move on to the next part. Please refer the below code.

C#
///
<summary>
   ///This method is used to find whether the given measure is mapped to the dimension
   ///
   <param name="cubeName">
   </param>
   ///
   <param name="measureGroupName">
   </param>
   ///
   <param name="dimensionGroupName">
   </param>
   ///
</summary>
[HandleError]
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult CheckMeasureGroupMapping(string cubeName, string measureGroupName, string dimensionGroupName, string serverName, string databaseName)
{
try
{
DataTable dt = new DataTable();
dt = adomdConn.CheckMeasureGroupMapping(cubeName, measureGroupName, dimensionGroupName, serverName, databaseName);
if (dt.Rows.Count < 1)
{
return Json("The given measure is not mapped with the dimension. Please check.", JsonRequestBehavior.AllowGet);
}
else
{
return Json("Mapped", JsonRequestBehavior.AllowGet);
}
}
catch (AdomdErrorResponseException ex)
{
string errrorText = "Query Error" + System.Web.HttpUtility.HtmlEncode(ex.Message);
return Json(errrorText, JsonRequestBehavior.AllowGet);
}
}

Here CheckMeasureGroupMapping is our ActionResult and we are passing the information to our model class, if our conditions are satisfied, the above function will return the rows in a datatable. With the row count of that data table we can come to our conclusionJ

3.Model function

Now we are in the last part, as we get the needed parameters in our model class, it is time to build the query and run it to the ADOMD server. So below is the function to do the same.

C#
public DataTable CheckMeasureGroupMapping(string cubeName, string measureGroupName, string dimensionGroupName, string serverName, string databaseName)
{
try
{
string query = string.Empty;
string queryBefore = string.Empty;
queryBefore = "SELECT  [MEASUREGROUP_NAME] AS [MEASUREGROUP],[MEASUREGROUP_CARDINALITY],[DIMENSION_UNIQUE_NAME] AS [DIM],[DIMENSION_GRANULARITY] AS [DIM_KEY],[DIMENSION_CARDINALITY],[DIMENSION_IS_VISIBLE] AS [IS_VISIBLE],[DIMENSION_IS_FACT_DIMENSION] AS [IS_FACT_DIM] FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS WHERE [CUBE_NAME] = {cubeName} AND [MEASUREGROUP_NAME] ={measureGroupName} AND [DIMENSION_UNIQUE_NAME]={dimensionGroupName}";
query = queryBefore.Replace("{cubeName}", "'" + cubeName + "'").Replace("{measureGroupName}", "'" + measureGroupName + "'").Replace("{dimensionGroupName}", "'" + dimensionGroupName + "'");
StringBuilder sbConnectionString = new StringBuilder();
sbConnectionString.Append("Provider=MSOLAP;data source=");
sbConnectionString.Append(serverName + ";initial catalog=" + databaseName + ";Integrated Security=SSPI;Persist Security Info=False;");
using (AdomdConnection conn = new AdomdConnection(sbConnectionString.ToString()))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(query, conn))
{
DataTable dt = new DataTable();
AdomdDataAdapter da = new AdomdDataAdapter(cmd);
da.Fill(dt);
return dt;
}
}
}
catch (AdomdErrorResponseException ex)
{
throw;
}
}

As you all could see below is the query for finding whether the given measure group is mapped with the dimension group or not.

C#
SELECT  [MEASUREGROUP_NAME] AS [MEASUREGROUP],[MEASUREGROUP_CARDINALITY],[DIMENSION_UNIQUE_NAME] AS [DIM],[DIMENSION_GRANULARITY] AS [DIM_KEY],[DIMENSION_CARDINALITY],[DIMENSION_IS_VISIBLE] AS [IS_VISIBLE],[DIMENSION_IS_FACT_DIMENSION] AS [IS_FACT_DIM] FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS WHERE [CUBE_NAME] = {cubeName} AND [MEASUREGROUP_NAME] ={measureGroupName} AND [DIMENSION_UNIQUE_NAME]={dimensionGroupName}";

We are running this query with the parameters, and we will return the result to our controller.

At the end

At the end we will check the condition as we shown in the ajax call.

C#
if (data.indexOf("Error") > -1) {
$('#btnCreate').css('enabled', 'false');
$('#Preview').html('
<p style="color:red;">Error : ' + data.replace('Error', '') + '</p>
');           
} else if (data == "Mapped") {
//Condition satisfied, write your codes here
ajaxAsync = true;
} else {          
$('#Preview').html('
<p style="color:red;">Warning : The given measure is not mapped with the dimension. Please check.</p>
');
}

So we have done with this requirement.

Happy Coding

Points of Interest

ADOMD, ADOMD Measure, ADOMD Dimension, ADOMD Measure Groups, ADOMD Dimension Group, Mapping measure group and dimension group.

Conclusion

I hope you liked my article. Please share your valuable feedbacks. It means a lotJ

Additional reference

Output

If the given measure group is not mapped with the measure group, you will get the following as output in your preview area.

Image 1

History

First Version: 04-May-2015

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sibeesh Passion
Software Developer
Germany Germany
I am Sibeesh Venu, an engineer by profession and writer by passion. I’m neither an expert nor a guru. I have been awarded Microsoft MVP 3 times, C# Corner MVP 5 times, DZone MVB. I always love to learn new technologies, and I strongly believe that the one who stops learning is old.

My Blog: Sibeesh Passion
My Website: Sibeesh Venu

Comments and Discussions

 
GeneralMy vote of 5 Pin
Mahsa Hassankashi5-May-15 10:40
MemberMahsa Hassankashi5-May-15 10:40 
GeneralRe: My vote of 5 Pin
Sibeesh Passion5-May-15 21:10
professionalSibeesh Passion5-May-15 21:10 
GeneralRare topic to find. Pin
Member 101106475-May-15 1:36
MemberMember 101106475-May-15 1:36 
GeneralRe: Rare topic to find. Pin
Sibeesh Passion5-May-15 21:09
professionalSibeesh Passion5-May-15 21:09 
GeneralMy vote of 5 Pin
Afzaal Ahmad Zeeshan4-May-15 22:23
mveAfzaal Ahmad Zeeshan4-May-15 22:23 
GeneralRe: My vote of 5 Pin
Sibeesh Passion4-May-15 22:24
professionalSibeesh Passion4-May-15 22:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.