OEG Best Practice: Best practices about Excel add-In
- We have listed below some of the best practices we recommend for the Anaplan product. These are based on our experience and are subject to change as we deliver new functionalities and learn more about how our customers use the product.
- If you would like to contribute or comment, please contribute to this forum post.
- You do not have to apply these best practices; we will still support you even if you don’t follow them. These are recommendations and there are times when it may make sense to do things in a different way.
About this article
We focus on how to set up your Anaplan model and your connections with the Excel add-in for performance and reliability.
|1||Performance of the add-ins||
The performance of our add-in has largely improved compared to the classic version (Excel add-in 2.6) as a result of a technology change. The performance can be impacted by multiple factors:
Please find below some indicative performance information in our testing conditions.
Send and refresh:
|2||Setting your Anaplan model and views for performance||
Try and minimize the amount of data you retrieve with the add-in in order to ensure great performance. If you want to retrieve a small amount of data from a large module, you might want to create a separate module just for the add-in.
|3||Cloning a connected worksheet or workbook||
You cannot copy/paste or copy the sheet with the standard Excel functionality, but you can use “clone the connection”.
As you cannot clone in a separate workbook, to duplicate your workbook you can:
|4||Saved view or module connection?||
Saved views are great because you immediately retrieve the data you need, without having to pivot and filter it manually. They are great for reporting purposes because you can create a standard report quickly. However, you are reliant on a model builder to set up the saved view in Anaplan. If you have Application Lifecycle Management (ALM), this needs to be done in the development model before synchronizing with your production model).
Modules are more flexible because you can pivot and filter them. However, this means you might need more time to set up exactly the view you want. They are great for ad-hoc analysis because they make it easier to explore your data.
|5||One big connection versus multiple ones||
The approach of one big connection is appropriate in some instances. It is good because it limits the number of connections in a workbook and you do not have to change the page selector every month for instance (see paragraph 9 about page selector change). On the other hand, given the limitation on the number of cells that can be retrieved in a single import, you would have to be mindful of every change in your module that could result in going other this limit.
There is no way to identify which modules or views are used by the add-ins in Anaplan.
If you make changes to a module or a saved view that is used by the add-in, you may risk breaking your connection. Although small changes are handled by the add-ins (new list item or line item for example), bigger changes like a change in dimensionality cannot be dealt with automatically.
It is recommended that you use a specific naming convention to indicate that a view or module is used by the add-in (for instance P&L_report_XL to indicate it is used by the Excel add-in). You can also mention this in the “notes” column but it is less visible.
|7||Interaction with other add-ins||
If you are using other add-ins, you may be experiencing conflict between those and the Anaplan add-ins. It is not possible for Anaplan to test the interactions with all the other add-ins available in the market. If you face issues, we can investigate it if you raise a ticket with Anaplan Support.
If the workbooks tend to crash with the add-in, one workaround is to turn the add-in off (via Excel Options -> Add-ins) when not actively using the add-in (ie during other Excel work).
|8||Do not delete models
The connections you create using the add-in are based on the model ID. Therefore if you delete a model, the model ID is lost and you will lose your add-in connections.
As a result, you will have to rebuild your files entirely. We recommend that you simply archive models instead of deleting them. This way, they can be unarchived easily if you need them back, they don’t take any space and their model ID is conserved.
|9||Report setup (page selectors)
When the page selector changes as a result of a saved view change (for example your saved view in Anaplan is filtered by the current month and you update this month every month in Anaplan directly), this is what happens in the background: the columns in your Excel spreadsheet are deleted first, and then the new data is retrieved. As a result, if you have a formula looking at one of the Anaplan data, it will change to #REF because the cell it was pointing at has been deleted. Also, the formatting gets removed for the same reason.
Alternatively, the #REF does not occur if the formula is in a different file and you don’t have this file open when you refresh. For example, you retrieve Anaplan data in file A, but your report which points at this data is in file B.
|10||Creating views to address cell volume limit||
There may be occasions when you need to retrieve data from a module that will take you above the 1m cell limit currently in place with the Excel Add-in. Follow these steps as a workaround.
Author Magali Pelissier.