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:
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.
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:
Advantages of Understanding DBRW
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.
For more information or a free trial, contact us at info@itlink.com
Don't miss our regular content updates! Bookmark this blog to stay informed!
