Sage 50 - Installation Guide

Installation and Configuration Instructions for pi and ETL

Requirements

• pi dashboard

• pi Sage 50 install files

• Sage 50 - v23 or above with valid login credentials

• Microsoft SQL Server

This guide will explain:

• Sage 50 install files

• Downloading and installing MSSQL Server Express (you can use an existing version if you already have it installed)

  • Creating a database

  • Creating a login

• Setting the connections with Sage 50 and a SQL database

• Setting up the ETL file

• Downloading and installing the latest pi dashboard

• Restoring the Sage 50 repository into the pi dashboard

• Updating the dashboard data connections with user-specific information (if not default) such as usernames, passwords and host names

pi Sage 50 install files

Download the Sage 50 install files from the Panintelligence Customer Portal and extract ALL files into the same folder in a location of your choice on the server.

 The files included are:

• Sage50_Connections.exe

  • This is to set up the credentials being used for SQL Server and Sage 50

• Sage50_ETL.exe

  • This is the process that will copy the data from Sage 50 and move this to SQL Server making it relational and accessible by pi

• Sage50_Repos.sql

  • This is the preconfigured dashboard repository that is to be restored into pi

• Sage50_Script.sql

  • This is an SQL file that the ETL process uses each time it runs

• Sage50_Views.sql

  • These are SQL views that have been created for some of the more complex calculations required for the dashboard and are checked each time the ETL process is run

• Exclude_Tables.txt

  • This is a config file containing a list of tables from Sage 50 that are excluded from the ETL process as not required in the default build. If a table is required then it can just be removed from this file, alternatively a table can be added to this file if you require it to be excluded

• PIDataSources.txt

  • This is used to list the data source name of the ODBCs that are used for the data transfer

SQL Configuration

The following explains how to:

• Set SQL Server Authentication Mode

• Create a new database

• Create a new user

• Set the TCP Port for inbound connections

• Firstly, open SQL Server Management Studio (SSMS) and login to the server you require

  • If this was set up as default it should look something like this:

    

• Make SQL Server authentication mode available

  • Right click on the Server name at the top left and select Properties

    

  • Select Security on the left-hand side, choose SQL Server and Windows Authentication mode and click OK

    

  • You may get asked to restart the server. Please check that this server is not in use by any other users before completing this action

• Create a new user:

  • Expand Security and right click Logins, now select New Login… from the options

    

  • Add the login name PI_Sage50 (this can be anything you like but remember the details as you will require these later) and select SQL Server Authentication. Use the password dashboard (this is the default password used in the pi data connections but can be anything you like; you will just be required to change these details later) and untick Enforce password policy as shown below:

    

  • Select Server Roles on the left-hand side, tick sysadmin and click OK

    

• Create a new database:

  • Right click on Databases and select New Database…

    

  • Name the new database PI_Sage50 (this can be anything you like but remember the details as you will require these later) and click OK

    

• Set the TCP Port for inbound connections:

  • Open Computer Management from the start menu and find SQL Configuration Manager

  • Expand SQL Server Network Configuration

  • Select Protocols for SQLEXPRESS

  • Right click on TCP/IP and select Properties

  • On the Protocol Tab, make sure this is set to Enabled

  • On the IP Addresses tab, scroll right to the bottom and set the TCP Port as 1433

    

  • You will be asked to restart the SQL Server (this is JUST the SQL Server, not the machine server). Please check that this server is not in use by any other users before completing this action

  • The Server can be restarted by Selecting SQL Server Services on the left-hand side, right-click the SQL Server in the central console and click Restar

  • 

Setting up the Sage ODBC connections (adding a company)

Sage 50 allows multiple companies which is accounted for in the Pi Sage 50 installer. To add more than 1 company, just follow the below process as often as required.

·         Search for ODBC in the windows task bar and select ODBC Data Sources (64 bit)

·        

·         Once the dialog box opens, select the System DSN tab and click Add… to create a new ODBC.

·        

·         Then select the Sage driver that is the version relevant to Sage 50 you have installed and click Finish.

·         Please note that if you cannot see the version available, this can be downloaded from the Sage website for free, search for “Sage 50 ODBC driver”.

·        

·         You will then be presented with options to add a Data Source Name (DSN) and a Data Path.

·         Enter a name into the DSN (with no spaces) and add the Data Path for this company for Sage 50 and click OK.

·         Please note – The data path can be found in the About screen within Sage 50

o   e.g. C:\Programdata\Sage\Accounts\2018\Company.002\ACCDATA

·        

·         Now add the name used in the DSN to the PIDataSources.txt file (this is how the ETL knows which ODBC connection(s) to use) and save the file.

·        

·         Repeat the above process if you require to add multiple companies.

Setting the connection details (Sage 50 & SQL Server)

·         Open the Sage50_Connections.exe file and enter the details (following the prompts) for the SQL Server (you have just set up) and the login details of the Sage 50 user you would like the process to use.

·         On first setup just select “Y” when asked if overwriting settings.

·         After typing each piece of information just press ENTER.

·        

·         If any of the credentials are not correct – invalid usernames or passwords – then the error will be shown. The process can just be repeated to correct any errors.

·         Once the credentials are successful, this will create a file in the same directory called PiConnections.db – this is an encrypted file that stores the credentials you have entered and is used by the ETL process.

·         If the credentials change or need updating just re-run the file and enter them again selecting “Y” when asked.

Running the ETL process

·         Once the connections have been set up successfully, you can run the Sage50_ETL.exe file by double clicking. This will copy the Sage 50 data across into the SQL Server database and give you a notification when complete.

·        

·         The duration of the process varies dependent on the volume of data and the hardware infrastructure.

·         If there are any errors, they will be shown on screen and also an error log file is created in the same directory called:  Sage50DataErrorsLog.txt

·         Please see the troubleshooting section for help with potential errors.

Setting up a scheduled ETL process

·         Using the Windows Task Scheduler, the Sage50_ETL.exe can be scheduled to run at a frequency of your choosing or ran manually. Please consider the length of the process when setting the frequency of your schedule.

·         Within the preconfigured dashboard there is a category called Data Update that provides information on when the data was last updated.

Download & Install Pi

pi Dashboard Installation Guide - Windows

Restoring the Sage 50 Pi Dashboard

·         Restore the Sage50_Repos.sql file into the Pi Dashboard as per standard restore process see here: Backup and Restore of the Dashboard

·         Default login credentials for the dashboard are:

o    Username: admin

o    Password: dashboard

·         If you have changed any of the default names or password throughout the process, you will need to go to the dashboard configuration screen (spanner) and then Settings tab.

·         Update the Variable values that correspond to your inputs as these are used in the Data Connections.

·         The defaults are:

o    DATABASE_SERVER: localhost

o    DATABASE_NAME: PI_SAGE50

o    DATABASE_USERNAME: PI_SAGE50

o    DATABASE_PASSWORD: dashboard

o    DATABASE_PORT: 1433

·         Once saved and refreshed, you should now have the dashboard running.

Troubleshooting

·         When Sage has been updated (change of version) make sure to update the driver that this process is using too.

·         Check you have the latest SQL Server ODBC driver available.

·         Check you have the correct Sage driver for the version you have.

·         Check all credentials are correct:

o    Use them in SQL Server and Sage to make sure they are working correctly.

·         Check the error log file in the directory for more information.