Working with Attributes in MDX Views
Numerical information
In Part 4 we had a look at using Calculated Members to return numerical values that were altered to show scaled values like values in thousand and millions. Using a similar concept we were able to create a read-only view. We next had a look at adding some strings to views as notes, validations or additional member information like the member’s type i.e. string, numeric or consolidated.
We also looked at deriving some string values to band performance results and link them to conditional formatting to better show areas of concern or good performance.
In this part we will continue to look at member information in the form of attributes. In PAW, attributes can be added alongside the member using the context menu on the dimensions stacked on columns or rows.
You may however have a need to show the values within the view or exploration. You may also want to use MDX to sort or filter on these attributes whilst showing them too.
Let’s take a look at some examples of working with attributes.
String or Alias Attributes
My Product dimension has the attributes detailed below:
- Brand, Demographic and Statistical Group are string type attributes.
- Caption is an alias.
- Alternate SKU is a numeric attribute.
To return the value of an attribute, we need to use the Properties accessor of the CurrentMember. As an example, let’s return the Demographic attribute value for each Product.
WITH
MEMBER [Sales Measures].[Sales Measures].[String Attribute] AS
[Product].[Product].CurrentMember.Properties("Demographic")
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[String Attribute]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
The returned view renders as follows with the value of the Demographic in my Calculated Member called String Attribute.
Similarly I could return the value of my Caption attribute which is an alias.
WITH
MEMBER [Sales Measures].[Sales Measures].[Alias] AS
[Product].[Product].CurrentMember.Properties("Caption")
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Alias]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
What is unique about the view above is that PAW does not allow you to select aliases from the list of attributes when you want to show them alongside the dimension. You may think that you could just update your set and choose the correct alias to display. That would work but what if you were looking at a view and needed to show multiple aliases e.g. Product with its Caption and another alias that may be required as an alternate reference?
Adding the alias like we did in the example above would be a good way to include an alias in the data grid.
Numeric Attributes
Next are numeric attributes and they can be read just like the other attributes using the Properties accessor on the member.
WITH
MEMBER [Sales Measures].[Sales Measures].[Numeric Attribute] AS
[Product].[Product].CurrentMember.Properties("Alternate SKU")
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Numeric Attribute]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
The above view shows our numeric attribute but why are we seeing all those decimals? When viewing them on the attributes tab of the dimension editor, they looked fine i.e. integer values with no decimals. In MDX, attributes are returned as strings by default and thus render per the above view. If you used the properties window in Architect to show attributes, you would see the decimals too.
Sets and Numeric Attributes
If you have ever used MDX on a set to filter on a numeric attribute you may have tried something like:
Filter(…[Product].[Alternate SKU] = 990148000)
After executing this and getting no results, you may have tried to add quotes around the number and still not got results. You may have then decided to record an expression and possibly ended up with something like:
Filter(…[Product].[Alternate SKU] = "990148000.000000")
This would have returned results but left you with a raised eyebrow wondering what was happening as the expression recorder added 6 decimals.
You may have then thought that this is the perfect opportunity to use StrToValue() or Val() to convert the attribute to a numeric value you could more naturally filter on. However, this approach fails when there are blank/null values and requires some workarounds to deal with these e.g.
{FILTER(
TM1SubsetAll([Product].[Product]),
(IIF([Product].[Product].CURRENTMEMBER.PROPERTIES("Alternate SKU")="",
0,
StrToValue([Product].[Product].CURRENTMEMBER.PROPERTIES("Alternate SKU"))) = 990148000
)
)}
The above MDX checks if we have a blank attribute, based on the default string type returned, it then substitutes the null with a zero, otherwise reads the string value where the attribute has a value and then uses the StrToValue() function to convert from text to a numeric value. Have a look at CoalesceEmpty() too.
This is quite a bit of fuss for when we just want to work with the numeric value.
A better and much less known solution is to use the TYPED modifier which returns the native type data for the property rather than a string. When using this on an attribute defined as a numeric attribute in TM1, we have a numeric value returned by the MDX rather than a string. Another benefit is that nulls or blanks where the attribute has not explicitly been set have values of zero and we do not need to try trap for nulls and deal with these.
Sticking with the example of filtering based on the Alternate SKU we could update our code to the following:
{FILTER(
TM1SubsetAll([Product].[Product]),
(
[Product].[Product].CURRENTMEMBER.PROPERTIES("Alternate SKU", TYPED) = 990148000
)
)}
This would now return a set containing Products where the Alternate SKU has a numeric attribute value of 990148000
Displaying Numeric Attributes
Applying the above modifier to our MDX view we could adjust the code as follows:
WITH
MEMBER [Sales Measures].[Sales Measures].[Numeric Attribute] AS
[Product].[Product].CurrentMember.Properties("Alternate SKU", TYPED)
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Numeric Attribute]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
The view would show as follows in PAW – note that 2 decimals are shown by PAW, and we no longer see the 6 decimals from the previous example.
You could change the display format of the Numeric Attribute to integers using FORMAT_STRING=’#’ after the member definition, like we have done in previous examples.
In an example where we need to perform some kind of mathematical operation on the Numeric Attribute of the member, the MDX below would not give a meaningful result as we are combining text (the untyped attribute value) and numerics (the denominator) and thus renders as zeroes:
WITH
MEMBER [Sales Measures].[Sales Measures].[Numeric Attribute] AS
[Product].[Product].CurrentMember.Properties("Alternate SKU") / 1000
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Numeric Attribute]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
However, adding the TYPED modifier returns the numeric value of the attribute and allows us to perform around division:
WITH
MEMBER [Sales Measures].[Sales Measures].[Numeric Attribute] AS
[Product].[Product].CurrentMember.Properties("Alternate SKU", TYPED) / 1000
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Numeric Attribute]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
Now that we know how to return the correct member type for an attribute, what else can we do with these attribute values?
In the previous example we looked at a simple operation of dividing by 1000 but this could just as easily be adding values whether a value from the cube, a constant or from another cube. The process is largely the same once you have used TYPED to return the attribute values as a numeric.
Working with the string attributes, we could do a whole lot more. We could join strings together, we can split strings, we can truncate strings etc. etc.
Our Product attributes are shown in the below screenshot:
You will notice that Description is an alias but also includes the Product code as part of the description. What if we wanted to show just the text part of the Description in our view?
I could possibly count the characters for the length of the code then add 1 for the hyphen but it is safer to rather find the hyphen in the Description then assume that from the next character to the end of the string is the text I want.
What we will do is use three MDX functions you may have already used when working with sets: INSTR, LEN and RIGHT
- INSTR will return the position in the text at which our hyphen occurs
- LEN will tell us how long our Description is
- RIGHT will allow us to work from the end of the string backwards to chop out the text after the hyphen.
I will add one more assumption and that is that we will only apply this rule to Leaf members and set any consolidated members to the Description per the alias.
Combining all these functions into my MDX code I have the following:
WITH
MEMBER [Sales Measures].[Sales Measures].[Description] AS
IIF(ISLEAF([Product].[Product].CurrentMember),
RIGHT([Product].[Product].CurrentMember.Properties("Description"),
LEN([Product].[Product].CurrentMember.Properties("Description")) -
INSTR(1, [Product].[Product].CurrentMember.Properties("Description"), "-", 1)),
[Product].[Product].CurrentMember.Properties("MEMBER_NAME")
)
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Description]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
My resultant view is rendered as follows:
For my Product set, I enabled the Description alias to show the difference between the Description including the Product code and the Calculated Member where we chopped out the Product code.
Similarly, I could have used LEFT() to return the first part of the Description if I needed to:
I updated the conditional formatting too and now have something like this where the issue is clearly highlighted:
WITH
MEMBER [Sales Measures].[Sales Measures].[Description] AS
IIF(ISLEAF([Product].[Product].CurrentMember),
LEFT([Product].[Product].CurrentMember.Properties("Description"),
INSTR(1, [Product].[Product].CurrentMember.Properties("Description"), "-", 1) - 1),
[Product].[Product].CurrentMember.Properties("MEMBER_NAME")
)
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Description]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
Note: PAW seems to have a mind of its own with some functions and removes them. I have had issues where LEN() is simply dropped but the member remains thus causing the code to raise an error. Also, supported functions like LEN() and INSTR() do not get coloured to indicate a known function and are not available if you type in the first character or two and press Ctrl + Space to bring up the autocomplete. With the new MDX editor around the corner, I hope these are already addressed.
What remains then is to join two attributes together or join text from different “fields” into one string.
Let’s assume that for some purpose we need to concatenate our Demographic and Caption using a vertical pipe separator into a Key e.g. Women’s Health|Cardiogrex for Product 148000. Let’s assume too that this is only relevant on Leaf members again.
Our adjusted code would simply use the + (plus) operator to join the three strings together:
WITH
MEMBER [Sales Measures].[Sales Measures].[Key] AS
IIF(ISLEAF([Product].[Product].CurrentMember),
[Product].[Product].CurrentMember.Properties("Demographic") + "|" +
[Product].[Product].CurrentMember.Properties("Caption"),
"")
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Key]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
Executing the MDX gives us the following view:
Our Key is shown as the combination of the Demographic and Caption, separated by a vertical pipe.
If we had to join Demographic with out Alternate SKU attribute, we could do this in exactly the same way:
WITH
MEMBER [Sales Measures].[Sales Measures].[Key] AS
IIF(ISLEAF([Product].[Product].CurrentMember),
[Product].[Product].CurrentMember.Properties("Demographic") + "|" +
[Product].[Product].CurrentMember.Properties("Alternate SKU"),
"")
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Key]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
The view returned does what we told it to but we have the original issue with the 6 decimal places on our numeric attribute:
If we used the TYPED modifier and change our code to the following:
WITH
MEMBER [Sales Measures].[Sales Measures].[Key] AS
IIF(ISLEAF([Product].[Product].CurrentMember),
[Product].[Product].CurrentMember.Properties("Demographic") + "|" + [Product].[Product].CurrentMember.Properties("Alternate SKU", TYPED),
"")
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Key]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
The result is a blank column as we cannot concatenate strings and numerics:
Typically, MDX will rely on a function like CStr() to convert the numeric to string but there is no such supported function in TM1/Planning Analytics currently.
The best one could do for now is assume that we will always have the additional 7 characters in our attribute i.e. “.000000”, and chop them out using LEFT():
WITH
MEMBER [Sales Measures].[Sales Measures].[Key] AS
IIF(ISLEAF([Product].[Product].CurrentMember),
[Product].[Product].CurrentMember.Properties("Demographic") + "|" +
LEFT([Product].[Product].CurrentMember.Properties("Alternate SKU"),
LEN([Product].[Product].CurrentMember.Properties("Alternate SKU")) - 7
),
"")
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Key]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
Our updated view looks exactly like what was required:
Taking this example a bit further, let’s assume that the Key needed to be uppercase instead of mixed, as stored in the attribute. We could use the UCASE() function to convert our string to Upper Case:
WITH
MEMBER [Sales Measures].[Sales Measures].[Key] AS
UCASE(IIF(ISLEAF([Product].[Product].CurrentMember),
[Product].[Product].CurrentMember.Properties("Demographic") + "|" +
LEFT([Product].[Product].CurrentMember.Properties("Alternate SKU"),
LEN([Product].[Product].CurrentMember.Properties("Alternate SKU")) - 7
),
""))
SELECT
NON EMPTY {[Sales Measures].[Sales Measures].[Units], [Sales Measures].[Sales Measures].[Key]} ON 0,
NON EMPTY {DRILLDOWNMEMBER({TM1SubsetToSet([Product].[Product],"Default","public")}, {[Product].[Product].[Total Products]})} ON 1
FROM [Sales]
WHERE ([Scenario].[Scenario].[Actual], [Period].[Period].[2023], [Customer].[Customer].[Total Customers])
The new view now shows the Key in upper case:
Likewise, I could have used LCASE() to convert the Key to lower case:
Unfortunately there are many string related functions that are not currently supported: CStr(), Mid(), SubStr(), Replace(), Trim() and Concat() to list a few. However, V12 PA aaS (and v3.1 when released), should have much better MDX function support due to the MDX parser and engine being completely new.
Summary
In this part we looked in more detail at how to retrieve attributes. We also looked at the difference in how to retrieve string attributes vs numeric attributes as the default behaviour in MDX is to return a string value.
Once we had the TYPED attribute value, we could then use it in mathematical calculations if it was numeric or string operations if it was a string.
We also had a look at manipulating strings using a combination of Left() and Right() to return only the required portion of the string needed. Together with Left() and Right() we also incorporated InStr() to locate a particular substring within the string and return its position. Similarly, Len() was used to return the number of characters in the string allowing us to combine this with the position returned by InStr and chop to the left or right to yield a new string.
Lastly we looked at some other text functions like UCase() and LCase() which could be used to convert our strings to upper case or lower case.
In the next part we will look at working with Time Series functions in MDX Views.
Keyword | Description |
Select | Specifies that you are looking to retrieve data from the cube |
Non Empty | Removes all empty tuples from the specified set combinations on each axis. NON EMPTY can be used with both SELECT and ON clauses to filter axes or sets, not just “set combinations.” |
On | Allows you to specify the relevant axis |
From | Specifies the underlying cube to retrieve the data from |
Where | Filters the query or view based on the specified tuple. This is an oversimplification and may require additional reading as it behaves more like a global filter. |
With | Tells the MDX to define a temporary calculated member or measure which lasts for the duration of the MDX query. It can also be used to define sets. WITH, MEMBER and AS are typically found together when working with calculated members. |
Member | This defines the context and name of the calculated member as part of the WITH clause. Multiple Member statements can be added to the MDX to create additional calculated members. |
As | Just a declarative almost like saying Let var = x and tells MDX to assign the calculation to the member |
Solve_Order | Where queries are complex and there are dependencies between calculated members, you will need to consider using the solve order to prevent sequencing or logical errors, ensuring correct evaluation. |
Format_String | Allows the calculated member to be formatted for presentation to the user. These could be numerical formats similar to what you have used in Excel, currency, dates, and conditional formatting to show formats for positive, negative and zero values. |
Sum | Calculates the total of a numeric expression evaluated over a set. |
Aggregate | Aggregates a set of tuples by applying the appropriate aggregation function, like sum or count, based on the context. |
Min | Finds the minimum value of a numeric expression evaluated over a set. |
Max | Finds the maximum value of a numeric expression evaluated over a set. |
Stdev | Computes the standard deviation of a numeric expression over a set, measuring the amount of variation or dispersion of the set. |
Var | Calculates the variance of a numeric expression evaluated over a set, indicating how spread out the numbers are. |
IIF() | The Immediate IF statement allows us to evaluate an expression for a certain condition and the return a result if true and a different result if false. |
Case, When, Else, End | The Case statement would be used where you expect multiple outcomes or need to extend the conditions beyond true and false. The first WHEN statement that returns true is returned as the result. An ELSE can be used for a catch-all scenario or default result. |
.CurrentMember | Allows us to address the current member the view is dealing with from the context area or another axis. |
.Properties(“<propertyname>”<, TYPED>) | Allows us to retrieve a property value which may be an intrinsic like the Element_Level or custom like an attribute that a modeller added. |
Instr() | Returns the position of a substring within a string, similar to TI’s Scan() |
Len() | Returns the length of a string |
Left() | Returns the n number of characters from the start of a string |
Right() | Returns the n number of characters from the end of a string |
LCase() | Returns the lower case of a string |
UCase() | Return the upper case of a string |
By George Tonkin, Business Partner at MCi.