Business Central Datawarehouse Connector

Introduction og functionality

The Ecodel Exclusive - Datawarehouse Connector extension for Microsoft Dynamics 365 Business Central helps you connect Business Central with an Azure SQL server.

In just three easy steps this gives you the opportunity to choose which data you wish to work with in a system outside Business Central.

All you need is a Business Central Environment and an Azure Subscription in a Tenant that you have administrative privileges for.

Price

The Connector is priced per environment based on the number of tables and fields you need to export to your datawarehouse.

Any of the paid plans also allow setting up the job queue for managing the exports on a set schedule.

Setup and use

Start by running the Datawarehouse Connector Assisted Setup guide, as it will walk you through the steps needed.

Step 1: Setup App Registration

Start by setting up an App Registration in your Azure subscription.

You don't have to set up anything besides a Name you can remember and a Client Secret.

Step 2: Setup SQL Database

Create or use an existing Azure SQL-server instance and database. For the best performance, choose the same Region for the server as the "Azure Region" that holds your Business Central Environment.

Choose a Production workload environment, if you want to do anything other than test the solution with less than a few million records. The Development workload is too unstable and not fast enough to handle more than a few 100k records per day.

Choose a "Public endpoint" and select "Allow Azure services and resources to access this server". If you don't want to allow all Azure services, you will have to set up a list of around 20 IP-adresses that the Datawarehouse Connector uses for communicating with your server and database. Contact support@ecodel.com to get the current list of IP-adresses in use.

Set up an SQL server authentication Login and Password that you can remember.

When the server and database have finished deploying, add a managed identity to the server by going to your server's Identity tab and activating a System-Assigned Managed Identity.

Step 3: Setup Storage Account & Container

When setting up your storage account, choose the same Location as your Business Central and SQL-database Region for best performance.

You can opt for a Standard tier LRS storage. "Enable hierarchical namespace" and set Access Tier to Hot. Disable soft-delete and other options in Data Protection.

After setting up the storage account, go to the storage account's Access Control tab and add a role assignment with "Storage Blob Data Contributor" to the previously created App Registration's Service Principal.

You also need to add a role assignment with "Storage Blob Data Contributor" to the previously created SQL-server's Managed Identity.

Finish setup

When finishing setup, the App will check your connections. You must allow the app to make requests to external services for it to function at all. Select "Allow Always".

Select tables and fields

Once the connection to App registration, Storage account and SQL database is verified, you can select the tables and fields that you want to synchronize on the Datawarehouse Connector Setup page.

Once you're happy with the table/field setup, press the Export command and/or set up a scheduled export.

Advanced options

Advanced options include

• Selecting a Decimal field for a table to be used as a checksum field. If selected the Business Central Datawarehouse Connector will compare the checksum as well as the record-count before marking the table with "Datawarehouse checksum OK.
• Selecting a custom Schema Name for the tables in your datawarehouse. The default value is [dbo] like in the Business Central database.