Skip to content

Execution Guide

Jump to bottom
Bert Verbeek edited this page Apr 9, 2026 · 3 revisions

Execution Guide

This guide explains how to execute data exports from Business Central and consolidate the data using either Azure Synapse pipelines or Microsoft Fabric notebooks.

📋 Table of Contents

Overview

The execution process consists of two main steps:

  1. Export from Business Central: Incremental data updates are exported to the data lake (deltas folder)
  2. Consolidation: Deltas are consolidated into final datasets
    • Azure Data Lake: Using Synapse pipelines
    • Microsoft Fabric: Using notebooks

Exporting Data from Business Central

💡 Tip: For details on all setup page fields and options, see the Configuration Reference page.

Step 1: Open the Export Page

  1. Open Business Central
  2. Search for Export to Azure Data Lake Storage (Page 82560)
  3. The page displays your configuration and table list

Export Page

Step 2: Configure Tables for Export

Add tables to export in the bottom grid of the page:

  1. Click + New in the table grid
  2. Select a Table ID from the lookup
  3. The Table Caption is automatically filled
  4. Select fields to export:
    • Click on the Fields action
    • Check the fields you want to include
    • ⚠️ Be selective - export only necessary fields for performance

Important Notes:

  • ⚠️ Unsupported field types: BLOB, Flow, Filter fields are not supported
  • ⚠️ Obsoleted fields: Fields marked as obsolete cannot be exported
  • ⚠️ Access property: Only fields with Access = Public are available
  • ⚠️ System fields: SystemCreatedAt for old records (pre-audit field era) defaults to 01 Jan 1900

Step 3: Configure Table Options

For each table, you can configure:

Option Description
Enabled Enable/disable export for this table
Export in a category Group tables into categories for selective export
Skip timestamp sorting Disable row version sorting for this table (use if timeouts occur)

Step 4: Run the Export

  1. Click Export action at the top of the page
  2. Multiple parallel sessions spawn (one per table and company)
  3. Monitor the Last exported status column:
    • In process: Currently exporting
    • Success: Export completed successfully
    • Failed: Export encountered an error

When all tables show a status other than "In process", the export is complete.

Step 5: Export from Multiple Companies

The standard export page runs only for the current company. To export from multiple companies:

Option 1: Multi-Company Export Page (Recommended)

  • Use the Export Companies to Azure Data Lake Storage page
  • Add all companies and export them from a single centralized page
  • See Multi-Company Export for details

Option 2: Manual Export

  1. Switch company in Business Central
  2. Repeat the export process for each company

Option 3: Scheduled Export

Performance Tips

Enable Read-Only Replica (BC Version 21+)

  1. Go to Feature Management in Business Central
  2. Enable Report read-only data access
  3. This makes exports read from the replica database
  4. Minimizes impact on normal ERP operations

Handle Large Tables

For tables with millions of records:

  • ✅ Enable Skip row version sorting temporarily during initial export
  • ✅ Reduce the number of fields exported
  • ✅ Export large tables outside business hours
  • ✅ Consider splitting very large tables into categories
  • ⚠️ Disable sorting after initial export completes

Telemetry

Control operational telemetry from the main setup page:

  • Emit telemetry: ON/OFF toggle
  • When enabled, telemetry is sent to Application Insights specified by the publisher
  • Helps track export performance and diagnose issues
  • Read more about BC telemetry

Running the Integration Pipeline (Azure Data Lake)

After exporting from Business Central, consolidate the deltas using Azure Synapse pipelines.

Pipeline Options

You have two options for triggering pipelines:

  1. Manual trigger (Azure Synapse Analytics directly)
  2. Automated trigger (Power Automate integration)

Option 1: Manual Pipeline Trigger

Using Azure Synapse Studio

  1. Open Azure Portal > Your Synapse workspace
  2. Click Open Synapse Studio on the Overview blade
  3. Navigate to Integrate tab (pipeline icon)
  4. Select Consolidation_AllEntities pipeline
  5. Click Add trigger > Trigger now

Trigger Pipeline

Configure Pipeline Parameters

Parameter Description Recommended Value
containerName Data lake container name Your container name (e.g., "businesscentral")
deleteDeltas Delete deltas after successful consolidation true (for production)
false (for testing)
sparkpoolName Spark pool for shared metadata tables Spark pool name or leave blank

About Parameters:

  • containerName: Must match the container configured in Business Central
  • deleteDeltas:
    • true: Cleans up deltas automatically (recommended)
    • false: Keeps deltas for debugging/troubleshooting
    • Deltas are NOT deleted if pipeline fails
  • sparkpoolName:
  1. Click OK to start the pipeline

Monitor Pipeline Execution

  1. Navigate to Monitor tab in Synapse Studio
  2. Select Pipeline runs
  3. View status, duration, and logs
  4. Click on a run to see detailed activity execution

Manual Pipeline Runs

Pipeline Status:

  • 🟢 Succeeded: All activities completed successfully
  • 🔴 Failed: One or more activities failed
  • 🟡 In Progress: Currently executing
  • ⚪ Queued: Waiting to start

Option 2: Automated Pipeline Trigger (Power Automate)

For automatic pipeline execution when BC export completes:

  1. Set up Power Automate integration (see Power Automate Integration)
  2. Power Automate triggers Consolidation_OneEntity pipeline automatically
  3. One pipeline run per table export completion
  4. No manual intervention required

Comparison:

Aspect Manual Trigger Power Automate
When After all exports complete After each table completes
Pipeline Consolidation_AllEntities Consolidation_OneEntity
Automation Manual Automatic
Use Case Scheduled batch processing Event-driven real-time

Pipeline Structure

The Azure Synapse solution includes three pipelines:

  1. Consolidation_AllEntities: Main pipeline that processes all tables

    • Checks for deltas
    • Calls Consolidation_OneEntity for each table
    • Creates shared metadata tables (if sparkpoolName provided)

  2. Consolidation_CheckForDeltas: Helper pipeline that checks if deltas exist

  3. Consolidation_OneEntity: Processes a single table/entity

    • Consolidates deltas into data folder
    • Handles duplicates
    • Updates CDM manifest

Important Notes

⚠️ Do not trigger pipeline when:

  • Data is currently being exported from BC
  • Another pipeline is already running for the same container

⚠️ Duplicate data handling:

  • Tables with DataPerCompany = false may have duplicates in deltas
  • Tables that were reset and re-exported may have duplicates
  • Pipeline removes duplicates automatically during consolidation

Running Fabric Notebooks (Microsoft Fabric)

After exporting from Business Central to Fabric Lakehouse, consolidate deltas using notebooks.

Step 1: Open the Notebook

  1. Navigate to your Fabric workspace
  2. Find your notebook (e.g., "CopyBusinessCentral")
  3. Click to open it

Step 2: Configure Notebook Parameters

The notebook has configurable parameters at the top:

# Parameters
lakehouse_name = "BusinessCentralData"  # Your lakehouse name
process_all_tables = True               # Process all or specific tables
specific_tables = []                    # List of specific tables (if not all)

Adjust as needed for your environment.

Step 3: Run the Notebook

Manual Execution:

  1. Click Run all in the toolbar
  2. Monitor cell execution
  3. Review output and logs

Scheduled Execution:

  • If you configured a schedule during setup, the notebook runs automatically
  • View scheduled runs in the notebook's schedule tab

Step 4: Monitor Execution

  • Watch each cell execute sequentially
  • Check output for any errors
  • Verify tables are created in the lakehouse

Step 5: Verify Results

  1. Navigate to your Lakehouse
  2. Expand the Tables section
  3. Verify tables are present and updated
  4. Click on a table to preview data

Parallel Processing Option

For large datasets, use the parallel processing notebook:

  1. Import fabric/CopyBusinessCentral_parallel_option.ipynb
  2. Configure parallel processing parameters: 
    max_parallel_tables = 5  # Number of tables to process simultaneously
  3. Run the notebook
  4. Benefits: Faster processing for many tables
  5. Considerations: Higher resource usage

Consuming the Data

After consolidation, data is available for consumption in various ways.

Consuming CDM Data (Azure Data Lake)

Option 1: Power BI via CDM Folder

  1. Open Power BI Desktop
  2. Click Get Data
  3. Select Azure Data Lake Storage Gen2
  4. Click Connect

Power BI Get Data

  1. Enter your Data Lake Storage endpoint:
    • Format: https://<storage-account>.dfs.core.windows.net
    • Find in Azure Portal > Storage Account > Endpoints blade
  2. Select CDM Folder View (Beta)
  3. Click OK

Power BI CDM

  1. Navigate to your container
  2. Expand the database icon labeled data-manifest
  3. Select tables to load

Power BI Manifest

  1. Click Load or Transform Data

Option 2: Shared Metadata Tables

If you created shared metadata tables (by providing sparkpoolName):

Access via Synapse Studio:

  1. Open Synapse Studio
  2. Go to Data section
  3. Expand Lake databases
  4. Find your database (named after container)
  5. Right-click a table > New SQL script > Select TOP 100

Shared Metadata Table Query

Connect Power BI via Serverless SQL:

  1. Open Power BI Desktop
  2. Select SQL Server connector
  3. Enter Serverless SQL endpoint:
    • Find in Azure Portal > Synapse workspace > Overview
    • Format: <workspace-name>-ondemand.sql.azuresynapse.net
  4. Database: Your lake database name
  5. Use DirectQuery or Import mode
  6. Select tables and create reports

Benefits:

  • ✅ DirectQuery support for real-time data
  • ✅ SQL query capabilities
  • ✅ Multi-table joins
  • ✅ Spark notebook access

Read more about Shared Metadata Tables

Consuming Fabric Data (Microsoft Fabric)

Native Fabric Integration

  1. Open Power BI Desktop
  2. Click Get Data > More
  3. Select OneLake data hub or Power BI datasets
  4. Navigate to your workspace
  5. Select your lakehouse
  6. Choose tables to load

