Import and Automate Current Date Import to Anaplan Using Python

JaredDolich

Connect or Python

Summary

As a supply chain Anaplan data integration modeler, you might be asked by your business partner to display actuals for elapsed periods and forecasts for unelapsed periods. If you are not using the current period, which must be manually updated by a workspace administer, and you want to automate this process, you will need to regularly update a current date line item, preferably through automation. This article demonstrates how to do this in three steps:

1. How to set up Anaplan for an automated import.
2. How to create an Anaplan Connect or Python 3.0 script that creates a CSV file with the current date, uploads the file to Anaplan, and runs the import action.
3. How to put the Anaplan Connect or Python script on a scheduler such as Windows Scheduler.

Assumptions

This demonstration makes several assumptions: namely,

• You have either Anaplan Connect (and Java 8+) or Python 3.0, a free object-oriented programming language installed.
• You will have administrator rights on the machine.
• You are using Windows. Although, this is only an assumption if you intend to use Windows Scheduler.
• You have access to Anaplan, the workspace, model, and module.
• You are a workspace administrator.
• We will use v1.3 RESTful API set and will use Basic Authentication. In the Python code example, I have added the code that will allow for a CA certificate (preferred method).
• You know how to obtain the IDs to run the APIs. To obtain the ID and Names for the APIs you can use the Python best practice article located here.

Known Idea

The current date function was first posed by @vadim.rogatskin on 5/31/2019, and as of 12/30/2020 has 272 votes. This idea has been discussed numerous times with the Community how to best implement this. This article may serve as a workaround until this function is available.

User Story

As a retail merchandise analyst, I need to see that my sales actuals replace my forecasts every day. I get into the office early, around 6 a.m. so I need to see the results as soon as I arrive. I know that the sales processing is complete around 2 a.m. so my expectation is that I can see the results first thing in the morning.

Architecture | Model Schema
This solution will use the D.I.S.C.O. methodology and will require one flat list and one system module.

Step 1 | Anaplan Setup
The first step is to create a flat list, a system module, and an import action in Anaplan

Flat List | Administration
This list is used to dimensionalize the system values. Importing data into a dimensionless model is fine for a data hub, but model to model imports is very challenging without at least one dimension.

Action: Create a flat list called “Administration” and add one list item called “Value”. You can add a code “Value” if you prefer.

System Module | SYS00 Date Properties | SYS00 Administration
This module is used to hold the current date and any other system values. As a courtesy to the modelers that will support this application, I have also added an SYS00 Administration module that holds all the IDs and Names needed for the RESTful APIs.

Action: Create a “SYS00 Date Properties” Module that uses only the “Administration” list. For this module, create one line item called “Current Date”, formatted as a date, and leave the date blank.

Optional: Create “SYS00 Administration” Module that also uses only the “Administration” list. Create a line item for all the ID and Names you will need or the APIs. This also serves as a way to document your process.
Note: The numbers shown below have been changed to illustrate the module.

Import Action | Import Current Date
To automate the process using Python, we will need to first create the import action in Anaplan manually. Then we will need to obtain the ID’s.

Action: Create a CSV file template that looks like the following and import the file as “Everyone” into SYS00 Date Properties. Rename the import to “Import Current Date”. Then use Python or Postman to obtain the Import action ID and the Source System ID. Read @chase.hippen best practice article to learn how.

Mapping is straight-forward. Just use the right date format for your locale.

Step 2a | Anaplan Connect Setup (Recommended)

The preferred method to import the date is to use Anaplan Connect. With one simple script, you can generate the date and the import file and call the import action.

Action: Create Anaplan Connect Batch File| Import Current Date.bat
Create a batch file called "Import Current Date.bat". Use the following script to import the current date.

@echo on
rem This batch file collects the system date and creates an import text file for Anaplan
rem The module is assumed to be using a list called "Dimension" and the single list item is "Value".

rem ---------------------
rem Instantiate Variables
rem ---------------------
set AnaplanUser="jdolich@columbusconsulting.com"
set WorkspaceId="9999b013706ef346017116f7bb8d9999"
set ModelId="9999AD91575949F999C7AB9B65059999"
set myDate=%DATE:~4,10%

