DBRW TM1 Excel Syntax and Use

DBRW is an Excel based function for use with TM1 that retrieves a value from a specific intersection of a cube TM1 and allows users to enter a value into a cell when all dimensions are showing input level elements.

Example

I have a Sales Analysis cube with the following dimensions: Year, Month, Customer, Product, Measures.  I want the intersection of:

  • 2015 (Year),
  • Oct (Month),
  • The Corner Cafe (Customer),
  • Coffee (Product) and
  • Sales $ (Measure).

If I open Architect and navigate to this intersection, it says $1,230.00.

I now want to get that value in a cell in an Excel report.  To do this I can use a DBRW or a DBR formula.

Syntax of DBRW

The syntax for DBRW is:

=DBRW(TM1ServerName:cubename, e1, e2, e3, …, en)

Where:

  • server:dimension, is the TM1 server ID, concatenated with a full colon and then the cube name you wish to interrogate
  • e1, is the element from the first dimension in the cube you are interrogating
  • e2, is the element from the second dimension in the cube you are interrogating
  • e3, is the element from the third dimension in the cube you want are interrogating
  • en, is the element from the nth dimension in the cube you want are interrogating

Importantly, the order of the elements listed in the DBRW formula must be identical to the order of the dimensions in the cube (otherwise TM1 will not know which dimension to look at for that element!).

Usage

Continuing the example from above, where we want 2015, Oct, The Corner Cafe, Coffee and Sales $ from the Sales Analysis cube, we would write the DBRW in Excel as follows:

DBRW('CXMD:Sales Analysis', '2015', 'Oct', 'The Corner Cafe', 'Coffee', 'Sales $')

Obviously I can have these referenced values in other cells in Excel and then refer to those cells rather than using hard coded values. This is exactly the method that the creation of a Dynamic Report or Custom Report does in Planning Analytics for Excel.

Example of DBRW

In the example below, I have the Server-Cube reference, Year, Product and Measure as sequential rows in column A of a spreadsheet and then the values for those in column B. Then in column A beneath that block have The Corner Cafe and all my other customers listed. Finally, in columns B onwards we have Jan, Feb, Mar… Dec entered.  

Then we simply create a DBRW that uses absolute referenced values for Server-Cube, Year, Product and Measure, then relative references for the Customer (rows) and Month (columns). We can then copy and paste this for all months and customers and then get our values out of TM1.

DBRW Example
Example of the use of DBRW in TM1 and Planning Analytics

One More Thing…

In a DBRW we need to define the TM1 model that we are querying. If you are migrating from a Dev or Test model to a Production model on the same server, you will run into problems with the TM1 Servername. You don’t have to though. You can define a variable that pulls the model name from TM1 and then you can refer to that in your formula. Please see this post for more information on how to use the TM1PrimaryDBName function.

IBM’s Notes on DBRW

Here is IBM’s very short write up on DBRW.