As technology evolves, things become more advanced and require businesses to upscale their technology in order to keep up. This is the same when it comes to data too with Microsoft Power BI becoming popular by enabling marketers to design dashboards that allows them to give a visual representation of how the business is doing against business KPIs. If your business is currently using Business Central or if you are looking to the make the move in the future then you are almost certainly hearing a lot of rumblings about Microsoft’s premier choice for reporting and analytics, Power BI. Even though, PowerBI can provide your business with all kinds of benefits, it does come at a cost.
If you are looking to make the transition from Dynamics NAV or GP, you will find that this will require a significant adjustment from the way you are currently reporting and analysing your data. The way in which you access data has changed dramatically in Dynamics 365 Business Central. Microsoft’s cloud-based ERP systems have stopped customers being able to access data from within their databases. The way round this now is that users have to rely on customer queries deployed as web services. To do this, you will need to have the capability in-house to be able to create and deploy those queries in your Business Central environment.
Furthermore, this change also creates several other problems when it comes to making changes to the database, such as when you are looking to run third-party applications in Business Central or when Microsoft’s updates give you additional features that result in changes being made to the overall data model.
When this happens, it will mean that customers will need to allocate additional resources to be able to create and maintain reports for Business Central, starting at the beginning of your deployment and continuing for as long as you use this system.
It doesn’t have to be like this, there is another path that you can go down to make it simpler and much more cost effective but still allows people in your business to continue to benefit from Microsoft Power BI. If you have the right tools in place, you can add self-service capabilities to Power BI which will improve your overall efficiency and productivity in the future.
How can you access data with Business Central?
To successfully diagnose problems and how to resolve them, you will need to have a firm understanding of how data is accessed in Dynamics 365 Business Central.
A few years ago, you simply installed business applications such as ERP on-premise. Your IT team had full control and direct access to the ERP’s SQL database. In this instance, you could stop physical access to the servers as well as stopping the data being held elsewhere. Even in the case that you moved your ERP system to a private hosted environment in the Cloud, you still could segregate your data from data belonging to other customers giving you that extra layer of security.
This has changed dramatically! You will find that almost all cloud-based software now operates on a multi-tenant model whereby just one database will host all your customer data. As well as this, to be able to keep this data separate from other customers, Microsoft has stopped direct access to this data through the traditional methods.
This means that the only way that users are able to access ERP data is through application programing interfaces (APIs) which provides you with an easy way to run queries which relays the information back to your reporting tools. Through this method, you will find that it has three serious disadvantages when trying to access your data.
1) Overreliance on specific IT skills
The way in which Microsoft approaches resolving an ERP reporting problem is to give users the tools that allows them to create custom queries and expose them as web services. Through Power BI you are then able to directly connect to those web services which will allow you to extract the data you need from Business Central.
The limitation of such an approach is the fact that you will need an experienced IT expert to create all of the queries you require to allow you to run the necessary reports for your business through Power BI. Given that the previously used approach of making use of SQL-based reporting tools revolved around a basic understanding of how the database was structured which meant that only a minimal knowledge was needed allowing you to speed things up. The same understanding is required when it comes to APIs however more knowledge is required on the specialised programming side.
It is safe to say that most businesses using Business Central as their main ERP system don’t have the IT resources with dedicated programmers to hand to create and maintain the library of queries to allow your business to use Power BI seamlessly with Business Central. The way round this is to recruit an external managed IT provider to do this for you. This would be an easy way of resolving this problem if it were a one-time requirement. The problem is that reporting queries need to be regularly maintained which means that the overall costs could increase around this ERP system.
2) If you add extensions, you will need to repeat the process
One of the major benefits of Business Central is the fact that you can easily customise it to meet the specific needs of your business. In Business Central terms, these customisations are referred to as extensions. For businesses looking for specific requirements or need to improve the functionality of Business Central through third-party products, extensions are the easy answer. This allows them to gain the results that they want from the ERP system.
However, on the slightly downside, extensions need extra storage for additional information to be stored alongside your Business Central data.
It is a given that most businesses want the ability to be able to have that kind of additional information in both financial and operational reports. Again, as we mentioned above, the only way it can be achieved is through the construction of customer queries that give that information through web services which enables Power BI to use that data. Furthermore, that requires specialised IT skills, whether you gain them in-house or from an external IT provider.
It is great to have extensions to improve the functionality of your Business Central system, however you may need to make additional changes to your library or reporting queries. Even if you don’t think you will be building any custom extensions, you could become a victim of this problem. If you are looking to integrate third-party applications into your Business Central environment, you will need to review your reporting queries to decide which ones that you need to adjust. If you don’t make these changes, the new data brought in with the third-party applications may not be accessible in your Power BI reports.
3) When Microsoft changes things, you need to do the same
One of the biggest benefits of using cloud-based ERP products like Business Central is the fact that automatic updates can be applied. This is great in the fact that you can get updates on a more regular basis as well as limiting any disruptions to your business. These smaller enhancements are good, right?
In practice, yes and no. It is fantastic to know that you don’t need to do another upgrade which means you can access new features and functionality quicker than before. However, when it comes to reporting, even very small changes to the data model could add additional things that need to be done to your library and reporting queries. This will mean that you will need to go back to your IT team or external partner to make the necessary changes again.
How can the above problems be resolved?
Let’s just say that you could gain everything you need from Power BI, without having to constantly maintain reporting queries for Business Central? You can achieve this using the Business Central adapter that comes as part of Jet Reports and Jet Analytics, the entire process can be improved, making it easier for anyone in your business to have the knowledge to develop a query and create reports and dashboards without any additional help.
Through the use of either Jet Reports or Jet Analytics, you can access all the data you need from Business Central without any custom queries. By using Jet’s custom-built connection to Business Central makes it easier for you to view all the tables and columns within your database as well as any customisations. From this, Power BI can use the Jet Reports output or the Tabular Models or OLAP cubes from Jet Analytics to make it simpler for developers to build dashboards and analytics which stops them having to worry about any changes made in the Business Central database. This will make it easier for teams to keep everything in-house and dramatically reduce costs.
Jet Reports also has a ‘Table Builder’ tool that makes it much easier for those not technically minded to easily pick the information they require from the Business Central database and create a custom query from it in a matter of minutes. Better still, these queries can be saved as template files that you are able to share, publish or download.
Through making use of a Jet Report query in Excel, a user can easily extract the required data from Business Central quickly and effectively. From this, the user could save that information as an Excel file which can be used as a starting point for building a report or dashboard in Power BI.
Furthermore, both Jet Reports and Jet Analytics have a scheduler that allows you to automatically update your Excel source files on a regular basis. You can use it to extract data daily, hourly, or even more frequently. After you have made this decision, the scheduler will automatically run the Jet Reports query and save it as an Excel file. In addition to this, you will find that the Power BI reports built will automatically be updated as well.
The great thing about this is the fact that almost anyone can do it. If you use Business Central on a day-to-day basis then you will probably already have the skills needed to produce a Power BI report without even going anywhere near the Microsoft data access APIs.
As well as the things you can achieve above, Jet Reports and Jet Analytics can reduce the cost of having Business Central in the long term. Jet’s Business Central adapter automatically detects any changes to the BC data model and from this makes the data available in both Jet Reports and Jet Analytics.
If you create custom extensions for Business Central, you will find that any new fields that you have added will be shown in the Table Builder, making it much simpler to add them to reports.
If you choose to use third-party extensions that integrate into your current Business Central solution, data from those extensions will be shown too.
You will find that Power BI Desktop is a fantastic tool that allows you to create both reports and dashboards which is likely to become more familiar to Microsoft users in the future. Furthermore, businesses that are looking to implement the whole PowerBI stack should consider the fact that the project will be very complex and will require specialist support to complete it successfully.
For businesses that are already using Business Central, a much easier way of doing this is to not use the major components of the Power BI stack and look to empower users with powerful self-service tools that allows them to complete the task quicker without any need for IT support. Jet Reports gives you that ease of use as well as the self-service capabilities that enables frontline workers to complete tasks faster without any additional help.
If your business is running Business Central, or is looking to upgrade, it might be worth looking into the implications of Microsoft’s reporting and analytics approach and to assess other options like Jet Reports and Jet Analytics to bring down your overall costs in the long term. Contact us today to discuss your specific requirements.
