Configuration Specific to Each System

Sage X3

Opening Balance

If your accounts are not closed in Sage X3, Opening Balances must be loaded for each fiscal year.

Opening Balances generated from UDM are more detailed than those from Sage X3.

Therefore, this is why we highly recommend that you use Opening Balances from UDM and not the ones generated by the closing fiscal year from Sage X3.

Important

Opening Balances must be generated before the loading of the Finance Staging cube.

To generate Opening Balances:

  1. Make sure the @@X3SIMULATED global variable is enabled:
    1. In the Administration section , click on Global Variables in the left pane.
    2. Make sure the Script value is set to YES. If not, click on , change the value and click on Update.
  2. In the Data Models and Views tab, expand the Universal Data Model folder.
  3. Expand the Configuration and Sage X3 Specific folders.
  4. Right-click on the Opening Balance data model and select View Info Pages.
  5. In the Fiscal Year fields (From and To), enter the year you want to generate the Opening Balances for and click on Calculate Open Balance depending on your server (SQL or Oracle).

Mapping of Analytical Dimensions

Important

In order to use the Analytical Dimensions, you must first do the mapping before loading the Finance Staging cube.

In order to do so:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Configuration and Sage X3 Specific folders.
  3. Expand the Analytical Dimension Mappings data model and open the Mapping view.
  4. Click on the cell of the module column then click on the Prompt symbol (purple question mark).
  5. In the Prompt window, select the Dimension you want to map to the Analytical Dimension ID and click on Confirm.

Note

Sage X3 offers Analytical Dimensions for most of modules.

In another hand, UDM allows you to set 5 modules up to 20 Analytical Dimensions per module.

Example

Here, we selected 5 modules (columns) for which we mapped the 4 first Analytical Dimensions only for the first module.

In other words, for the Grand Ledger (GL) Module:

  • For the Analytical Dimension with ID 01, it will refer to CC (Cost Center)
  • For the Analytical Dimension with ID 02, it will refer to DIV (Division)
  • For the Analytical Dimension with ID 03, it will refer to IND (Industry)
  • For the Analytical Dimension with ID 04, it will refer to MAR (Market)
  1. After having loaded the Finance Staging cube (refer to Building and Loading Cubes for more details), whenever you will use Analytical Dimension (From Analytical Dimension 01 to Analytical Dimension 04 in relation to the example above) in your Views (in the right panel, click on to get access) from the Finance data model, it is the values you set in the Mapping view that will be used.

Sage 100

If you are using DataSync, do as follows. If not refer to Installation on Production Environment (Without DataSync).

For Sage 100, there is no Chart of Accounts to set. Chart of Accounts are the same as Company IDs.

The only thing you need to do is to map the reference currencies to each of the companies.

In order to do so:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Finance and Configuration folders.
  3. Right-click on the Sage 100 Ledger Currency Setup data model and select View Info Pages.
  4. Under the Load Companies section, click on Import and close the window.
  5. Expand the Sage 100 Ledger Currency Setup data model and open the Ledger Currency Setup view.
  6. In the Ledger Currency column, enter for each Company their reference currency.

Installation on Production Environment (Without DataSync)

If you are not using DataSync, you need to set up Global Variables before building an loading the cubes.

  1. In the Administration section , click on Global Variables in the left pane.
  2. In the upper right-hand corner, enter 100 in the Search Bar and press the Enter key.
  3. Click on the pencil icon to edit the three following Global Variables:
    1. For @@UDM_S100P_CPY: Enter the company code in the Script and Value fields.
    2. For @@UDM_SAGE_100_LIVE_COA_CODE: Enter the Chart of Account Code of the company in the Script and Value fields.
    3. For @@UDM_SAGE_100_LIVE_CURRENCY: Enter the currency of the company in the Script and Value fields.

Sage 300

Mapping of Chart of Accounts

To set the Chart of Accounts for each Company available in Sage 300, you first need to import the Companies.

To do so:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Finance and Configuration folders.
  3. Right-click on the Sage 300 Chart of Accounts Setup data model and select View Info Page.
  4. Under the Refresh Companies section, click on Import.
  5. Once imported, assign a Chart of Accounts code to each of them.

If the Companies share the same account structure, assigning the same Chart of Accounts is recommended.

If not: assign a different code of Chart of Accounts and perform an Account Grouping (refer to Account Grouping for more details).

To assign a code of Chart of Accounts:

  1. Expand the Sage 300 Chart of Accounts Setup data model and open the Company Setup view.
  2. In the Chart of Account column, select a row and enter a code.
Important

Make sure to use a code with no spaces and no special characters.

Mapping of Analytical Dimensions

Note

No mapping is required for Account Segments as they are already mapped in the UDM DimSubaccounts cube.

Regarding the Account Level Option Fields, compared to the version 8 of Nectari, they are now mapped to User Defined Fields (UDF) in the UDM Accounts cube.

In order perform analyses on Optional Fields, we must first map the Optional fields to Analytical Dimensions in UDM.

To do so:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Configuration folder and Sage 300 Optional Fields Mapping data model.
  3. Open the Analytical Axes view.
  4. Click on one of the row of the Optional Fields columns then click on the Prompt symbol (purple question mark).
  5. In the Prompt window, select the Field Code you want to map to the Analytical Dimension ID and click on Confirm.

Choosing a default Sales Representatives

In Sage 300, you can assign multiple Sales Representatives to a Customer and assign a Percentage to them but there is no flag(indicator) to define the default Sales Representative. In order to define the default Sales Representative, we need to import the Customers with the 5 possible Sales Representative to a new table.

