Find Datatype Of Each Measure In SSAS MDX Queries

In this post we will see how we can get the data type of each measure 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 get from the cubes, such as formatting the data, assigning the data as grid data source, or formulating the data to any other form. I got a requirement to show the cube data as a grid, so I needed to know the types of each measure users select so that I could assign the grid column types accordingly. There are many ways we can find the types of measure, here I am going to discuss that with you. I hope you will like this.

Background

I went through this requirement and I was able to do this in time with the help of Mr. Greg Galloway (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.

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.