DBRA TM1 Excel Syntax and Use
DBRA is an Excel based function for use with TM1 that retrieves the value of an Attribute from a Dimension in TM1. It is roughly equivalent to using a ATTRS in a Rule or TI.
For example if I have a dimension called Customer with an attribute called Channel. An element of this dimension is called “Fred’s Grocery Store” (they’e a customer) and his Channel is “Retail”. In this case “Retail” would be recorded against the attribute Channel for Fred’s Grocery Store and we can then retrieve this attribute from within an Excel report by using the DBRA function against in combination with the element ID for Fred’s store.
Syntax of DBRA
The syntax for DBRA is:
- server:dimension, is the TM1 server ID, concatenated with a full colon and then the dimension name
- element, is the element you wish to retrieve the attribute for
- attribute, is the attribute you want to return
Continuing the example from above, where I have a dimension called Customer, an attribute called Channel and an element called “Fred’s Grocery Store” where their Channel is “Retail”.
So, in Excel to insert the contents of the Channel attribute into a report I would enter:
=DBRA("CXMD:Customer","Fred's Grocery Store","Channel" )
Obviously if I have “Fred’s Grocery Store” in one cell, I can reference that cell from within the DBRA formula.
Note that the elements are in double quotes. Also note that when the syntax is correct, you will get a result in the cell you enter the formula. From there you can Copy, Paste and Recalc.
Importantly DBRA only accepts text as the element ID, so if your element is named using a numeric only sequence, you must ensure that you convert the numeric value to a text value. To do this insert TEXT(elementcell,”0″), where elementcell is the cell containing the numeric element ID.
Alternative to DBRA
If you find, for whatever reason, that DBRA gives you an error, like a #VALUE error in Excel, then consider using a DBRW or a DBR from the control cube for the dimension.