Business Central Queries

Hi, I have joined a new compay who use cloud Business Central. I have been asked to create some transactional reports in Excel, what is the best most efficient way to extract data from BC? I have a background in SQL but i understand that i cannot use MySQL etc. to query the cloud BC but how and what software do i use to create BC equivalents of SQL views? Extracting data into Excel via Odata and then transforming the data, merging queries etc. is really slow so i want to push back as much of the work to BC so any suggestions would be appreciated, thanks

Have you looked at PowerBI. It’s quick and easy tool to get to the data out of the box.

Hi, it’s more transactional reports than analysis so the users want to use Excel. We are at the early stages of looking at PBI, just on that subject would you recommend using Dataverse or Dataflows to do the transformations before consuming in PBI?

Business Central has OData endpoints you can connect to, too. Lots of times, though, if you have a lot of data, they’re pretty poor performing. In that case, you might need to extract through a dataflow to Dataverse or somewhere else.

No one answer here. First identify what the requirements are. PowerBI is a quick way to collect data and feed to Excel or other tools. But if you have LOTS of data it can be slow. Also, LOTS is subjective.

If you need, you can push data to the DataVerse or Data Lake or even a local database. Too often people start writing reports and extracts without looking at the big picture and months later have a mess.

Start with a plan, then you can see what meets your needs.

I’ll get off my soapbox.

Good Luck.

1 Like

Are these different to the web service odata?

Do you have an opinion with what one of the options you listed is best? Dataverse, Data Lakes or local database? I realise that different people have different ideas and preferences but is there a logical application to pair with BC to transform and store BC data for use in reports?

not that i know of. i think they’re the same. @lifehacks365?

@B_Stirton - what Jacob is referring to is the same thing as the web services you are referring to.

Like others said, this is a fairly specific to the situation type of thing and not one answer will be a best fit. End requirements are the main driver for what is the best path forward from here.

1 Like