Not Just A Dashboard: Why You Need to be Using Power BI if you’re on Business Central

You did it – you rolled up your sleeves, put in the time, learned a new system, and, despite the bumps and hiccups along the way, have migrated your entire ERP solution to the cloud. You’re running Business Central and you’re feeling really good about yourself right now. Everything, as that LEGO Movie song goes, is awesome. Mission accomplished. Box checked. Job complete.

But getting onto the cloud isn’t the end of the journey. No siree, Bob. It’s where the fun begins! And I, for one, would put forth an argument that if you are on the Dynamics 365 platform and not utilizing the Power Platform in one way, shape, or form, then your solution is only partially deployed. Specifically, I would argue if you’re not using Power BI yesterday, you need to get on that today!

Ketchup and fries, chocolate and nuts, bacon and everything – these are examples of undisputed and clinically-proven(citation needed) pairings that don’t just ‘work well’ together, but elevate and alter the overall experience to new heights. In economics, the end game of perfect complements is utility maximization. Utility in your cloud-based Business Central solution would be better contextualized as adoption, control, responsiveness, and insight. In that context, Power BI can be considered to be the jam to your peanut butter (or, for fellow Vancouverites, the Stevia-sweetened chia-raspberry spread to your raw almond butter).

Now you may be skeptical, and I don’t blame you. Every shot of Power BI seems to be this dashboard right here:

And while the wall-of-visuals dashboard is certainly one view of Power BI, it’s not the only (or even most common) use-case for Power BI vis-à-vis Business Central. So, let’s take a minute to brain-storm some other possibilities to, hopefully, inspire you to explore it for your own organization.

One of the sorely-lacking functionality of reporting within Business Central is cross-company analysis. This is, invariably, by design; having a clear and reliable firewall between companies and their data are undeniably core requirements of any ERP. It, therefore, fell to either extracting reports company by company and manually marrying them in Excel or setting up business units within a consolidation company and pulling G/L data in there.

However with Power BI, you have the ability to pull in and play with the data from whichever company you choose, appending the data with a column to identify the source company.

Thanks to the capabilities of parameters and functions, however, you can also simply automate the extraction of data for all companies by simply using the Company table with an invoked function (e.g. “GetGLEntries”) and off to the races you go!

In the example below with fake data, for example, I’m pulling in the G/L Entry table from multiple companies with homogenous Dimensions. Pretty cool, huh?

However much we’d like it to be so, a fact of life is that not all data that you report off of will live in Business Central. Sometimes that data will be other systems, databases, flat files, or even the internet.

Thankfully Power BI has an awesome ETL engine under the hood (the same Power Query engine running off of the M-language) and can manage many different types of data.

One of our awesome clients is using Statistical G/L Accounts to capture the number of shares they hold as investments – using Dimensions to tag it with an ‘entity’ and ‘class’ code. That inspired me to whip up the example below, where I’m using a function that takes the Ticker code of an investment and then hits up the Google stock market widget to retrieve stock prices for the day. I then use that information in measures to compute the total fair-market value of the stock, pitting against the historic cost, to determine an unrealized gain or loss.

This is just one example using web data, but you can apply this concept of taking data from Business Central, data from somewhere else, and then having the two interact with each other after normalization.

There may come a point when you want to distribute a financial statement to a stakeholder that is not a Business Central user. You could, naturally, export the data to a file of some sort (Excel, PDF, etc.) or you could share an interactive Power BI report to use!

It’s no secret that the very specific format of financial statements don’t lend themselves well to PivotTables or Power BI. Having said that, there are creative ways to get it all to work in Power BI – for example you could essentially surface an Account Schedule or a pre-defined layout (e.g. an Excel file with the base layout) and, through a series of tweaking and linking, build out a financial statement like the P&L I made below:

The other alternative is to use Power Query and Power Pivot in Excel, get your measures and DAX all set up, and put your pixel-perfect layout on a different worksheet and leverage the GETPIVOTDATA formula.

There are several areas of Business Central where you may have some form of scheduling set up – whether you’re all-in on robust project management functional vis-à-vis the Job module or if you just want a nice ‘schedule view’ of your User Tasks, dates are a thing in your data.

While within Power BI itself there are a ton of different ways you can surface this sort of information with custom calendar and task visualizations, even using a standard table or matrix, you can get a nice glimpse of what’s to come, similar to this “week view” report I made below for our own internal ERP consultants to see what they’ve been planned and scheduled to work on for the week (as well as the hours that they’ve been allocated for each task):

Power BI really is an extremely versatile and capable solution for ‘doing stuff to’ and ‘showing stuff with’ data from a whole variety of sources. The challenge with such a broad piece of software is figuring out how it best works with your business systems and what it can do for you. It is unfortunate that the overwhelming majority of the content out there when you Google ‘Power BI’ is heavily dashboard and sales management centric because it leaves out a lot of the possibilities and use-cases for the rest of us who don’t particular care for or need any extreme roll-up sort of reporting, but rather need a tool that can dovetail with the out-of-box reporting that ships with Business Central to meet some form of analytical, operational, or strategic decision-support need.

Truth be told, this post barely scratches the surface – you can do some truly fascinating things with Power BI (including using a Power App within Power BI)! But hopefully it helps inspire you to get started today on the ‘Power Platform’ – specifically taking the first steps with Power BI and officially marking your Business Central solution as “fully deployed”.