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

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.