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.
- An ajax call to pass the information need to check the process
- A controller function where we create a adomd connection object and pass the information to the model class
- 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.
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") {
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.
- http://www.w3schools.com/ajax/
- 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.
<summary>
<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.
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.
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.
if (data.indexOf("Error") > -1) {
$('#btnCreate').css('enabled', 'false');
$('#Preview').html('
<p style="color:red;">Error : ' + data.replace('Error', '') + '</p>
');
} else if (data == "Mapped") {
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.
History
First Version: 04-May-2015
