Creating a script to automate a business process
With growing globalization, evolving markets, and regulatory pressure to improve risk and control environments under rapidly changing business conditions, it is critical that organizations develop continuous monitoring programs. These continuous monitoring programs often require scripts to automate business processes. In this article, we discuss how to create a script in Analytics to automate a business process.
This article illustrates how to automate the review of procurement cards (Pcards) using an Analytics script. However, the same workflow can also be applied to a variety of other finance, IT, audit, compliance, and risk use cases.
Note
For the purposes of this solution guide, you don't need to understand scripting in detail. If you want to learn more, see Scripting in Analytics.
Where can I create scripts?
You can create scripts in Analytics.
The big picture
An Analytics project stores data analysis information, including tables and scripts.
A script imports, prepares, analyzes, and exports data to Results.
You can then take a script that you run ad hoc in Analytics, convert it to an analytic script (by adding an analytic header), and upload the script to Robots to run tasks on an automated basis.
Steps
Ready for a tour?
Let's take a closer look at these features in context.
Note
If you want to try out the workflow presented in this solution guide, you can download Excel data files (Pcard_Holders and Pcard_Transactions), and copy and paste the example script into Analytics. Alternatively, you can simply review the script to get a general sense of what it does.
1. Create a script
A script may be something as simple as running a command on a single field, or it may be substantial enough to perform the bulk of the work to achieve analysis objectives.
Tip
Typically, the best practice is to segregate tasks into separate scripts. For your convenience, we have presented data import, preparation, analysis, and export in a single script.
See an example
Example
Scenario
You are an internal auditor at Vandelay Industries, the country's premier latex manufacturer. To assist the employees at Vandelay in making small purchases of office supplies and computer equipment, management has implemented a Pcard program. The Pcard program requires an annual audit review to make sure that transactions comply with Vandelay policies.
You want to use a script to automate the key tasks involved in performing an analysis. You intend to export exception and transactional data to Results for reporting purposes.
Process
Scripting data import, preparation, and analysis
First, you create an Analytics project and add the two Excel data files to the same location where the Analytics project is saved. Then, you create a single script in Analytics called Pcard_Review that:
- imports the Excel data files required for analysis
- prepares data prior to analysis
- analyzes data and identifies Pcards that exceed their monthly limits
Finally, you run the script in Analytics, which produces a series of source, prepared, and result tables.
Scripting the export of data to Results
You go to Results, and create two data containers:
- Collection Procurement card review
- Analysis Procurement card audit 2018
From Analytics, you manually export:
- r_EmployeesOverMonthlyLimit to a new Employees over monthly limit table in Results
- p_Pcard_Transactions_2018 to a new Procurement card transactions 2018 table in Results
Finally, you copy the EXPORT syntax from the Analytics log into the script.
Note
You manually export first to get the correct scripting syntax. Once scripted, the data export is repeatable on an automated basis. The EXPORT syntax contains the specific Results table IDs you require to run the data export portion of the script. You can copy the entire script below, and replace the two instances of "XXXXXX@us" with the unique Results table IDs.
Result: Pcard review script
See the script
COMMENT
*************************************************
*** Script name: Pcard_Review
*** Description: This script does the following:
*** 1 - Imports Pcard_Holders and Pcard_Transactions information
*** 2 - Prepares the above tables for analysis
*** 3 - Identifies employees and cards that exceeded their monthly limits
***
*** Written by: Your name, Vandelay - Today's date
*** Version: 1.0
*************************************************
END
SET SAFETY OFF
SET SESSION
CLOSE PRIMARY
CLOSE SECONDARY
COMMENT
**********************
** This portion of the script imports the Pcard_Holders table and Pcards_Transactions table required for the Pcard review.
**********************
END
SET FOLDER /Data/_1_Source_tables
COMMENT*** Import the Pcard_Transactions data
IMPORT EXCEL TO s_Pcard_Transactions "Pcard_Transactions.fil" FROM "Pcard_Transactions.xlsx" TABLE "Pcard_Transactions$" CHARMAX 150 KEEPTITLE
COMMENT*** Import the Pcard_Holders data
IMPORT EXCEL TO s_Pcard_Holders "s_Pcard_Holders.fil" FROM "Pcard_Holders.xlsx" TABLE "Pcard_Holders$" CHARMAX 150 KEEPTITLE
COMMENT
**********************
** This portion of the script prepares data for analysis.
**********************
END
SET FOLDER /Data/_2_Prepared_tables
COMMENT*** Harmonize the card_number fields in the s_Pcard_Holders and s_Pcard_Transactions tables so you can use it as a key field in a later Join
OPEN s_Pcard_Holders
DEFINE FIELD c_card_number COMPUTED STRING(card_number, 16)
OPEN s_Pcard_Transactions
DEFINE FIELD c_card_number COMPUTED STRING(card_number, 16)
COMMENT*** Extract dates in 2018 in the s_Pcard_Transactions table and send them to p_Pcard_Transactions_2018
OPEN s_Pcard_Transactions
EXTRACT FIELDS ALL IF BETWEEN(transaction_date, `20180101` , `20181231 235959`) TO "p_Pcard_Transactions_2018"
OPEN p_Pcard_Transactions_2018
COMMENT*** Extract required fields in s_Pcard_Holders and send them to p_Pcard_Holders
OPEN s_Pcard_Holders
EXTRACT FIELDS employee_number employee_name email_address c_card_number limit_transaction limit_monthly TO "p_Pcard_Holders"
OPEN p_Pcard_Holders
COMMENT
**********************
** This portion of the script analyzes data to identify whether Procurement cards are exceeding their monthly limits. If cards are exceeding monthly limits it means controls are failing and follow-up and remediation is required.
**********************
END
SET FOLDER /Data/_3_Results_tables
COMMENT*** Create a computed field to determine the month each transaction occurred in so you can identify cards that exceed their monthly limit
OPEN p_Pcard_Transactions_2018
DEFINE FIELD c_month COMPUTED ZONED(MONTH(transaction_date),2)
COMMENT*** Group transactions by card and month to calculate the monthly total for each card.
OPEN p_Pcard_Transactions_2018
SUMMARIZE ON c_card_number c_month SUBTOTAL transaction_amount TO "t_SumTransByCardMonth.fil" OPEN PRESORT
COMMENT*** Determine the monthly limit for each card by relating the t_SumTransByCardMonth
OPEN p_Pcard_Holders
INDEX ON c_card_number TO "Pcard_Holders_on_card_number"
OPEN t_SumTransByCardMonth
DEFINE RELATION c_card_number WITH p_Pcard_Holders INDEX Pcard_Holders_on_card_number
COMMENT*** Filter and extract issues where the monthly limit was exceeded
OPEN t_SumTransByCardMonth
SET FILTER TO transaction_amount > p_Pcard_Holders.limit_monthly
EXTRACT FIELDS ALL TO "r_CardsOverMonthlyLimit.fil"
OPEN r_CardsOverMonthlyLimit
COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit.
OPEN r_CardsOverMonthlyLimit
OPEN p_Pcard_Holders SECONDARY
JOIN PKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORT SECSORT
CLOSE SECONDARY
OPEN "r_EmployeesOverMonthlyLimit"
COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit.
OPEN r_CardsOverMonthlyLimit
OPEN p_Pcard_Holders SECONDARY
JOIN PKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORT SECSORT
CLOSE SECONDARY
OPEN "r_EmployeesOverMonthlyLimit"
COMMENT
**********************
** This portion of the script exports data to the Results app.
**********************
END
COMMENT*** Export records of employees who are over their monthly limit to Results
OPEN r_EmployeesOverMonthlyLimit
EXPORT FIELDS c_month c_card_number COUNT email_address employee_name employee_number limit_monthly transaction_amount ACLGRC TO "XXXXXX@us" OVERWRITE
COMMENT*** Export Pcard Transactions from 2018 to Results
OPEN p_Pcard_Transactions_2018
EXPORT FIELDS transaction_number transaction_date transaction_amount source_currency merchant_number merchant_name merchant_location MCC country_code c_month c_card_number billing_currency account_id ACLGRC TO "XXXXXX@us" OVERWRITE
2. Add an analytic header to the script
An analytic header is a series of tags enclosed in a comment block at the start of an Analytics script. An analytic header is required for any analytic script that you intend to run in Robots.
Tags are declarative commands inside the analytic header that define inputs, outputs, and instructions for an analytic script. Tags allow the analytic script to run unattended, either immediately, or at a scheduled time.
Tip
An analytic header provides instructions for how to run the analytic script in Robots. Some tags are used by Robots to receive information and others specify what should be produced by the analytic script. For example, you may need to tell Robots which file it needs for an import or which results table to make available to end users.
See an example
Example
Scenario
Currently, you run the Analytics script on an ad hoc basis. You want to program the script to run on an automated basis. This will free you up from the time-consuming task of manually running the analysis.
Process
You convert the regular script to an analytic script by adding an analytic header to Pcard_Review that includes the following tags:
- FILE specifies an Excel file that provides input for an analytic script running in Robots
- PASSWORD creates a password input parameter for the analytic script
- RESULT LOG ensures a log is generated every time the analytic script runs
- RESULT TABLE ensures a results table is available to end users when the analytic script runs successfully in Robots
Since the analytic header now contains the PASSWORD tag, you also add the PASSWORD syntax to the portion of the script that exports data to Results.
Note
The password input parameter and the password parameter in the EXPORT command are linked by using the same numerical identifier:
//PASSWORD 1 HighBond Access Token
.
.
.
EXPORT ... PASSWORD 1
.
.
.
EXPORT ... PASSWORD 1
Result: Pcard review script with analytic header and updated PASSWORD syntax
See the script
COMMENT
//ANALYTIC TYPE Analysis Pcard_Review
This analysis identifies all cards and employees that exceeded their monthly Pcard limit and exports exceptions and transactions to Results.
//FILE Pcard_Holders.xlsx
//FILE Pcard_Transactions.xlsx
//PASSWORD 1 HighBond Access Token
//RESULT TABLE r_CardsOverMonthlyLimit
//RESULT LOG
END
COMMENT
*************************************************
*** Script name: Pcard_Review
*** Description: This script does the following:
*** 1 - Imports Pcard_Holders and Pcard_Transactions information
*** 2 - Prepares the above tables for analysis
*** 3 - Identifies employees and cards that exceeded their monthly limits
***
*** Written by: Your name, Vandelay - Today's date
*** Version: 1.0
*************************************************
END
SET SAFETY OFF
SET SESSION
CLOSE PRIMARY
CLOSE SECONDARY
COMMENT
**********************
** This portion of the script imports the Pcard_Holders table and Pcards_Transactions table required for the Pcard review.
**********************
END
SET FOLDER /Data/_1_Source_tables
COMMENT*** Import the Pcard_Transactions data
IMPORT EXCEL TO s_Pcard_Transactions "Pcard_Transactions.fil" FROM "Pcard_Transactions.xlsx" TABLE "Pcard_Transactions$" CHARMAX 150 KEEPTITLE
COMMENT*** Import the Pcard_Holders data
IMPORT EXCEL TO s_Pcard_Holders "s_Pcard_Holders.fil" FROM "Pcard_Holders.xlsx" TABLE "Pcard_Holders$" CHARMAX 150 KEEPTITLE
COMMENT
**********************
** This portion of the script prepares data for analysis.
**********************
END
SET FOLDER /Data/_2_Prepared_tables
COMMENT*** Harmonize the card_number fields in the s_Pcard_Holders and s_Pcard_Transactions tables so you can use it as a key field in a later Join
OPEN s_Pcard_Holders
DEFINE FIELD c_card_number COMPUTED STRING(card_number, 16)
OPEN s_Pcard_Transactions
DEFINE FIELD c_card_number COMPUTED STRING(card_number, 16)
COMMENT*** Extract dates in 2018 in the s_Pcard_Transactions table and send them to p_Pcard_Transactions_2018
OPEN s_Pcard_Transactions
EXTRACT FIELDS ALL IF BETWEEN(transaction_date, `20180101` , `20181231 235959`) TO "p_Pcard_Transactions_2018"
OPEN p_Pcard_Transactions_2018
COMMENT*** Extract required fields in s_Pcard_Holders and send them to p_Pcard_Holders
OPEN s_Pcard_Holders
EXTRACT FIELDS employee_number employee_name email_address c_card_number limit_transaction limit_monthly TO "p_Pcard_Holders"
OPEN p_Pcard_Holders
COMMENT
**********************
** This portion of the script analyzes data to identify whether Procurement cards are exceeding their monthly limits. If cards are exceeding monthly limits it means controls are failing and follow-up and remediation is required.
**********************
END
SET FOLDER /Data/_3_Results_tables
COMMENT*** Create a computed field to determine the month each transaction occurred in so you can identify cards that exceed their monthly limit
OPEN p_Pcard_Transactions_2018
DEFINE FIELD c_month COMPUTED ZONED(MONTH(transaction_date),2)
COMMENT*** Group transactions by card and month to calculate the monthly total for each card.
OPEN p_Pcard_Transactions_2018
SUMMARIZE ON c_card_number c_month SUBTOTAL transaction_amount TO "t_SumTransByCardMonth.fil" OPEN PRESORT
COMMENT*** Determine the monthly limit for each card by relating the t_SumTransByCardMonth
OPEN p_Pcard_Holders
INDEX ON c_card_number TO "Pcard_Holders_on_card_number"
OPEN t_SumTransByCardMonth
DEFINE RELATION c_card_number WITH p_Pcard_Holders INDEX Pcard_Holders_on_card_number
COMMENT*** Filter and extract issues where the monthly limit was exceeded
OPEN t_SumTransByCardMonth
SET FILTER TO transaction_amount > p_Pcard_Holders.limit_monthly
EXTRACT FIELDS ALL TO "r_CardsOverMonthlyLimit.fil"
OPEN r_CardsOverMonthlyLimit
COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit.
OPEN r_CardsOverMonthlyLimit
OPEN p_Pcard_Holders SECONDARY
JOIN PKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORT SECSORT
CLOSE SECONDARY
OPEN "r_EmployeesOverMonthlyLimit"
COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit.
OPEN r_CardsOverMonthlyLimit
OPEN p_Pcard_Holders SECONDARY
JOIN PKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORT SECSORT
CLOSE SECONDARY
OPEN "r_EmployeesOverMonthlyLimit"
COMMENT
**********************
** This portion of the script exports data to the Results app.
**********************
END
COMMENT*** Export records of employees who are over their monthly limit to Results
OPEN r_EmployeesOverMonthlyLimit
EXPORT FIELDS c_month c_card_number COUNT email_address employee_name employee_number limit_monthly transaction_amount ACLGRC TO "XXXXXX@us" OVERWRITE PASSWORD 1
COMMENT*** Export Pcard Transactions from 2018 to Results
OPEN p_Pcard_Transactions_2018
EXPORT FIELDS transaction_number transaction_date transaction_amount source_currency merchant_number merchant_name merchant_location MCC country_code c_month c_card_number billing_currency account_id ACLGRC TO "XXXXXX@us" OVERWRITE PASSWORD 1
3. Upload the script to Robots
Once you create an analytic script in Analytics, you can upload it to a robot in the Robots app to test and configure the task automation that you need. The action of uploading scripts from Analytics to Robots is called committing scripts.
Tip
You can use tools in Analytics to validate the analytic header syntax to ensure that it is correct. Perform the validation before committing scripts to Robots so that the analytic script does not fail when it runs.
See an example
Example
Scenario
You want to run the analytic script on an automated basis in the Robots app. You need to upload the script to the Robots app to get started.
Process
From the Analytics main menu, you select File > Commit Scripts. You select your organization, specify the name Pcard Review 2018 for the new robot, and click Create.
Finally, you enter the following commit message and click OK:
This is the initial upload of Pcard_Review, a script which is used to automate the review of Pcards.
Result
You have successfully uploaded the analytic script to a robot.
What's next?
Learn how to run the analytic script on an automated basis
The Robots app is used to automate repetitive tasks using scripts built in Analytics, and free up time and resources for you and your team to perform work of a more strategic nature.
To find out more, see Scheduling an analysis.
Enroll in an Academy course
Continue to build your knowledge on the concepts introduced in this article by taking the ACL 270 learning path.
Academy is Diligent's online training resource center. Academy courses are included at no extra cost for any user with a Diligent One subscription. For more information, see Academy.
