Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 302 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Excel - data connections to BC On-Premises > SQL-Server or NAV-Query + OData or ...?
neckit
Member Posts: 24
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
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
0
Answers
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é