Connecting to Sage without buying a connector?
SAGE 50 and SAGE 100 are two of the most used accounting tools for small to medium businesses. A common wish for managers and business owners is to have accounting information and KPI’s in a power BI dashboard. When we type ‘connecting Power BI to Sage’ in Google we find a lot of paid connectors. This is all nonsense as Sage provides a native way to establish these connections via ODBC.
If we install Sage, the Open Database Connection (ODBC) driver is also installed. This driver facilitates the connection between Power BI and the Sage database. To have access to this driver, we need to have Sage Running and logged in. Sage will function as the database server to which we connect via ODBC. In this tutorial I explain how to use these tools to combine Power BI with Sage accounting software to create the dashboards that help making better business decisions.
Setting user permissions
To connect to this ODBC driver, we need to have the user rights to do this. These rights can be given by the Sage Administrator by enabling 3rd party app access in ‘Setup Users and Roles’. To do this work trough the following steps:
1. go in Sage to the setup menu and open at the bottom of this dropdown menu ‘Setup Users & Roles…’. If this item is not in the setup menu, you are not logged in as an admin.
2. Then, you get to a list of users. Select the user you would like to give the 3rd party app access rights and click ‘Modify’.
3. In the next window (see screenshot), select ‘read only access’ or ‘read and write access’ under ‘Rights with Third-party products’ and save the settings by clicking ‘OK’.
Connecting Power BI to the Sage database
Now we have obtained the appropriate user rights, we can acquire access in Microsoft Power BI to the sage database by adding a ODBC data source. To do this, click ‘More…’ in the menu with data sources in Microsoft Power BI desktop.
Then, we search for ODBC in the menu with data sources, select ODBC and click ‘Connect’.
In the next window we select the database that we want. This is the Sage 50: database that you want to access, (‘Test’ in our case) and we click connect. Note that Sage needs to be running and logged in to that specific database while doing this.
Normally you should be prompted to enter your Sage login details. If this doesn’t happen and you get an error instead try to delete and re-add the ODBC connection. We can do this with the ODBC tool by searching for OBDC in the Windows start menu.
Open the ODBC tool by clicking ‘ODBC Data Sources’, which will open the following screen:
In this screen you select the DSN you failed to open with Microsoft Power BI, and click configure. Then, copy – past all the information in the different fields in a notepad document.
Then, close the windows by clicking cancel and remove the DSN by clicking ‘remove’. Then, Add a new DSN by clicking ‘add’ and select (in case of Sage 50 ‘MySQL ODBC 8.0 ANSI Driver’ Populate the fields exactly as they were with the removed DSN. Then try again to connect in Microsoft Power BI as explained earlier.
Importing tables from the Sage 50 database into Power BI
If a connection is established the ‘Navigator’ window should open in Power BI. Now you can start to add and connect the different tables in the database. The table shown in the example, Tjourent in the Simply database is where Sage 50 saves all this year’s journal entries.
When connecting the right tables Power BI and Sage can be a very powerful combination. Since Power BI allows you to simultaneously use different data sources, we can create very extensive dashboards and combine even multiple Sage databases in one Dashboard. The nice thing about ODBC connections is that they tap directly into the Sage database, which means that we can schedule refreshes such that the data in your dashboards remains up to date. To make dashboards work smoothly and integrate them with Sage users processing transactions in Ss\age the same time requires some thoughts about the IT data infrastructure. I have configured this type of Power BI dashboards with supporting infrastructure in many different setups and environments. For questions or help with these types of projects, feel free to contact me!