Acumatica

Introduction

To analyze data from Acumatica with Nectari, a few steps of configuration are required.

This section will guide you through it.

First, Nectari DataSync will be installed to read the data and write it in a SQL Server database. Then, data from Acumatica will be made available to the synchronizer through OData general inquiries, installed by a customization project provided. Nectari DataSync must be executed next. The Nectari templates will then be installed, providing ready-to-use Reports and Dashboards.

Prerequisites

The following are required before starting to use Nectari DataSync:

  • A version of Acumatica supported by the Acumatica Team is installed.
  • The Destination Collation SQL Instance must be set to SQL_Latin1_General_CP1_CI_AS (same as Acumatica SQL Instance if you are using a separate SQL Instance for Nectari DataSync).
  • If you are using Acumatica in HTTPS: ensure that you put a binding for Nectari DataSync with a certificate approved by a Certificate Authority.
  • If you have a custom identity on Acumatica Application Pool: ensure that you put the same one on the Application Pool of Nectari DataSync.
Note

Acumatica and Nectari are installed (refer to their respective installation guides).

A specific Acumatica ERP license is not required, only a Nectari license is.

Requirements

Nectari DataSync must be installed.

Refer to Installing DataSync for more details regarding the installation.

Setting up the Acumatica connector

Important

Before starting:

  • Make sure that you have an Acumatica account.
  • Make sure at least one Company is registered into this account.

If you have more than one Company, you need to create as many connections as Companies.

Note

Per Company, two Source connections needs to be created.

One of the Source connections will be refreshed based on the Date tracking and the other one will not. This is due to the fact that all tables do not have the LastModifiedDate field thus we need to create a separate connection without the option to refresh. These tables in general does not contain much data.

  1. In your Web browser, go to the application URL of your Acumatica ERP.
  2. In the My Username and My Password fields, enter your credentials.
  3. In the Company drop-down list, select the company you want to connect to.
  1. Before clicking on Sign in, at the bottom of the Login page, write down the version of Acumatica as you will need it to install the template file. Here, it is the 2019 R2 version.
  2. Copy the URL address.
Note

The URL address should have this structure: Acumatica Application URL following by OData/Company Name.

  1. Go back to DataSync and create a new Source Connection.
  2. In the list, select Acumatica.
  3. Paste the URL address you copied from Acumatica into the URL field in DataSync.
Note

%20 is how web browser encode spaces, leave it as it is.

  1. In the Username and Password fields, enter the credentials of your Acumatica account.
  2. Once these fields are filled in, in the Description field, enter a name for this Source connection and change the other settings according to your preferences.
  3. For the first Source Connection, in the Tracking drop-down list, select None.
  4. Click on Save to finish.
  5. For the second Source Connection of the same Company, repeat the previous steps and, for the Tracking drop-down list, select Date.
  6. Define the settings regarding the Time Zone and click on Save.
  7. Create a Destination connection by following the procedure described in Add a Source and Destination Connection then refer to Configuration to finish the configuration.

Configuration


Importing a project

There are three possible actions for the tool. When using the Source connection the first time for an Extraction, run only Truncate and Load. In different contexts, you can use Incremental Load and Process Deleted Records.

However, before doing these actions, you will need first to import a project in Acumatica ERP.

Important

We highly recommend you to coordinate this step with your Acumatica ERP Consultant to avoid to accidentally unpublish others existing Projects.

When using Nectari DataSync for the first time, the database will be created and the inquiries starting with NEC will be read from OData and written into the SQL Server tables. This process can take time.

  1. Connect to Acumatica with a user with the proper access rights and navigate to Customization > Customization Projects.
  2. Click on Import and install the file Nectari20XXRX.zip corresponding to the version of Acumatica.
Note

To get the version of Acumatica, you can either:

  • Click on the Tools drop-down list located at the upper-right hand corner then select About...

  • OR look for the version at the bottom of the Login page.
  1. Click on Save.
  2. Check the box on the new line Nectari20XXRX.zip, and click on Publish.

Importing the Extractions

This feature allows you to import a pre-defined template or to restore a backup that you may have done yourself with the Export feature (refer to Export an Extraction for more details).

Some pre-defined templates are already available; if you don't have access to them, please contact your partner. An example of a pre-defined template you could use is the one which defines the list of tables and fields to be synchronized to send Sage 300, X3, Acumatica, Salesforce data to the Cloud.

  1. Click on one of the extractions in the list then on the Import icon located on the upper right-hand corner.
  2. In the Import Extraction window, click on the Choose a zip file hyperlink to browse to the location you saved the export .zip file or drag it directly into that window and click on Next.
Note

For Acumatica, four zip files will be provided.

  • For Company with Single Tenant (Customer): use the DS_EXTR_[Software-Version]_[Extraction-Version]_Acumatica with refresh DS-SYNC.zip file for the Source connection with the tracking by Date and the DS_EXTR_[Software-Version]_[Extraction-Version]_Acumatica w_o refresh DS-SYNC.zip file for the one with none.
  • For Company with Multi Tenant (Customers): use the DS_EXTR_[Software-Version]_[Extraction-Version]_Acumatica with refresh DS-CONSO.zip file for the Source connection with the tracking by Date and the DS_EXTR_[Software-Version]_[Extraction-Version]_Acumatica w_o refresh DS- CONSO.zip file for the one with none.
  1. On the left pane, select the type of extraction you want to perform and click on Next.
Important

Regarding extractions for Acumatica:

  • For Company with Single Tenant (Customer): select Synchronization.
  • For Company with Multi Tenant (Customers): select Consolidation.
  1. Refer to Setup the Extraction Panel to define the extraction and click on Import to finish the process.

Note

The Extractions window will automatically switch to the Tables window.

Refer to Add a SQL Query if you want to add SQL statements to some tables and Setup the Field Section to customize the fields (add calculation, change destination name etc.)

 

Validating and Building the Extractions

Once your extraction (source, destination connection and their related tables) is set up, the next step is to validate the setting of an extraction, before being able to run it.

The feature will:

  • Ensure that all the tables/fields exist in the source connections,
  • Validate all SQL queries or calculated fields,
  • Ensure that the data integrity in the destination connection is not affected (ex: change the table structure).

To do so:

  1. Select the extraction you want to validate and build in the list and click on the Validate and Build icon.
  2. In the new window, choose the action which best fits your needs and click on Build (Validate for Migration and Export extraction types).
Note

The choice will be different accordingly to the extraction type you select.

Example

For Synchronization / Consolidation and extraction types:

For Migration and Export extraction types:

  1. Wait for the process to be done.
Note

A Validation report window will appear to give you a quick overview on the process once it's finished. The results are displayed in the Status column and if there is an error, you will get more details by clicking on the hyperlink in the Error column which leads to Log Page.

Running the Extractions

Once your data have been validated (refer to Validate and Build an Extraction for more details), you can manually run the extraction if you want an immediate result instead of scheduling it.

  1. Select the extraction you want to run in the list and click on the Run Extraction Now icon.
  2. In the upper-right hand corner, choose the action you want to execute and the table(s) then click on Run.
Example


Note

Load (for the Migration extraction type only): Loads all data in your destination from your source.

Truncate and Load: Replaces all data in your destination with the current data from your source.

Incremental Load: Retrieves only records that have changed since your last Incremental Load and replace their corresponding records in your destination with the updated ones.

Process Deleted Records: Maximum quantity of days for the validation process to check if records have been deleted based on the last changed date. i.e. If value is set to 30 days, the system will check all the transactions that were created or updated in the last 30 days and then validate if they still exist in the source. If they don't exist anymore in the source, they will be then deleted from the destination.

  1. Wait for the process to be done.
Note

When the process is finished, the results are displayed in the Status column. If there is an error, you can view more details on it by clicking on the hyperlink in the Error column, which leads to the Log Page.

Data Source Configuration

The Templates installation will provide you with a ready-to-use set of Views, Dashboards and Reports. Some Templates also provide Cubes.

Their installation (assuming the Central Point is freshly installed and empty), will consist of two steps:

  • Configuring the Data Sources for the ERP and Data Warehouse (optional) databases.
  • Importing the Templates into the Central Point.
  • There may be other steps including building and loading the OLAP Cubes.

Environments and Data Sources

Tip

The description given to a Data Source created for the first time is used throughout the environments to describe this specific Data Source.

Give a generic description for the first time (e.g. ERP Data Source, Cube Data Source) and if necessary, rename it after the first environment has been created.

The following information is needed to configure the Data Sources:

  • Database server credentials: Server name, Instance, Authentication strategy.
  • Main ERP database information: Database and schema name.

ERP Data Source

  1. In the upper-right hand corner, click on the to access the Administration section.
  2. On the left pane, select Env. & Data Sources.
  3. By default, there is already an environment called Production, which you can rename by double-clicking in the name box. Once changed, press the Enter key.
  4. In the Data Sources section, click on Add to create the first Data Source.
  5. Complete the ERP Data Source configuration. See instructions for MS SQL Server below.

Datasource description:
Acumatica Data Source
Type:
SQLSERVER
Server:
Database server of Acumatica sync database
Database name:
Name of the Acumatica sync database (beware of the case sensitivity)
Database schema name:
Click on the icon to enter: AcumaticaSync.dbo (adjust the database name if different)
Nectari schema:
Enter the chosen Nectari custom schema for the current environment
Authentication stategy:
UseSpecific
User Name:
SQL User accessing the Acumatica sync database. For example, sa.
Password:
The user's password.
  1. Click on Validate then on Save to complete the configuration of the Data Source.

Importing Templates

For each environment, the following previously configured information will be required:

  • ERP Database Name
  • Nectari Custom Schema
  • ERP Schema

Download the Template file: TPL_9.5.XXX_Acumatica.zip.

The X represents the build number of the template (use the highest available).

Running the Import Template

  1. In the upper-right hand corner, click on the to access the Administration section.
  2. In the Administration section, click on the Templates drop-down menu in the left pane.
  3. Select Import Template.
  4. Choose the specific location where the new templates will be installed and click on Next.
    Note

    Usually, the Root folder is used.

  1. In the Import Template window, click on Select files....
  2. Find the folder where you saved the Template.zip file in order to select it then click on Open.
  3. In the Data Sources Mapping screen, associate the Data Sources (ERP) listed in the Received Data Sources Description column (those from the template) with the Data Sources you previously defined in the Central Point (listed in the Current Data Sources Description column)
    • In the Received Data Sources Description column, ensure that only the Data Sources checkboxes you want to use from the template are ticked off.
    • In the Current Data Sources Description column, click on Bind a Data Source to access the drop-down list containing the existing Data Sources and click on Next.

In the next screen all of the Templates content is displayed, against what the Central Point already has.

By default, on the first install, everything will be set to Add (leave everything by default) .

  • In the case of a first installation, the first four columns will display None and Never Installed, the next three will detail the Template content, and the last three gives you the choice to Add, Update or Skip during the installation.
    Note

    In the case of an update, you can refer to Updating template for more details.

  1. Click on Next (this can take time).
  1. Once this has been completed, a window will be prompted to input the necessary parameters to create the custom objects.
  2. If more than one Environment have been created, you will see a column per Environment. You can untick an Environment checkbox, in which case the Global Scripts will not run in it.
Note

In the case of Acumatica, just enter dbo in as the Acumatica Schema and click on Next.


  1. After importing, an Execution Report will be produced, as shown below.
    Note

    The first section is for the ERP Data Source and the one below it is for the Cube Data Source.

    You can click on the button to see the details of each script individually. If no failures are reported, close the window.

  1. If any of the scripts failed to run, a fail icon will be displayed. Click on the fail symbol to view the Report Preview window, which shows the respective SQL script.
  • Copy this script, debug, and run it separately if needed. Users who are proficient with SQL can debug it straight in the Report Preview window and run it by clicking on the Try to rerun button.

You can now use Nectari with the ready-to-use reports.

Note

The Universal Data Model (UDM) also has a set of Acumatica data sources that can be used (refer to Configuration Specific to Each System ).

Updating template

Important

Some considerations you must take into account before starting:

  • Making fresh backups of both the Nectari database and Central Point before doing a template update is highly recommended.
  • Check the Nectari Data Models and Nectari custom SQL objects that may have been delivered with the initial template version, as you might lose these customizations upon updating.
  • You must have a template version that matches the software version installed. If you are using Nectari 9, the template should be also 9.

When performing an upgrade of the Nectari software, it will only update the software and not the template. In other words, the existing Nectari Data Models and Views won't be affected.

Note

After a software upgrade, it is not mandatory to systematically perform a template update. A template update is useful if you have encountered problems with specific Nectari Data Models or Nectari custom SQL objects as it includes fixes.

To update a template:

  1. After having mapped the Data sources, tick the checkboxes of the objects you want to upgrade and click on Next.
    Note

    By default, no checkbox in the Update column will be ticked. If there is a new Data Model / View the Add checkbox will be ticked. Select Skip if you want to ignore it.

    Important

    If you tick the Update checkbox, it will overwrite the existing Nectari objects associated with that Data Model or connected to the others (dependencies). Please note that if any customizations have been done, they will be lost.

  1. Select the environment in which the scripts will be executed and click on Next.
  2. Complete the parameters and click on Next.
  3. In the Execution report window, If any of the scripts failed to run, a fail icon will be displayed. Click on the fail symbol to view the Report Preview window, which shows the respective SQL script.
  4. Copy this script, debug, and run it separately if needed. Users who are proficient with SQL can debug it straight in the Report Preview window and run it by clicking on the Try to rerun button.

Building and Loading the OLAP Cubes

To create the Cubes database structure in the NectariCube database previously installed, you need to Build the Cube.

  1. In the upper right-hand corner, click on the to access the Administration section.
  2. In the left pane, click on OLAP Manager.
  3. In the right pane, click on Manage.
  4. In the left section, select all the Cubes to build by ticking the checkbox next to the Description column.
  5. In the Manage window, select Build in the Action drop-down list then the environment(s) and click Confirm.
  6. In the Confirmation window, tick the checkbox and click on Yes.
  7. If errors occur refer to Logs in the Administrator section to activate the logging feature.

Now that the Cubes are built, you can now populate them.

  1. In the right pane, click on Manage.
  2. In the left section, select all the Cubes to load by ticking the checkbox next to the Description column.
  3. In the Manage window, select Load All in the Action drop-down list then the environment(s) and click Confirm.
  4. In the Confirmation window, tick the checkbox and click on Yes.

The template installation is now complete.

Regular data refresh jobs for the Cubes can now be scheduled. For more details on how to do this, refer to Scheduler.

Important

Web Browsers have updated their policy regarding Cookies and these changes must be applied to your Web Client if you want Nectari embedded into your ERP website, or use Single Sign-On (SSO). Refer to Cookie Management for more details.

Uninstallation

To completely uninstall Nectari and Acumatica customizations for Nectari, proceed with the following steps:

Uninstalling the template in Nectari

If Nectari will be used in other contexts, skip this step. Otherwise:

  1. Log in to Nectari as an admin user and navigate to Admin > Templates > Manage Templates.
  2. Click on Select All.
  3. If more than one template zip file was imported, review all object types (Data Models, Prompts, Dashboards etc) to make sure only the objects to be deleted are selected.
  4. Click on Uninstall.

Uninstalling Acumatica Customizations

  1. Log in to Acumatica with an admin user.
  2. Navigate to Customization > Customization Projects.
  3. Unselect the project Nectari20XXRX.zip (do not select new lines) and click on Publish to unpublish the unselected project.
  4. Navigate to Customization > Generic Inquiry and remove all inquiries starting with NEC.
  5. Delete all site map in the path hidden/nectari sync (all objects start with NEC).