Scenario 2: Consolidated for Sage Intacct

Sage Intacct, Scenario 2: Consolidated

In Sage Intacct

Configure Sage Intacct

DataSync connects to Sage Intacct through Web Services. Before you start the template installation, get a Web User License from Sage Intacct.

Activate Web Services

1. Log in to Sage Intacct and expand Applications, then under Company, click Admin.
2. Click Subscriptions, enable Web Services, then click Subscribe.

Give DataSync access

1. Expand Company and click Overview in the right corner.
2. Click Company Information, then Edit.
3. Select the Security tab and click Manage filters.
4. In Username, enter the company ID you use to log in, then click Add.
5. Select the same username, then enter the IP address from the Sage Enterprise Intelligence team and click Save.
6. Scroll down to Web Services authorizations and click Add.
7. In Sender ID, enter NectariMPP and click Save.

Create the role

1. Expand Company and click Roles.
2. Click Add, enter Sage Enterprise Intelligence, then click Save.
3. Select Application or module to include all modules.
4. Next to Administration, click Permissions, select Read only, then click Save.
5. Repeat step 4 for each remaining module.
6. Next to Platform Services, click Permissions, select List and Views, then click Save.
7. Click Save again to save the role.

Create the user

1. Expand Company and click Web Services users.
2. Click Add, enter the User ID and Account email address, then select the Contact Name.
3. Under Admin privileges, select Limited.
4. In the Role table, click a line and select Sage Enterprise Intelligence.
5. Click Save, then Create.
6. Enter your Sage Intacct password and click Verify.

Keep the credentials sent by email for the DataSync source connections.

Enable audit tables

1. Expand Company and select General Ledger in the sidebar.
2. Select the Setup tab and click Configuration.
3. Under Subledger transactions posted to GL, select Enable auditing of transactions posted from subledgers.
4. Click Save.

Create opening periods

1. Expand General Ledger and click Reporting periods.
2. Click Add and fill in the fields:
   Name

   Nectari Opening 20XX

   Column header

   Nectari Opening 20XX

   Start date

   01/01/20XX

   End date

   12/31/20XX
3. Click Save, then repeat for every reporting year you need.

In DataSync

Configure DataSync for Sage Intacct

Add a source with refresh

1. Log in to DataSync, then click Connections.
2. Next to Source Connections, click New.
3. Select Sage Intacct and fill in the Connection Properties:
   Description

   Sage Intacct Refresh - [Company Name]

   Replace [Company Name] with the name of the company.

   URL

   Enter https://api.intacct.com/ia/xml/xmlgw.phtml

   User ID, Password, Company ID

   The credentials from the Sage Intacct email.
4. Clear Use Legacy API.
5. In Additional Connection Properties, add EnableSubscriptions with the value Construction.
6. In Advanced Settings, set Tracking Type to Date and match the time zone to the Sage Intacct server.
7. Click Save, then repeat these steps for each remaining company.

Create the connections for the other companies by duplicating a connection.

Add a source without refresh

1. Next to Source Connections, click New.
2. Select Sage Intacct and fill in the Connection Properties:
   Description

   Sage Intacct without Refresh - [Company Name]

   Replace [Company Name] with the name of the company.

   URL

   Enter https://api.intacct.com/ia/xml/xmlgw.phtml

   User ID, Password, Company ID

   The credentials from the Sage Intacct email.
3. Click Save, then repeat these steps for each remaining company.

Create the connections for the other companies by duplicating a connection.

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.

Configure global variable

1. In the sidebar, select Variables, then click New.
2. In Variable Name, enter INTACCT_SCHEMA.
3. In Variable Type, select String.
4. In Variable Value, enter [YOUR_SCHEMA].
5. Click Save.

Import the process deleted records extraction

