Power BI with your accounting data

How to connect Power BI to Sage 50 Accounts

Posted by Hugh Johnson on 02-Apr-2018 19:45:59
Hugh Johnson
Find me on:

 Set up Sage 50 Accounts as one of your Power BI data sources

You can achieve a powerful PowerBI Sage 50 integration by setting up your Sage 50 Accounts system as an OData feed for Power BI.  By doing this, you will be able to:

  • Pull your Sage 50 Accounts data into Power BI Desktop
  • Author compelling reports based on your Sage 50 Accounts data
  • Publish these reports to the Power BI Pro service on the web
  • Create dashboards and share these across your organisation
  • Automate the data refresh from Sage 50 Accounts to Power BI
As a pre-requisite you will need Power BI Desktop, Sage 50 Accounts v22 or higher and an active active Suntico BI user account.  Once you have these, just follow these three steps:
  1. Set up a user in Sage 50 Accounts for the OData feed
  2. Install the OData feed connector for Sage 50 Accounts
  3. Connect Power BI to Sage 50 Accounts

 

Set up a user in your Sage 50 Accounts system

How to set up a new user in Sage 50 Accounts

The OData feed connector takes up a Sage 50 Accounts user, so you will need to add a new user to Sage for each company that you want to connect to. 

To do this, login to each Sage company and go to Setttings > Access Rights and select New.

It is here that you will define the connector as a user for Sage 50 Accounts with access rights to that particular company (in this example Best Wholesale Limited).

The connector can link to several companies in Sage 50 and it is here in the Settings section for each company that you are setting up a username and password for the connector.

 

 

Enable 3rd Party Integration

If you are running Sage 50 Accounts 24.1 or above, you no longer need to do this step.  If not and you have not already done this step for other 3rd party products then in Sage 50 just go to Tools > Activation > Enable 3rd Party Integration.

Sage 50 Accounts Enable 3rd Party Integration
You will be asked for a serial number and activation key which depends whether you have Sage 50 v22, v23 or v24 as follows:

Sage Version Serial Number Activation Key
Sage 50 v22 SAGESDO EAHDFBL
Sage 50 v23 SAGESDO RCYLSWK
`Sage 50 v24 SAGESDO ZHIEKPK

 

Video instructions - prepare your Sage 50 Accounts for Power BI

Here is a video showing all three of these things that you need to do to set up Sage 50 Accounts for connection with Power BI via Suntico.
 

 

 Back to top

 

 

Install the OData feed connector for Sage 50 Accounts

Download your Sage 50 Accounts Connector

 Go to Suntico and login to your account.  You will see a screen like this:

Download the Suntico connector for Sage 50 Accounts

Make sure that you select the connector for Sage 50 Accounts and click on Download and Install.

If this is the first time you are downloading the connector then this screen will automatically appear.  If you need to access this screen in the future (for example it you want to reininstall the connector on another server), then you can find this screen at:

Settings > Connector > Download

 

Install your Sage 50 Accounts Connector

Important Notes

  1. You will need to install the connector on a machine that has Sage 50 Accounts installed, and that in this instance of Sage 50, the company you wish to connect to appears in your company list.  If the company is not there, then chose in Sage 50 to add the company.

  2. Ideally, you should install the connector on your server that holds the Sage 50 data for your company, but this is not strictly necessary but it will help you to make sure that the connector is always running.

Click to run the setup.exe file that you have downloaded.  Stay with the default options unluess you have good reason to do otherwise.  Once the connector is installed, you will see this screen:

Click the green arrow to run the Suntico connector

Click the green arrow.  If by mistake you clicked "Finish", then just go to your Desktop and double-click on the Suntico icon to run the connector.

Enter your API keys for your Sage 50 Accounts Connector

Your Suntico account has a unique set of keys for each connector.  These keys are used to give the connector the authority to link to your Suntico account.

Generate API Keys

Click on the "Generate API Keys" for your connector and enter these into the dialogue box for the connector then click "OK".

Select which companies you want to connect to

You will see a list of companies that you may include for synching to the Suntico platform.  This should be the same list of companies (v23 or higher) that you see in your companies list when you open Sage.


Company selection using the Suntico BI connector

You can include multiple companies and to include each one just check the "Included" check-box.  You will be prompted to enter the username and password you set up in each Sage company for the Suntico connector.

I recommend that you start with one company.  You can go back and add others later.

Review company selection in the Suntico connector

Click "Review Selection" and you will be presented with a summary of all the companies selected and whether or not you are making their sync active.

Select "Apply and Save".

Apply and save your company data selection for the Suntico connector

Your connector will start to run and sync the selected company(s) data to the Suntico Platform, ready for use by Power BI.

Your connector screen should look something like this:

Suntico connector running

Depending on the size of your company dataset, this first sync may take a while (up to a few hours for a really large dataset).  Let it run.  If you need to stop the connector for example to do a Sage backup (remember that the connector is set up as a Sage user), then you can simply restart it after the Sage backup simply by double-clicking in the shortcut on your desktop.  It will resume where it left off.

Thereafter by default, the connector will poll Sage every 30 mins for updated information.  You can change this sync frequency and define time-slots for the connector to run in if you wish.

One the first sync has completed, you can connect Power BI to your Sage 50 data.

Video instructions

Here is a video showing all three of these things that you need to do to set up Sage 50 Accounts for connection with Power BI via Suntico.

 

 

Back to top

 

Connect Power BI to Sage 50 Accounts

In section I show you how to link your Sage 50 company (or companies) that you have in your Suntico account to Microsoft Power BI Desktop.  To get you started with Power BI Desktop you can also download a Power BI Template file that will save you a lot of work setting up your Sage 50 model.

The first thing you need to do is to generate your Power BI keys (username and password) for the Suntico BI OData feed.

Select the Sage 50 company that you wish to connect to

As we saw when you installed your Suntio connector, the Suntico Platform is multi-company.  This means that you may sync many companies to your Suntico account via one or multiple connectors.  Each company has it's own set of keys (OData username and password) that are generated by the Suntico Platform, which are used to provide the link to a Power BI dataset.

To select the company that you wish to connect to, login to Suntico and at the top right-hand corner of the screen you will see the company selector.  By default, Suntico will remember the last company that you selected, but if this is the first time that you have logged in since you installed your connector you will need to select your company even if you only have one company connected.

Select your Sage 50 company in Suntico

Generate your Power BI OData keys for the selected company

 After selecting the Sage 50 company that you would like to connect to Power BI, go to the yellow botton at the top left-hand corner of your Suntico Dashboard and click on "View More".

Generate Power BI Keys

 You will see a screen like this:

Generate OData credentials hereClick on the blue button that says "Generate Credentials Here", and the Suntico Platform will generate a unique Username and Passwork for you to use with the OData feed for Power BI.

You will see something like this:

Power BI CredentialsYou are now ready to connect this company dataset to Power BI.  

Load your Sage 50 data into Power BI Desktop

To make this really easy for you, I have created a Power BI Template file that is optimised for the data feed and has a lot of the basic stuff already done for you, like creating a Dates table, and a number of pre-prepared measures and sample visualisations.

Video instructions for connecting to Power BI Desktop

Watch this video on how to connect your Sage 50 data to Power BI Desktop:

 

 

  

Back to top

Still not sure?  Well why not take a look at this demo?

 

Topics: Connect Power BI to Sage 50 Accounts

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts