DB TM1 Function: How to Use, Syntax and Example
The DB function returns the contents from a specific intersection in a TM1 cube (either string or numeric). It allows you to interrogate one TM1 cube from another and return a value from a specific intersection from the source cube.
For example, you might have an assumption cube with a standard Wage Increase of 5% and you want to apply that to all of your employees. Using a DB within the rules of your Labour cube will allow you to pull that standard wage increase into the Labour cube for all employees. In this way it is entered once in the assumption cube and used everywhere.
Syntax of DB
The syntax is:
DB (cube, element1, element2, element3, elementn);
- cube is the cube you want to look up the value from
- element1 is an element from the first dimension of the source cube
- element2 is an element from the second dimension of the source cube
- element3 is an element from the third dimension of the source cube
- elementn are the elements from subsequent dimensions in order from the source cube
An example is:
DB ('Opex', !Year, !Month, 'Actual', !Opex Account, !Cost Centre, !Measures Opex );
which will return the value from the Opex cube that corresponds to the currently addressed values in the Year, Month, Opex Account, Cost Centre and Measures Opex for the ‘Actual’ element from the scenario dimension.
Example Combined with Other Functions
Note that DB is very often combined with other TM1 rule functions. For example:
['Asset Life'] = N: IF ( ELISANC ( 'Asset', 'New assets', !Asset) = 1 & ['Purchase Date'] <> 0 ,ATTRN ( 'Asset Type', DB('Capex','No Time','Forecast','No Cost Centre','No Asset Type',!Asset,'Asset Type'), 'Asset Life' ) ,CONTINUE );
- This uses ELISANC to test if ‘New Assets’ is an ancestor to !Asset AND that Purchase Date has been entered.
- If that is True, then it uses an ATTRN to look up the numeric attribute called Asset Life from the Asset Type dimension using the Asset Type selected when an entry was made in the Capex cube (which was pulled from the Capex cube via a DB).
- If it is False, then do nothing and CONTINUE to the next rule.