ITLink website

Understanding the DBRW Function

DBRW is a commonly used function within PAFE. It allows users to retrieve or send data from an intersection or data point in the company’s TM1 cube to another. The syntax for this function is:

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

e1, e2, e3, …, en represents elements from each dimension of the cube. Each element must be placed in the correct order in accordance with the order of the dimensions of the source cube.

An example being the DBRW function. For those who are unfamiliar with this function, the error message “#VALUE!” may ring a bell.

Example of the DBRW Function

Using an example of the Finance cube, if we would like data from a server named Finance Budget from the “CompanyX” TM1 server for instance, each element from each dimension of the Finance_Budget cube will have to be specified to define the intersection between each element and dimension where data has been placed. Let's say that we have the following dimension order with sample elements:

Version - Actual
Entity - United States
Cost Center - Marketing
Year - 2015
Month - Jan
Account - Promotions

The DBRW function for this example is:

=DBRW(“CompanyX:Finance_Budget”, “Actual”, “United States”, “Marketing”, “2015”, “Jan”, “Promotions”)

Follow the dimensions order and ensure the element name is correct. If you make a mistake, "#VALUE!” will appear. In order to build a maintainable report, most users prefer to reference cells instead of typing out the exact full name of each element. Do note that the naming of these elements is not case sensitive.

=DBRW($B$1, $B$4, $B$2, $B$3, $B$5, G$8, $A19)

For those who are unfamiliar with the function, it may look like a daunting task with high margin for error when typing in the function.

‍

Who Needs to Learn the DBRW Function?

The majority of TM1 users only generate reports, hence, do not require the knowledge or use of DBRW functions.

TM1 automatically builds DBRW functions for a Dynamic Report when users slice or create one from a cube view. Report builders may need to create or modify DBRW functions if more advanced reports that pull data from multiple cubes or with attribute values are needed.

‍

Function Wizard

To manually create a DBRW function, users can “Insert Function” under the excel “Formulas” ribbon. However, this is meant to be a guide for new users as more experienced users may opt to write the function themselves.

‍

‍

DBRW versus DBR

One function that works the same way as the DBRW function is DBR. This function is one that TM1 users are more familiar with. The DBRW and DBR functions are similar in that both provide the same data.

The difference lies in that the DBRW function is optimised for network traffic and performs better over WAN (Wide-Area Network) or the internet. This is because data is being sent all at once as a packet while the DBR function’s method of transmission – the cell-by-cell method – requires more bandwidth to response time. Users will experience improved performance from the DBRW function in both Excel and TM1 Web.

‍

Replacing the DBRA Function

The DBRA function is not optimised for WAN as it retrieves attribute data for a dimension element. By using DBRW or DBR functions in place of DBRA functions in our reports, it greatly improves performance. With either of the 2 functions we can connect it directly to the attribute cube.

It is good to note that in our report, if the DBRW functions indicates attributes, we must use DBR functions to connect to an attribute cube. Reason being that the DBR functions are sent before the DBRW data packet reaches the server. This ensures the attributes are updated on refresh and our report will show correct values.

Always reference Excel cells instead of hardcoding element names directly into DBRW functions. This makes your TM1 reports highly maintainable and less prone to errors.

Example of Replacing a DBRA Function

Original function:
=DBRA(“CompanyX:Finance_BudgetACorp:Account”, “Net Income”, “Account Type”)

Replacing DBRA with DBR:
=DBR(“CompanyX:}ElementAttributes_Account”, “Net Income”, “Account Type”)

How to fix a broken DBRW function?

What do we do when there is an error showing “#VALUE!” in a DBRW or DBR function? Here are a few possible ways to identify the problem:

If the same error message is shown on all cells, it is likely that one of more common elements may be missing. In most cases, it would be that the user has lost connection with the TM1 server which results in SUBNM functions being unable to return values for DBRW functions.
One of the reasons references in DBRW functions may point to incorrect cells is because not all cells show the error message. The simplest solution is by copying a DBRW function from a cell that returns correct values over to the problematic cell, changing its function and move references. If we are not able to find a working cell, use TM1s’ Edit Function Tool found on the ribbon to construct our desired DBRW function.
Another possibility could be that pointed elements do not exist within the TM1 database that could need to the “#VALUE!” error. Do check if the elements exist within the dimension.

Still wrestling with TM1 performance issues?

We’ve helped multiple Singapore teams cut calculation times by 50%+

Get your system reviewed →

Advantages of Understanding

If we are generating and managing reports from TM1, the DBRW function is one of the most used functions. The more we learn and understand how this function works, the quicker we can generate, manage, and diagnose error messages in reports.