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.
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).
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.
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.
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.
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.
Original function:
=DBRA(“ACorp:Account”, “Net Income”, “Account Type”)
Replacing DBRA with DBR:
=DBR(“ACorp:}ElementAttributes_Account”, “Net Income”, “Account Type”)
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:
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.