Best practices about Excel add-In

[#1]

• General information
• About this article
• Best practices

General information

• 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 tothis 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 toset up your Anaplan model and your connections with the Excel add-infor performance and reliability.

Best practices

1	Performance of the add-ins	The performance of our add-in haslargely 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: Machine (in particular the available RAM) Network connection speed Number of connections to refresh Size of the connection (number of dimensions and cells) Number of cells edited (for the send and refresh functionality in Excel) Please find below some indicative performance information in our testing conditions. Read-only: Send and refresh:
2	Setting your Anaplan model and views for performance	Try andminimize the amount of data you retrievewith 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	克隆一个连接的工作表或工作簿	You cannot copy/paste or copy the sheet with the standard Excel functionality, but you canuse “clone the connection”. 当你cannotclone in a separate workbook, to duplicate your workbook you can: Once you have created workbook 1, save the file 1 Take a copy of the file 1: this is now your file 2 and it is connected to Anaplan.
4	Saved view or module connection?	Saved views are great because youimmediately 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 flexiblebecause 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 ofone big connectionis 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.
6	Naming convention	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 aspecific naming conventionto 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 (viaExcel 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 yousimply 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. If you do have to reference a cell, we recommend youset up your report in a way that does not impact the page selector.例如,您可以put the dimension you want in pages in rows or columns instead, and use vlookup or index(match) formulae to get the right row or column. Alternatively, the #REF does not occurif the formula is in a different fileand 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 the1m cell limitcurrently in place with the Excel Add-in. Follow these steps as a workaround. Identify the moduleyou wish to bring data into Excel from. Identify the lists used in the moduleand ensure each list item has a code In the module where the data is held create anew line item Excel Codeas Text 创建一个公式concatenating the codesof the component lists together where data exists. Separate each code by an underscore. Create a new list calledExcel代码 Import into the listfrom the module where you have created the code Create a new moduledimensioned by the Excel Code list. In this module break the code down into it constituent parts by using Find, Left, Right and Mid text functions. Using the codes also return the name of the corresponding element. You are going to import this into Excel as a reference table. Create a moduleto hold your data dimensioned by the Excel Code list and extract the data with reference to the module created above by means of a Lookup If you don’t already have so,create a moduledetailing the contents of each list namely the code and name In Excel, use the add-in tocreate a connectionto pull the data into a sheet Similarly,pull in the reference table created in Step 7 and all the supporting reference information Create your reportto display the information. This can be achieved by reconstructing the code based on the elements either in the rows and columns and or drop-down selectors.