Register

Retail Knowledge Base

Sort by:
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: How to set up Anaplan for an automated import 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 How to put the Python script on a scheduler such as Windows Scheduler 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 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 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. 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 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. 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 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: https://community.anaplan.com/t5/user/viewprofilepage/user-id/22354 # 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: https://community.anaplan.com/t5/user/viewprofilepage/user-id/22354 # 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: https://community.anaplan.com/t5/user/viewprofilepage/user-id/22354 # 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 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. Whew! It worked. 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!
View full article
Retail Anaplanners! New functionality came out today 9/26/2020 that makes ordering lists much easier. Release Notes: https://community.anaplan.com/t5/Releases/What-s-New-September-26-2020/ba-p/85429 Documentation: https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Actions/Order-list.html   A game changer for many use cases. I will show you an example plus a way to reorder a list subset. I'm sure many of you will have a more creative way to order the list subset so please share! First you have to create a module that uses the list, and only the list, that  you want to reorder. Add a line item that contains the order you wat - can be text or general number.  Create a Order List Action that uses that line item for that list. You cannot select a list subset. It must be a module that uses the complete list.   Run the action. Success! For List Subsets I created a module that uses the list subset and added a Boolean line item set to TRUE. In the module that uses the complete list I lookup the Boolean from the module using the subset list, as shown.   Blueprint:     Create the Action using the Final Order to Use Line Item   The module using the list subset is now reordered.  
View full article
How to detect retail trends in Anaplan
View full article
This is a response I made to a question posed by @CommunityMember128799 in this post here. I thought it would be helpful to have it in our retail group knowledge base.   With regard to retail specifically, there are several themes you're going to want to take as it relates to FP&A and pretty much all finance related applications in Anaplan. I'm not going to mention the questions to ask for a specific FP&A use case (like how to consolidate the GL, or generate financial documents like income, balance sheet, cash flow and shareholder equity - assuming you already know that) but rather the nuanced nature of retail that will enable the processes in Anaplan: Note: I'm going to avoid some of the real estate planning as that is pretty generic to the industry. Inventory Valuation Accounting Financing Calendars | Time Master Data Inventory Valuation Inventory is the working capital for the retailer. While this theme can be lumped with Accounting there are some very specific questions related to inventory that need to be asked. You have to know which valuation method the retailer is using so you can utilize the historical transaction data. Typically, Finance will obtain forecasts from the merchandising team but the inventory valuations don't always align, nor do the calendars (discussed in another theme). To enable Anaplan, you have to make sure the inventory valuation is converted correctly in a common way. Do you use cost averaging, standard costing, or retail inventory method to value your inventory? If LIFO is used how is this reconciled with all the other inventory planning? How does the merchandising team value inventory?  How are you projecting the inventory receipts? What are the conditions that move inventory from unowned to owned? (owned means the inventory is the retailer responsibility and is moved from an open order to the stock ledger). Does the merchandising team provide Finance their projections using the same inventory accounting used by Finance? What differences are there and how/where is this reconciliation managed? Spreadsheet, 3rd Party Tool? Does the company utilize direct ship to stores or customers, or does all inventory get received in a distribution center? Accounting Explain how the stock ledger is maintained, how often is it updated, and what line items are needed, and how are they defined? Typical line items: Receipts, In-Transit, Net Sales, Markdowns (if RIM is used), Sales Discounts (if cost averaging is used), Shrinkage, Ending Inventory, Gross Margin Are imports utilized? How and where are all the cost components maintained? How is the currency exchange managed? Is there any hedging and how does that factor into the use case? What are all the sales channels used? Typical is wholesale, retail, and e-commerce but there's also leased departments (store in a store), concession, affiliate, outlet, close-out, franchise, and pop-ups. Are there any special accounting considerations needed? For example, the lease agreements with lessee's. How are store opening and store closing costs accounted for? How are the accruals maintained? Accounting rules used? Sensitivity to the data - who is able to see it? Do you need to consider comparable stores? Meaning a comparison between stores open last year that are open this year? Financing Explain the capital budgeting process? Rules of thumb for projection horizons? (this is needed for NPV calculations) Cash Management (needed for cash flow analysis). Collections, Disbursements, Banking, Investments, Currency Gain/Loss What are your typical Cash Sources: Net Sales, AR collections, credit card payments and interest, Layaway, Leased Depts, Vendor rebates, Investment income, property sales What are your typical Cash Uses: Inventory Purchases (once owned), Payroll, Rent, Lease settlements, Taxes, Insurance, Utility, Transportation, Advertising, Professional Services (you, most likely), Benefits, Bonuses, Capital expense, debt service, shareholder dividends Royalties? Can go both ways. Depends on who owns the brand. Do you need to use scenario planning - portfolio management? Is there an assignment of the initiative to an individual sponsor? How are the initiatives tracked to show performance and do you need any consolidation, rollups? What happens if the initiative is multi-year? Calendars | Time What is the calendar used by Finance? What is the calendar used by other planning teams upon which you rely? For example, merchandising will typically use a 445 calendar and Finance will use a Gregorian Calendar.  How are these calendars reconciled? If 445 is use, how is the 53rd week calculated and in which month is the extra week added? How is the 53rd week comp'ed? Is there any need for time zone conversions? Stores / transactions can be in different time zones. Daily light savings? How are holiday shifts handled? For example, Easter or Christmas? Any special considerations on how the Pandemic of 2020 should be handled? Master Data Describe the product hierarchy needed? Describe the location hierarchy needed? Describe the vendor hierarchy needed? Where are these hierarchies maintained and how reliable is the source? How frequently are they updated? Where are all the properties of the hierarchies maintained? Do any of them need to be maintained in Anaplan? What other hierarchies are needed? Do you need placeholder line items (e.g., a new cost center that hasn't been added yet but will be needed for planning)? What is the expectation in Anaplan with regard to a current hierarchy? Who has the authority to Add, update, delete (change) the hierarchy in Anaplan? How should discrepancies be handled in Anaplan? Additional Reading: Textbooks I highly recommend Retail Accounting and Financial Control, 5th Edition, Robert M. Zimmerman, John Wiley & Sons, 1990. Retail Auditing, A Practitioners Guide, Leo F. Griffin, John Wiley & Sons, 1998
View full article