Automating data exports to Results Manager

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

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 ACL Analytics with the same encoding type as your Analytics Exchange installation
• access to Results Manager 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 Manager or from the Export dialog of ACL 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 Manager 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 ACL 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 Results Manager Data Analytic.
4. Import the ACL 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 Results Manager 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
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 Manager

Using the EXPORT command, specify the fields to export from the results table and the target Data Analytic in Results Manager. 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 TO "173358" APPEND
CLOSE High_Earners

After the script runs

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