...
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
| Info |
|---|
The following explains a default setup. If you already have an instance of SQL Server, please check with an SQL administrator before making any changes. |
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, nowselect 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 thepassword 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.