Check Whether a Measure Group is Mapped With Dimension Group

Introduction

For the past few months I have been working on a dashboard application where ADOMD is the data source. Our application contains many 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. Only then can we get the correct visuals in the charts, grids, maps and so on. So it is necessary to check for the proper data and we must avoid the unwanted calls to the server for fetching the data.

Background

A few days ago I was working on a Bubble Map in High Map. For the map we had to specify the latitude and longitude without any faults in the data. Here we will 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 continue to run. This will definitely make the end users close our application and search for another one. So my idea is to provide a message if the measure group and dimension groups are not mapped, to avoid running the queries for a long time.

What we will do

We will use the following procedure.

1. An ajax call to pass the information 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 necessary parameters.

The last step will provide you an information that 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?

• 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")
        {
            //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 understand that I have passed all the necessary information. We will list what they all are.

1. Cube Name.
2. Measure Group Name.
3. Dimension Group Name.
4. Server Name.
5. 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 works. If you are new to the ajax calls, please refer to the following links.

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

• Controller Function

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

///<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 then the preceding function will return the rows in a datatable. With the row count of that data table we can arrive at our conclusion.

• Model Function

Now we are in the final part, since we have the necessary parameters in our model class, it is time to build the query and run it in the ADOMD server. So the following is the function to do that.

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 can see, the following is the query for determining 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 have 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")
{
    //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 are done with this requirement.

Happy coding!

Conclusion

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

Additional references

• How to Convert Microsoft ADOMD Data Source to JSON
• Convert CellSet to HTML Table and From HTML to JSON and to Array
• Convert Microsoft ADOMD Cell Set to JSON

Output

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



Points of interests

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