Constructing Calculated Measures in MDX for different measures using same columns in a fact table

Question

i have a fact table with 2 columns corresponding to dimensions Dim1, Dim2. In the same table i have 4 other columns Value_Type(int), INT_VALUE(int), FLOAT_VALUE(float), TEXT_VALUE(string). There are a number of measures which are identified by Value_Type and depending on their nature could be written in one of the 3 columns (INT_VALUE(int), FLOAT_VALUE(float), TEXT_VALUE(string)) Let's say Measure1 with Measure_Type=1 is age, 2 is account balance and 3 is Name for clarity. There could be other measure types that use these 3 same columns for data. So the sample fact table looks like this

Dim1    Dim2    Measure_Type      INT_VALUE       FLOAT_VALUE    TEXT_VALUE
10      10      1                 25         
10      10      2                                  2000,34   
10      10      3                                                John
10      20      1                 28         
10      20      2                                  3490,23   
10      20      3                                                Frank

My task is to write an MDX query for each Dim1, Dim2 combination which returns all 3 measures in the same row. The idea is to construct a calculated member for each Measure that returns value from the right field. For example for Measure1 we take INT_VALUE with measure_type=1. The problem is i don't know how to construct MDX query for these calculated members. Can you please help me?

So my final goal is to write an MDX query that returns all measures in one row for each set of Dim1, Dim2

SELECT [Measure1], [Measure2], [Measure3] ON COLUMNS,
NON EMPTY [Dim1].[Dim1].[Dim1].Members*[Dim2].[Dim2].[Dim2].Members ON ROWS
FROM [Cube]

Dim1    Dim2    Measure1    Measure2    Measure3
10      10      25          2000,34     John
10      20      28          3490,23     Frank

Strings cannot be aggregated. What would you want to show for Measure3 if you are not at the bottom level of the selection? — FrankPl
i need a text value for measure_type=3. Apparently there's a function that concatenates text values. — kosmipt
concatenating strings as aggregation is not available in Analysis Services. — FrankPl
no, i mean a function which i can use in a calculated member — kosmipt

FrankPl FrankPl · Accepted Answer · 2014-04-11T18:11:35

As floats and ints can just be summed, I do not think there is anything special needed for Measure1 and Measure2. Assuming that the empty fields in you sample table are nulls, you even do not need the measure_type column for anything, as summing across nulls is fine, i. e. you could collaps your fact table to one third of its size by coalescing the three records for different measure types to one, and omitting the null values.

Thus, we are left with the aggregation of the string values. As strings cannot be used as physical measures, we must put this column in an attribute and implement the aggregation as a calculated measure. To do this, you can proceed as follows:

Create a dimension table with just a numeric primary key column and the distinct values from the text value column. I would suggest to also add a record with a special text like '<n/a>' to this table, for cases where there is no text_value for a combination of dim1 and dim2. It is generally a good idea to avoid null attribute values and null foreign keys in Analysis Services.
Add a foreign key to the fact table referencing this dimension from each record.
In BIDS, create the dimension, let's name it text and I am assuming that the attribute is called text value. Set the reference between the measure group and the dimension in Cube Editor.
Define a calculated measure for Measure3 with the following expression:

.

Generate( (EXISTING [text].[text value].[text value].members )
                    - { [text].[text value].[<n/a>] } 
          as a,
          a.Current.Name,
          ', '
        )

Make the dimension or the attribute invisible.

Of course, you need not create the dimension table and the foreign keys in the fact table physically.You can as well generate them as views or named query in the Data Source View.

And you can use a different delimiter than the comma and space which I used, this is the third argument to the Generate MDX function.

Constructing Calculated Measures in MDX for different measures using same columns in a fact table

1 Answers