使用脚本来自动执行业务流程

随着日益发展的全球化、不断变化的市场和在迅速变化的业务条件下改善风险和控制环境的监管压力，组织制定持续监控计划至关重要。这些持续监控计划通常要求脚本自动执行业务流程。在本文中，我们将讨论如何在 Analytics 中创建脚本以自动化业务流程。

本文演示如何使用 Analytics 脚本自动审查采购卡 (Pcards)。不过，相同的工作流程也可以应用于各种其他财务、IT、审计、合规和风险用例。

说明

对于本解决方案指南的目的，您不需要详细了解脚本。如果您想了解更多信息，请参阅 Analytics 中的脚本。

可以在哪里创建脚本？

可以在 Analytics 中创建脚本。

全盘视角

Analytics 项目存储数据分析信息，包括表和脚本。

脚本导入、准备、分析数据并将其导出到结果。

然后，您可以获取在 Analytics 中临时运行的脚本，将其转换为分析脚本（通过添加分析标头），然后将该脚本上载到机器人应用程序以自动运行任务。

步骤

准备好进行了解了吗？

让我们在情境中仔细了解上述功能。

说明

如果要尝试本解决方案指南中提供的工作流程，可以下载 Excel 数据文件（Pcard_Holders 和 Pcard_Transactions），并将示例脚本复制并粘贴到 Analytics 中。或者，您可以简单地查看脚本以大致了解它的作用。

1.创建脚本

简单的脚本可能只是对单个域运行命令，复杂的脚本可能执行大量工作以实现分析目标。

提示

通常，最佳做法是将任务分离为单独的脚本。为方便起见，我们在单个脚本中提供了数据导入、准备、分析和导出功能。

示例

场景

您是国内首屈一指的乳胶制造商 Vandelay Industries 的内部审计师。为了帮助 Vandelay 员工购买小额办公用品和电脑设备，管理层实施了 Pcard 计划。Pcard 计划需要进行年度审计，以确保交易符合 Vandelay 政策。

您想使用脚本来自动执行进行分析所涉及的关键任务。您打算将异常和交易数据导出到结果应用程序以进行报告。

流程

脚本数据导入、准备和分析

首先，创建一个 Analytics 项目，将这两个 Excel 数据文件添加到保存 Analytics 项目的同一位置。然后，在 Analytics 中创建一个名为 Pcard_Review 的脚本，该脚本：

  • 导入分析所需的 Excel 数据文件
  • 在分析之前准备数据
  • 分析数据并识别超出其月度限额的 Pcard

最后，在 Analytics 中运行脚本，该脚本生成一系列源表、准备表和结果表。

编写脚本，将数据导出到结果应用程序

转到结果应用程序，并创建两个数据容器：

  • 集合采购卡审查
  • 分析2018 年采购卡审计

从 Analytics 中，您手动导出：

  • r_EmployeesOverMonthlyLimit 到结果应用程序中的新表超过每月限额的员工
  • p_Pcard_Transactions_2018 到结果应用程序中的新表2018 年采购卡交易

最后，将 EXPORT 语法从 Analytics 日志复制到脚本中。

说明

首先手动导出以获得正确的脚本语法。编写脚本后，可以自动重复数据导出。EXPORT 语法包含运行脚本的数据导出部分所需的特定结果应用程序表 ID。您可以复制下面的整个脚本，并使用唯一的结果应用程序表 ID 替换 "XXXXXX@us" 的两个实例。

结果：Pcard 审查脚本

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.向脚本中添加分析标头

分析标头是围在 Analytics 脚本开头处注释块中的一系列标记。对于您打算在机器人应用程序中运行的任何分析脚本，解析标头都是必需的。

标签是分析标头内的声明性命令，它们定义分析脚本的输入、输出和指令。标签可让分析脚本在无人值守的情况下立即或在预定时间运行。

提示

分析标头提供有关如何在机器人应用程序中运行分析脚本的说明。一些标签被机器人应用程序用来接收信息，另一些则指定分析脚本应该生成什么。例如，您可能需要告诉机器人应用程序需要哪个文件进行导入或哪些结果表可供最终用户使用。

示例

场景

目前，您临时运行 Analytics 脚本。您想对脚本进行编程，使其自动运行。这将使您从手动运行分析的耗时任务中解脱出来。

流程

通过向 Pcard_Review 添加包含以下标签的分析标头，您将这个常规脚本转换为分析脚本：

  • FILE指定一个 Excel 文件，为在机器人应用程序中运行的分析脚本提供输入
  • PASSWORD为分析脚本创建密码输入参数
  • RESULT LOG确保每次运行分析脚本时都会生成日志
  • RESULT TABLE分析脚本在机器人应用程序中成功运行时，确保最终用户可以使用结果表

由于分析标头现在包含 PASSWORD 标签，因此您还要将 PASSWORD 语法添加到将数据导出到结果应用程序的脚本部分。

说明

密码输入参数和 EXPORT 命令中的密码参数通过使用相同的数值标识符进行链接：

//PASSWORD 1 Diligent One Access Token
.
.
.
EXPORT ... PASSWORD 1
.
.
.
EXPORT ... PASSWORD 1

结果：带分析标头和更新了 PASSWORD 语法的 Pcard 审查脚本

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 Diligent One 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.将脚本上传到机器人应用程序

在 Analytics 中创建分析脚本后，您可以将脚本上载到机器人应用程序中的机器人，以便测试和配置所需的任务自动化。从 Analytics 向机器人应用程序上传脚本的操作被称为提交脚本。

提示

您可以使用 Analytics 中的工具验证分析标头的语法，以确保其正确性。在将脚本提交至机器人应用程序之前执行验证，以便分析脚本不会在运行时失败。

示例

场景

您希望在机器人应用程序中自动运行分析脚本。您需要将脚本上传到机器人应用程序以便开始。

流程

从 Analytics 主菜单中选择文件 > 提交脚本。选择组织，为新机器人指定名称 Pcard Review 2018，然后单击创建。

最后，输入以下提交消息，然后单击确定：

Pcard_Review 的初始上传，这是一个用于自动审查 Pcards 的脚本。

结果

您已成功将分析脚本上传到机器人。

后续步骤 

了解如何自动运行分析脚本

通过机器人应用程序，可使用在 Analytics 中生成的脚本自动执行重复性任务，为您和您的团队腾出时间和资源来执行更具战略性的工作。

要了解更多信息，请参阅计划分析。

注册学校课程

通过学习 ACL 270 课程内容，继续深入了解本文介绍的概念。

学校是 Diligent 的在线培训资源中心。对于所有拥有 Diligent One 订阅的用户，Academy 课程均无须额外付费。有关详细信息，请参阅 Academy。