Daily Batch Process for Demand Forecasting - Roadmap and Import Actions

In this data workflow, a roadmap is prepared for batch processes related to demand forecasting. It includes steps for uploading and importing data into Anaplan, SQL sources, EBS processes, and various file imports. The script names and processes are outlined with caution against renaming data hub processes, emphasizing the use of Anaplan processes as wrappers for import actions.

• Data workflow
• Demand forecasting
• Batch processes
• Data imports
• Anaplan

jkemp Follow

Uploaded on Sep 18, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Daily Batch Process for Daily Batch Process for Demand Forecasting Demand Forecasting 2019-01-16

2. Uploads and Imports Prepare a roadmap of your batch processes files, queries, import actions / processes in Anaplan File / SQL Source EBS Process Name BAT-01a Product Hierarcy Import Action Name 1.1 LST Row ID Product Data as CSV2L 1.2 MOD EBS Product Hierarchy CSV2M 1.10 DAT09: Product Properties from EBS Product Properties Import into 'DAT09: Product Properties Data Upload' SQL 1.11 ROWID Product Properties Data CSV2L 2.1 LST ROWID Fulfillment Location Data CSV2L 2.2 MOD ROWID Fulfillment Location Data CSV2M 3.1 LST ROWID Sales Location Data as CSV2L 3.2 MOD Sales Location Hierarchy as CSV2M 3.2.1 LST ROWID Store Hierarchy Data s CSV2L 3.2.2 MOD DATXX: Store Hierarchy Data as CSV2M 4.1 LST 2016 COSC Order Header CSV2L 4.2 MOD 2016 COSC Order Header Data CSV2M Import Action Description Import into ROWID Product Hierarchy Data Import into 'DAT01: Product Hierarchy' File / SQL SQL Source File / SQL Name ProductHierarchy BAT-01b Product Properties ProductProperties Import into ROWID Product Properties Data Import into ROWID Fulfillment Location Data Import into 'DAT02: Fulfillment Location Hierarchy' Import into ROWID Sales Location Data Import into 'DAT03: Sales Location Hierarchy' Import into ROWID Store Hierarchy Data Import into 'DATXX: Store Hierarchy Data' Import into Transaction Sales Header Import into 'DAT04: Sales Header Transaction Data' BAT-02 Fulfillment Locations SQL SBL HubAndMarket FILE MANUAL BAT-03 Store Hierarchy Data FILE BI str_chanl_hier.csv BAT-04 COSC Order Header FILE BI ordr_hdr_lvl_data_cosc.csv BAT-05 Trans Sales Header Wholesale 5.1 LST Transaction Sales Header Wholesale CSV2L 5.2 MOD Transaction Sales Header Wholesale Data CSV2M Import into Transaction Sales Header Import into 'DAT04: Sales Header Transaction Data' FILE BI ordr_hdr_lvl_data_whlsale.csv BAT-06 Order Header Primary DMA 6.1 LST 2016 Order Header Primary DMA CSV2L 6.2 MOD 2016 Order Header Primary DMA CSV2M Import into Transaction Sales Header Import into 'DAT04: Sales Header Transaction Data' Import into Transaction Sales Line Import into 'DAT06.5: Transaction Sales Lines' Import into ROWID EBS Ship Date Data Import into 'DATXX: EBS Ship Data' Import into ROWID Internal Sales Data Import into 'DATXX: Internal Sales Data' FILE BI ordr_hdr_prmry_dma.csv BAT-07 Transaction Sales Lines 7.1 LST Trans Sales Line CSV2L FILE BI lne_itm_trnsctn_lns.csv 7.2 MOD DAT6.5 Transaction Sales Data CSV2M 8.1 LST ROWID EBS Ship Date Jan16-Apr16 CSV2L 8.2 MOD DATXX: EBS Ship Date Jan16-Apr16 CSV2M 9.1 LST ROWID Internal Sales Order Data CSV2L 9.2 MOD DATXX: Internal Sales Data CSV2M BAT-08 Ship Date SQL EBS soScheduleShipDate BAT-09 Internal Sales FILE BI intrnl_sls_ordr_trnsctns.csv

3. Script name BAT-01a Product Hierarchy.bat Anaplan Action (Process) name BAT-01a Product Hierarchy Consistent Naming Reject log name BAT-01a Product Hierarchy.log AC Process log name BAT-01a Product Hierarchy 2019- 01-16 0800.log

