{"id":187,"date":"2023-08-18T16:21:15","date_gmt":"2023-08-18T16:21:15","guid":{"rendered":"https:\/\/www.spakman.pro\/?p=187"},"modified":"2024-02-11T15:30:51","modified_gmt":"2024-02-11T15:30:51","slug":"how-to-connect-powerbi-to-sage","status":"publish","type":"post","link":"https:\/\/www.spakman.pro\/index.php\/how-to-connect-powerbi-to-sage\/","title":{"rendered":"How to connect Power BI to Sage 50 (Canada)"},"content":{"rendered":"\n

Connecting to Sage without buying a connector?<\/h2>\n\n\n\n

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\u2019s in a power BI dashboard. When we type \u2018connecting Power BI to Sage\u2019 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.<\/p>\n\n\n\n

Setting user permissions<\/h2>\n\n\n\n

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<\/sup> party app access in \u2018Setup Users and Roles\u2019. To do this work trough the following steps: <\/p>\n\n\n\n

1. go in Sage to the setup menu and open at the bottom of this dropdown menu \u2018Setup Users & Roles\u2026\u2019. If this item is not in the setup menu, you are not logged in as an admin. <\/p>\n\n\n\n

2. Then, you get to a list of users. Select the user you would like to give the 3rd<\/sup> party app access rights and click \u2018Modify\u2019. <\/p>\n\n\n\n

3. In the next window (see screenshot), select \u2018read only access\u2019 or \u2018read and write access\u2019 under \u2018Rights with Third-party products\u2019 and save the settings by clicking \u2018OK\u2019.<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

Connecting Power BI to the Sage database<\/h2>\n\n\n\n

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 \u2018More\u2026\u2019 in the menu with data sources in Microsoft Power BI desktop.<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

Then, we search for ODBC in the menu with data sources, select ODBC and click \u2018Connect\u2019.<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

In the next window we select the database that we want. This is the Sage 50: database that you want to access, (\u2018Test\u2019 in our case)  and we click connect. Note that Sage needs to be running and logged in to that specific database while doing this.  <\/p>\n\n\n\n

\"Screenshot,<\/figure>\n\n\n\n

Normally you should be prompted to enter your Sage login details. If this doesn\u2019t 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.<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

Open the ODBC tool by clicking \u2018ODBC Data Sources\u2019, which will open the following screen:<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

Then, close the windows by clicking cancel and remove the DSN by clicking \u2018remove\u2019. Then, Add a new DSN by clicking ‘add’ and select (in case of Sage 50 \u2018MySQL ODBC 8.0 ANSI Driver\u2019 Populate the fields exactly as they were with the removed DSN. Then try again to connect in Microsoft Power BI as explained earlier.<\/p>\n\n\n\n

Importing tables from the Sage 50 database into Power BI<\/h2>\n\n\n\n

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\u2019s journal entries.  <\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

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<\/a>!<\/p>\n","protected":false},"excerpt":{"rendered":"

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. …<\/p>\n","protected":false},"author":1,"featured_media":199,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"_links":{"self":[{"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/posts\/187"}],"collection":[{"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/comments?post=187"}],"version-history":[{"count":3,"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/posts\/187\/revisions"}],"predecessor-version":[{"id":465,"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/posts\/187\/revisions\/465"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/media\/199"}],"wp:attachment":[{"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/media?parent=187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/categories?post=187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spakman.pro\/index.php\/wp-json\/wp\/v2\/tags?post=187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}