Connect Power BI to Sage 50 Accounts - Part 4 of 4

Power BI

Connect Power BI to Sage 50 Accounts - Part 4 of 4

Connect Power BI to Sage 50 Accounts via Suntico. How to connect your Sage 50 data to Power BI Pro and Power BI Desktop. Free Power BI Desktop Template will save you weeks.

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:

  1. Connecting your Sage 50 company to the "Accounting Insights for Sage 50" Content Pack on the Power BI Service.
  2. 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.

Power BI vs Tableau: who is winning the mindshare?

Power BI

Power BI vs Tableau: who is winning the mindshare?

In an analysis of Google Trends over the last 12 months, Power BI occupies nearly 50% of UK Google searches relating to Business Intelligence. Look at the relative searches relating to Power BI vs Tableau. Is Power BI in danger of becoming a generic term, like Google, Hoover, or Jet-Ski?

Is Power BI in danger of becoming a generic term?

We search for something on the internet, we "Google" it.  When we vacuum the floor, we "Hoover" it.  There are many examples of brand names that have become generic terms.  Could Power BI be going the same way for business intelligence?

My favourite three Power BI DAX functions

Power BI

My favourite three Power BI DAX functions

A summary of my favourite Power BI DAX functions. SUMX, CALCULATE and FILTER. Why I always use SUMX and not SUM. The power of CALCULATE. The super combination of CALCULATE & SUMX together. The importance of filters and FILTER. Example given in DAX code.

DAX (Data Analysis Expressions) is the language used to write formulae in Power BI when you want to create new Calculated Columns or Measures.  In many respects it is similar to the way you write formulae in Excel.  Stated more simply, DAX helps you create new information from data already in your model.  While it is easy to make visuals in Power BI from your existing data model, we regularly want to see information for particular time periods, or with specific filters applied or create new calculated values based on our raw data.  There are some super free online resources available to learn DAX, which will help you to turn your simple Power BI model into something great.  Learning just a few basic functions of DAX can assist you with unlocking numerous new bits of knowledge from your data.  Here I introduce my favourite DAX functions that I use in the Accounts Receivable Solution for Sage 50 and the Power BI Template for Sage 50.


DAX Running Total Calculation in Power BI

Power BI

DAX Running Total Calculation in Power BI

Why and how to do a DAX running total calculation in Power BI. Two methods are presented: using the Quick Measure facility in Power BI, and writing your own DAX formula from scratch. Use variations to calculate running totals like YTD and rolling totals like sales last 52 weeks (example given).

What is a running total?
Power BI Adoption By Country

Power BI

Power BI Adoption By Country

An estimate of relative Power BI adoption by country, based on Google search volume for the term "Power BI". It appears that interest in the UK is more than double that of the USA, and in New Zealand more than four times.

Microsoft does not publish figures for Power BI adoption by country.  I am not surprised, but it is something that I wanted at least some kind of feel for to help me plan the development and roll-out of Suntico BI.  So as a proxy for relative adoption rates by country, I looked at monthy Google search volume per capita for the term "Power BI".  I picked a few mature English-speaking countries for which Power BI would have been available for the same length of time, and that for various reasons were countries that we were interested in targeting for our service.  The results are below.

Days sales outstanding (DSO) - A useful or misused KPI?

Accounts Receivable

Days sales outstanding (DSO) - A useful or misused KPI?

Days Sales Outstanding (DSO) is a common KPI for Accounts Receivable. It is often misused as an indicator for the Average Collection Period or Average Debtor Days. What does it really mean, and how can you enrich it with a rolling average DSO measure or trend?

Sage 50 Accounts as one of your Power BI data sources

Power BI

Sage 50 Accounts as one of your Power BI data sources

How to set up your Sage 50 Accounts system as one of your Power BI data sources. Use Suntico BI to set up an OData feed of your Sage 50 Accounts data. Use it to deliver powerful, automated Sage 50 reports in Power BI. Try it now.

Add Sage 50 Accounts to your list of Power BI data sources

The Suntico BI OData feed turns your Sage 50 Accounts system into an online business intelligence data source for tools like Microsoft Power BI.  It delivers the following data tables:

  1. BICategories - definining your nominal code categories for each chart of accounts
  2. BICharts - your charts of accounts listing
  3. BICountries - your Sage 50 Accounts countries table enriched 
  4. BICurrencies - your customer currencies
  5. BICustomers - records of your Sage 50 Accounts customers
  6. BIDepartments - your Sage 50 department list
  7. BIMetaData - data about your Sage 50 dataset
  8. BINominalBudgets - your nominal code budgets for the current financial year
  9. BINominalRecords - your nominal account records
  10. BIPeriods - deinitions of your financial year
  11. BIProductSales - sales details from your customer invoices
  12. BIPurchaseOrderLines - purchase order line items
  13. BIPurchaseOrders - your Sage 50 supplier purchase orders
  14. BISalesOrderLines - Sales Order line items
  15. BISalesOrders - your Sage 50 customer sales orders
  16. BIStocks - your products and services
  17. BISupplierCurrencies - your supplier currencies
  18. BISuppliers - your supplier records
  19. BITransSplits - your full transaction audit trail
Power BI templates for Sage 50 Accounts - an introduction

Power BI

Power BI templates for Sage 50 Accounts - an introduction

An introduction to the Suntico Power BI Templates for Sage 50 Accounts. Save yourself hours if not weeks building your reports and dashboards. Automate your data refresh. Ideal for accountants, Sage business partner as well as in-house Power BI users. Subscribe for tips, trick and templates.

In this post I introduce a series of Microsoft Power BI template files that I am introducing for anyone wanting to work with Sage 50 Accounts.

Suntico Power BI Templates for Sage 50 Accounts
  1. What are they
  2. Who are they for?
  3. When would you use them?
  4. Where can you get them?
  5. Why would you use them? 
How to update your Suntico BI Connector software

Power BI

How to update your Suntico BI Connector software

The Suntico BI Connector software is designed to receive updates automatically, but to avoid interruptions to your Sage 50 server you have a lot of flexibility over when or if these auto-updates happen. This article describes your options.

Auto-update or update now

This blog describes how to update your Suntico BI connector when there is a new version available. 

How to uninstall your Suntico BI connector for Sage 50

Power BI

How to uninstall your Suntico BI connector for Sage 50

Step-by-step instructions to uninstall your Suntico BI connector for Sage 50 Accounts.

There may be times when you want to unintsall your Suntico BI connector for Sage 50, for example if you want to move the connector from one machine to another.  Just follow these steps desribed in this blog.

Copyright © 2019,
Accounting Insights Ltd.
All Rights Reserved.

Our Office :
Unit 4, Roof Garden Offices,
Clarehall Shopping Centre,
Dublin,
Ireland
+353 1 567 4069
contact@accountinginsights.co.uk