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.
... View more
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.
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?
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.
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.
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.
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.
... View more
You can use the formula below in a line item to generate a sequential number. This works best when you only have one List in your module and the numbering follows the order of that list. RANK(1, DESCENDING, SEQUENTIAL) Your solution to go from Source to Target seems odd since it uses an arbitrary sequential number. This is not guaranteed to maintain the original order relative to the loaded data since the primary List could change.
... View more
Hi Josh, The export option "Tabular Multiple Column" generates a fairly good form of transactional data for downstream systems. You have the option to "Omit Empty Rows" so sparsity of data is handled. You can also use your own filtering logic instead of "Omit Empty Rows" if you have something more specific in mind.
... View more
Importing fact data into "flat modules" in a Data Hub is challenging when your data source is unable to provide a single unique Key to act as a List Item. That work ends up taking place between the extracted data and the import into Anaplan and oftentimes involves concatenating Key columns into a single column, sometimes manually (e.g. Excel). And there will be times when the length of the concatenated Keys can exceed the maximum supported for a unique Item Name or Code. It would be great if during import, Anaplan can let a model builder choose the Key fields and Anaplan can automatically generate something like an MD5 Hash on those Key fields that can be imported into the List first and then the fact data into the Module afterwards.
... View more
I did build something to fix the stated problem scenario. I didn't want to create something that relied on a fixed number of parent lists since that's a maintenance problem and requires List specific logic (and we all know P&L hierarchies are ragged). Instead I built the P&L hierarchy within a single list and created a formula to compute the Sign based on IF/THEN logic that used ISANCESTOR to reduce the number of IF cases. I was able to specify the Sign at all levels of the P&L hierarchy and apply that sign to correctly display the sub-total members. Thanks again to David Smith for the idea - a brilliant use of Anaplan functionality.
... View more
Hi Harish, I know that the Ratio line item is there strictly to control how the the Value items are displayed at sub-total level (since the true sub-totals are accurate as per the sign-flip). I had my doubts about how this would work in all cases and I did create a scenario where it doesn't work the way it should. The problem scenario is if the parent total is supposed to be a natural negative (e.g. Expense Total) - if there are any contituent children that is a natural sign positive the parent is assigned natural sign positive. So when I created a child under expense that was a natural sign positive, Expense Total "Value" showed up as a negative number (when it still should be positive). I think the ideal solution is to be able to tag parent members as -1 or 1 explicitly, rather than trying to calculate it with the Ratio Summary.
... View more