A free, useful Power BI download is linked at the bottom of this article. You don’t have to give us your personal information to get it! Technology solutions have trade-offs between easy to use and highly functional. Over the past two months, I’ve been exploring the trade-offs around Power BI with Business Central. After doing a lot of research, I found out with some Power Query creativity, we can have our cake and eat it too! This blog will walk through some of the common documented ways to hook Power BI and Business Central together. Then, I will explain how I took a different approach to bypass some limitations of the documented approaches. In the free provided file, there are Power Query functions that make our solution easy to use and highly functional. With this file, you can deploy a solution that is multi-company, performs well, is compatible with advanced PowerBI.com features, and is beautifully designed.

Summary

There are three ways to get Business Central Data into Power BI. This article will explain the pros and cons of each approach.

  • Consuming the Microsoft built Business Central Apps.
  • Building a Data Model with the Business Central Connector in Power BI.
  • Building a Data Model with the Web Connector in Power BI.


    • In addition, there are important concepts to understand about deploying the Web Services from inside of Business Central, and the impact on the Power BI reporting strategy.

    Business Central Power BI Apps

    Deploying the Microsoft built Apps is simple. However, as you can infer from the App ratings, with the simplicity of deployment comes some drawbacks. Additional reports can be authored from these Apps, but they are not designed to be a starting point for a “BI” strategy. Instead, they are designed to display basic Reports and Metrics. Pros:

  • Simple to Deploy
  • Helpful to explore PowerBI.com features.


    • Cons:

  • Not a starting point for building a properly structured BI solution.
  • No detailed data.

    • Business Central Power BI Connector

    Power BI Desktop utilizes a specific Connector for Business Central. The connector makes it easy to get started authoring custom Data Models and Reports. This connector works great when pulling data from a single company and publishing a typical PowerBI.com deployment. Pros:

  • Pull data from any Page or Query.
  • Easy to connect to Business Central environment.


    • Cons:

  • Single company only (there’s a hack for multi-company, but the performance isn’t good).
  • Some advanced Power BI features, such as Dataflows, aren’t available with this connector.

    • Using Web Connector and Custom Functions

    Sometimes with Power BI, there are additional undocumented methods of pulling data, and that’s the case with Business Central. The standard “Web” Connector can connect to the Business Central OData feed. In addition, with this type of connection, custom Power Query functions can be written to retrieve the data. There are two complex functions in the free Power BI file that make this as simple to use as the standard Business Central Connector!

    Function to Retrieve Data from One Company

    Function to Return Data from All Companies

    Utilizing One of the Functions in Power Query

    With the custom web services approach, data from many companies can be unified in Power BI in a performant way. Dataflows, which are the PowerBI.com version of Power Query, can also be setup to use these same functions. Pros:

  • Pull Data from many companies with excellent performance.
  • Simple to use Functions.
  • Code can be transferred into Dataflows, which subsequently allows pushing data into a Data Lake.


    • Cons:

  • Uses custom Power Query Functions.


    • Business Central Web Services

    Business Central contains enticing Web Service Queries called “Power BI…”.

    “Power BI” Web Services

    Pulling a the “Power BI GL Amount List” Query seems simpler to start, because it combines fields from Ledger Accounts and General Ledger Transactions. However, if more fields are needed in Power BI, such as Financial Dimensions, report writers will get stuck using this approach. Also, in Power BI the proper way to model this data is to have the Accounts and Transactions in two different tables with a Relationship defined. The alternative is to publish the Pages (tables) of source data. For example, the Chart of Accounts and GL Transactions tables can be made available to create the same data structure in Power BI as the Power BI GL Amount List. With the raw data approach, additional columns like Financial Dimensions are available. Using raw tables allows building a Power BI model that conforms to best practices of Power BI design.

    Properly Structured Data Model from the Free Power BI File

    Business Central has upcoming Power BI roadmap items for improvements around deploying web services. Visit our blog and subscribe to the sa.global newsletter to stay up to date on the latest Power BI and Business Central news!

    You’ve Made It This Far, You Deserve Free Stuff!

    Download our FREE Power BI file which has:

  • Instructions on how to deploy.
  • A General Ledger and Budget data model that supports multi-company and a Data Model using best practices.
  • A few General Ledger and Budget Reports.


    • *Note: You can share, modify, reshare, and/or internally consume this file freely without restrictions; we only ask when sharing or resharing components we built please leave attribution to sa.global in the file.

    One of the Free Reports – An Income Statement Report Where Revenue, Expenses, and Net Income are All Positive Numbers. Magic!

    If your organization needs help deploying Power BI for Business Central, or if you have ideas for improvements to our free Power BI content, please Contact Us! sa.global is a unique partner since we are experts at all Dynamics 365 products, Power Apps, and Microsoft 365 technologies. Joel Leichty is the Power Practice Manager at sa.global. He also writes about Power BI in a casual style at https://joelleichty.com/.

    Tagged With

    AutomationIntegrationMSDyn365BCPower BI