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.

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.

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.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

One comment on “DBRA TM1 Excel Syntax and Use

This site uses Akismet to reduce spam. Learn how your comment data is processed.