In this article we look into creating a dynamic server reference with PAFE workbooks so that they can be successfully opened across multiple instances. Read on to learn why we do this, how to set it up, best practices, and some troubleshooting tips.
What is a Dynamic Server Reference?
Planning Analytics for Excel worksheet functions require a reference to a Planning Analytics sever. This allows the functions to properly communicate with the user’s intended server when there are multiple servers present. In practice, we can make the server reference dynamic to represent the server you are signed in to. This allows for seamless deployment of development work done on a development server, to production use on a production server.
Why use a Dynamic Server Reference?
Dynamic server references make it easy to move PAfE workbooks between Planning Analytics servers. This is useful to allow development, testing, and production to exist on three separate Planning Analytics servers.
How to set up Dynamic Server Reference?
The dynamic server reference can be set up using the =TM1PRIMARYDBNAME() or =TM1USER() PAfE worksheet functions. Note that =TM1PRIMARYDBNAME() is not backwards compatible to the Perspectives Excel client. These functions are used to determine the server or servers that a user might be logged into. It is best practice to create a separate and hidden tab to perform the dynamic server logic. This allows for it to do its job in the background without the end user needing to know it is there.
The TM1PRIMARYDBNAME() Function
The =TM1PRIMARYDBNAME() function returns the primary server the user authenticated through, even if they are logged into multiple servers. There are no arguments for this function. On a separate ‘Server’ tab we enter =TM1PRIMARYDBNAME() in a cell. We then give the cell a named range name such as ‘PA_Server’. You can now replace any hardcoded server references in your =SUBNM(), =DBRW(), and other functions with a reference to the named range.
In the screenshot below we have entered =TM1PRIMARYDBNAME() in cell B2 and given cell B2 the named range name ‘PA_Server’ The function has rendered as ‘24Retail_Dev’, indicating that we are logged into the development environment.
On our reporting tab we switch our =SUBNM(Server:Dimension) functions to concatenate the dynamic PA_Server named range with a dimension name and our =DBRW(Server:Cube) functions to concatenate the dynamic PA_Server named range with a cube name. As shown in the screenshot below.
The workbook is now setup to dynamically function using the =TM1PRIMARYDBNAME() function to find the PA environment that the user is logged in to.
The TM1USER() Function
The =TM1USER() function requires a server name as an argument and returns the user’s ID if the user is logged into that environment. This function is useful because it can identify multiple environments that a user is logged in to. Additionally, this function works in both PAfE and in the legacy Perspectives Excel clients.
In the screenshot below we have populated column A with the Dev, Test, and Prod server names. Column C has been populated with the =TM1USER() function referencing each row’s server name from column A. Since we are logged into Dev and Test, our user ID is populated for both servers in column C. Since we are not logged into Prod, the function returns nothing.
In this example we have moved our ‘PA_Server’ named range to cell B6 and given it some tiered logic. Nested IF() statement logic is used to force the lowest environment available to win out over any higher environments. In our example the ‘PA_Server’ named range shows as 24Retail_Dev, even though we are logged into both Dev and Test. This is a best practice in order to prevent any accidental development or testing to occur in production. If a user were to be logged into all three at once, the nested IF() logic first checks if you are logged into the Dev environment, then Test, and then Prod. If you are not logged in anywhere it will display that you are not logged in.
On our reporting tab we switch our =SUBNM(Server:Dimension) functions to concatenate the dynamic PA_Server named range with a dimension name and our =DBRW(Server:Cube) functions to concatenate the dynamic PA_Server named range with a cube name. As shown in the screenshot below.
The workbook is now setup to dynamically choose the PA server based on the =TM1USER() function and the tiered list of server importance.
Bonus Best Practice
It is best practice to hide the Server worksheet from the end users so that it can function in the background. The hidden Server tab can additionally be used to track and modifications to the report for future reference.
