Connect Microsoft Power BI to Sage 50

Updated On: February 6, 2025 5:00 am

SAGE 50 and SAGE 100 are the most popular accounting tools for small to medium-sized businesses. For most managers and business owners it is important to have various accounting details and KPI’s in a power BI dashboard. When a user types ‘connecting Power BI to Sage’ in Google, one finds quite a few paid connectors. However, this is found to be quite unnecessary, as Sage offers a native way to establish these connections using ODBC. When we install Sage, the Open Database Connection (ODBC) driver also gets installed. This driver thus, allows the connection between Power BI and the Sage database. To access this driver, one needs to have Sage Running and logged in as well. Sage will function as the database server to which one can connect using ODBC. In this article, we will try to explain how to best use these tools in order  to connect Microsoft Power BI to Sage 50 accounting software to create dashboard, sshipping performance, and reporting activity that help make better business decisions.

What is the Use of Connecting Microsoft Power BI to Sage 50?

Sage 50 is one of the best accounting software with many latest tools, sales order invoice processing, etc. It also stores a huge amount of data, customer information as well as company information. Sage is used to converting to the sales order, manage the product stock, and invoice customers on completion. This is also used to monitor completion of shipments and many things and all the things are stored in the sage database on the server.

How to Connect Microsoft Power BI to Sage 50 accounts?

Sage 50 has an ODBC driver, which is used to connect as this is an unavailable data source in Power BI. To connect the same you need to have an ODBC driver for sage 50 installed, updated Microsoft excel, and basic knowledge of power BI.

To Set the user permissions

To connect to this ODBC driver, you will need to acquire the user rights. These rights can be offered by the Sage Administrator by enabling the 3rd party app access within ‘Setup Users and Roles’. To get this done, you need to follow the below steps:

• Navigate to Sage
• Next, go to the setup menu and launch at the bottom of this dropdown menu ‘Setup Users & Roles…’.
• In case this item is not in the setup menu, you are not logged in as an admin.
• Next, go to a list of users.
• Now, select the user you would like to offer the 3rd party app access rights.
• Finally, click ‘Modify’.
• Go to the next window and select ‘read-only access’ or ‘read and write access’ within ‘Rights with Third-party products’.
• Now, save the settings by clicking ‘OK’.

Also Read: Setting Up Accounts Payble in Sage 50

To Connect Power BI With the Sage database

Now that the required user rights are met, you can gain access to Microsoft Power BI with the Sage database by adding an ODBC data source. To do this:

• Click on ‘More…’ within the menu along with data sources within the Microsoft Power BI desktop.

• Next, you need to search for ODBC in the menu with data sources, select ODBC, and click ‘Connect’.

• Within the next window, you need to select the database that you want.
• This is the Sage 50: database that you wish to access.
• Next, click on Connect.

Note: It is important that Sage needs to be running and logged in to that specific database when doing this step.

• Generally, you will be prompted to enter your Sage login details.
• When this does not happen and you get an error instead try to delete and re-add the ODBC. connection.
• This can be done with the help of the ODBC tool by searching for OBDC within the Windows start menu.

• Next, launch the ODBC tool by clicking ‘ODBC Data Sources’, which will open another screen.

• Within this screen, you need to select the DSN that you failed to open with Microsoft Power BI and click Configure.
• Next, copy–-paste all the details within the different fields in a notepad document.

• After this, close the windows by clicking cancel
• Next, remove the DSN by clicking ‘remove’.
• Now add a new DSN by clicking ‘add’ and selecting (in the case of Sage 50 ‘MySQL ODBC 8.0 ANSI Driver’ Populate the fields exactly as they were with the removed DSN.
• Finally, try again to connect in Microsoft Power BI as explained previously.

To Import tables from the Sage 50 Database Over to Power BI

After a connection has been established the ‘Navigator’ window should open within Power BI. Now you can start to add and connect the different tables within the database.

When trying to connect the right tables Power BI and Sage can be quite a powerful combination. As Power BI allows you to simultaneously use various data sources, one can create very extensive dashboards and combine several multiple Sage databases within a single Dashboard. The good thing about ODBC connections is that they can tap directly into the Sage database, which implies that one can schedule refreshes so that the data within your dashboards remains up to date. To make dashboards function smoothly and integrate them with Sage users who are trying to process the transactions within Sage at the same time requires some thoughts about the IT data infrastructure.

Also Read: Create New Sage ID Login

How to Create an Excel Through Microsoft Query?

Open the blank spreadsheet in the excel then go to the Data tab. Now click on “Get data” and now under option”other”  you need to select the Microsoft query.  You will get a new list of available data sources and now if you have set up the ODBC driver for sage 50 properly you can see the names of the Sage 50 company in the list. After that select, the company >>click on open, make sure to check the “use the Query Wizard to create/edit queries” checkbox is ticked.

After that, you will be asked to enter the Sage 50 login details and then it will be redirected to the query wizard, where you need to select the required column from the sage data tables.

Now stick with a single table, as you can merge these more easily in Power BI after the once link has been created. If you will choose the field, you can filter and limit the data by column values but prefer to do the manipulation in Power BI to minimize the number of connections that need to be made, just import all data for the column. Limit your query to only the required fields because too many required fields can cause the errors.

Now in the last step of query wizard, select the option to edit the query in Microsoft query instead of selecting the option to import the data to excel.  Now click the SQL button. After this step copy your SQL query either directly in too power BI or into a text file so that you can easily find it later.

• Create an Updating Report using Sage 50 Data

Once you have a running query you can easily start the report in Power BI. Sage 50 accounts can store both the sales and other data as well, it can also save the data of the customer accounts. You can link these tables with the Power BI to get useful awareness about the customer activity

• Publish your Report to the PowerBI Service

To share the report and schedule updates of your reports there is a need of sharing it to powerBI service. Once the report is finished, click on the publish button in powerBI desktop to publish your report to web service.

• Set up your Data Gateway

This process is to diverge depending on the IT policy. Microsoft allows you to create a link between your computer through which you can access the data and the powerBI web service. You need to keep turn on for this connection and need to install on an office computer which may access sage but no need to switch off.

• Schedule Data Refresh

Now its turn to schedule your data set from sage 50 to refresh automatically. Open the data set view in the Power BI service and select the scheduled refresh option. While entering the schedule refresh settings, you need to set up how often and when you need to refresh the data. There may be a need of entering the login details. Once you see a good connection response and have scheduled the refresh it’s all done.

Also Read: How to Fix Sage 50 Data Corruption Error?

Conclusion!

To get the solutions from the Sage 50 helpdesk simply dial Sage 50 technical support number and get connected with the technicians. Sage experts are professional, trained, and educated. You need all the solutions related to sage here at a single point of contact. Sage helpline is available 24*7 to assist you with the resolutions. You can also email at [email protected] and you will be contacted by the Sage experts soon.

Frequently Asked Questions: