The main difficultly when migrating a Planning Analytics for Excel (PAX) report or input template from one Planning Analytics (PA)/TM1 Server to another is making sure it will work across both servers. Every PAX function, like a DBRW or SUBNM, requires the cube name or dimension name be prefixed with the Server name that is being called. When this server name is hardcoded in a PAX formula, the formula will return an error when a user opens the report when connected to a different Planning Analytics Server. For example, if a SUBNM formula is written SUBNM( “Dev:Accounts”, “P&L”, “Expense”) and the user tries to open the report while connected to Prod, the SUBNM formula will fail. It is important to make the PA Server name in all PAX formulas a dynamic reference to avoid this. This can be accomplished using two methods: the TM1PRIMARYDBNAME function or the TM1USER function.
TM1PRIMARYDBNAME()
The simplest way to dynamically determine the server a user is connected to while in PAX is to use the TM1PRIMARYDBNAME function. This function has no arguments, all that is needed is the function name and a pair of open/closed parenthesis:
Once a user connects to a PA Server via PAX, this function will return the name of that server. If a user connects to a PA Server named “DEV”, DEV will be the result of this function. This cell can then be referenced in all PAX formulas instead of a hardcoded server name. Better yet, an Excel Named Range can be used, i.e. rngServer, instead of a cell-reference. The formula SUBNM( “DEV:Accounts”, “P&L”, “Expense”) would then be written as SUBNM( rngServer & ”:Accounts”, “P&L”, “Expense”). An easy way to fix these formulas across an entire book is to do a CTRL-H Find/Replace and replace “Dev with rngServer & “. This will update all formulas and remove any hardcoded server names.
It is important to note that while very easy to implement, TM1PRIMARYDBNAME does have its drawbacks when working with multiple PA Servers. If a user is connected to a PA Server and then that user connects to a second PA Server, the TM1PRIMARYDBNAME formula will not update. The formula only returns the first PA Server name that a user connects to during a PAX session. If a user would like the PAX workbook to connect to a different PA Server dynamically, that user would need to completely disconnect from all PA Servers in PAX and then connect to the server of choice first.
TM1USER( <Server Name> )
The TM1USER function allows PAX to determine which PA Servers a user is connected to. Unlike TM1PRIMARYDBNAME that can only return the first server a user connects to in a session, TM1USER can be used to determine if a user is connected to one, multiple, or all the client’s PA servers. The TM1USER function is used by passing it the name of a PA Server. For example, if a PA Server named “DEV” exists, the function could be used in PAX as follows: TM1USER( “DEV” ). This function returns the username of the current PAX user if they are connected to the function specified server. If the current PAX user is not connected to the function specified server, the TM1USER function will return a blank value.
The admin user is connected to DEV only:
The admin user is connected to DEV and PROD:
Using some basic Excel IF logic, the PAX worksheet can figure out which server to use in all the PAX formulas. For example, logic could be applied to the cells above as such:
=IF( B2 <> “”, “PROD”, IF( B1 <> “”, “DEV”, “” )
This above formula will first check to see if B2 contains a value, which would signify the user is connected to PROD. If they are, the server name should be PROD. If B2 is blank, meaning the user is not connected to PROD, the formula will check if B1 is blank. If B1 is not blank, the user is connected to DEV and the server name used across the sheet would be DEV. If B2 and B1 are both blank, then the user is not connected to either server. Once this formula is in place, the cell containing this formula can be given a named range i.e. rngServer. Then, as similarly described in the previous section, this dynamic rngServer server name can be used in every PAX formula where a hard-coded server name was used previously.
Summary
Dynamic Planning Analytics server names are a must when working with multiple servers and migrating reports. Migrating a group of PAX workbooks can be a headache when server names are hardcoded and maintaining a separate set of identical workbooks for each server is confusing and unnecessary. Making use of the TM1PRIMARYDBNAME or TM1USER functions in PAX workbooks is an easy way to remove this hassle and make a single workbook work across all your Planning Analytics servers.