Migrate data from AX to Robots

Important

Only proceed with migrating the data if you have completed the previous stages of the migration. For more information, see Recreate your AX repository structure in Robots and Migrate scripts from AX to Robots.

If your AX repository contains either of the types of data listed below, you may need to migrate the data to Robots. Depending on the nature of the data, using a script to import a fresh instance of the data to Robots may be quicker and easier, and more appropriate, than physically migrating an existing table from AX.

If your AX repository does not contain any data that needs to be migrated or recreated, you can move to the next stage in the migration: Recreate automated AX processes in Robots.

Type of data AX location
Analytics data tables Data subfolder
Input files such as Excel and *.csv Related Files subfolder

Recreating or migrating Analytics data tables

This general guideline can help you decide whether to recreate Analytics data tables in Robots or migrate them from AX:

  • Recreate Data tables that are overwritten every time an analytic script runs can be recreated in Robots rather than migrated from AX.
  • Migrate Data tables that have records appended to them every time an analytic script runs should be migrated.

Note

When you migrate data tables, all the data tables in a Data folder are migrated as a group. So if a Data folder contains some tables that are overwritten and some tables that are appended to, all the tables are migrated.

Create the robots first

Whether you recreate or migrate Analytics data tables, the first thing you need to do is make sure that you have created the appropriate robots to contain the tables in Robots. You may already have created the required robots, but if not, you need to create them now. For more information, see Create robots to structure content and automated processes.

How you intend to use the data tables in Robots dictates how you should structure individual robots to contain them.

Intended use of Analytics data table Robot structure
Store and protect a single copy of data imported from a data source that can then be linked to by multiple automated processes in other robots Create a robot with a name such as "Source Data". Create multiple robots if you want separate containers for different types of source data. For example, "SAP Source Data", "Oracle Source Data", and so on.
Provide input for subsequent analytic scripts as part of the same automated process, or several automated processes Create a robot to contain all the scripts and data tables required for the automated process or processes. For example, "A_01_AP analytic tests".

Recreating data tables

Analytics data tables that are overwritten every time an analytic script runs should be recreated in Robots rather than migrated from AX. Migrating tables in this situation is wasted effort because the tables will be overwritten as soon as the scripts that create them run in Robots.

For example, if you import a monthly transaction table from your organization's SAP or Oracle installation, and overwrite the previous month's table, the easiest approach is to start importing the monthly table to Robots using the same analytic script that you use to import the table to AX.

Likewise, any data tables that are overwritten each time automated analysis runs should be recreated by running the analysis in Robots rather than migrated.

Options for recreating data tables in Robots

You can use any of the following options to recreate data tables in Robots:

  • Run an entire automated process
  • Run only a portion of an automated process
  • Use a set-up script to initialize data tables

Run an entire automated process

After migrating the scripts associated with an automated process, create a task in the robot that contains the scripts and run the task ad hoc. If the task runs successfully, all data tables output by the scripts are recreated in the robot containing the task.

This option runs all the analysis and creates all the output results associated with the automated process. If you want to recreate only the data tables at this point, use one of the other options described below.

Run only a portion of an automated process

If you have organized an automated process into separate portions, create a task that runs only those scripts that create data tables. For example, run only the scripts in the import portion of an automated process, or in the import and prepare portions.

If the task runs successfully, the data tables containing imported data, or imported and prepared data, are recreated in the robot containing the task. You avoid creating output results.

Use a set-up script to initialize data tables

If you have created a centralized location for imported data such as a "Source Data" robot, you can create a set-up analytic script to perform a one-time initialization of the data tables in the robot.

In Analytics, create a single analytic script that gathers all the ACLScript commands that you use to import data, such as ACCESSDATA and IMPORT ODBC. Commit the script to the "Source Data" robot and create a task to run the script. You can run the task ad hoc, or schedule it to run overnight. If the task runs successfully, the data tables containing imported data are recreated in the "Source Data" robot.

Note

If a single import in the set-up script fails, the task fails and discontinues any further processing. If you have a number of imports that you want to perform, you can divide them among several set-up scripts and create separate tasks for each script. This approach prevents the failure of a single import from disrupting the other imports.

Migrating data tables

Analytics data tables that have records appended to them every time an analytic script runs should be migrated from AX to Robots rather than recreated in Robots. Migrating tables in this situation is required to preserve the accumulating, historical data that the tables contain.

For example, if you import a monthly transaction table from your organization's SAP or Oracle installation, and append the records to an accumulating yearly table, the yearly table needs to be migrated to Robots.

Likewise, any data tables that have records appended each time automated analysis runs should be migrated to Robots rather than recreated.

The process for migrating data tables to Robots

Diligent Support has made scripts available to assist with migrating historical data tables from AX to Robots. The first script exports the data tables from AX to an intermediary location on your network. The second script imports the data tables from the intermediary location to Robots.

The migration scripts are necessary because, unlike AX, you cannot directly upload Analytics tables to Robots through the user interface.

Note

The scripts work at the individual AX folder level and the individual robot level. You need to run the scripts repeatedly for each folder containing data tables that you want to export, and for each robot that you want to import data tables into.

Create an intermediary location on your network

Create a temporary folder for storing the historical data tables as you migrate them from AX to Robots. You can create the folder on your own computer, but if the amount of data you need to move is large, you will probably find it more convenient to use a network location for the folder.

The folder must be accessible to the service accounts that run the AX and the Robots Agent Windows services on their respective servers. Both accounts need Full control permissions for the folder. The names of the Windows services are:

  • ACL Analytics Exchange Service
  • Robots Agent

Enable the EXECUTE command in AX

If the EXECUTE command is not enabled for AX, you need to enable it.

Caution

The EXECUTE command is disabled by default in AX. If you normally have the command disabled, you should disable it again after you have completed the migration of the historical data tables.

  1. In a browser, sign in to the AX Server Configuration web application.

    By default, the AX Server Configuration web application is located at https://<ax_server_name>/aclconfig

  2. Under Server > Settings, click Yes for Allow execute command?
  3. Click Update Server Settings.

Organize the contents of the Data folder

Important

Think ahead to the tables that you want in the destination robot in Robots. After you have organized the Data folder, only tables that will be contained in the same destination robot should remain.

Before you run the export script in AX, make sure that the Data folder from which you are exporting contains only tables that you want in the same destination robot in Robots.

The export and import scripts use a bulk process to copy tables and associated data files as a group between a Data folder, the temporary storage folder, and a single destination robot in Robots. You cannot select individual tables for export or import.

If all the tables in a Data folder align with a single robot, no additional organization is required. If there is not exact alignment between the Data folder tables and a single robot, you need to create the alignment before you export.

How to create alignment between a Data folder and a robot

You can align tables between a Data folder and a robot using either of these approaches, depending on the situation:

  • Create a staging Data folder Create a new collection and folder for staging data tables before exporting. Copy tables aligned with a single destination robot to the staging Data folder and export them from there.

    Use this approach if you intend to run AX and Robots in parallel for a period of time and you do not want to disrupt the contents of an existing Data folder.

  • Edit an existing Data folder Delete or move non-aligned tables from an existing Data folder before exporting. Delete or move any tables that are not aligned with a single destination robot.

    Caution

    Only use this approach if the required modifications to the existing Data folder do not disrupt any ongoing automated process in AX.

How to handle different table and layout types in a Data folder

Important

Before you run the export script in AX, review the information below regarding the different types of tables and layouts that can appear in a Data folder. If you are editing an existing Data folder, delete or move tables or layouts as required.

Table or layout type Data folder requirements

standalone table

 Include only standalone tables that should be contained in the destination robot.

shared table

Include only shared tables that should be contained in the destination robot.

AX shared table functionality is not migrated to Robots.

master table

Include only master tables that should be contained in the destination robot.

Linking relations between tables are not migrated and can be recreated in Robots.

linked table

If you export a linked table it becomes an unlinked table with an associated data file like any other table in the temporary storage location. This result may or may not match your intentions.

If you want the linked table to also be a linked table in Robots, delete the linked table from the Data folder in AX, and recreate the link in Robots.

standalone layout

Remove any table layout from the Data folder. The script requires that all data tables are associated with a source data file.

For a layout, the Data source property in the table Summary tab states No data source is associated with this data definition.

master layout

linked layout

Download the migration scripts from Diligent Connect

  1. Go to: https://connect.diligent.com/hbfrontdoor/login?startURL=/s/article/Project-migration-from-AX-to-Robots
  2. Under Attachments, click the Table_Migration Analytics project to download it.

    Note

    If you do not see the project, you may need to log in to Diligent Connect using your Diligent One account credentials.

Import the Analytics project with the migration scripts to AX

  1. Open AX Client and sign in.
  2. In the Server Explorer, select the folder containing the historical data tables that you want to export.
  3. Select File > Import and navigate to the location where you downloaded the Table_Migration Analytics project.
  4. Select the project and click Open.
  5. In the Select the Import Destination dialog box, make sure the correct folder is selected and click Import.

    The project is imported and added to the Analysis Apps folder.

Run the export script in AX

  1. In the Table_Migration analysis app, select the Data Tables Export script and select Analytics > Run.

    Note

    The Data Tables Import script is for use in Robots, not AX.

  2. In the Run dialog box, leave Create a new set selected, enter a name for the new input value set such as Export destination, and click Next.
  3. In Tables files common folder path, enter the path to the temporary storage folder that you created for the data files, and click Next.
    • If the folder is on the same server as AX, specify a local file path such as C:\Temp_data_files_storage
    • If you are exporting the files to a network location, specify a UNC path such as \\<servername>\Temp_data_files_storage
  4. Click Run.

    Result The tables in the Data subfolder are exported to the storage folder.

Note

If the export script does not work when you specify a UNC path, create a temporary storage folder on the server where AX is installed, and specify a local file path for Tables files common folder path.

This method may require that you manually copy the data tables in the temporary storage folder at a subsequent point in the migration process.

Commit the migration scripts to Robots

  1. Open the Table_Migration Analytics project.
  2. From the Analytics main menu, select File > Commit Scripts As.
  3. If required, in the Select Destination dialog box, double-click the appropriate Diligent One instance.

    The Robot Collection appears with the list of existing robots.

  4. In the list of robots, select the appropriate destination robot for the data tables and click OK.

  5. Enter a short commit message such as Commit data table migration scripts, and click OK.

    The scripts are committed to development mode in the robot.

  6. In the Commit Scripts Successful dialog box, click the second link to inspect the newly committed scripts in the Robots app.

    The Script versions tab opens with the committed version of the scripts.

  7. Select the script version (vn).

    The Version details side panel opens. The names of the individual analytic scripts and auxiliary scripts are listed beneath Script version changes.

  8. In Analytics, click OK to exit the Commit Scripts Successful dialog box.

Create a task and run the import script in Robots

  1. In Robots, select the Tasks tab and click Create task.

    The Task Designer opens.

  2. Type a name for the task such as Import data tables, and click Save.
  3. Select Activate analytic for Data Tables Import.

    Note

    The Data Tables Export script is for use in AX, not Robots.

  4. Click the down arrow to expand the parameter section.
  5. In Tables files common folder path, enter the path to the temporary storage folder that you created for the data files.
    • If the folder is on the same server as the Robots Agent, specify a local file path such as C:\Temp_data_files_storage
    • If you are importing the files from a network location, specify a UNC path such as \\<servername>\Temp_data_files_storage
  6. At the bottom of the page, click Skip to end and then Confirm and create task.
  7. On the Tasks tab, select the task that you just created and click > Run now.

    Result The data tables in the temporary storage folder are imported to the robot. You can monitor the progress of the task in either the Tasks tab or the Task runs tab.

  8. Select the Input/Output tab to view the imported data tables.

    The tables appear in the Other tables section. Select individual tables to open the Table details side panel.

    Once you are satisfied that the data tables were successfully imported to Robots, you can delete the entire contents of the temporary storage folder.

Note

If the import script does not work when you specify a UNC path, manually copy the temporary storage folder to the server where the Robots Agent is installed, and specify a local file path for Tables files common folder path.

You may be able to copy-and-paste the temporary storage folder, or use FTP. Your IT department should be able to assist you if you have difficulty copying the temporary storage folder.

Repeat the process for additional AX folders and robots

Repeat the data table migration process for additional AX Data folders and robots as required.

To match your planned organization of data in Robots, you can migrate data tables from different AX Data folders to the same robot. Empty the temporary storage folder before each export operation.

  • Copy the scripts in AX Copy the Table_Migration analysis app between AX folders as a quick way to replicate the scripts. You do not need to repeatedly import the Table_Migration project.

    Note

    If you are using a staging Data folder, you do not need to copy the scripts. Update the contents of the staging Data folder as required, and run the export script from the same location.

  • Empty the temporary storage folder Once you are satisfied that the previous group of data tables was successfully imported to Robots, delete the entire contents of the temporary storage folder. The migration scripts are not designed to support the accumulation of data tables from multiple Data folders in the temporary storage folder.
  • Re-commit the scripts to Robots You need to commit the scripts in the Table_Migration Analytics project to each robot in which you want to use them. Copying scripts between robots is not supported.

Create linked tables in Robots

If you use master and linked data tables in AX and you want to continue this arrangement in Robots, you need to recreate the linking relations in Robots. For set-up information, see Sharing and linking data tables in ACL robots.

Uploading or migrating non-Analytics related files

You have two options for populating non-Analytics related files such as Excel or *.csv in Robots:

  • migrate the files from AX to Robots
  • upload the files from a network location

Uploading the files requires fewer steps. You may need to use both options depending on the availability of files. Regardless of which approach you take, make sure that you populate Robots with the current version of each file.

Migrate the files from AX to Robots

You cannot migrate related files directly from AX to Robots. Export related files from AX to an intermediary location, accessible to both AX and Robots, such as your own computer.

This procedure assumes that you are using your own computer as the intermediary location.

  1. Create a folder on your computer for storing exported files.
  2. In the AX Server Explorer, do one of the following:
    • select a Related Files folder to export all the files in the folder
    • select individual files in a Related Files folder using Ctrl+click
  3. Select File > Export.
  4. In the Export dialog box, deselect Work with the exported file(s)?.
  5. Click Browse, select the folder that you created for storing files, and click OK.
  6. Click OK to export the file or files.

    The files are exported to the specified location.

  7. Repeat the process for files in other Related Files folders that you want to migrate.
  8. Continue with the procedure below for uploading files.

Upload the files from a network location

If you have current versions of related files stored locally, or available on your network, you can upload them directly to Robots.

Note

Related files must not exceed 1 GB in size and must be a permitted file type. For a list of permitted file types, see Permitted types of files in Robots.

  1. From the Launchpad home page (www.highbond.com), under Audit & Analytics, select the Robots app.

    You need to sign in to Diligent One if you are not already signed in.

  2. From the dashboard in Robots, select ACL Robots.
  3. Navigate to the robot that you want to upload the related file or files to, and select the robot to open it.
  4. In the upper-right corner of the robot, click Development to switch to development mode.

    The production mode header has a light background and the development mode header has a dark background.

  5. On the Input/Output tab, do one of the following:
    • Drag and drop Select one or more files from your local file system and drag them onto the Drag and drop upload section.
    • Choose files Click Choose files, browse to the files that you want to upload from your local file system, select them, and click Open.

    Result the file or files are uploaded and appear under Files in the Input/Output tab.

What to do next

Once you have migrated the data for one or more automated processes you can move to Recreate automated AX processes in Robots.