Anaplan Excel Add-In

Why would I need the add-in?

Anaplan is designed to display data within grid views or through graphs and charts within its own dashboards, but there will be occasions when the data will need to be loaded into a third party piece of software, like Excel.

Even without the add-in, Anaplan users can have the ability to export data from any dashboard view, however there might be occasions when you want to export multiple views out of Anaplan, and populate these data points into pre-determined cells in an Excel workbook.

How can I get the Excel add-in?

Anaplan’s Excel Add-in can be downloaded from the Anaplan Community website. If you’re logged into the Anaplan Community website, you can download v3.1 of the add-in here: https://help.anaplan.com/anapedia/Content/Extensions_and_Addins/Excel3_1_Add-in.htm

Once you have followed the installer and successfully installed the add-in, it should appear in your Excel ribbon.

What can I do with the Excel add-in?

At the time of publishing this post, the Excel add-in only supports pulling data from Anaplan, and doesn’t allow write back. Nevertheless, this doesn’t limit the add-ins use to just reporting.

You may want to pull data from Anaplan into an Excel template where values can be updated, saved as a CSV file and then uploaded back into Anaplan. This might be particularly handy if you’re planning to do bulk data changes.

The add-in also allows you to pivot and filter your data before you pull it into Excel. This means that while Anaplan continues to hold your full data set in a multi-dimensional format, you can still comfortable use the data in a two dimensional worksheet.

What limitations should I be aware of?

  • The Excel Add-in creates a one-way connection between Anaplan and Excel. You can view and edit Anaplan data in Excel, but you can’t push those changes back to Anaplan to update the source module.
  • To avoid overloading and crashing Excel, there is a limit of 1 million cells which can be exported.
  • The add-in doesn’t allow you to load up any subsidiary views.

Anaplan Connect API

Anaplan Connect enables a user with Administrator credentials to automate the running of Anaplan actions, avoiding the need for Anaplan GUI’s

What is Anaplan Connect?

Anaplan Connect is an API Client with a command-line interface that supports the following types of Anaplan actions:

  • Import
  • Export
  • Delete – eg. Delete from List using Selection to remove specific items from a list.
  • Process – a combination import/export actions

Why use Anaplan Connect?

  • Anaplan actions can be run outside the normal Anaplan GUI. Actions can be scheduled to run automatically at a defined interval.
  • Limiting the need for user import Dashboards and having a more direct feed to lists and module input values
  • Quicker and cheaper to set up than other integration tools

How to use Anaplan Connect

  1. The Anaplan Connect API can be downloaded from Anapedia.
  2. Create the required actions in your model.
  3. Create your batch (.bat) files to call your commands. Each batch file needs to contain:
    1. Workspace ID. This is unique and will not change.
    2. Model ID. This is unique to each model and will not change.
    3. Set the Operation Type, which includes:
      1. Anaplan Action. A specific Import or Export
      2. Credentials: The User Name and Password, unless you are using Certificate-based Authentication

When writing your Batch file there are good example files included within the Anaplan Connect Client install and the support pdf on Anapedia to assist you.

What can Anaplan Connect do?

  • Run an Import Action to import data direct to your Model from databases such as Sequel and Oracle or from simple text .txt or .csv files.
  • Delete data, List items using Selection.
  • Export module or List content to Excel workbooks, txt or csv files

Alternatives to Anaplan Connect

In addition to Anaplan Connect, there are several data integration connectors to support integration between Anaplan and third-party applications, including:

  • Dell Boomi
  • Informatica
  • MuleSoft
  • SnapLogic
  • Tableau

Anaplan also provides integration support with SalesForce.com, allowing any SalesForce.com user access to Anaplan from within SalesForce.com.

If you’re looking for more information regarding Anaplan integration approaches, please get in touch with us.

Dynamic Cell Access in Anaplan

Last week I was given a customer requirement which allowed me to use a new Anaplan feature – Dynamic Cell Access.

My remit was to limit a user to be able to map/configure only a single option from the specific list’s items and disable the other options.

For example, if the user needed to set a ‘Proficiency level’ for a particular job role, the functionality had to disable the other options once one option had been selected or leave all options open, writeable.

The Proficiency Level for an Area Manager for a particular role had to be set as one of the following, Expert, Practitioner or Foundation level and once one had been set to TRUE the other options/list items should be read only.

What is Dynamic Cell Access?

Dynamic Cell Access (DCA) is a powerful tool in an Anaplan Architects toolbox giving them the ability to set Read, Write or no access, to a cell, an entire row or column.

When I investigated further, I looked at the Anaplan’s tutorial app and was impressed by the variety of uses and how helpful it could be.

With DCA, a model builder can provide the ability to control a users’ read or write access from a dashboard, rather than the old and more conventional way of a Workspace Admin setting this access via the settings tab.

For example, if your model had a list of Regions but only certain users should see or have write access to particular data for specific Regions items.

By creating a module dimensioned by users and a particular list – in this case our Regions list – and using Boolean line items, we can build a dashboard that displays a nice user-friendly way of setting these permissions to Read or Write or not showing the cell values at all.

I was also very impressed after looking at the Learning App on the various uses from straightforward to the more complicated solutions, on how DCA can add value to a model’s design.

Some Examples…

Summaries and Subtotals

You can use DCA to apply different security at different levels of a hierarchy. Very powerful if you want to suppress total levels for certain users. For example, in a line which calculates employee salaries, you could use DCA to control a users’ ability to see individual salaries, and then also use DCA to hide the summary totals from them (as this might allows them to calculate the missing values!)

Controlling Workflow and User Experience

When Actual data for a year is mixed with Forecasting data, in the months where Actuals existed a planning adjustment line item could be made read only or invisible so nothing could be entered into these cells, eliminating the need for validations.

Another good use of DCA could be where we want to create a workflow. For example, approval might be required before the next steps of data entry can begin. Lines Items can be set to read only until approval had been granted.

The uses in fact are endless and I for one will be incorporating more Dynamic Cell Access into my future Models