DBRA TM1 Excel Syntax and Use

DBRA is an Excel based function for use with TM1 that retrieves a value from, and writes a value to, an Attribute from a Dimension in TM1. It is roughly equivalent to using a ATTRS in a Rule or TI, except you can use it to update an attribute as well.

Example

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:

DBRA(server:dimension,element,attribute)

where:

  • 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

Usage

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.

Warning!

If you are using DBRA in an Excel sheet to display the attribute (say a Description) for an ID, be careful as if the user types over the top of the DBRA, it will update the attribute with the freshly typed in Description. To overcome this, consider putting it in a hidden cell and then referencing the hidden cell from the displayed cell. This will mean that users can’t overwrite it.

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.