web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Business Central forum / Extracting data from B...
Business Central forum

Extracting data from BC on premises (for Data Warehouse)

(0) ShareShare
ReportReport
Posted on by christopher.jones 5

Hi,

what is the preferred way to extract data from Business Central? We need to pull out data daily for a Data Warehouse use case.

With on premises edition I understand that we could access the database directly or use a web API.

Is access to the database a documented and recommended way to access the system? 

Categories:
I have the same question (0)
  • Suggested answer
    jcastro Profile Picture
    jcastro 2,245 on at

    Hello Christopher,

    I would encourage you to use the ways BC provides in order to get the information. You can use SOAP/OData webservices depending on your needs, and recently also REST APIs.

    Webservices: docs.microsoft.com/.../web-services

    REST API: docs.microsoft.com/.../

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    If you’re talking about SaaS enviroment, recommended way is using APIs or OData endpoint.

  • christopher.jones Profile Picture
    christopher.jones 5 on at

    Thansk for your anwser.

    The BC we need to extract data from is on premises.

    So even if BC is hosted on premises, it is not recommended to extract data from the underlying MSSQL database directly? If we would go MSSQL way, is it officially supported/documented by Microsoft? (or only SOAP/Rest APIs)

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    On-premise is a totally different thing. You can still access SQL Server and for complex ETL scenarios (big amount of data, performances etc) creating a data import in a data warehouse via SQL is the best way again. OData has some limits on the possibility of queries that you can apply.

  • christopher.jones Profile Picture
    christopher.jones 5 on at

    Thanks, then it makes sense to go the MSSQL route. Can you also tell if the tables are documented somewhere and if the tables differ from the NAV system?

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    Tables are the same. Just remember that if you have tableextension objects (customizations created with AL language) that adds custom fields to standard tables, these newly added fields are on a new table called like the original table + $extensionID (so you need a join).

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Business Central

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans