Skip to main content

Scenario 3: Self-hosted for Acumatica

Acumatica, Scenario 3: Self-hosted
In Acumatica

Configure Acumatica​

Before you install the template, import and publish the Sage Enterprise Intelligence customization project in Acumatica. It adds the OData inquiries that DataSync reads.

Sage Enterprise Intelligence maintains the projects in ShareFile for the four latest Acumatica versions. When Acumatica releases a new version, the oldest project is removed.

View your Acumatica version​

  1. Log in to Acumatica.
  2. In the upper right corner, click Tool, then select About.
  3. Note your version, shown as Acumatica 20XX RX.

Upload the project​

  1. In the sidebar, select Customization, then click Customization Projects.
  2. Click Import.
  3. Select the file for your version in ...\20XX Release X\Acumatica\Acumatica ERP Projects, then click Upload.
  4. Select the Sage Enterprise Intelligence project, then click Publish.
Work with your Acumatica consultant so you do not unpublish or overwrite other customization projects.

Count your tenants​

  1. In the sidebar, select System Management, then click Tenants.
  2. Note the number of tenants listed.

Create the client ID and secret​

  1. In the sidebar, select Integration, then click Connected Applications.
  2. Click +, enter OAuth 2.0 in the Client Name, and select Authorization Code in Flow.
  3. Click Save then note the Client ID.
  4. Click Add Shared Secret, enter OAuth Secret then note the Value (Client Secret).

Prepare the redirect URI​

  1. In Connected Applications, select your OAuth 2.0 application, then click the Redirect URIs tab.
  2. Click +, then keep the page open to paste the DataSync callback URL.
In DataSync

Configure DataSync for Acumatica​

Add a source with refresh​

  1. Log in to DataSync, then click Connections.
  2. Next to Source Connections, click New.
  3. Select Acumatica and fill in the Connection Properties:
    Description
    Acumatica Refresh
    URL
    Your Acumatica URL. Keep the %20 for spaces.
    Authentication Mode
    OAuth2, the recommended mode. Basic is also supported.
    Callback URL
    Redirect URL to add to the connected application in Acumatica.
    Client ID
    Client ID from Acumatica.
    Client Secret
    Value (Client Secret) from Acumatica.
    Authorization URL
    Your base Acumatica URL followed by identity/connect/authorize.
    Access Token URL
    Your base Acumatica URL followed by identity/connect/token.
  4. In Advanced Settings, set Tracking Type to Date and match the time zone to the Acumatica server.
  5. Click Save.
DataSync Acumatica source connection with the refresh connection properties

Add a source without refresh​

  1. Next to Source Connections, click New.
  2. Select Acumatica and fill in the Connection Properties:
    Description
    Acumatica without Refresh
    All other properties
    Same values as the refresh connection.
  3. Click Save.
DataSync Acumatica source connection with the no refresh connection properties

Create a destination connection​

  1. Next to Destination Connections, click New.
  2. Select SQL Server or Oracle, depending on your data warehouse.
  3. In Description, enter Data Warehouse.
  4. Enter the Server, Database, Username, and Password.
  5. Click Save.
DataSync destination connection to the SQL Server data warehouse

Import the refresh extraction​

  1. In the sidebar, select Extractions, then click Import Extraction in the top right corner.
  2. Drag the DS_202X.0.X.XXX_Acumatica SYNC Refresh.zip file, then click Next.
  3. In Description, enter Acumatica Refresh.
  4. In Unique Identifier, enter your company name.
  5. In Column Name, enter CompanyID.
  6. In Source Connection, select Acumatica Refresh.
  7. In Destination Connection, select Data Warehouse.
  8. In Destination Schema, click Add and enter Acumatica.
  9. Click Import.
DataSync Import Extraction dialog for the Acumatica refresh extraction

Import the no refresh extraction​

  1. Click Import Extraction in the top right corner.
  2. Drag the DS_202X.0.X.XXX_Acumatica SYNC NoRefresh.zip file, then click Next.
  3. In Description, enter Acumatica without Refresh.
  4. In Unique Identifier, enter your company name.
  5. In Column Name, enter CompanyID.
  6. In Source Connection, select Acumatica without Refresh.
  7. In Destination Connection, select Data Warehouse and in Destination Schema, select Acumatica.
  8. Click Import.
DataSync Import Extraction dialog for the Acumatica no refresh extraction

Run the extractions​

  1. Select the first extraction in the list and click Validate and Build in the top right corner.
  2. Select Drop the previously created object and recreate the objects based on the new definition.
  3. Click Build.
  4. Select the same extraction and click Run Extraction Now.
  5. Select the tables, set the load mode to Truncate and Load, then click Run.
  6. Repeat for each remaining extraction.
If the Status column shows Done with errors, click the link to review the logs.

Schedule an incremental load​

  1. In the sidebar, select Scheduler, then click New.
  2. In Description, enter Acumatica Incremental Load.
  3. In Action, select Incremental Load.
  4. In Extraction, select Acumatica Refresh.
  5. Select Active.
  6. In the left panel, select Weekly, then set Start On and At.
  7. Change the Time Zone if needed, then click Save.

Schedule a full reload​

  1. Click New, then in Description, enter Acumatica Truncate and Load.
  2. In Action, select Truncate and Load.
  3. In Extraction, select Acumatica without Refresh.
  4. Select Active.
  5. In the left panel, select Weekly, then set Start On and At.
  6. Change the Time Zone if needed, then click Save.

Schedule deleted records processing​

  1. Click New, then in Description, enter Acumatica Process Deleted Records.
  2. In Action, select Process Deleted Records.
  3. In Extraction, select Acumatica Refresh.
  4. Select Active.
  5. In the left panel, select Daily, then set Start On and At.
  6. Change the Time Zone if needed, then click Save.
Optional

Load large tables by partition​

Load by partition​

Use loading by partition for large tables. Partition on a date or an integer field, and avoid date fields that can contain future dates.

In Sage Enterprise Intelligence

Configure Sage Enterprise Intelligence for Acumatica​

Set up the data source​

  1. Log in to Sage Enterprise Intelligence, then click the gear icon in the navigation panel.
  2. Select Env. & Data Sources.
  3. In the Data Sources panel, click +.
  4. In Description, enter Acumatica.
  5. In Type, select SQLSERVER or ORACLE, depending on your data warehouse.
  6. Fill in the other fields:
    Database NameSQL Server
    Data warehouse database name created in DataSync. Case sensitive.
    SIDOracle
    Data warehouse database name. Case sensitive.
    PathOracle
    Oracle instance port number.
    PoolingOracle
    Turn on pooling and adjust its parameters if needed.
    Database Schema Name
    Click + twice and enter DatabaseName.PROD and DatabaseName.NEC_PROD.
    Replace DatabaseName with your database name. NEC_PROD can use another name, in capital letters only.
    Nectari Schema
    Enter NEC_PROD.
    Authentication Strategy
    Select UseSpecific.
    User Name, Password
    SQL Server or Oracle user and password.
  7. Click Validate, then Save.
Data Sources panel with the Acumatica data warehouse data source

Set up the OLAP data source​

  1. In the Data Sources panel, click +.
  2. In Description, enter Acumatica OLAP.
  3. In Type, select SQLSERVER or ORACLE, depending on your data warehouse.
  4. Fill in the other fields:
    Database NameSQL Server
    Data warehouse database name created in DataSync. Case sensitive.
    SIDOracle
    Data warehouse database name. Case sensitive.
    PathOracle
    Oracle instance port number.
    PoolingOracle
    Turn on pooling and adjust its parameters if needed.
    Database Schema Name
    Click + twice and enter OLAPCube.PROD and OLAPCube.NEC_PROD.
    NEC_PROD can use another name, in capital letters only.
    Nectari Schema
    Enter NEC_PROD.
    Authentication Strategy
    Select UseSpecific.
    User Name, Password
    SQL Server or Oracle user and password.
  5. Click Validate, then Save.
  6. Select Acumatica OLAP in the Data Sources panel, then click Set as Data Warehouse.
  7. In Data Warehouse Schema, enter NEC_FOLDER.
  8. Click Validate, then Save.
Data Sources panel with the Acumatica OLAP data warehouse data source
Optional

Set up Acumatica real-time finance​

Complete this section only if you use real-time financial reporting. Since Release 1 Version 2026, uploading the project to Acumatica is optional.

Upload the real-time project​

Optional
  1. In Acumatica, select Customization, then click Customization Projects.
  2. Click Import, then select the real-time file in ...\Acumatica\Acumatica ERP Projects\Add-ons.
  3. Click Upload.
  4. Select the Real Time project, then click Publish.

Create the global variable​

  1. In Sage Enterprise Intelligence, select the gear icon from the navigation panel.
  2. Select Global Variables, then click Add New Record.
  3. In Key, enter @@AcumaticaURL, then in Variable Type, select Constant.
  4. In Description, enter Acumatica Server URL.
  5. In Data Type, select Text.
  6. Enter your Acumatica URL, then click Create.

