Uncategorized

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 Bloggs” (he’s a customer) and his Channel is “Retail”.  In this case “Retail” would be recorded against the attribute Channel for Fred 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.

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 Bloggs” where his Channel is “Retail”.

So, in Excel to insert the contents of the Channel attribute  into a report I would enter:

=DBRA(“CXMD:Customer”,”Fred Bloggs”,”Channel” )

Obviously if I have “Fred Bloggs” 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.

Posted in:

...

Leave a Comment





Need help with TM1?
We're here for you

Categories

Tags

Popular Articles