In this article we delve into the DBRW function in IBM Cognos TM1. Read on for our rundown of what it is, when to use it, and how to fix it when it's broken.
What is a DBRW function?
IBM Cognos TM1 is a great planning, forecasting and reporting system. One of its functionalities is to communicate meaningful results to different stakeholders.
Many types of reports are used as a vehicle to achieve that goal. One of the common ways to drive most reports is with the DBRW function. While some users are familiar with it, most users only notice the function when they see the error message “*KEY_ERR.”
DBRW is a Microsoft Excel function that connects to an intersection, or data point, in a TM1 cube. A user can retrieve or send data to and from the cube with this function. The syntax of the DBRW function is:
=DBRW(<name of the TM1 server>:<cube name>, e1, e2, e3, …, en)
where e1, e2, e3, …, en are the elements from each dimension of the cube (and they must be in the correct dimension order).
DBRW Function Example
If you want to retrieve data in the Finance cube, for example, from a server named ACorp, you need to know the dimension order and elements that specify the cube intersection. For example, the dimension order with sample elements is:- Version - Actual
- Entity - United States
- Cost Center - Marketing
- Year - 2015
- Month - Jan
- Account - Promotions
The DBRW function for this example is:
=DBRW(“ACorp:Finance”, “Actual”, “United States”, “Marketing”, “2015”, “Jan”, “Promotions”)
If you do not follow the order of the dimensions, or the element name is incorrect, or there is a typo in the function, you’ll see the error message “*KEY_ERR.” In general, the element names are not spelled out - they generally reference spreadsheet cells:
=DBRW($B$1, $B$4, $B$2, $B$3, $B$5, G$8, $A19)
This looks terrifying for users who are not familiar with the function, and it is very easy to make a mistake when entering the function.
Who needs to understand DBRW functions?
Most TM1 users do not type the DBRW functions because they only consume TM1 reports. Even a report builder or administrator who wants a report that looks like a cube view doesn’t need to deal with DBRW functions.
When users slice a cube view or create an Active Form directly from a cube view, TM1 automatically builds the DBRW functions in the report. However, for advanced reports that pull data from multiple cubes or use attribute values, report builders may need to create or modify DBRW functions.
Function Wizard
To create a DBRW function manually, use the wizard in the TM1 ribbon:
DBRW vs. DBR
A related function familiar to TM1 users is DBR, which behaves the same way as a DBRW function except that the latter is optimized for network traffic. The two functions return the same data, but the calculation speeds are different.
The DBRW function performs faster than the DBR function over WAN (Wide-Area Network) or the internet. The DBR function sends and receives data cell by cell whereas the DBRW function sends the data as one packet. The latter methodology reduces the required bandwidth and response time. The DBRW function improves the overall performance of a report whether it is in Excel or TM1 Web.
Replacing the DBRA Function
The DBRA function, which retrieves attribute data for a dimension element, is not optimized for wide area networks. Replacing DBRA functions with DBRW or DBR functions in your reports improves performance. With a DBRW or DBR function, you can connect directly to the attribute cube.
Note: if the DBRW functions in your report refer to attributes, you must use DBR functions rather than DBRW functions for connecting to an attribute cube. The reason is that the DBRW functions are sent to the server in one packet – the DBR functions are sent to the server before the DBRW data packet. This way, the attributes are updated before the DBRW functions are refreshed and your data will be correct.
Example of replacing a DBRA function:
Original function:
=DBRA(“ACorp:Account”, “Net Income”, “Account Type”)
Replacing DBRA with DBR:
=DBR(“ACorp:}ElementAttributes_Account”, “Net Income”, “Account Type”)
How to fix a broken DBRW function
When there is an error in a DBRW or DBR function, the cell shows “*KEY_ERR.” Here are a few ways to diagnose the problem:
- If all cells on the report show the error message, it is likely that a common element(s) is missing. It can be the server name or an element name (SUBNM) on the top of the report. In this case, 9 out of 10 times, the user has lost the connection with the TM1 Server so SUMNM functions cannot return values for DBRW functions.
- If only some cells show the error message, some references in the DBRW function may point to incorrect cells (assuming it is not the problem discussed in #1). The easiest way to fix this problem is to copy the function from a DBRW cell that returns a correct value to the problem cell, edit the function and move references. If you cannot easily find a cell that works correctly, use the Edit Function tool on theTM1 ribbon to create the DBRW function that you want. This tool puts the element names in a cube order.
Benefits of Understanding DBRW
The DBRW function is the most commonly used function in TM1 reports. The more you understand the function, the faster you can generate, manage and change reports. More importantly, the ability to diagnose error messages quickly comes in handy.
Click here for more Planning Analytics/TM1 tips!
For other TM1 Tech Topics, whitepapers and videos, fill out the subscription form above.
