Excel - data connections to BC On-Premises > SQL-Server or NAV-Query + OData or ...?

neckit

hi,

we are busy with preparing to switch from NAV 2013 to BC On-Premises.

In our own SQL server database, we set up some SQL server views of the NAV 2013 database today. In many Excel reports, we’ve got access to these views via the Excel data connections and use them for Excel dashboards, pivot tables, power queries and general reports. This has worked very well for many years.

The new table structure introduced with BC (Guid + table extensions attached to tables) makes it difficult for us to keep this procedure. We would then have to work in the SQL server with Alias and Joins to the extension tables.
As an alternative, we could create a NAV query for all externally required tables and call them up in Excel via web services (OData) and process them further. But here the performance seems much worse to us than with the SQL server views.

Which way would you recommend to retrieve data (when using BC On-Premises) performant, securely and without a cloud connection in Excel 2016 (no 365)?

regards
Ralf

comknowhow

Hi Ralf,

I´m new in BC-on Premise too.
We´re also dissatified with the performance of the BC querys in Power Query / Power Pivot.
Do you got a solution?

Best regards
André