Connect Power BI to your Sage 50 OData Feed
This blog describes part four of a four-part process to connect Power BI to Sage 50 using the Suntico BI service. In this blog I show you how to link your Sage 50 company (or companies) that you have in your Suntico account to Microsoft Power BI. We will look at two examples:
- Connecting your Sage 50 company to the "Accounting Insights for Sage 50" Content Pack on the Power BI Service.
- Connecting your Sage 50 company to Power BI Desktop.
You can do either or both. Maybe it is a good idea to connect to the Content Pack first, as that will give you a ready-made dashboard and report that you can start to use right away. Meanwhile, you can connect to Power BI Desktop and start to build out some custom reports is you alsow wish to. 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.
Let's start with the common activity that you will nee to do, and that is to generate your Power BI keys (username and password) for the Suntico BI OData feed.
1. 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.
2. 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".
You will see a screen like this:
Click 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:
You are now ready to connect this company dataset to Power BI. You can connect to the Suntico BI "Accounting Insights for Sage 50" Content Pack on the Power BI Service (aka Power BI Pro) and/or to Power BI Desktop.
3. Connect to the "Accounting Insights for Sage 50" Content Pack
To use the Accounting Insights for Sage 50 Content Pack, you will need to have a Power BI Pro account. Login to Power BI Pro. At the bottom left-hand corner of the screen click "Get Data" and select Services.
Search for "Sage" and you will find the Accounting Insights for Sage 50 Content Pack.
Opt to "Get it now".
Now enter your Power BI credentials that you generated on the Suntico Platform into the settings for the Content Pack.
In Power BI Pro, go to Settings:
Then select Datasets > Credentials > Edit Credentials > Basic authentication method as shown below.
You can then enter you Username and Password which are the Power BI Credentials that you generated on the Suntico Platform.
Once you have done this, go to the menu on the left-hand side of the screen and navigate to Datasets. Click in the three elipses next to the Dataset called "Accounting Insights for Sage 50". You will see something like this:
Here you can "Refresh Now", which will populate your Accounting Insights for Sage 50 Content Pack with your Sage 50 company data immediately (well it might take a couple of minutes).
You can also set up an automated, scheduled refresh, so that your company data in Power BI Pro is always up to date say as of close of business yesterday.
5. Video instructions for connecting to the Power BI Content Pack
Here are video instructions for connecting to the "Accounting Insights for Sage 50" content pack.
4. Load your Sage 50 data into Power BI Desktop
In addition to using the Suntico BI "Accounting Insights for Sage 50" Content Pack, you can also pull your Sage 50 company data into Power BI Desktop so that you can build your own analytical model around your Sage 50 Accounts data. 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.
Watch this video on how to connect your Sage 50 data to Power BI Desktop: