Writing scripts for Analytics Exchange

Analytics scripts are not limited to running in Analytics only. By converting them into analytic scripts and packaging them in analysis apps, you can schedule and run scripts in Analytics Exchange too.

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 the sample data that comes with Analytics

  Note

  By default, the sample data projects are installed at C:\Users\username\Documents\ACL Data\Sample Data Files on your local file system.

Script execution on AX Server

Analytics Exchange uses the AX Engine to run analytic scripts on AX Server. Once a script is converted to an analytic script and imported onto the server, each analytic script execution represents a single job with its own results set.

When an analytic script runs on AX Server:

1. A new temporary folder is created on the server for the job.
2. The analytic script and any associated tables are exported into a new Analytics project in the temporary folder.
3. The AX Engine on the server opens the Analytics project, creates a new command log, and runs the analytic script.
4. Any files, tables, or logs that the analytic header specifies are retained in a Results folder that is associated with the specific job execution.
5. The temporary folder and files are deleted from the server.

Converting a script to an analytic script

You convert regular scripts into analytic scripts by including an analytic header and removing any Analytics functionality that does not run on AX Server.

The following Analytics script runs in Analytics and does the following:

1. Imports data from the Pay_scales.xlsx file from the ACL_Rockwood sample data using the IMPORT command.
2. Sorts the data on an expression using the SORT command.
3. Prompts the user for a number of records to extract using the ACCEPT command.
4. Extracts the number of records the user specifies from the sorted table using the EXTRACT command.

SET SAFETY OFF

COMMENT Import EXCEL data into project
IMPORT EXCEL TO pay_scales "C:\Users\username\Documents\acl_working\pay_scales.fil" FROM "C:\Users\username\Documents\ACL Data\Sample Data Files\ACL_Rockwood\Pay_scales.xlsx" TABLE "Payscales$" KEEPTITLE FIELD "DEPARTMENT" C WID 19 AS "" FIELD "POSITION" C WID 25 AS "" FIELD "MIN_HOURLY" N WID 6 DEC 2 AS "" FIELD "MAX_HOURLY" N WID 6 DEC 2 AS ""

COMMENT Open imported table and sort on difference between max and min hourly rate
OPEN pay_scales
SORT ON (MAX_HOURLY - MIN_HOURLY)  D  TO "pay_scales_sorted" OPEN

COMMENT ask user for number of records to extrat from sorted table
ACCEPT "Number of records to extract: " TO v_top

COMMENT extract top N records based on difference
EXTRACT FIELDS DEPARTMENT MAX_HOURLY MIN_HOURLY POSITION (MAX_HOURLY - MIN_HOURLY) AS "SALARY_D" IF RECNO() <= %v_top% TO "top_difference" OPEN

SET SAFETY ON

Adding the analytic header

Start converting the script into an analytic script by adding an analytic header at the top of the file.

An analytic header is a series of declarations that are enclosed inside a comment block at the start of a script. After you create and test a script in an Analytics project, include an analytic header to convert it to an analytic script than runs in Analytics Exchange.

Analytic headers must start with the ANALYTIC tag that identifies the script as an analytic script:

COMMENT
//ANALYTIC TopWageGap
  Imports an EXCEL file and extracts the top N records based on wage gap
END

For more information about analytic tags, see the Analytic headers and tags.

Changing the IMPORT command

Because the AX Engine executes analytic scripts in a temporary folder, change the IMPORT command's absolute paths to relative paths so that the AX Engine can locate files on the server:

COMMENT Import EXCEL data into project
IMPORT EXCEL TO pay_scales "pay_scales.fil" FROM "Pay_scales.xlsx" TABLE "Payscales$" KEEPTITLE FIELD "DEPARTMENT" C WID 19 AS "" FIELD "POSITION" C WID 25 AS "" FIELD "MIN_HOURLY" N WID 6 DEC 2 AS "" FIELD "MAX_HOURLY" N WID 6 DEC 2 AS ""

You must also add the FILE tag to the header to specify the input file Pay_scales.xlsx:

COMMENT
//ANALYTIC TopWageGap
  Imports an EXCEL file and extracts the top N records based on wage gap
//FILE Pay_scales.xlsx
END

The AX Engine looks for input files in the Related Files sub-folder in AX Server when the analytic script executes. For more information, see FILE.

Remove user interaction

Because the analytic script runs on the server and without user interaction, you must remove any commands that ask for user input during execution. Check for common Analytics script scenarios before importing the script into AX Server:

• interactive commands to accept user input that is normally entered in a dialog box, replace the following commands with input parameters: PAUSE, ACCEPT, DIALOG, PASSWORD
• overwriting files to overwrite files without confirmation, add SET SAFETY OFF at the beginning of the script, you can then reset this with SET SAFETY ON at the end of the script
• confirmation dialogs to avoid confirmation dialogs after the DELETE or RENAME command, use the OK parameter: DELETE table.fmt OK

In this case, you must remove the ACCEPT command and replace it with a parameter that is set in AX Client when the analytic script is scheduled or run:

COMMENT Open imported table and sort on difference between max and min hourly rate
OPEN pay_scales
SORT ON (MAX_HOURLY - MIN_HOURLY)  D  TO "pay_scales_sorted" OPEN

COMMENT extract top N records based on difference
EXTRACT FIELDS DEPARTMENT MAX_HOURLY MIN_HOURLY POSITION (MAX_HOURLY - MIN_HOURLY) AS "SALARY_D" IF RECNO() <= %v_top% TO "top_difference" OPEN

You must add the parameter to the analytic header using the PARAM tag so that you can set the value for v_top from AX Client:

COMMENT
//ANALYTIC TopWageGap
  Imports an EXCEL file and extracts the top N records based on wage gap
//FILE Pay_scales.xlsx
//PARAM v_top N v_top
  Number of records to extract
END

When you run or schedule the analytic script from AX Client, you are prompted for a numeric value that the AX Engine then uses wherever %v_top% appears in the analytic script. For more information, see PARAM.

Saving analytic script results

Just like you use analytic tags to define analytic inputs, you also use analytic tag declarations to define output tables and files that are saved to the Results sub-folder on AX Server. Only items that are specified using the RESULT tag in the analytic header are retained, while any other tables, files, or logs are discarded after the analytic job completes:

COMMENT
//ANALYTIC TopWageGap
  Imports an EXCEL file and extracts the top N records based on wage gap
//FILE Pay_scales.xlsx
//RESULT LOG
//RESULT TABLE top_difference
//PARAM v_top N v_top
  Number of records to extract
END

The log and top_difference table are now saved as results and available in AX Web Client after each execution of the analytic script on AX Server. For more information, see RESULT.

The complete analytic script

You can now import the complete analytic script along with the related MS Excel file into a folder on AX Server. When the analytic script runs, you specify the number of records to extract, and a table and log file are retained as results that are available from AX Web Client:

COMMENT
//ANALYTIC TopWageGap
  Imports an EXCEL file and extracts the top N records based on wage gap
//FILE Pay_scales.xlsx
//RESULT LOG
//RESULT TABLE top_difference
//PARAM v_top N v_top
  Number of records to extract
END

SET SAFETY OFF

COMMENT Import EXCEL data into project
IMPORT EXCEL TO pay_scales "pay_scales.fil" FROM "Pay_scales.xlsx" TABLE "Payscales$" KEEPTITLE FIELD "DEPARTMENT" C WID 19 AS "" FIELD "POSITION" C WID 25 AS "" FIELD "MIN_HOURLY" N WID 6 DEC 2 AS "" FIELD "MAX_HOURLY" N WID 6 DEC 2 AS ""

COMMENT Open imported table and sort on difference between max and min hourly rate
OPEN pay_scales
SORT ON (MAX_HOURLY - MIN_HOURLY)  D  TO "pay_scales_sorted" OPEN

COMMENT extract top N records based on difference
EXTRACT FIELDS DEPARTMENT MAX_HOURLY MIN_HOURLY POSITION (MAX_HOURLY - MIN_HOURLY) AS "SALARY_D" IF RECNO() <= %v_top% TO "top_difference" OPEN

SET SAFETY ON