There are four cornerstones in The Anaplan Way—data being one of them. We all know that we should spend at least 1/3 of the project time on data alone because this is the cornerstone that we have no control over, and it needs to be thoroughly investigated and understood. One of the important things to consider about data is the format of the time that the source system has. This article will explain the different formats Anaplan can accept in an import and the ones which it cannot and should be avoided.
Data in the source system can be at any granularity i.e., daily, weekly, monthly, quarterly, semiannually, or annually. However, most of the planning happens either at the weekly level or at a monthly level. Therefore, this article will cover the import options for these two granularities only.
Import Mapping for Months
If the intention is to get the data loaded into the system at month level granularity, here are the four options that you get within an Import.
1. Dates—This format can be used when the source file contains dates. The question is what is the format of the dates in the source system? Based on that there will be a further selection in the import mapping screen.
i) Pattern with Separators: There are four choices given to us, but the number of formats that come within this bucket is vast.
Y-M-D like 2020/06/01; 2020-06-01; 20/06/01; 20-06-01; 2020, Jun 1; 20, Jun 1; 2020/Jun 1; 20/Jun 01 etc.
Y-D-M like 2020/01/06; 2020-01-06; 20/01/06; 20-01-06; 2020, 1 Jun; 20, 1 Jun; 2020/1 Jun; 20/01 Jun etc.
D-M-Y like 01/06/2020; 01-06-2020; 01/06/20; 01-06-20; 1 Jun, 2020, 1 Jun, 20; 1 Jun/2020; 1 Jun/20 etc.
M-D-Y like 06/01/2020; 06-01-2020; 06/01/20; 06-01-20; Jun 01, 2020; Jun, 1, 20; Jun 1/2020; Jun 1/20 etc.
Here, separators can be any non-alphanumeric character making the combinations go higher. Also, the 0’s in Month and Day needn’t have 0 prefixed with their numbers. In summary:
Y can represent 1, 2 or 4 digit year number
M can represent 1 or 2 digits or even Name
D represents 1 or 2 digits Day Number
ii) Fixed Position Patterns: There are eight choices that are available to us. Here we don’t have greater flexibility to play around with the formats. Here the month digits have to be exactly what is defined in the import
YYYYMMDD like 20200601
YYMMDD like 200601
YYYYDDMM like 20200106
YYDDMM like 20200106
DDMMYYYY like 01062020
DDMMYY like 010620
MMDDYYYY like 06012020
MMDDYY like 060120
iii) Custom Fixed-Position Pattern: You can have at least eight choices from Fixed Position Patterns here as well. If you wish to ignore any character within the format, “?” symbol is introduced on that location of the character.
For example, 06/01/FY20 date format in the source file can be Imported as MM?DD???YY. For every character to be ignored you introduce the “?” Symbol
2. Periods—When the source file contains periods, based on the format of the periods in the source file, selections need to be made in the imports.
i) Pattern with Separators: Two different options under this pattern are given.
Y-M like 2020/06; 2020/Jun; 2020-06; 2020-Jun; 20/06; 20-06; 20/6; 20-6 etc.
M-Y like 06/2020; Jun/2020; 06-2020; Jun-2020; 06/20; 06-20; 6/20; 6-20 etc.
ii) Fixed Position Patterns: Four different options under this pattern are given.
YYYYMM like 202006
YYMM like 2006
MMYYYY like 062020
MMYY like 0620
iii) Custom Fixed-Position Pattern: You can have at least four choices from Fixed Position Patterns here as well. For example, 06FY20 can be imported as MM??YY.
3. Periods in Specific Year—This is the least used Import because the mapping needs to be changed every year, and this type of import can only load the data for one year. If you look at the example below, when you click on this option “Patterns with Separators” gets highlighted with one option in the box “M”. This M represents a one-or-two-digit month number or even month name. Once selected you will see “Year number represents”, and other fields under “Options” get activated.
Calendar year will respect the year that is selected in specific year and will Import the values into that particular year. For example, in this case, 2019.
There can be scenarios when the fiscal year cuts across two calendar months. For example, Indian companies start their fiscal year in April and end in March the following Year. In this case “Start of the Fiscal Year” option and “End of the Fiscal Year” option comes in handy as the import will look at the main page of the model and will decide which year should be updated.
If the model calendar looks like April 19 - March 20, the start of the fiscal year will load the data into FY19 and end of the fiscal year will load it into FY20.
Note:“Options” section also gets activated when selecting dates or periods option but practically, this becomes quite irrelevant there because we will always have years coming thru in the source file.
4. Match Names—当时间格式the source system matches exactly with Anaplan timescale you choose this option. Anaplan’s default timescale is “MMM YY”. For Example, Jun 20.
Import Mapping for Weeks
1. Dates—There is no difference between dates for months and weeks. All the combinations remain the same.
2. Periods—When the source file contains periods:
i) Pattern with separators: Two options are given.
Y-W like 2020/06; 2020-06; 20/06; 20-06; 2020/6; 2020-6; 20/6; 20-6 etc.
W-Y like 06/2020; 06-2020; 06/20; 06-20; 6/2020; 6-2020; 6/20; 6-20 etc.
Y represents 1, 2 or 4 digit Year number
W represents 1 or 2 digit Week number
ii) Fixed Position Patterns: Four options are given.
YYYYWW like 202006
YYWW like 2006
WWYYYY like 062020
WWYY like 0620
iii) Custom Fixed-Position Pattern:
FY20 06 can be imported as “??YY?WW” mapping.
3. Periods in Specific Year—Logic remains the same as it was for months; the only difference is instead of months you have weeks “W” in import mapping.
4. Match Names—当时间格式the source system matches exactly with Anaplan timescale you choose this option. Anaplan’s default timescale is WEEK<>Number<>FYYY like “Week 06 FY20”.
Now there can be a requirement where you have to change the week format from “Number” to “Week Commencing” or “Week Ending”. Does it mean that you need to change the source files too? The answer is no because Anaplan in the backend still remembers what is week 1 of the current year based on the selection that you make on the time page.
