Automating data exports to HighBond Results

Automate the way you get data into HighBond Results using Analytics Exchange. First, use a scheduled analytic to identify exceptions and then extract them to a results table that you export to a Data Analytic in Results.

What do you need?

To complete this quick start, you need the following:

• an installation of AX Client with a working connection to an AX Server instance
• an installation of Analytics with the same edition (Unicode or non-Unicode) as your Analytics Exchange installation
• access to Results and the unique identifier of the Data Analytic you want to export to

  Tip

  You can obtain the Data Analytic's unique identifier from the URL of the data analytic in Results or from the Export dialog of Analytics.

• an understanding of how to import and schedule analytic scripts on AX Server

  For more information, see Importing a project and scheduling an analytic.

The scenario

Your company needs to setup an automated process for identifying employees with a reported salary exceeding 80000 for the reporting period. Once they are identified, these employee records must be exported to Results for further analysis.

Each period, you are provided with a Microsoft Excel file containing employee records, and you are able to access this file on the C drive of the AX Server machine.

To setup the automation, you:

1. Create a destination folder in the Working directory.
2. Create a new Analytics project for import into AX Server.
3. Add an analytic script that:
   1. Imports the employee data from the Excel file.
   2. Extracts records where the salary exceeds 80000.
   3. Exports and appends the table of exceptions to a Data Analytic in Results.
4. Import the Analytics project into AX Server and schedule the analytic script to run once per period.

Each time the script runs on AX Server, the exceptions that it identifies are appended to the Data Analytic you specify in the EXPORT command of the script.

The analytic script logic

Add the analytic header

The analytic header allows your script to run on AX Server and identifies the data table and results table:

COMMENT
//ANALYTIC GRC export test 
 verify GRC export on AX Server
//DATA employees
//RESULT TABLE High_Earners
//PASSWORD 1 GRC Token:			
END

Setup an ODBC import for the Excel file

Use the ACCESSDATA command to import data from an Excel source file into a table in AX Server. In this example, the file C:/source/Employees_May_with_CCN.xls is imported into the employees table:

COM Import data into table using ODBC connection
ACCESSDATA32 ODBC "Dsn" NAME "Excel Files" TO "employees.FIL" CHARMAX 50 MEMOMAX 100 
 SOURCE( dbq=C:\source\Employees_May_with_CCN.xls;driverid=1046;maxbuffersize=2048;pagetimeout=5)
 SQL_QUERY( 
    SELECT
        `Employees_May_with_CCN_`.`First_Name` AS `First_Name`,
        `Employees_May_with_CCN_`.`Last_Name` AS `Last_Name`,
        `Employees_May_with_CCN_`.`CardNum` AS `CardNum`,
        `Employees_May_with_CCN_`.`EmpNo` AS `EmpNo`,
        `Employees_May_with_CCN_`.`HireDate` AS `HireDate`,
        `Employees_May_with_CCN_`.`Salary` AS `Salary`,
        `Employees_May_with_CCN_`.`Bonus_2002` AS `Bonus_2002` 
    FROM
        `Employees_May_with_CCN$` `Employees_May_with_CCN_`
 ) END_QUERY 

Extract exceptions

Use EXTRACT to identify employees with a salary greater than 80000 and add those records to the results table:

COM extract high earning individuals
OPEN employees
EXTRACT RECORD TO "High_Earners.fil" IF Salary > 80000

COM remove employees table
CLOSE employees
DELETE FORMAT employees OK
DELETE employees.fil OK

Export records to Results

Using the EXPORT command, specify the fields to export from the results table and the target Data Analytic in Results. In this example, the command exports all fields to the Data Analytic 173358:

COM export results to Results GRC module
OPEN High_Earners
EXPORT FIELDS Bonus_2002 CardNum EmpNo First_Name HireDate Last_Name Salary ACLGRC PASSWORD 1 TO "173358" APPEND
CLOSE High_Earners

Running the script

To run the script, the user must provide an access token for HighBond as part of the analytic input set. The token authenticates the user and verifies the permissions required to export data to Results. You can obtain the access token in Analytics by clicking Tools > HighBond Access Token.

Every time the analytic script executes on AX Server, additional records are exported and appended to the specified Data Analytic. Any visualizations or metrics associated with the Data Analytic update to reflect the new records and the process can continue in an automated fashion every period.