Reporting options for Business Central

Business Central/NAV Developers Forum
1

Hi everyone, we are looking at solutions for reporting purposes using our data from Business Central and would welcome any advice / real life experiences.

We have about 10 finance users and 5 project users who consume finance and job related data via Power BI and Excel but the problem we have is the speed at which the data is extracted from Business Central and then transformed into the required state. We use Dataflows with Power BI but these can be painfully slow and often Business Central forcibly closes the connections so the Dataflows fall over. We also have some Excel project files which are used for cost tracking and again the OData connections to extract the data into Power Query can be horribly slow even with filtering etc. within the OData query string.

So we need a low cost solution which will allow us to extract and transform data from Business Central to be used within Power BI and Excel and potentially Power Apps going forward.

An old colleague recommended replicating the required Business Central data into Azure SQL database which because this is a SQL backed solution we could write Views and Stored procedures etc. but he did caveat that with Azure SQL database can be really expensive and as with everything Microsoft it can be hard to understand the cost until you get the monthly invoice. Does anyone have experience with Azure SQL database that can give real life experience? Would this solution be overkill?

Is there any other solutions that we should be considering?

Thanks

2

Without understanding how the calculations are working, it makes it hard to understand if it is a solution issue, or if the calculations done via power query can be moved into a BC Query object for better performance. I’m not 100% sold that you need Azure SQL database just yet.

Take a look at Jet Reports as an option to pull data into Excel and complete calculations, it may give you the finance data that you are looking for in a quicker method.

For more advanced work - Jet Reports also has a Jet Analytics product where you can build data cubes within a SQL database, and complete your calculations within the SQL database, and then connect PowerBI to SQL to pull in what the cubes are calculating.

1 Like
3

I used Jet reports extensively a few years ago and found the solution painfully slow when trying to perform complex financial calculations to the point we actively shut down that programme.

I haven’t however used Jet Analytics and if this is indeed backed by SQL then i will definately take a look at this. Thanks for your response.

1 Like
4

I have had customers utilize Jet Analytics successfully for complex calculations. Because the processing is being done in a SQL database, performance increases when running the Jet reports.