Set up real time data​

  1. In the Administration section, select Env. & Data Sources.
  2. In the Data Sources panel, click +.
  3. In Description, enter Acumatica Real-time.
  4. In Type, select Acumatica.
  5. Fill in the other fields:
    URL
    Your Acumatica URL. Keep the %20 for spaces.
    Company
    Name of your Acumatica company (tenant).
    EndpointsOptional
    Endpoint name and version from Acumatica, to activate the writeback.
    Authentication Strategy
    OAuth2, the recommended mode. Basic is also supported.
    Client ID
    Client ID from Acumatica.
    Client Secret
    Value (Client Secret) from Acumatica.
    Scope
    api and offline_access.
    Callback URL
    Redirect URL to add to the connected application in Acumatica.
  6. Click Validate, then Save.
Nectari Data Sources panel with the Acumatica real-time data source fields
Optional

Add Acumatica manufacturing reporting​

Complete this section only if you use manufacturing reporting. Since Release 4 Version 2026, uploading the project to Acumatica is optional.

Upload the manufacturing project​

Optional
  1. In Acumatica, select Customization, then click Customization Projects.
  2. Click Import, then select the manufacturing file in ...\Acumatica\Acumatica ERP Projects\Add-ons.
  3. Click Upload.
  4. Select the Manufacturing project, then click Publish.
In Sage Enterprise Intelligence

Import the Acumatica template​

Import the template​

  1. In the sidebar, expand Templates, then click Import Template.
  2. Select the template zip file from ...\Templates - Official\20XX Release X\Acumatica\WebServer.
  3. In Current Data Sources Description, click Bind a Data Source and select Acumatica Data Source.
  4. Click Next.
  5. In Data Models, clear Add for the objects to skip, then click Next.
  6. Select the environment.
  7. Enter NEC_PROD in Schema for Custom Objects and PROD in Schema for Acumatica.
  8. Click Next and review the execution report.
Optional

Set up Sales and Finance reporting for Acumatica​

Complete this section only if you have the Data Management add-on and use the Sales and Finance cubes. The cubes run on a separate data warehouse database, so they need their own source connection and extractions.

Add the source connection​

  1. Log in to DataSync, then click Connections.
  2. Next to Source Connections, click New.
  3. Select SQL Server and fill in the Connection Properties:
    Description
    Enter Data Warehouse (Source).
    Server
    Data warehouse server, such as SQLSERVER01\MAIN.
    Database
    Data warehouse database, such as SalesFinance.
    Username, Password
    Data warehouse database user and password.
  4. In Advanced Settings, set Tracking Type to Date and match the time zone to the Acumatica server.
  5. Click Save.

Import the table staging extraction​

  1. In the sidebar, select Extractions, then click Import Extraction in the top right corner.
  2. Drag the DS_20XX.0.X.XXX_Acumatica OLAP ETL Staging.zip file, then click Next.
  3. In Description, enter Cube - Table Staging.
  4. In Source Connection, select Data Warehouse (Source).
  5. In Destination Connection, select Data Warehouse.
  6. In Source Schema and Destination Schema, select PROD.
  7. Click Import.

Import the dimension mapping extraction​

  1. Click Import Extraction in the top right corner.
  2. Drag the DS_20XX.0.X.XXX_Acumatica OLAP ETL Refresh.zip file, then click Next.
  3. In Description, enter Cube - Dimension Mapping.
  4. In Source Connection, select Data Warehouse (Source).
  5. In Destination Connection, select Data Warehouse.
  6. In Source Schema and Destination Schema, select PROD.
  7. Click Import.

Run the cube extractions​

  1. Select the first extraction in the list and click Validate and Build in the top right corner.
  2. Select Drop the previously created object and recreate the objects based on the new definition.
  3. Click Build.
  4. Select the same extraction and click Run Extraction Now.
  5. Select the tables, set the load mode to Truncate and Load, then click Run.
  6. Repeat for each remaining extraction.
If the Status column shows Done with errors, click the link to review the logs.

Set the data warehouse​

  1. Log in to Sage Enterprise Intelligence, then click the gear icon in the navigation panel.
  2. Select Env. & Data Sources.
  3. Select the Acumatica data source and click Set as Data Warehouse.
  4. In the Data Source Definition panel, select Columnstore indexes.
  5. Click Validate, then Save.

Build and load the cubes​

  1. Select OLAP Manager in the sidebar, then click Manage in the right panel.
  2. Select all the template cubes from the list.
  3. In the Manage panel, select the environment.
  4. Click Confirm, select the checkbox to confirm that current cube data will be lost, then click Yes.
  5. Click Manage again and select all the built cubes.
  6. In Action, select Load All and the environment, then click Confirm.

Schedule the cubes​

Schedule regular refresh jobs to refresh or reload your OLAP cubes.

Acumatica template installation complete​

The Acumatica template is installed. Continue with these pages to adapt it to your company and review its content.