rem ---------------------------------------------------
rem Write Date to Import File "C:\temp\CurrentDate.txt"
rem ---------------------------------------------------
echo Dimension,Current Date > C:\temp\CurrentDate.txt
echo value,%myDate% >> C:\temp\CurrentDate.txt

rem ---------------------------------------------------
rem Import File to Anaplan
rem ---------------------------------------------------
set Operation=-service "https://api.anaplan.com" -auth "https://auth.anaplan.com" -file "CurrentDate.csv" -put "C:\Temp\CurrentDate.txt" -import "Import Current Date" -execute -output "C:\Users\jdoli\AnaplanConnect\Errors.txt"

rem *** End of settings - Do not edit below this line ***
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% -workspace %WorkspaceId% -model %ModelId% %Operation%
@echo %Command%
cmd /c %Command%
pause

 

Step 2b | Python Setup (Alternative)
For the Python part, we will have three steps: Create a CSV file, upload the file to Anaplan, run the import action in Anaplan. We will go ahead and create one Python script that runs all three processes, but we will separate out the IDs and the Authentication into separate files. So, we will create three Python files.

Action: Create Python File 1 | getGUIDs
Create a Python file called “getGUIDs.py”. This file will contain all the IDs for the API. Because we separated this, we can reuse it for other Python scripts. We need 6 variables defined (shown below). Note: The numbers have been changed for illustration purposes

# getGUIDs.py
# This script will hold the UIDs
# Written by Jared Dolich
# LinkedIn: https://www.linkedin.com/in/jareddolich/
# Anaplan: /profile/JaredDolich
# Retailitix March 2020
#

# =======================
# Workspaces
# =======================
# Jared Dolich Workspace
wGuid = '8a81b013706ef346017116f999999999'

# =======================
# Models
# =======================
# Jared Dolich Workspace (COE)
#--------------------------------------------------
# Retail Planning Demo
mGuid ='7EDFAD91575949F999C7AB9999999999'

# =======================
# Import Files
# Jared Dolich Workspace
# =======================
# Current Date
dataSource = '113000000001'
fileID = '112000000001'
fileName = 'Import Current Date'
csvFileName = 'C:/Users/jdoli/Box/Personal/Python/Anaplan/Retailitix/CurrentDate.csv'

Action: Create Python File 2 | getAuthentication
Create a Python file called “getAuthentication.py”. This file will hold the information necessary to authenticate the user for Anaplan. Note: this is for Basic Authentication. Ideally, you will use a CA certificate which I provide the code in the 3rd file. Separating this file allows you to secure it without worrying about anyone seeing your sensitive information.
Note: the password has been changed to illustrate the code

# getAuthentication.py
# Written by Jared Dolich
# LinkedIn: https://www.linkedin.com/in/jareddolich/
# Anaplan: /profile/JaredDolich
# Retailitix March 2020
#
# This Script will hold the authentication values

import base64

# Insert the Anaplan credentials
username = '[Your Anaplan Login Email]'
password = '[Your Password]'

userBA = 'Basic ' + str(base64.b64encode((f'{username}:{password}').encode('utf-8')).decode('utf-8'))

Action: Create Python File 3 | importCurrentDate

Create a Python file called “importCurrentDate.py”. This script will run three processes: Create a CSV file with the system date; Upload the file to Anaplan; Run the import action to move the data from the import file to the module we created in Step 1.

# This script creates a system date CSV file, uploads to Anaplan, then runs import action
# Written by Jared Dolich
# LinkedIn: https://www.linkedin.com/in/jareddolich/
# Anaplan: /profile/JaredDolich
# Retailitix March 2020
#
# 3 Steps
# 1. Create CSV file with system date using MM/DD/YYYY format (US format for Short Date)
# 2. Upload the CSV file to Anaplan
# 3. Run the Import action in Anaplan to update the module

# Import Python Libraries
import requests
import base64
import sys
import string
import os
import json
import time
import csv

# Import System Variables
from getAuthentication import userBA
from getGUIDs import wGuid, mGuid, fileID, fileName, dataSource, csvFileName

# Instantiate and Declare Variables
timeStr = time.strftime("%m/%d/%Y") #Uses MM/DD/YYYY Can also add H, M, and S

# File Data Variables - This is a 1 chunk import, so we don't need to use most of these
fileData = {
"id" : dataSource,
"name" : csvFileName,
"chunkCount" : 1,
"delimiter" : ",",
"encoding" : "",
"firstDataRow" : 2,
"format" : "",
"headerRow" : 1,
"separator" : ","
}

# Create the Upload URL and the Import URL
uploadURL = (f'https://api.anaplan.com/1/3/workspaces/{wGuid}/models/{mGuid}/' +
f'files/{fileData["id"]}')
importURL = (f'https://api.anaplan.com/1/3/workspaces/{wGuid}/models/{mGuid}/' +
f'imports/{fileID}/tasks')

# -----------------------------
# STEP 1 - CREATE THE CSV FILE
# -----------------------------
# Create the CSV File and add one row for that adds the current date
# Two Columns:
# Value - This is the single list item in the "Administration" List
# Current Date - This stores the current date value
with open(csvFileName, 'w', newline='') as f:
fieldNames = ['Dimension', 'Current Date']
theWriter = csv.DictWriter(f, fieldnames=fieldNames)

theWriter.writeheader()
theWriter.writerow({'Dimension' : 'Value', 'Current Date' : timeStr})

# ------------------------------------
# Authentication - Select a Method...
# ------------------------------------
# Use this authentication logic if you are using a CA Certificate
# cert = open('cert.pem').read()
# user = 'AnaplanCertificate ' + str(base64.b64encode((
# f'{username}:{cert}').encode('utf-8')).decode('utf-8'))

# Use this authenticcation logic if you are using Basic Authentication
putHeaders = {
'Authorization': userBA,
'Content-Type': 'application/octet-stream'
}
postHeaders = {
'Authorization': userBA,
'Content-Type': 'application/json'
}
# -------------------------------------
# STEP 2 - UPLOAD FILE TO ANAPLAN (PUT)
# -------------------------------------
# Opens the data file (filData['name'] by default) and encodes it to utf-8
dataFile = open(fileData['name'], 'r').read().encode('utf-8')
fileUpload = requests.put(uploadURL, headers=putHeaders, data=(dataFile))

# ---------------------------------
# STEP 3 - RUN IMPORT ACTION (POST)
# ---------------------------------
# Runs an import action
postImport = requests.post(importURL, headers=postHeaders, data=json.dumps({'localeName': 'en_US'}))

Step 3 | Automate the Process
In this step, I'll show you how to set up the Python script but it's the same for Anaplan Connect. For the last step, I have chosen to use Windows Scheduler and create a task. You can really use any scheduling tool that can run a Python script.

Action: Create a task in Windows Scheduler that runs at 4:00 AM every morning that runs the Python script. To accomplish this create a new task and set the properties the following way:

General – Make sure you run the task as an administrator.

Triggers – Set the trigger to run at 4:00 AM or whatever suits your business partner’s requirement.

Actions – For the actions, you’ll need to specify where the Python.exe is located and where your Python script is located. In the “add arguments” section, make sure you put your path and file name in double-quotes, as shown below.

Unit Testing

Well, we cannot really finish our development without testing our process.

Conclusion

As part of connected planning, you will be asked by your business partner to apply the logic that requires some type of knowledge about the current date. You can use the Anaplan RESTful API set to accomplish this goal.

From here, you can import the system date model to model, from the data hub to the spoke applications if necessary. Congratulations!

Sarahjayne.Estrada

Thanks for this contribution, Jared!!

 

One thing I've encountered while creating dates via batch script: each system may display date differently. While the above operation may work for some computers - you may need to tweak the following line: %DATE:~4,10% for your system. I've had issues with it before and had to mess around a bit - it doesn't happen often though! 

 

A great reference site if anyone is interested:

https://ss64.com/nt/

 

- SJ

 

 

JaredDolich

Excellent callout @Sarahjayne.Estrada I should note that I used Windows Command Prompt. Some use Power Shell and the formatting is different as well.

Khanna

Thanks a lot this is good!

vazqulu1_*39348

Thanks, Jared, this is great!