How to Get Data from Microsoft D365 BC into Power BI

Microsoft Power BI is a popular tool for designing visual dashboards that help everyone in your organization to better understand how the company is performing against key metrics. If your business is running Microsoft Dynamics 365 Business Central (D365 BC), or if you are planning to do so in the near future, then you are probably hearing a lot about Power BI as Microsoft’s preferred reporting and analytics platform for the company’s business applications. Although Power BI certainly has its strengths, it can also add substantially to your ERP system’s total cost of ownership.

If you are moving from Microsoft Dynamics NAV, GP, or SL, you will need to adjust to a very different paradigm for reporting and analytics. Data access for reporting has fundamentally changed with Microsoft D365 BC. Microsoft’s cloud-based ERP systems no longer allow customers to directly access information in the database. Instead, users must rely on custom queries deployed as web services. You need substantial IT skills to develop and deploy those queries in your Microsoft D365 environment.

This change also complicates matters considerably whenever you make changes to the underlying database, such as when your company builds custom extensions or installs third-party products alongside Microsoft D365 BC, or when Microsoft adds new features to the product that result in changes to the underlying data model.

The net result is that customers should plan on allocating additional time and/or budget to develop and maintain reports for Microsoft D365 BC, starting with your initial deployment and continuing throughout the life of your Microsoft D365 system.

There is, however, a different path that makes life easier and less expensive, but still enables people throughout your organization to benefit from the strength and flexibility of Microsoft Power BI. With the right tools, you can add self-service capabilities to Power BI and save considerable time and money over the long-term.

How Does Data Access Work with Microsoft D365 BC?

To understand the problem and how to solve it, it’s helpful to have a little bit of background information on how data access works with Microsoft D365 BC.

Traditionally, you deployed business applications such as ERP on-premise. Your company’s IT department had complete control and direct access to the ERP’s SQL database. You could limit physical access to the server room, and your data were never co-located with information from other businesses. Even if you were to move your ERP system to a privately hosted cloud environment, your data were still segregated from any data belonging to other customers. That provided a natural layer of security and privacy.

In the world of multi-tenant cloud applications, all of that changes. Most modern cloud-based software (including Microsoft D365 BC) operates on a multi-tenant model, in which a single database server in the Cloud may host information from multiple customers. In order to keep your information isolated from that of other customers, Microsoft has restricted its customers’ ability to directly access their ERP database using traditional methods.

The primary alternative is to allow users to access ERP data through application programming interfaces (APIs), which provide a controlled environment to run queries against the database and return information to your reporting or analysis tools, or other applications. This, however, has three serious drawbacks when trying to get full access to your data.

1. Heavy Dependence on Specialized IT Skills

Microsoft’s solution to the ERP reporting problem is to provide a set of tools that enables programmers to build custom queries and expose them as web services. Power BI can then connect to those web services to extract data from Microsoft D365 BC.

The primary problem with this approach is that it requires a skilled IT professional to develop all of the queries you’ll need to run reports for your organization using Power BI. While the traditional approach of using SQL-based reporting tools required some understanding of the underlying database structure, the learning curve was relatively low, making it easy to come up to speed quickly. The newer API-based method calls for that same understanding of the ERP data model, but it also requires specialized programming skills.

Most of the businesses that run Microsoft D365 BC as their primary ERP system don’t have a large IT department with dedicated programmers on staff to build and maintain the library of queries necessary to effectively use Power BI with Microsoft D365 BC. The alternative is to pay expensive outside consultants to do the job for you. If this were a one-time requirement, that might be an acceptable approach, but as we will discuss shortly, reporting queries typically require ongoing maintenance. That, in turn, can lead to a significantly higher total cost of ownership for your ERP system.

2. If You Add Extensions, You Have To Do It Again

One of the great things about Microsoft D365 BC is that you can customize it to meet the unique needs of virtually any small or midsize business. In the world of Microsoft D365, those customizations are called “extensions.” For businesses that need to accommodate unique requirements, or need to extend BC’s functionality using third-party products, extensions can be a game changer. They make it possible to get exactly what you need out of your ERP system.

However, extensions often require storage of additional information alongside your standard D365 BC data. Simply adding a few additional fields to a customer address record, for example, requires an extension to that the database to store, update, and retrieve that information.

Naturally, most companies will want the capability to include that kind of additional information in financial and operational reports. Once again, the way to achieve that is to build custom queries that expose that information via web services, which Microsoft Power BI can, in turn, consume. That means more dependence on specialized IT skills, whether you source them in-house or they come in the form of outside consultants that charge you an hourly rate.

Every extension that you build on top of Microsoft D365 may result in a cascade of additional required changes to your library of reporting queries. Even if you don’t plan on building any custom extensions, you can still potentially fall into this trap. If you plan on adding third-party products to your Microsoft D365 environment at any point, you will end up having to review your reporting queries to determine which ones you need to modify. Without making the necessary changes, the new data associated with your third-party extensions will not be available in any of your Power BI reports.

3. If Microsoft Makes Changes, You Have To Do It Again

One of the promised benefits of moving to a native cloud product like BC is that it enables the software vendor to apply upgrades automatically. That means getting updates and enhancements more frequently, as well as eliminating the disruptions that are typically associated with major software version upgrades. Smaller, low-risk, incremental enhancements sound great, right?

Well, yes and no. The promise of never having to endure a major upgrade again is great, as is the prospect of getting new functionality sooner. When it comes to reports, though, even small changes to the underlying data model can add to the burden of maintaining your library of reporting queries. So it’s back to the IT department (if you have the right people on staff), or it’s time to make a call to your local ERP consultants to get an estimate on the revisions you’ll need to all of your reporting queries.

There Is a Better Way

What if you could have everything you want from Power BI, without the need to maintain a library of reporting queries for D365 BC? Thanks to the Business Central adapter that comes with Jet Reports and Jet Analytics, the process can be vastly simplified, making it possible for virtually anyone to develop a query and build sophisticated reports and dashboards, without specialized IT activities expertise.

Using Jet Report or Jet Analytics from insightsoftware, you can easily access data from Microsoft D365 BC without having to rely on custom queries. Jet’s purpose-built connection to BC allows you to see all tables and columns in the underlying database, including any customizations. Power BI can consume Jet Reports output or the Tabular Models or OLAP cubes from Jet Analytics to provide an easy way for developers to create dashboards and analytics, which insulates them from any changes to the underlying BC database. This enables teams to bring dashboard development in-house, without being dependent on third-party resources.

Jet Reports from insightsoftware provide a “Table Builder” tool that makes it simple for a non-IT professional to select the exact information they need from the Microsoft D365 database and build a custom query from it in just a few minutes. Those queries can even be saved as template files that you can easily share, publish, or download.

By executing a Jet Reports query from within Microsoft Excel, a user can pull the data they need from Microsoft D365 quickly and easily. The user can save that information as an Excel file, which can, in turn, provide the starting point for a Power BI report or dashboard.

Jet Reports and Jet Analytics also come with a scheduler that makes it possible to automatically update your Excel source files on a periodic basis. You might choose to extract data daily, hourly, or even more frequently. The scheduler will automatically run the Jet Reports query and save it as an Excel file. The Power BI reports that you have created will automatically update to reflect the new information.

The nice thing about this is that virtually anyone can do it. If you know a thing or two about the ERP system – that is, if you use BC as part of your day-to-day job – then you probably have all the skills necessary to develop Power BI report, without ever touching the Microsoft data access APIs.

In addition, Jet Reports and Jet Analytics can dramatically lower your total cost of ownership for Microsoft D365 BC over the long-term. Jet’s D365 adapter automatically detects changes to Business Central’s underlying data model and makes the resulting data available within Jet Reports and Jet Analytics.

If you develop custom extensions for Microsoft D365 BC, any new fields that you have added will show up automatically in the Table Builder, making it easy to add them to reports.

If you install third-party extensions that integrate with Microsoft D365 BC, data from those extensions will automatically show up as well.

When Microsoft publishes updates to D365 BC that result in changes to the underlying data model, those will also become visible in Jet Reports and Jet Analytics.

Power BI Desktop is a useful tool for developing reports and dashboards, and it is likely to become more familiar to users of Windows-based systems in the future, but the desktop software is just one part of a larger collection of technologies that fall under the Power BI umbrella. Companies that intend to deploy the entire Power BI stack should prepare for a fairly complex project involving numerous architectural decisions and extensive IT support.

For companies running Microsoft D365 BC, a simpler approach makes much more sense, that is, to dispense with the big-ticket components of the Power BI stack and focus on providing users with powerful self-service tools that enable them to get the job done quickly, without extensive IT expertise. Jet Reports from insightsoftware provides the ease of use and self-service capabilities that empower frontline workers to get the job done quickly, efficiently, and without outside help.

If your organization is running Microsoft D365 BC, or is considering an upgrade, it’s worth spending some time to understand the implications of Microsoft’s recommended approach to reporting and analytics and to explore options like Jet Reports and Jet Analytics to lower your costs over the long term. To learn more, contact us today to request a free demo.