How to Clear Data in Anaplan
Overview
I came to Anaplan from different database technologies in support of Enterprise Performance Management (EPM), and it was a common activity to ensure that the forecast or plan periods were clear before planning began. These other technologies allowed you to use a scripted language (e.g. SQL) to be very specific about the data to clear. You do not have the option of a scripted language in Anaplan, but Anaplan still gives you the ability to perform the same action. This article will go through all the different ways you can clear data, from the basic to the programmatic.
Ad Hoc Clears
In a View (Dashboard or Module) you can simply use the delete key (Mac) or backspace key (Win) on a single cell. This is good for any line item format (text, number, date, boolean, list, etc.). If you select multiple cells to clear (see below screenshot), you have to use a different flavor of delete: fn-delete (Mac) or DEL (Win).
Highlighting multiple cells for clearing.
Once you’ve cleared out cells, you can copy these cells (command-C (Mac), ctrl-C (Win)) and paste them (command-V (Mac), ctrl-V (Win)) over a highlighted range, which can be larger than the original copied cells. You can also use the Copy-Across or Copy-Down feature in Anaplan, using Edit | Copy Across or Down, or if you are in a module using the menu icons to do the same things. To limit the range of Copy Across/Down, you can filter the rows and columns you have visible on the screen.
Module or Dashboard View Copy Across, Down
Module or Dashboard View Copy Across
The above methods work well when the scope of data to clear is relatively small and it's truly ad hoc. But what happens if you have to clear a very large number of cells or regularly repeat this activity?
Clearing Data by Adding and Deleting a Formula
I will state this up front; DO NOT use this method unless you are doing so in purely test mode. The reason for this caveat is that a formula change is a structural change, and in an environment with ALM (Application Lifecycle Management), this structural change will be applied to a downstream model, potentially wiping out all data for a line item that users have entered into.
But if this model is purely for testing, you can simply add a formula that effectively clears the data for a line item along every dimension/list in the module. This has to be done for each line item you want to clear, and the formula can be different for each line item format. Below are the formulas you would use and then remove after clearing the data.
Text, Date, List, Time: BLANK
Number: 0 (number 0)
Boolean: FALSE
Clearing the contents of a Line Item using a formula
Clearing Options in Imports
People do not normally use all the clearing options available in an Import definition. Without changing any of the clear options the Import will simply replace only the cells represented by the incoming data. But what happens when the incoming data represents only a subset of the existing data—all the non-represented cells will retain whatever value they have which could lead to incorrect results if those also needed to be cleared.
Default Clear Options – Mapping Tab of Import (top right)
If you pick one or the other and do not change anything else, the results would be identical—you are only updating cells represented in the incoming data. For Option 1 (Clear target prior to import), you have to go to the other tabs (dimensions) in the import definition to specify other clear options.
Time options
Line Item options
The default options are shown above (matched items), which corresponds to only updating the cells related to the incoming data.
If you choose “All Items”, then all items related to the dimension will be cleared prior to loading the data. Remember, this is specific to each dimension in your module, so if you qualify ALL in one dimension but not the others, it will only clear the ALL dimension related to the incoming data for the other dimensions. If you specify ALL items for ALL dimensions, then you will effectively clear the entire module (of input data) prior to loading data.
If you choose “All Mapped Items” (for line items), this will limit the clear to items that are defined in the target mapping, leaving other items untouched. This comes in handy when you are importing into a module from multiple sources, each with their own set of mapped data.
All of these clear options are nice but still somewhat limited. For one, you cannot use any kind of logic to clear data as it is based primarily on the incoming data. And in another, this clear only works when you are importing data. What about the data that is pure user input?
Programmatic Clearing
Now we get to the good stuff: automatic targeted clearing of cells in a module. The foundation for this is the fact that you can use Anaplan as a source for importing into Anaplan. In this case, you can make the source module be the same as the target module but with line items that will act to “clear” other line items. In addition, an Anaplan source can include filtering logic, across any dimension.
Setting Up
- In the module you want to clear, create one or more line items you want to use to clear one or more target line items. Each source can only map to one target.
- You can create specific clearing modules if you think the structure can be used to clear multiple modules.
- Create a Saved View that includes your Clearing line items. Include any filtering logic needed for your application.
- In the case of specific clearing modules, you can create filtering logic here too, referring to elements in the target.
- Create an import into this module using your saved view as a source. You can even leave the clear options at their default since you have explicitly created the logic you need for clearing data defined in the saved view.
Example Module to Clear: Time + Line Items
I want to clear all input data based on Current Period logic.
😎
😎😎
Sample Module to clear
Create the “Clearing” line items as below. Pay close attention to the format—it is set to “No Data”. “No Data” can clear any line item format. And being “No Data”, these additional line items are effectively not taking up any memory.
Line items for clearing
Create a Filtering line item to limit what gets cleared. In our example above, we limit it to Forecast periods, identified by the Current Period in Time Settings (below screenshot). Note: the above filtering formula is here strictly to simplify the example so that you can easily build it yourself. In an actual application, you would have a Time Settings module that centralizes this type of logic which can then be used in other time-dimensioned modules. Refer to the Planual guidelines, section 2.02-03, for more information.
Time Settings—Current Period
Create a saved view that includes the filter that will clear your target. Note: the filter line item is included for visibility. This can be simply ignored in the import definition. Notice too that only the Forecast Periods are included in this view.
Saved view with filter
Import into your target module – select your source saved view
Define your Import mappings
Map the clearing Line Items
Your import is now saved as an Action that you can run every forecast period (as controlled by the Current Period).
There is one drawback of using “No Data”—you do not get any confirmation about the number of affected items for the line items being updated (image below). If that is important you can define Clear Actions that use actual Line Item Formats, rather than “No Data”. Just be aware though, a line item that returns BLANK will still produce the same results as “No Data”—there is an example later.
Run completion
Results of running the import
You can use formatted clear items, but you can still make it efficient. Remove all the dimensionality except for where you need to apply logic, and make sure Summary is set to None. In this example, you need to keep Time. Remember, this module is only 2 dimensions.
Remove all the dimensionality except for where you need to apply logic, and make sure Summary is set to None
Define your Saved View and Import definition as before
Run your Import. Notice that only the Number and Boolean sources return the item counts. The formatted sources with BLANK as a formula act the same as No Data.
Number and Boolean sources return the item counts
How About Deleting List Items?
It is true that deleting list items effectively removes all the data related to those items, but it is a blunt instrument that has a lot of overhead and breaks the Planual rule (section 1.05-10). A few drawbacks related to deleting: deleting list items impacts all modules that use that list, and you have no control over what parts of other dimensions are cleared (for example, you have no ability to limit what periods are cleared). In the end, you have to re-add those list items back in before you start loading data again. In short, avoid using Delete List items just for the purpose of clearing out data in a production environment.
In Closing
I hope that gives you a good idea of the types of clearing you can perform in Anaplan. It’s not as singular as writing a script (both a plus and minus) but you still have all the flexibility you need for targeted clearing.
Comments
-
Great guide Paul, thank you!
Also there is one nice trick in case you use import's option to clear all items prior import. You can set a kind of dummy action that would move single cell value (say true value from a system module) into the target module's line item you want to clear up. Target line item would be in the subsidiary view with no dimensions as well. This way you limit your traffic in the model and improve action's performance since only a single boolean is being moved around. However it still works only if you want to clean the whole module. In some cases it may be useful - for example if you have just a single 'input like' line item.
1 -
Hi Piotr - that's a nice trick for a quick bulk clear that's not necessarily tied to particular data import. I have done something similar by having a source View with a filter that doesn't return any data - the target module is cleared but nothing loads.
2 -
Great Article!
Deleting the list items - As you rightly mentioned it is really not required to wipe out the data. Instead we way better ways of achieving that objective. However I would like to state that whenever we try to delete list items followings things happen-
1.All the the related modules' data will be wiped out even though the intention was to delete only part of it.
2. System has to generate the log everytime the delete action is run which is time consuming
3. System has to redo all the calculations again whenever the updation happens, which again is time consuming and affects the performance of the model
.
2 -
Great article. So glad this is part of the L3 certification. It's a process we all use in practically all applications. So well done!
0 -
Thanks for the article @paul.rosal. I will leverage your post to share some learnings as well.
We expireinced some data loss issues when using module to module import in the Target module. Mostly because of not fully understanding the default settings of "Clear target prior to import" in the settings of the import action.
Module to module import is a two step process:
1. Clear the target
2. Import values from source to targetThe issue we are seeing in all our imports, is that Step 1 "Clear the target" is clearing more values than we intent to import. After a few hours of troubleshooting we now fully understand the behavior, which is best explained by your example where you set source line items to No Data.
When Source Line Items are set to No Data, no values are actually imported, hence the dialog message after the import shows that nothing is updated.
The reason values are cleared, is because Anaplan finds Matches between Source view and the Target module and Clears them as part of the Step 1 of the module to module import.
This is also true in your other example, when we create a custom action to import Blanks (for lists and time) or '0' (for numbers), Anaplan first Clears values for us, and then imports Blanks and '0' where we ask it to import. The dialog message after the import will show us how many items were imported, but it won't tell us how many were cleared as part of Step 1. This creates confusion when we see more data is cleared, than we actually intent to set to Blank or '0'.
There is an article explaining this behavior, however, I thought the above learnings will still be valuable to share with the group.
0 -
For those still using List Properties (which you should avoid as per the Planual), No Data or Blank Values to clear property values will only work on Text formatted properties. They will not clear other formats.
1 -
Thanks Paul for sharing !
Very useful in a well explained way, i found very easy to follow.
One reflect to me, if need to clear line items, one extra line item (no data) could do the job by ticking on all items ( non-time dinmentsions). This method works as for now. Hopefully it may be some of use when someone in future visited this article.
0
