Retail

Join

Automate Current Date Import Using Python

JaredDolich
JaredDolich
in Groups

Import and Automate Current Date Import to Anaplan Using Python

By Jared Dolich

Retail Planning Enthusiast

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 a 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 Python script on a scheduler such as Windows Scheduler

Heineken_HorizontalLine.PNG

Assumptions
This demonstration makes several assumptions: namely,

  • You have 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 @chase.hippen excellent Python best practice article located here

Heineken_HorizontalLine.PNG

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.

Heineken_HorizontalLine.PNG

User Story
As a retail merchandise analyst, I need to see 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 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 DISCO methodology, outlined by @DavidSmith and will require one flat list and one system module.

ModelSchema.png

Heineken_HorizontalLine.PNG

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.

Step1 Create SYS00.png

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 a 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.

Step1 Create SYS00 Optional.png
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.

Step1 Create SYS00 Optional View.png

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.

Step 1 Template.png

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

Step 1 Template Mapping 1.png

Step 1 Template Mapping 2.png

Heineken_HorizontalLine.PNG

Step 2 | Python Setup
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'}))

Heineken_HorizontalLine.PNG

Step 3 | Automate the Process
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.
Step 3 General.png
Triggers – Set the trigger to run at 4:00 AM or whatever suits your business partner’s requirement.
Step 3 Triggers.png
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.

Step 3 Mapping.png

Heineken_HorizontalLine.PNG

Unit Testing
Well, we cannot really finish our development without testing our process.
CurrentDateUnitTest.gif
Whew! It worked.

Heineken_HorizontalLine.PNG

Conclusion
As part of connected planning you will be asked by your business partner to apply 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!

Comments

Categories