Universal Data Model Template

Use the following links to quickly jump to a section:

• Universal Data Model Template
• Introduction
• Data Sources Configuration
• Import Templates
• Update template
• Building and Populating OLAP Cube
• Universal Data Model Configuration

Introduction

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.

Data Sources Configuration

Environments and Data Sources

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:

Universal Data Model Data Source

Type:

SQLSERVER

Server:

Database server of Universal Data Model

Database name:

Name of the Universal Data Model database (beware of the case sensitivity)

Database schema name:

Create the 2 following entries by clicking on the icon (replace DatabaseName by the appropriate value):

DatabaseName.dbo

DatabaseName.NEC_CUSTOM_SCHEMA

Note

This second line contains the Nectari Custom Schema.

You can use a different one, but we highly recommend following this naming convention:

• Start with NEC
• Use all capitals
• Separate words by underscore

Important

Choose a unique Custom Schema name for each Environment.

Nectari schema:

Enter the chosen Nectari custom schema for the current environment

Authentication stategy:

UseSpecific

User Name:

SQL User accessing the Universal Data Model database. For example, sa.

Password:

Password of the user.

6. Click on Validate then on Save to complete the configuration of the Data Source.

Oracle Database

Instructions for MS SQL Server precede

Datasource description:

Universal Data Model Data Source

Type:

ORACLE

Server:

Name of the Oracle server

SID and Port:

SID and Port of the Universal Data Model database instance

Database schema name:

Create 2 entries by clicking on the + icon:

dbo

NEC_CUSTOM_SCHEMA

Note

This second line contains the NectariCustom Schema.

You can use a different one, but we highly recommend following this naming convention:

• Start with NEC
• Use all capitals
• Separate words by underscore

Important

Choose a uniqueCustom Schema name for each Environment.

Nectari schema:

Enter the chosen Nectari custom schema for the current environment.

Authentication stategy:

UseSpecific

User Name:

For example: system.

Password:

Password of the user.

• Click on Validate then on Save to complete the configuration of the Data Source.

Cube Data Source

In the same Environment as the ERP Data Source, create a new Data Source for the OLAP Cube.

Complete the Data Source Definition with all the appropriate information.

An example screenshot follows

Server:

Database server where the package: Nectari OLAP For SQL Server is installed.

Database name:

NectariCube.

Database schema name:

NectariCube.NEC_FOLDER (replace FOLDER by the folder name) .

Where NEC_FOLDER (replace FOLDER by the folder name) is the schema used in the ERP Database of the same environment.

Nectari schema:

Enter the chosen custom schema for the current environment

• At this point, click on Validate then on Save.
• Click on Set as Data Warehouse to define the Data Source as a Data Warehouse then enter the following information:

Database warehouse schema:

Enter again the chosen Nectari custom schema

Use MARS during the cube loading:

Unchecked

• Click on Validate then on Save.
  Tip

  Refer to Environments and Data Sources in the Administrator section for more details about the MARS option .

Oracle Database

Instructions for MS SQL Server precede

Server:

Name of the database server where the Central Point is installed

SID and Port:

SID and Port of the Oracle database instance

Pooling:

Activating this option will improve performance (Refer to Environments and Data Sources in the Administrator section for more details).

Database schema name:

NEC_CUSTOM_SCHEMA

Where NEC_CUSTOM_SCHEMA is the custom schema used in the ERP database of the same environment

Nectari schema:

Enter the chosen Nectari custom schema for the current environment.

Database warehouse schema:

After saving the data source and set it as a data warehouse, enter the chosen Nectari custom schema for the current environment.

• Click on Validate then on Save to complete the configuration of the Data Source.

Import 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.X_UDM_FINANCE.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.

5. In the Import Template window, click on Select files....
   
6. Browse to the folder where you saved the Template.zip file in order to select it then click on Open.
7. 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, only keep ticked the Data Sources checkboxes you want to use from the template.
• 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 the content of the Templates will be displayed, compared with what Central Point already has.

By default, on the first install, everything will be set as 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 Update template for more details.

• If ERP templates were installed already, some items will display Update or Skip, because they are shared in different templates. Keep the default values.

1. Click on Next (this can take time).
   

2. Once this has been completed, a window will be prompted to input the necessary parameters to create the custom objects.
3. 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.

4. Complete the parameters, see examples below, and click on Next.
   

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

   It has a first section for the ERP Data Source and a second one below 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.

6. 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 separately if needed. Users who are very proficient with SQL can debug it straight in the Report Preview window and run it by clicking on the Try to rerun button.
  

Update template

When doing 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.

To update a template:

1. After having mapped the Data sources, tick the checkboxes of the objects you want to upgrade an 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.

