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.
There are three ways to get Business Central Data into Power BI. This article will explain the pros and cons of each approach.
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:
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:
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!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:
Business Central Web Services
Business Central contains enticing Web Service Queries called “Power BI…”.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. Business Central has upcoming Power BI roadmap items for improvements around deploying web services. Visit our blog and subscribe to the Dynamic Consulting 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:
*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 Dynamic Consulting in the file.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! Dynamic Consulting 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 Dynamic Consulting. He also writes about Power BI in a casual style at https://joelleichty.com/.