Save content
Have you found this content useful? Use the button above to save it to your profile.
Connectivity | accountingweb | ABC of Power BI: ‘O’ is for ODBC
iStock_sankai_connectivity

ABC of Power BI: ‘O’ is for ODBC

by

Continuing his ABC of Power BI series, Hugh Johnson looks at Open Database Connectivity to connect to Sage 50 Accounts.

15th Aug 2023
Save content
Have you found this content useful? Use the button above to save it to your profile.

One of the most common Power-BI-related questions I get is how to connect Power BI to Sage 50 Accounts. The answer, most definitively, is to use an ODBC connection. Not only does it provide a robust way to connect to and refresh the Sage 50 data in your Power BI report, but coupled with the Microsoft Data Gateway and the Power BI service, an ODBC connection also provides a way to automate the data refresh of your published Power BI reports. 

What is ODBC?

ODBC stands for Open Database Connectivity and it provides a standardised way for one application (such as Excel or Power BI) to query an external data source without needing to understand any of the technical details of that data source. As a standard, it dates from the early 1990s, but is still commonly used. For data analysis and reporting, it is currently the best (in my opinion) way to connect to Sage 50 Accounts. 

What is so nice about ODBC?

If you want to pull data into Excel or Power BI via ODBC, all you need to know is the Data Source Name (DSN) and any credentials necessary to connect. You don’t even need to know the tables that are available, as these are displayed through the wizard that guides you through the connection, as shown below.

list of sage 50 tables | accountingweb
odbc_list_of_sage_50_tables

 

Knowing a little bit of SQL (just a tiny bit), you can specify the table that you want to connect to, the columns that you would like to retrieve, include filters (such as date ranges, or to exclude deleted transactions, and even perform joins between tables before you load them into Power BI.

The following Power Query M Code, for example, will return the “Company” table with the four columns [NAME], [START_MONTH], [FINANCIAL_YEAR], and [DEFAULT_CHART].

ODBC query | accountingweb
odbc_query

Using this code you can create a query in Power BI, or Excel to load this table. In the example, the DSN is Sage50-AI. For Sage 50 Accounts, you will need a different DSN for each company that you would like to connect to. In this video I show you how to create an ODBC DSN for Sage 50 Accounts.

Overcoming the downside of ODBC

The downside of an ODBC connection is that its design, going back to the early 1990s, assumes that the DSN and the client application (in this case Power BI or Excel) are on the same machine. The DSN in turn needs access to the data source. In practice, for Sage 50, this means that your Sage 50 database, the DSN and Power BI/Excel all need to be on the same local network. 

If you want to provide remote access to your Sage 50 reports or data, you can use the Microsoft on-premises data gateway with the Power BI service (you will need at least a Power BI Pro subscription for each user to make this work).  

Online access to Sage 50 Accounts data and reports

The Microsoft on-premises data gateway is a free download that enables you to set up a secure connection between the Power BI service that is in the cloud and an on-premises data source (for example, a DSN to connect to Sage 50 Accounts). With this combination, you can provide remote, online access to your Sage 50 data and reports in several ways, starting with the simplest as follows.

  1. You can create your Power BI report in Power BI Desktop on the same network as Sage 50 and publish to the Power BI service. Once published, you can share access to it with anyone in your domain that has a Power BI Pro account. You can also set an automated, scheduled refresh for your report.
  2. For more flexibility you can create a dataflow of your Sage 50 tables that is effectively your Sage 50 tables online. This can be consumed by any Power BI or Excel client (online or desktop). The main advantages it offers over option 1 are as follows.
    • It means that you can author your Power BI or Excel Sage 50 reports from any desktop computer that has internet access. 
    • It provides a simple way to give access to the same data for multiple report authors.
    • You can build multiple reports of a shared dataflow, giving a single version of the truth.

No matter how many reports you write, there is only one demand on Sage 50, which comes from the dataflow. When the dataflow is refreshed it connects to Sage 50 (via the on-premises gateway) and then holds a copy of the Sage 50 data that is in turn queried by the Power BI or Excel reports. When the reports are refreshed, these connect to the dataflow as their data source. In other words, there is an asynchronous connection between your reports and Sage 50.

The disadvantage of this over option 1 above is that there are two steps to refreshing your Power BI or Excel reports. Firstly, you need to refresh the dataflow, and then you need to refresh your report. If you are happy with a scheduled refresh this is not a problem as you can schedule the dataflow to refresh first and then your reports. If you want to refresh your report against live Sage 50 data, however, this will mean you would manually have to refresh the dataflow first and then the report.

  1. For a more robust and enterprise-wide solution, you can do all of your data transformations inside dataflows so that you end up with an online pre-built data model that is optimised for financial reporting.
sage_50_financial_data_model | accountingweb | power BI
sage_50_financial_data_model

The image above shows an example financial data model built from Sage 50 in dataflows.

This is by far my current preferred approach because the necessary transformations from the raw Sage 50 tables are quite complex (it took me over six weeks to do this for the first time with Sage 50, even though I had a very good understanding of the data and the requirements). Building these transformations into dataflows means that you do them once to create a common financial data model for your entire company (or companies). It takes you one step closer to having a single version of the truth in all reports for your business. The downside is that if you are not already using Power BI Premium or Premium Per User, then it will double your Power BI licensing costs. Well worth it, in my opinion, in most situations.

In summary

  • To connect Power BI or Excel to Sage 50, ODBC is the best option (at least in my opinion) if you are wanting to create just about any custom report.
  • ODBC gives you access to all the Sage 50 tables and their columns, and from there you can do anything.
  • Combined with the Microsoft on-premises data gateway and the Power BI service it also enables you to create online reports with scheduled, automated refreshes.
  • Using dataflows, you can automate the availability of your Sage 50 data online for report building in Power BI or Excel.
  • With a Power BI Premium or Premium Per User account you can create a robust, financial reporting database that incorporates Sage 50 and other data into a single enterprise data source.

Useful links

Tags:

Replies (2)

Please login or register to join the discussion.

All Paul Accountants in Leeds
By paulinleeds
18th Aug 2023 15:45

I've been using the Sage 50 ODBC link for well over 20 years, mainy to extract monthly TBs / NL movements to prepare monthly PL, BS and Cash Flow report and link this to graphs and charts.

It's quick and (relatively) simple to set-up and updates in Excel, literally at the click of a button.

Sage has never been great at reporting, but the ODBC link provides a quick and simple way to extra data

Thanks (0)
avatar
By markabacus
20th Aug 2023 11:16

Like Paul I've been using ODBC well over 20yrs. We still run the desktop version of Sage 50 with the option to use remote data. ODBC into Excel makes reporting easier. I have std Sage Reports spreadsheet for each client.

Thanks (0)