1. In the sidebar, select Extractions, then click Import Extraction in the top right corner.
2. Drag the DS_202X.0.X.XXX_Sage Intacct CONSO Refresh - PD.zip file, then click Next.
3. In Description, enter Sage Intacct PD - [Company Name].
4. In Unique Identifier, enter your company name.
5. In Column Name, enter CPYID.
6. In Source Connection, select Sage Intacct Refresh - [Company Name].
7. In Destination Connection, select Data Warehouse.
8. In Destination Schema, click Add and enter Intacct.
9. Click Import, then repeat these steps for each company.

Replace [Company Name] with the name of the company.

Import the smart events extraction

1. Click Import Extraction in the top right corner.
2. Drag the DS_202X.0.X.XXX_Sage Intacct CONSO Refresh - TRG.zip file, then click Next.
3. In Description, enter Sage Intacct TRG - [Company Name].
4. In Unique Identifier, enter your company name.
5. In Column Name, enter CPYID.
6. In Source Connection, select Sage Intacct Refresh - [Company Name].
7. In Destination Connection, select Data Warehouse and in Destination Schema, select Intacct.
8. Click Import, then repeat these steps for each company.

Replace [Company Name] with the name of the company.

Import the no refresh extraction

1. Click Import Extraction in the top right corner.
2. Drag the DS_202X.0.X.XXX_Sage Intacct CONSO NoRefresh.zip file, then click Next.
3. In Description, enter Sage Intacct without Refresh - [Company Name].
4. In Unique Identifier, enter your company name.
5. In Column Name, enter CPYID.
6. In Source Connection, select Sage Intacct without Refresh - [Company Name].
7. In Destination Connection, select Data Warehouse and in Destination Schema, select Intacct.
8. Click Import, then repeat these steps for each company.

Replace [Company Name] with the name of the company.

Import the CRE extraction

1. Click Import Extraction in the top right corner.
2. Drag the DS_202X.0.X.XXX_Sage Intacct CRE CONSO Refresh.zip file, then click Next.
3. In Description, enter Sage Intacct CRE - [Company Name].
4. In Unique Identifier, enter your company name.
5. In Column Name, enter CPYID.
6. In Source Connection, select Sage Intacct Refresh - [Company Name].
7. In Destination Connection, select Data Warehouse and in Destination Schema, select Intacct.
8. Click Import, then repeat these steps for each company.

Replace [Company Name] with the name of the company.

Filter the account balances by period

1. Select the Sage Intacct PD and TRG extractions for each company and click the link in the Tables column.
2. In the Glaccountbalance table, click the pen icon in the Filter row.
3. Enter the following filter. In Period IN, list your period names, including future years, separated by commas:

   NOT (Openbal=0 and Totdebit=0 and Totcredit=0 and Totadjdebit=0 and Totadjcredit=0 and Forbal=0 and Endbal=0) AND
   Period IN (
       'Nectari Opening 2025',
       'Nectari Opening 2026',
       'Nectari Opening 2027',
       'Nectari Opening 2028',
       'Nectari Opening 2029',
       'Nectari Opening 2030'
   )

4. Click Save, then repeat these steps for each remaining refresh extraction.

Come back to this filter before the last year in the list ends, and add more years.

Partition the account balances

1. In the Sage Intacct PD and TRG extractions of each company, select the Glaccountbalance table.
2. Click Partition Settings in the upper right corner.
3. Select Use partition settings.
4. In Partition On, select Integer, then in Field, select Accountrec.
5. In Minimum Value and Maximum Value, enter your values.
6. Click Confirm, then repeat these steps for each remaining refresh extraction.

To find your minimum and maximum values, select the Glaccount table and click Data Preview in the upper right corner. Under Recordno, look for the smallest and largest numbers.

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 Sage Intacct Incremental Load.
3. In Action, select Incremental Load.
4. In Extraction, select the extractions of each company:
   • Sage Intacct PD - [Company Name]
   • Sage Intacct TRG - [Company Name]
   • Sage Intacct CRE - [Company Name]
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.

Avoid scheduling jobs that start or run between 7:30 PM and 11:30 PM (PST) on Fridays, because Sage Intacct performs regular maintenance during this time.

Schedule a full reload

1. Click New, then in Description, enter Sage Intacct Truncate and Load.
2. In Action, select Truncate and Load.
3. In Extraction, select Sage Intacct without Refresh - [Company Name] for each company.
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 Sage Intacct Process Deleted Records.
2. In Action, select Process Deleted Records.
3. In Extraction, select the extractions of each company:
   • Sage Intacct PD - [Company Name]
   • Sage Intacct TRG - [Company Name]
   • Sage Intacct CRE - [Company Name]
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 Sage Intacct

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 Sage Intacct.
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.

   NEC_PROD

   Enter NEC_PROD.

   Authentication Strategy

   Select UseSpecific.

   User Name, Password

   SQL Server or Oracle user and password.
7. Click Validate, then Save.

Optional

Connect Sage Intacct for real-time finance

Complete this section only if you use real time financial reporting. Some Excel Add-in formulas return errors with this data source, so review the Sage Intacct real-time formula errors first.

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 Sage Intacct Real-time.
4. In Type, select Sage Intacct.
5. Fill in the other fields:
   URL

   Enter https://api.intacct.com/ia/xml/xmlgw.phtml

   Client IDOptional

   Registered third party integration ID.

   Locations IDOptional

   Location ID for multi entity setups.

   Company ID

   Sage Intacct company ID.

   User Name, Password

   User with Web Services access.
6. Click Validate, then Save.

In Sage Enterprise Intelligence

Import the Sage Intacct template

To embed Sage Enterprise Intelligence in your ERP website or use single sign-on (SSO), change the cookie management settings.

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\Sage Intacct\WebServer.
3. In Current Data Sources Description, click Bind a Data Source and select Sage Intacct 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 Sage Intacct.
8. Click Next and review the execution report.

Optional

Add Sage Intacct custom fields

Complete this section only if your reports use custom fields on purchase or sales documents.

Add DocParID

1. Log in to DataSync, then click Connections.
2. Select the Sage Intacct Refresh source connection and click the pen icon.
3. In Additional Connection Properties, click Add property and select Encrypted.
4. In Property, enter DocParID.
5. In Value, enter the document type, such as Vendor Invoice.
6. Click Save.

DocParID accepts one document type per connection. For another type, create another source connection.

Import purchase order

1. In the sidebar, select Extractions, then click Import Extraction in the top right corner.
2. Drag the DS_202X.0.X.XXX_Sage Intacct PO DOCPARID CONSO file, then click Next.
3. In Description, enter Sage Intacct Purchase Order.
4. In Unique Identifier, enter your company name.
5. In Column Name, enter CPYID.
6. In Source Connection, select Sage Intacct Refresh.
7. In Destination Connection, select Data Warehouse and in Destination Schema, select Intacct.
8. Click Import.

Import sales order

1. Click Import Extraction in the top right corner.
2. Drag the DS_202X.0.X.XXX_Sage Intacct SO DOCPARID CONSO file, then click Next.
3. In Description, enter Sage Intacct Sales Orders.
4. In Unique Identifier, enter your company name.
5. In Column Name, enter CPYID.
6. In Source Connection, select Sage Intacct Refresh.
7. In Destination Connection, select Data Warehouse and in Destination Schema, select Intacct.
8. Click Import.

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.

Sync custom fields

1. In Extractions, select the Sage Intacct Purchase Order extraction and click the link in the Tables column.
2. For each table, click the link in the column.
3. Select Sync for each custom field you want to add.
4. Repeat for the Sage Intacct Sales Order extraction.

Optional

Set up the Sage Intacct OLAP cubes

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. Clear Use Legacy API.
5. In Advanced Settings, set Tracking Type to Date and match the time zone to the Sage Intacct server.
6. 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_Sage Intacct 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_Sage Intacct 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 Sage Intacct 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.

Sage Intacct template installation complete

The Sage Intacct template is installed. To adapt it to your company and see what it contains, continue with these pages:

• Customize the Sage Intacct template
• Sage Intacct predefined analyses