Direct Lakehouse Access

  1. In Fabric workspace, open your lakehouse
  2. Click Open > SQL endpoint
  3. Use the SQL endpoint for querying
  4. Or use Spark notebooks for advanced analytics

Advantages of Fabric

  • ✅ Native Power BI integration
  • ✅ No connection string configuration needed
  • ✅ Automatic schema detection
  • ✅ Real-time updates
  • ✅ DirectQuery support

Data Format Overview

Azure Data Lake

  • Deltas folder: CSV files (incremental updates)
  • Data folder: CSV or Parquet (consolidated data)
  • Manifest: data.cdm.manifest.json (metadata)
  • Format: Common Data Model (CDM)

Microsoft Fabric

  • Deltas folder: CSV files (incremental updates)
  • Tables: Delta Parquet format (consolidated)
  • Format: Delta Lake

Monitoring and Troubleshooting

Monitoring BC Exports

Check Export Status

  1. Open Export to Azure Data Lake Storage page
  2. Review Last exported status for each table:
    • ✅ Success: Completed successfully
    • 🔴 Failed: Error occurred
    • 🕐 In process: Currently running
  3. Check Last Timestamp to see when export ran
  4. Review Error Message if status is Failed

Track Export Runs

View historical exports in the ADLSE Run table:

  1. Navigate to Export to Azure Data Lake Storage page
  2. Click Related > Runs
  3. View all historical export attempts with:
    • Started time
    • Ended time
    • Status
    • Error messages

Track Files in Data Lake

Each export creates files in the deltas folder:

  • Modified timestamp shows when export finished
  • Compare with ADLSE Run table to match files to runs
  • Use telemetry to track file creation

Monitoring Azure Synapse Pipelines

  1. Open Synapse Studio
  2. Navigate to Monitor > Pipeline runs
  3. View:
    • Run ID and status
    • Start time and duration
    • Trigger type (manual/scheduled)
  4. Click a run to see:
    • Activity-level details
    • Input/output parameters
    • Error messages

Monitoring Fabric Notebooks

  1. Open your notebook in Fabric
  2. View Recent runs at the top
  3. Click a run to see:
    • Execution duration
    • Cell outputs
    • Any errors
  4. Check Schedule tab for scheduled run history

Common Issues and Solutions

Export Issues

Issue: Token cache errors on BC On-Premises

  • ⚠️ The BC server setting DisableWriteInsideTryFunctions defaults to True on On-Premises, blocking database writes (including IsolatedStorage) inside TryFunction procedures
  • ✅ Set DisableWriteInsideTryFunctions to False in your BC server configuration
  • ℹ️ This is a platform difference from BC SaaS, where this restriction is not enabled
  • 📖 See Microsoft documentation on TryFunction behavior

Issue: Export timing out

  • ✅ Enable Skip row version sorting for large tables
  • ✅ Reduce number of fields exported
  • ✅ Reduce Max payload size setting
  • ✅ Export during off-peak hours

Issue: Fields not available for export

  • ✅ Check field is not BLOB, Flow, or Filter type
  • ✅ Verify field Access property is Public
  • ✅ Ensure field is not obsoleted

Issue: SystemCreatedAt showing 01 Jan 1900

  • ℹ️ This is expected for records created before audit fields
  • ℹ️ Artificial value set for old records

Pipeline Issues

Issue: Pipeline fails with timeout

  • ✅ Increase timeout parameter in pipeline URL
  • ✅ Reduce payload size in BC configuration
  • ✅ Process fewer entities per run

Issue: Duplicate records in data

  • ℹ️ Expected for tables with DataPerCompany = false
  • ℹ️ Expected after table reset and re-export
  • ✅ Pipeline removes duplicates automatically

Issue: Pipeline won't start

  • ✅ Check if another pipeline is running
  • ✅ Verify no active export from BC
  • ✅ Review Synapse workspace capacity

Notebook Issues

Issue: Notebook cells fail

  • ✅ Verify lakehouse is attached
  • ✅ Check delta files exist
  • ✅ Review error messages in cell output
  • ✅ Ensure Spark pool is available

Issue: Tables not created

  • ✅ Confirm notebook completed successfully
  • ✅ Refresh lakehouse view
  • ✅ Check deltas folder has data

Performance Optimization

BC Export Performance

  • ✅ Enable Report read-only data access feature
  • ✅ Export during off-peak hours
  • ✅ Use categories to group tables by update frequency
  • ✅ Schedule frequent exports for high-change tables
  • ✅ Schedule infrequent exports for static tables

Pipeline Performance

  • ✅ Use Parquet format for better compression
  • ✅ Increase Synapse workspace scale if needed
  • ✅ Process tables in batches
  • ✅ Enable deleteDeltas to keep storage clean

Fabric Performance

  • ✅ Use parallel processing notebook for many tables
  • ✅ Ensure adequate Fabric capacity
  • ✅ Schedule consolidation during off-peak hours
  • ✅ Monitor capacity metrics in Fabric admin

Next Steps

Related: Scheduling Exports | Power Automate Integration | FAQs | Back to Home

Clone this wiki locally