To assign a default Sales Representative:

  1. In the Data Models and Views tab, expand the Universal Data Model and Configuration folders.
  2. Right-click on the Sage 300 Customer Default Sales Rep data model and select View Info Page.
  3. Under the Generate Sales Rep List section, click on Generate.
  4. Expand the Sage 300 Customer Default Sales Rep data model and open the Default Sales Rep view.

In the generated list, the default Sales Representative is chosen by being the one with a higher percentage than the others. However, in cases where there are 2 Top percentage Sales Representative, you will need to define it manually through the Default Sales Rep column.

Note

Unless you never plan to use the Default Sales Representative feature on a Customer for your BI Analysis (and use only the one assigned to an invoice or order), you need to re-generate the list as described above whenever you add new Customers.

Acumatica

Mapping of Company

To set the Chart of Accounts for each Company available in Acumatica, you first need to import the Companies.

To do so:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Finance and Configuration folders.
  3. Right-click on the Acumatica Chart of Accounts Setup data model and select View Info Page.
  4. Under the Refresh Companies section, click on Import.
  5. Once imported, assign a Chart of Accounts code to each of them.

If the Companies share the same account structure, assigning the same Chart of Accounts is recommended.

If not: assign a different code of Chart of Accounts and perform an Account Grouping (refer to Account Grouping for more details).

To assign a code of Chart of Accounts:

  1. Expand the Acumatica Chart of Accounts Setup data model and open the Company Setup view.
  2. In the COA column, select a row and enter a code.
Important

Make sure to use a code with no spaces and no special characters.

Opening Balances

Important

Opening Balances must be generated before the loading of the Finance Staging cube.

To generate Opening Balances:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Finance and Configuration folders.
  3. Right-click on the Acumatica Opening Balance data model and select View Info Pages.
  4. In the Year fields (From and To), enter the year for which you want to generate the Opening Balances.
  5. [Optional step] In the Company field, click on the Prompt symbol (question mark) to select the company for which you want to generate the Opening Balances.
  6. Click on Run.

Sage 200 UK

Depending on the type of installation performed, the Data Models used for the specific configuration will be in either the Finance or Configuration folder.

Important

For the data to be taken into account, the steps below must be completed before loading the UDM cubes.

Editing the Global Variable

Important

This section applies only to Single-Company environments, where DataSync is not used.

  • Edit the Global Variable called @@UDM_200UKLIVECPYID by clicking on the pencil icon located on the right.
  • Change the value in the Script field and click on Update.
  • You should have a result similar to this:
Important

Make sure to use a code with no spaces and no special characters.

Opening Balances

Important

Opening Balances must be generated before loading the Finance Staging cube.

To generate Opening Balances:

  1. Right-click on the Sage 200 UK Opening Balance data model and select View Info Pages.
  2. In the Fiscal Year fields (From and To), enter the year you want to generate the Opening Balances for and click on Generate.

Updating the Fiscal Calendar

After installing the template or after generating new fiscal years from Sage 200 UK, you must update the Fiscal Calendar so that the Finance data models can take into account all fiscal years.

  1. Right-click on the Sage 200 UK Update Fiscal Calendar data model and select View Info Pages.
  2. Click on Update.

Mapping of Chart of Accounts

To set the Chart of Accounts for each Company available in Sage 200 UK, you first need to import the Companies.

Important

This section applies only to Multi-Company environment. If you are using a Single-Company environment, the Chart of Accounts will be hardcoded S200-LIVE.

To do so:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Finance and Configuration folders.
  3. Right-click on the Sage 200 UK Chart of Accounts Setup data model and select View Info Page.
  4. Under the Refresh Companies section, click on Import.
  5. Once imported, assign a Chart of Accounts code to each of them.

If the Companies share the same account structure, assigning the same Chart of Accounts is recommended.

If not: assign a different code of Chart of Accounts and perform an Account Grouping (refer to Account Grouping for more details).

To assign a code of Chart of Accounts:

  1. Expand the Sage 200 UK Chart of Accounts Setup data model and open the Company Setup view.
  2. In the Chart of Account column, select a row and enter a code.
Important

Make sure to use a code with no spaces and no special characters.

Generating Retained Earnings

To obtain the amount of Retained Earnings for the Equity section:

  1. Right-click on the Sage 200 UK Retained Earnings data model and select View Info Pages.
  2. In the Fiscal Year fields (From and To), enter the year you want to generate the Opening Balances for.
  3. In the Account Number field, enter the account used for Retained Earnings and click on Generate.

Sage Intacct

Mapping of Chart of Accounts

To set the Chart of Accounts for each Company available in Sage Intacct, you first need to import the Companies.

To do so:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Finance and Configuration folders.
  3. Expand the Sage Intacct folder.
  4. Right-click on the Chart of Accounts Setup data model and select View Info Page.
  5. Under the Import Companies section, click on Import.
  6. Once imported, assign a Chart of Accounts code to each of them.

If the Companies share the same account structure, assigning the same Chart of Accounts is recommended.

If not: assign a different code of Chart of Accounts and perform an Account Grouping (refer to Account Grouping for more details).

To assign a code of Chart of Accounts:

  1. Expand the Chart of Accounts Setup data model and open the Company Setup view.
  2. In the COA column, select a row and enter a code.
Important

Make sure to use a code with no spacesandno special characters.

Opening Balances

Important

Opening Balances must be generated before the loading of the Finance Staging cube.

To generate Opening Balances:

  1. In the Data Models and Views tab, expand the Universal Data Model folder.
  2. Expand the Finance and Configuration folders.
  3. Right-click on the Sage Intacct Opening Balance data model and select View Info Pages.
  4. In the Year fields (From and To), enter the year for which you want to generate the Opening Balances.
  5. Click on Generate.