In this post I’ll show how to link an on site Sage 50 Accounts database to Microsoft power BI, to create an automatically updating dashboard, reporting sales activity and shipping performance. I learn’t how to do this from scratch, so some of the methodology is a bit rough around the edges, but it works and we now have a (somewhat) live overview of our Sage 50 Data via a powerBI dashboard. It’s also free and requires very little knowledge of the inner workings of the system to implement, meaning anyone should be able to replicate it.
What is this for?
Sage 50 Accounts is a popular accounting software with both pre-sales quotation, sales order and invoice processing features. It stores a huge amount of information on company activities as well as customer and supplier information. We use Sage 50 to create quotations, convert these to sales orders when won, manage product stock and invoice customers upon completion. We also monitor completion of shipments vs. expected shipping dates to keep track of how well our packaging and manufacturing teams are doing. All of these activities are stored in the Sage database on our local server rather than a cloud platform.
Linking Sage 50 Accounts to powerBI
Sage 50 does have an ODBC driver, which would see an easy way to connect as this is an available data source in powerBI, but no matter how many times I tried to get this to work I got continuous errors with a direct ODBC connection, so I came up with a modified approach. For this to work you will need to have the ODBC Driver for Sage 50 installed, an up to date version of Microsoft Excel (we have an office 365 subscription) and a basic knowledge of powerBI.
Create a query in excel via Microsoft query
I found that the way to overcome errors with the ODBC driver link to powerBI was to give it a specific SQL query, rather than trying to use the powerBI query interface select the correct tables and columns. You don’t actually need to know and SQL to do this as Microsoft query can create the expression for you via excel. powerBI seems to throw errors when these queries go over a certain number of columns (I havn’t determined the exact number) but you can always make multiple queries and combine them in powerquery.
First open up a blank spreadsheet in excel and navigate to the data tab. Click on “Get Data” and under “Other”, select Microsoft query. You should now be presented with a list of available data sources, and if you’ve set up the ODBC driver for Sage 50 correctly, you’ll see the name of your Sage 50 “Company” in the list. Select the company and click “Open”, making sure that the “Use the Query Wizard to create/edit queries” checkbox is ticked.
You’ll be prompted for your Sage login details and then taken through the query wizard, where you can select the required columns from and of the Sage data tables.
Try to stick to a single table, as we can merge/append these more easily in powerBI once the link has been established. If you choose to, you can filter and limit the data by column values, but I prefer to do the data manipulation in powerBI to minimise the number of connections that need to be made, and just import all data for the column (this might be impractical for larger datasets but I work for a small business and it works fine for us). Limit your query to only the required fields, as too many fields can cause errors in powerBI later in the process.
In the last step of the query wizard, rather than choosing the option to import the data into excel, select the option to edit the query in Microsoft query. Now look for the little button with “SQL” on it and click on this.
Copy your SQL query, either directly into powerBI as explained below, or into a text file so you can find it later.
Create a query in powerBI using ODBC
powerBI has a standard connector for ODBC data sources. In theory you can use this connector to link directly to your Sage database but I haven’t been able to get this to work, and I’ve seen other threads compaining of the same issue. I always got this error:
Error: Attempted to read or write protected memory.
To get around this error, you can narrow the ODBC query by entering the the SQL statement we made earlier into the “SQL Statement” Field. Once you’ve entered this query, you should be taken to the powerquery interface where you can edit and transform your query.
Create an updating report using Sage 50 data
Once you have a working query, you can start to build your report in powerBI. Here’s an example of a sales report I built using data from the Invoice, Invoice Items and Sales Ledger tables, based on different individual queries.
Sage 50 Accounts can store both your sales and purchasing data as well as details of your customer accounts. You can link these tables with a powerBI relationship to get useful insights about customer activity.
Publish your report to the powerBI service
In order to share and schedule updates of your report, you’ll need to share it to the powerBI service. Once your report is finished, click the publish button in powerBI desktop to push your report to the web service.
Set up your personal data gateway
This is where the process can diverge depending on your IT policy. I work for a small business, so we dont have any streaming data connection that we can use to refresh the Sage 50 data, instead I use a personal data gateway. Microsoft allows you to create a link between your PC, where you can access the data, and the powerBI web service, using the On-premesis data gateway (personal mode). You can find the details here.
Your PC will need to be turned on for this connection to work, so its best installed on an office machine that has access to Sage but doesnt need to be switched off. If you do need to switch off, just remember to start the data gateway when you switch back on.
Schedule data refresh
Once your data gateway is set up, you can schedule your dataset from Sage 50 to refresh automatically. Open the dataset view in the powerBI service and select the scheduled refresh option.
When entering the schedule refresh settings, you just need to set up how often, and when you want your data to refresh. You might also need to enter your sage login details. Once you see a positive connection and have scheduled your refresh, you’re all done!
You can now share your reports and dashboard between employees with a powerBI pro account, or, if you have a basic account you can view the data online only yourself.
If you have any questions about the process, or any improvements or comments, just post them below.