4. Dont Rename Data Hub Processes! Use Anaplan Processes as wrappers for import actions. Import job scripts have the name of the Anaplan Process hardcoded in them. It is okay to rename Import Actions. Just don t change Process names.

5. Pre-import processing Test for file creation process completion, notify Anaplan admin if upstream job was delayed Copy files to an upload folder. Avoids a potential conflict when file processing is delayed. Set current date Other Tasks Post-import processing Cancelled order purge Delete files from upload folder Email notification of process completion Kill hanging java.exe sessions after 30 minutes (Short-term workaround for AC 1.4 bug)

6. Anaplan Connect versions (as of Jan 2019) 1.3.3.5 Most stable, but missing 2 key features. No CA Certificate authentication No jdbc.properties 1.4 Hangs in one specific type of reject Bug: cmd and java.exe threads left hanging on specific duplicate record reject Workaround: Kill scheduled jobs after 30 minutes and kill all java.exe after batch is done. 1.4.1 Due out Feb 2019. Should be best of all worlds. Okay to have some of each: 1.3.3.5 for BI File uploads and 1.4 for SQL queries Recommend consolidating to 1.4.1 when released.

7. A service account like svcanaplan@customer.com is used to login to Anaplan in all scheduled batch jobs. This account has an Outlook email box necessary for setting up the account in Anaplan and for password resets. Anaplan Connect does NOT support SSO. This account s Anaplan password is maintained in Anaplan and will expire every 90 days. Anaplan Service Account SET AN OUTLOOK REMINDER TO UPDATE PASSWORD APRIL 1 AND REPEATING AT 90 DAY INTERVALS. Store info like login credentials and connect strings for jdbc in a GlobalVariables.bat file on Windows server single place to update when it expires. Future state: CA Certificate authentication with certificate that expires once a year. More secure, less maintenance. Requires AC 1.4+ only (wait for next release due in Feb).

8. Anaplan Connect Scripts 1. Set variables unique to this job. File, SQL query, Action, Log Name 2. Get connection info from Globals.bat 3. If SQL, run CreateJDBC.Properties.bat 4. Assemble the OPERATION= line Connection, upload, action, and reject log flags 5. Run AnaplanClient.bat, record the chatter to process log file 6. Check ERRORLEVEL and log for errors, send email if trouble 7. Exit

9. CreateJDBCProperties.bat Custom script collects connection, credentials, and SQL query information from other files and assembles a short-lived plaintext file for Anaplan Connect s needs. Globals.bat contains connection and credentials. Intention is to encrypt that file to avoid plaintext password storage. SQLQueries.txt contains the SQL statements* used by jobs: ::QueryName Select ABC, DEF From Table Where GHI<0 ::end * No double quotes allowed in SQLQueries.txt, other special characters (<>=&|) are ok.

10. CreateJDBCProperties info online A best practices forum post in Community.Anaplan.com contains full details. https://community.anaplan.com/t5/Best- Practices/Windows-cmd-shell-script-for- dynamically-generating-JDBC

11. Logging Import action rejects (if any) are saved to file in \\<servername>\Anaplan\ErrorLogs\ Process chatter while AnaplanClient is interacting with Oracle and Anaplan is captured on the Windows server at D:\Anaplan\ProcessLogs\ Process logs are named with <job name> <date> <start time>.log History in Anaplan

12. Rejected Record Logs Same information as found on Details tab after a manual import into Anaplan. No rejects or warnings = no file. Reject files are overwritten daily. No retention. Could add Date Time to filename and retain for 30 days, same as Process Logs.

13. Process Logs Connection SQL query Upload Import summary stats To List To Module Process logs are retained for 30 days to allow some forensics on past trouble or performance changes.

14. Windows Task Scheduler on Windows server Simple scheduler, limited features. Most data centers have a more robust solution that monitors errors and notifies support team, branches on conditions, handles job dependencies Setting jobs up can be a significant project. Two primary jobs start at 8 am File import jobs (AC 1.3.3.5) SQL jobs (AC 1.4) These could be merged after AC 1.4.1 is released with bug fix. Cleanup task at 8:30 to delete copies of data files and kill java.exe threads (bug workaround) Scheduling