2. Select the environment in which the scripts will be executed and click on Next.
3. Complete the parameters and click on Next.
4. 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.
5. Copy this script, debug, and run separately if needed. Users who are very 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 Populating OLAP Cube

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 this, you can refer to Scheduler in the Administrator section for more details.

Universal Data Model Configuration

To use the UDM template, you must first follow these configuration steps.

The Universal Data Model template can only load Field descriptions in one language.

By default the language is ENG.

If the ERP is in a different language than ENG, you can change this value:

1. In the Administration section , click on Global Variables in the left pane.
2. Find the variable UDM_LANG and change ENG by the external code of the language to use, as defined in Languages.

As for the names of the account groups used in the template, they are available in English and French and the connection language of the user is used to determine which.

If the users might connect in a different language, you can hardcode a language by changing ##CurrentLang by the external code of the language to use in the variable @@UDM_RPTLANG.

Build and load the cubes

• With OLAP Manager, Build all the UDM cubes (Refer to the OLAP documentation).
• With OLAP Manager, Load All the cubes except for UDM Finance Staging (UDM_FINANCESTAGING) and UDM Finance (UDM_FINANCE).

ERP Specific configuration

In this section, follow the instructions corresponding to the ERP systems that you use before continuing to the next section.

Sage Enterprise Management

Set the Analytical Dimensions

There are 20 Analytical Dimensions available in the UDM Template.

To define the Analytical Dimensions:

• Find the Data Model Analytical Dimensions in the Configuration for Enterprise Management Specific Setup folder.
• Open the view Analytical Dimensions within the folder.
• In the Dimension column, enter the dimension codes.
  

Tip

You can also use the to select the values from the list then click on Apply to add the dimension code.

Note

The view is editable and the values are automatically saved.

Note

The order of the dimensions defined in this view will always be the same.

Tip

To change the name of the Dimensions (to see for example Department instead of Analytical Dimension 1 in the Views and Dashboards:

1. In the Data Models and Views tab, locate the Finance Data Model and right-click on it
2. Choose Translate Data Model
3. In the Fields tab, locate the dimensions: from udm_finance_dimension1 to udm_finance_dimension20 and use the desired name for the Dimensions.

 

Create the Opening Balances

Opening Balances can be created either in the ERP or in Nectari.

If they are done in Sage Enterprise Management, they will be automatically used.

If they are done in Nectari, do the following:

1. Right-click on the Data Model Opening Balances and select View Info Pages.
2. Enter the Years for which to calculate the Opening Balances and click on Calculate Open Balance.
   

 

Sage 300

Analytical Axes

There are 20 Analytical Axes available in the UDM Template.

To define them:

1. Find the Data Model Optional Fields Mapping in the Configuration > Sage 300 Specific Setup folder.
2. Open the view Analytical Axes within the folder.

The Analytical Axes can be an account optional field, a posting optional field, or an account segment, but only one at a time.

Create the Company Structure

To create the company structure:

1. Find the Data Model Chart of Accounts Setup in the Sage 300 Specific Setup folder, right click on it and select View Info Pages.
2. Click on Import.
   

3. Open the view Company Setupto map the companies with the chart of accounts you are about to generate.
4. In the column Ledger Type , enter 1 for all the companies, and for Ledger enter Sage300 for all the companies.
   
   Note

   You can use the same chart of account for different companies with the same account structure.

 

Load the Chart of Accounts cubes

With the OLAP Manager, Load All the UDM Chart of Accounts (UDM_COA) and UDM Chart of Accounts Dictionary (UDM_COADIC) cubes. (Refer to the OLAP Documentation)

Acumatica

Create the Opening Balances

1. In Acumatica Specific Setup folder, right click on the Data Model Opening Balances and select View Info Pages.
2. Enter the Year to calculate and click on Run.
   

Create the Company structure

To create the company structure:

1. Find the Data Model Chart of Accounts Setup in the Acumatica Specific Setup folder, right click on it and select View Info Pages.
2. Click on Import.
   
3. Open the view Company setupto map the companies with the chart of accounts you are about to generate.
4. In the column COA, enter the COA code to use.
   

Note

You can use the same chart of account for different companies with the same account structure.

Load the Finance Cubes

• With the OLAP Manager, Load All the Finance Staging cube (UDM_FINANCESTAGING). (Refer to the OLAP Documentation).
• After successfully loading the Finance Staging cube, execute a Load All on the Finance (UDM_FINANCE) cube. (Refer to the OLAP Documentation).

Schedule the Refresh jobs

You can now set the regular refresh jobs to update the data in the cubes.

The UDM_FINANCESTAGING and UDM_FINANCE should be scheduled at separated time to respect the order.