Microsoft Dynamics 365 Business Central

This plugin is currently in private preview and distributed directly to your Snowflake account instead of via Marketplace listing. Please get in touch to request access.

Prerequisites

You will need to use Business Central to create a Connect app. This allows point-to-point connection between Dynamics 365 Business Central and Omnata Sync for Snowflake via a REST API to interchange data. Learn more at Microsoft Docs for Business Central API (v2.0)

Authentication

OAuth (Microsoft Entra ID)

Visit the Microsoft Docs for Setting up Microsoft Entra ID based authentication for Business Central.

Setting up a new connection

When setting up a new connection you have 2 options for authentication:

• OAuth (Microsoft Entra)

• OAuth (Plugin managed tokens)

Tokens need to be refreshed every 60 minutes. If your sync is going to take longer than 60 minutes, you will need to use the Plugin managed tokens option.

Items you will need to setup a connection:

• Client ID

• Client Secret

• Tenant ID

• Environment (eg Production)

Inbound Syncs

We support the following endpoints for Business Central:

• api/v2.0

• OData

• Custom API's

Optimizing performance

Due to the size of initial syncs, its recommended that you split your inbound syncs into multiple streams. The following are the recommended streams to use for Business Central:

• General

• Accounts

• Customers

• Sales

• Purchases

• General Ledger

• Fixed Assets

Unsupported Endpoints

The following endpoints are currently not supported

• timeRegistrationEntries

• subscriptions

• picture

• documentAttachment

• pdfDocument

---

Using a Custom Storage Locations

It is recommended that you use customise the Omnata Sync Storage Location so your projects can easily access the data as necessary. The following are the recommended properties to use for the storage location:

	Raw Tables	Normalized Views
Database Name	SOURCE_DATA_LOAD	SOURCE_DATA_LOAD
Schema Name	BUSINESS_CENTRAL_RAW	BUSINESS_CENTRAL
Table Name	{{stream_name}}	{{stream_name}}

It is important that the Omnata Sync Engine native application has the correct permissions to create tables in the database(s) and schema(s) you specify. The following is an example of the permissions required for the above storage location:

create database SOURCE_DATA_LOAD comment='Data Sourced through Omnata Sync Engine';
create schema SOURCE_DATA_LOAD.BUSINESS_CENTRAL;
create schema SOURCE_DATA_LOAD.BUSINESS_CENTRAL_RAW;
grant usage on database SOURCE_DATA_LOAD to application "OMNATA_SYNC_ENGINE";
grant create schema on database SOURCE_DATA_LOAD to application "OMNATA_SYNC_ENGINE";
grant create table on schema SOURCE_DATA_LOAD.BUSINESS_CENTRAL to application "OMNATA_SYNC_ENGINE";
grant create table on schema SOURCE_DATA_LOAD.BUSINESS_CENTRAL_RAW  to application "OMNATA_SYNC_ENGINE";
grant create view on schema SOURCE_DATA_LOAD.BUSINESS_CENTRAL to application "OMNATA_SYNC_ENGINE";
grant usage on schema SOURCE_DATA_LOAD.BUSINESS_CENTRAL to application "OMNATA_SYNC_ENGINE";
grant usage on schema SOURCE_DATA_LOAD.BUSINESS_CENTRAL_RAW to application "OMNATA_SYNC_ENGINE";

Outbound Syncs

Outbound syncs are not supported for Business Central.

Webhooks

Webhooks is the way to get notified if an entity changes in Business Central and is the only way to get notified about deletes. Unfortunately, Snowflake doesn't currently provide the ability to use webhooks, therefore alternative strategies are required.

We recommend you use the Inbound Sync Strategy "Full Refresh" for the following entities instead:

• accounts

• companies

• company_information

• countries_regions

• currencies

• employees

• payment_methods

• payment_terms

• tax_areas

• tax_groups

• units_of_measure

• vendors

Secondary full-refresh schedule

An alternative method to capture deletes.

If you wish use the "Incremental Refresh" strategy, you can set an additional schedule when you would like a full refresh to occur by running the following stored procedure in Snowflake. This will enable eventual consistency of the reference data.

call OMNATA_SYNC_ENGINE.API.SET_FULL_REFRESH_SCHEDULE('slug_name', 'main', PARSE_JSON('{
      "frequency": "0 0 * * * UTC",
      "frequency_name": "Custom"
    }'));

Functions

There are two direct query options available for Business Central which allows you to query the data directly in Snowflake worksheets and notebooks.

api/v2.0

This is the recommended direct query type for Business Central. It allows you to query the Business Central API directly.

Parameter	Description
connection_slug	The connection slug you have setup for Business Central
company_id	The company id you wish to query
entity_name	The name of the entity you wish to query
fields	List of fields names to return
filter	List of filter expressions to apply
expansions	List of child entities to return with the results

Example Usage

select * from table(OMNATA_DYNAMICS_365_BUSINESS_CENTRAL.UDFS.API_QUERY('business-central-prod', 'f3b0d711-5dc5-4b0c-9eef-52d947634567', 'locations', ['id', 'displayName'], ['code eq \'Main\''], []));

Custom Endpoints

If you need to query a custom endpoint, you can use the following stored procedure to do so:

Parameter	Description
connection_slug	The connection slug you have setup for Business Central
company_id	The company id you wish to query
entity_name	The name of the entity you wish to query
fields	List of fields names to return
filter	List of filter expressions to apply
expansions	List of child entities to return with the results

select * from table(OMNATA_DYNAMICS_365_BUSINESS_CENTRAL.UDFS.CUSTOM_API_QUERY('business-central-prod', 'f3b0d711-5dc5-4b0c-9eef-52d947634567', 'locations', ['id', 'displayName'], ['code eq \'Main\''], []));

Companies

To get a list of companies you can query, you can use the following stored procedure:

Parameter	Description
connection_slug	The connection slug you have setup for Business Central

select * from table(OMNATA_DYNAMICS_365_BUSINESS_CENTRAL.UDFS.GET_COMPANIES('business-central-prod'));