Share via

Find Datatype Of Each Measures In SSAS MDX Queries

  • Article

In this post we will see how we can get the datatype of each measures we use in our SSAS MDX Queries. This post may be helpful if you are working with SSAS cubes especially you need to work with the data you gets from the cubes like formatting the data, assigning the data as grid data source or formulate the data to any other form. I have got a requirement to show the cube data as a grid, So I was needed to know the types of each measures user selects so that I can assign the grid column types accordingly. There are few many ways we can find the types of measures, here I am going to discuss that with you. I hope you will like this.

Background

I had gone through this requirement and I could do this in time with the help of Mr.GregGalloway (Stack overflow user). You can find my question here in stack overflow.

If your cube has data with the types of string and numbers alone, finding the types will be too easy. Now before going through the processes listed in this article, if you don’t have only string and numbers as the types in the cube, this post will definitely help you.

If you are looking for some sample stored procedures that will help you with your development with your analysis services, you can always see it here: ASSP – Analysis Services Stored Procedure Project.

Using the code

Here we are going to create a function which accepts server name, database name, and the measure name collection in which we need to find out what type it is. The core part of this function will be a DMV query which we can run against our SSAS cube. The query will be as follows.

select [CATALOG_NAME],[CUBE_NAME],MEASURE_NAME, DATA_TYPE,EXPRESSION,MEASURE_IS_VISIBLE,MEASUREGROUP_NAME,MEASURE_DISPLAY_FOLDER,DEFAULT_FORMAT_STRING  from $system.mdschema_measures

Now before going further, please make sure that you have included AnalysisServices Adomd Client.

using Microsoft.AnalysisServices.AdomdClient;

Now we will create the function which will give you the data about the datatypes of your measures.

#region Return the data types of measures
        /// <summary>
        /// FindMeasureDataTypes-Find the measure type whetehr it is a currency or a percentage or a number
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="databaseName"></param>
        /// <param name="myMeasureCollection"></param>
        public DataTable FindMeasureDataTypes(string serverName, string databaseName, string myMeasureCollection)
        {
              
            try
            {
                string res = string.Empty;
                List<string> myMeasures = new List<string>();
  
                //Buiding the connection string start
                StringBuilder sbConnectionString = new StringBuilder();
                sbConnectionString.Append("Provider=MSOLAP;data source=");
                sbConnectionString.Append(serverName + ";initial catalog=" + databaseName + ";Integrated Security=SSPI;Persist Security Info=False;");
                //Buiding the connection string start
  
                AdomdConnection conn = new AdomdConnection(sbConnectionString.ToString());
                myMeasures = myMeasureCollection.Split(new string[] { "||" }, StringSplitOptions.None).ToList();
                  
                for (int i = 0; i < myMeasures.Count; i++)
                {
                    //Format the measure name
                    if (i == 0)
                        res += "MEASURE_NAME ='" + myMeasures[i].Replace("[Measures].", "").Replace("[", "").Replace("]", "") + "'"; 
                    else
                        res += " OR MEASURE_NAME ='" + myMeasures[i].Replace("[Measures].", "").Replace("[", "").Replace("]", "") + "'";
                }
  
                string query = "select [CATALOG_NAME],[CUBE_NAME],MEASURE_NAME, DATA_TYPE,EXPRESSION,MEASURE_IS_VISIBLE,MEASUREGROUP_NAME,MEASURE_DISPLAY_FOLDER,DEFAULT_FORMAT_STRING  from $system.mdschema_measures where " + res;
                using (AdomdCommand cmd = new AdomdCommand(query, conn))
                {
                    DataTable tblMeasureType = new DataTable();
                    AdomdDataAdapter da = new AdomdDataAdapter(cmd);
                    da.Fill(tblMeasureType);
                    return tblMeasureType;
                }
  
            }
            catch (Exception)
            {
                return null;
            }
        }
  
        #endregion

So in the table tblMeasureType, we will get the data. In the column *DEFAULT_FORMAT_STRING *, you can see the actual type of your measure. It will show as ‘Currency’ if it is a currency type and ‘Percentage’ if it is a percentage type and ‘#,##’ for the numbers. The *DEFAULT_FORMAT_STRING *actually describes each measures which are all available in the cube. And *DEFAULT_FORMAT_STRING *rowset contains the following columns.

  • CATALOG_NAME

  • SCHEMA_NAME

  • CUBE_NAME

  • MEASURE_NAME

  • MEASURE_UNIQUE_NAME

  • MEASURE_CAPTION

  • MEASURE_GUID

  • MEASURE_AGGREGATOR

  • DATA_TYPE

  • NUMERIC_PRECISION

  • NUMERIC_SCALE

  • MEASURE_UNITS

  • DESCRIPTION

  • EXPRESSION

  • MEASURE_IS_VISIBLE

  • LEVELS_LIST

  • MEASURE_NAME_SQL_COLUMN_NAME

  • MEASURE_UNQUALIFIED_CAPTION

  • MEASUREGROUP_NAME

  • MEASURE_DISPLAY_FOLDER

  • DEFAULT_FORMAT_STRING

    You can always check here to see for more information about MDSCHEMA_MEASURES Rowset.

    As you can see the function accepts one parameter called myMeasureCollection, this is the collection of our measures and we are formatting the same as follows.

    for (int i = 0; i < myMeasures.Count; i++)
               {
                   //Format the measure name
                   if (i == 0)
                       res += "MEASURE_NAME ='" + myMeasures[i].Replace("[Measures].", "").Replace("[", "").Replace("]", "") + "'"; 
                   else
                       res += " OR MEASURE_NAME ='" + myMeasures[i].Replace("[Measures].", "").Replace("[", "").Replace("]", "") + "'";
               }

    The myMeasureCollection is a string variable which has the values in the format of [Measures].[My Measure 1]||[Measures].[My Measure 2]||[Measures].[My Measure 3]. That is why we are splitting the values with || as follows.

    myMeasures = myMeasureCollection.Split(new string[] { "||" }, StringSplitOptions.None).ToList();

    I hope everything is clear. Have a happy coding.