Skip to main content

Set Up the Dynamics 365 Business Central Data Source

This article explains how to set up the Jet Analytics Dynamics 365 Business Central — Online and Jet Analytics Dynamics 365 Business Central — SQL Server data sources.

Unlike the Business Central 365 data source, these data sources provide features that simplify working with Dynamics 365 Business Central (formerly Dynamics NAV). In a BC database, each account (also called a "company") has its own set of tables. These data sources merge tables across accounts automatically, so that accounts share tables without requiring manual merging in the Prepare instance. The accounts to extract from can be specified, avoiding the need to filter out unwanted account data later. Option values are also included as part of the extracted tables.

Prerequisites

For the Online provider with Service to Service Authentication, an Azure App Registration is required.

Add the data source connection in the Portal

  1. Sign in to the Jet Analytics Portal, navigate to Data Estate > Data source connections, and click Add data source connection.

    The Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support .

  2. On the Add data source connection page, select the provider from the Data source list:
    • For the SaaS version of BC, select Jet Analytics Dynamics 365 Business Central — Online.
    • For the on-premises version of BC, select Jet Analytics Dynamics 365 Business Central — SQL Server.
  3. Enter a Name for the data source connection.
  4. Enter the connection information for the selected provider:

    Online provider

    • API URL: Enter https://api.businesscentral.dynamics.com/v2.0/{tenantID}, replacing {tenantID} with your Tenant ID. The Tenant ID can be found by signing in to businesscentral.dynamics.com and navigating to Help & Support.
    • Environment: Enter the name of the environment to connect to. The default is Production. To view available environments, sign in to businesscentral.dynamics.com and click Environments.
    • Authentication method:
      • OAuth 2.0: Enter a file path to store the OAuth token, for example C:\OAuth\OAuthSettings.txt.
      • Service to Service Authentication: Enter the Application (Client) ID and Client Secret from the Azure App Registration.

    SQL Server provider

    • Enter the Server name where the database resides.
    • Enter the Database name.
    • Select Trust server certificate.
    • Select SQL Server Authentication or Active Directory Password Authentication and enter the Username and Password.
  5. In the Accounts box, enter the accounts to include or exclude:
    • When Include all accounts is checked, all accounts are included except those listed in the Accounts box.
    • When Include all accounts is unchecked, only the accounts listed in the Accounts box are included.
    • If the Accounts box is empty and Include all accounts is checked, all accounts are included.
  6. In the Template account box, enter the name of the account whose structure should be used. This account determines the tables and fields copied from the source.
  7. In the Translation box, enter the three-letter language code for table and column names. Leave blank to use the default ENU (English). For a full list of available languages for the Online provider, see the BC language table.
  8. Under Advanced Options, configure provider-specific behaviour as needed. The available options depend on the selected provider type.

    Available for both providers:

    • Disable option tables: When enabled, tables generated by the adapter that contain option values (for example, "Item Ledger Entry.Document Type") are excluded from the extracted data. If Enhanced metadata read behavior is set to Disabled, option tables are always excluded regardless of this setting.
    • Option text length: The maximum length of an option text in option tables. Increase this value if the length is too short for the data in your system.

    SQL Server provider only:

    • Enhanced metadata read behavior: Controls how the adapter reads option values and other metadata. Options: Disabled, Read from 'Adapter' tables, Read from 'Objects' (BC 2013 or later), Read from 'Objects' and merge tables (BC 2016 or later).
    • Include SIFT tables/views: When enabled, SumIndexField Technology (SIFT) tables and views are included in the extracted data.
    • Read invalid identifiers: When enabled, characters that cannot be used in object names are read from the source, and invalid characters are replaced with underscores in database object names.
    • Invalid identifiers: A list of characters that cannot be used in object names. This setting is ignored when Read invalid identifiers is enabled.
    • Allow dirty reads: When enabled, tables are not locked before data is read from them.
  9. Click Add to save the data source connection.

Add the data source in Jet Analytics Data Integration

  1. Open Jet Analytics Data Integration and open the Ingest instance. Right-click Data sources and select Add Data Source…
  2. Enter a Name for the data source and click Next.
  3. Select the BC data source connection configured in the Portal and click Next.
  4. Select the tables to include:
    • All tables in the data source: Extracts all tables. Click Next. If using the Online provider with OAuth authentication, right-click the data source after it is added, select Edit Data Source, and click Authorize OAuth to authorise the connection.
    • Let me select the tables: Extracts specific tables. Click Next. When using the Online provider with OAuth authentication, Jet Analytics Data Integration automatically attempts to authorise OAuth. A "Log in as a specific user" prompt may appear, after which the Azure App Registration is granted access to Business Central.
  5. Search for and add the required tables. For example, search for G/L Account Category.
    Note:

    The schema for option tables is set to Option.

  6. Select the columns to include using Select Columns…
  7. Configure Incremental Load. The recommended field is timestamp if available, or Modified At.
  8. Transfer the tables from the Ingest instance to the Prepare instance. Preview a table to verify the included accounts, which appear in the DW_Account field.

Was this article helpful?

We're sorry to hear that.