Presenting to a group

Microsoft BC Connector Updated: What You Should Know

When a company moves from a traditional on premise ERP system to a multi-tenant cloud product such as Microsoft Dynamics 365 Business Central (D365 BC), there are usually some fundamental changes to the way you generate reports. In an on-premise environment, you generally have complete control of your database server. You can do whatever you want with it, because no one else stores their data there. It’s all yours.

From a technical perspective, that affords you a high level of flexibility. As long as you’re careful about who has access to the database admin password, and you apply the appropriate security measures and make regular backups, you can rest assured that your data is safe and secure.

Historically, reporting tools for traditional on premise ERP products have relied on direct database queries to pull information from the database. That has the benefits of being both fast and very straightforward.

Typing On A Laptop

With the advent of cloud ERP, software publishers have been forced to abandon that direct access approach because it presents potential security threats in a multi-tenant environment. The ability to easily insert new records or update or delete records en masse is inherently dangerous. On a shared database server, that kind of capability could impact other customers’ data as well. For that reason (and a few other reasons), the old method of querying data directly from the database has been discontinued for most cloud ERP scenarios.

The Microsoft Business Central Connector Has Changed

Microsoft launched Dynamics 365 Business Central a few years ago as a multi-tenant cloud ERP solution for small and midsize enterprises. The company offered its Power BI analytics product as a foundation for Microsoft D365 BC’s financial and operational reporting. For the reasons described earlier, Microsoft closed off customers’ ability to directly access the underlying ERP data using SQL database queries, opting instead to publish a dedicated set of web services APIs (application programming interfaces) that would allow programmatic access to the data.  In other words, Power BI asks the APIs for data, the APIs act as an intermediary in requesting that information from the Microsoft D365 BC database, and it delivers the results back to Power BI. There are also Power BI APIs, which allow you to perform actions such as embedding reports, dashboards, and tiles for Power BI users and non-Power BI users.

In June of 2021, Microsoft announced some significant changes that affect this process. The existing APIs that have been in use for several years now were specifically designed for Power BI reporting from Dynamics Business Central. In other words, they’re designed to read data from the ERP system, and you generally can’t use them for other purposes, such as writing a mobile app that allows a user to enter a sales order, for example.

In June 2021, Microsoft released version 2.0 of the web services APIs that connect Power BI to Microsoft D365 BC. This comes with some benefits, most notably, improved performance. However, there are also some drawbacks associated with the new version. Customers who have written reports using version 1 of the D365 BC connector APIs can continue to use them. In fact, customers can even write new reports using those older versions of the web services; they are still accessible.

However, neither version of Microsoft’s D365 BC connector APIs really does a stellar job of financial and operational reporting for most customers, especially if they have added custom fields or have performed any modifications to Business Central. These APIs don’t provide complete coverage, which means that there will almost always be some information from your ERP system that you simply can’t include in a Power BI report.

Limitations of the Microsoft BC Power BI Connectors

Microsoft’s standard APIs only expose information for a subset of standard tables and fields in the ERP database. If you’re working with localized versions of Business Central, those web services do not expose some important required fields, which means you can’t include them in your Power BI reports unless you do some custom programming.

If you have added extensions to Microsoft D365 BC, you’ll need to do some additional programming to expose any custom tables and fields that are part of each extension. That requires technical expertise, which can be expensive. Most customers will end up paying expensive outside consultants to provide these services. If you have more than just a few custom tables or fields, that can add up to a lot of money.

Desk With Two Comptuer Screens

As you add those kinds of extensions, the complexity of your Business Central environment will grow considerably. Because a single API page or query extension can only serve a single designated purpose, the number of extensions can accumulate over a period of time and will require ongoing maintenance. That, in turn, creates long-term costs for your business.

While the newer version of the Microsoft D365 BC connector is considerably faster than the older one, there are still some significant limitations that can cause a drag on performance, resulting in slow reports and dashboards. For example, it is not possible to incrementally load data from Business Central using Power BI or Power Query. This means that, with every refresh of a report, you must query and process the entire dataset all over again. That takes significant time and causes inconvenient delays for end-users.

The Power BI connectors for Microsoft D365 BC also suffer from a problem with duplicate loading of data. If customers have several Power BI dashboards, for example, then they may be reloading the same data from Business Central multiple times, potentially with differing transformation steps in the Power Query editor, which could lead to inconsistent data being used in different reports. Customers could potentially use a feature called DataFlows to work around this problem; however there is currently no DataFlows connector for the Business Central API libraries. That necessitates a lot of work by highly skilled technical experts, which translates to more time, money, and more ongoing maintenance.

Finally, the Microsoft D365 BC connector for Power BI does not handle multi-company reports very easily. The Power Query editor requires specific steps to support multi-company reporting, and while it is feasible, that capability simply isn’t within the reach of the average business user.

Power and Versatility with Jet Reports and Jet Analytics

Jet Reports and Jet Analytics are products from insightsoftware specifically built to work with Dynamics Business Central, with its predecessor Dynamics NAV, and with other products within the Microsoft Dynamics family.  Jet Reports and Jet Analytics offer seamless integration to the ERP system and eliminate many of the common technical challenges associated with Microsoft’s out-of-the-box reporting tools. They allow business users to access, report, and perform analysis on their data without requiring technical expertise.

Perhaps just as importantly, we designed Jet Reports and Jet Analytics to perform a full range of operational financial reporting tasks, as well as advanced analytical tasks. Microsoft designed Power BI primarily as a dashboard individualization tool for data analytics, so it tends to fall short when it comes to performing many of the standard reporting tasks that ERP users rely upon every day. Power BI can be particularly challenging when it comes to producing financial reports, whereas insightsoftware designed Jet Reports and Jet Analytics to meet that need.

Jet Reports allows users to query ERP data and design reports directly within Microsoft Excel. It provides full reporting capabilities, with access to the entire Business Central database (or ERP data from other Dynamics products) and full drill-down capabilities that enable users to immediately connect to the underlying source transactions in the ERP system. Users of Jet Reports can build reports with simple drag-and-drop tools and use the Jet functions embedded in Excel to extract real-time information straight from Microsoft D365 BC. By using these in combination with all other available Excel functions and formatting tools, financial users can build robust reports without relying on technology experts from the IT department or expensive outside consultants. Updating information in Jet’s Excel-based reports simply requires a click of a button; users can refresh data quickly, and as many times as they want.

Group Meeting Around A Table

Jet Reports also handles multi-company reporting with ease, and can draw from multiple source ERP systems.

Jet Reports automatically works with the user permissions from your ERP system. You don’t need to maintain a separate security model for your reports. The product also includes a set of report scheduling and distribution tools that automate routine processes, saving time and effort, and ensuring that information gets to the people who need it, on time, every time.

Jet Analytics is a robust Business Intelligence (BI) solution that complements Jet Reports with a data warehouse and advanced analytics capabilities. It includes pre-built projects, cubes, and data models, as well as a suite of ready-to-run reports and dashboards. Jet Analytics also includes automated data warehouse management tools that enable companies to centralize their data from multiple systems under one umbrella, building sophisticated reports and analytics that span the various systems in place of the organization. That allows your business to break down data silos and gives decision makers a holistic view of the organization and all of its data. Many organizations even incorporate legacy data from their prior ERP systems into the Jet Analytics data warehouse, enabling them to maintain a long-term view of sales history, purchasing, and financial data.

We designed Jet Analytics for operational efficiency. It can incrementally load data and combine data from multiple data sources on the fly, for example.  Users can model the data to suit a specific set of requirements (for example, converting transactions from different databases into a single reporting currency, or mapping legacy ERP data to conform to a new, updated chart of accounts for comparative financials).

With Jet Analytics, business leaders can have a single source of truth for their entire organization, refreshed according to a schedule that suits their unique needs.

Finally, Jet Analytics has the same eye toward ease-of-use that goes into all of insightsoftware’s products.  We believe that when you empower end-users to develop and modify reports, they will make better use of the information that is available to them. With Jet Reporting and Jet Analytics, there is no need for end-users to understand the complex structures of the source systems in which the data resides.

With Jet Analytics, users can choose to develop their front-end reports using Microsoft Excel or Power BI.  Since both tools point to the same sets of data, and since all of the transformations and data models have been prepared in advance within Jet Analytics, you can be sure that you will have a single source of truth, regardless of what tool you are using for analysis.

Access to All Tables and Fields

Jet Reports and Jet Analytics come with purpose-built adapters that give customers full access to all tables and fields within the source ERP system. Those adapters dynamically read the source database schema, including any custom tables and fields that might have been added by the customer. That means you don’t need to create custom web services APIs or do any other work to access your data. It’s simply available to you automatically.

Jet Reports and Jet Analytics also make for a smoother upgrade process. When you move to the next version of Microsoft Dynamics, you can take your reports with you; insightsoftware takes care of any required technical changes, so the process works seamlessly without any effort on your part.

At insightsoftware, we provide financial reporting and analytics tools for the entire family of Microsoft Dynamics products. For nearly three decades, we have been helping business leaders get the information they need quickly, accurately, and efficiently. If your organization is looking to improve your Microsoft Dynamics Business Central reporting capabilities, we urge you to learn more about the benefits of our simple, powerful reporting tools.  Contact us today for a free demo.

12 2020 Whitepaper Jetreports 9fatalflaws Resource (1)

9 Fatal Flaws to a Dynamics 365 Business Central BI Project

Download Now: