Introduction
Access Coins ERP+ now supports extracting entity data directly to Microsoft Fabric OneLake (Data Lake) in JSON format. This extends the existing extract and analytics framework to push data from Coins into a Fabric Lakehouse, enabling clients to leverage Microsoft Fabric's analytics capabilities for reporting and dashboarding.
Background
Access Coins has supported data extracts to Azure SQL for analytics for a number of years, using datasets and Change Data Capture (CDC) to provide near-real-time data feeds. More recently, we introduced a timestamp mechanism that records the last time each record changed in Coins, allowing incremental extracts.
The Data Lake integration builds on this timestamp technology to allow Coins to push changes to a Fabric OneLake Lakehouse as JSON files, rather than requiring an external system to pull data via REST. The data content is identical - it is the direction and mechanism of delivery that differs.
Key Features
Push-based data delivery from Coins to Microsoft Fabric OneLake.
Initial (full) extracts to populate the Lakehouse.
Delta (incremental) extracts based on timestamps to keep data current.
Flat JSON format optimised for SQL-friendly consumption in Fabric.
Configurable poll interval for delta extract frequency.
Per-entity control of Data Lake extraction.
Coexists with existing SQL extract solution (both can run simultaneously).
Architecture Overview
High-Level Data Flow
Component | Description |
Access Coins ERP+ Server | Source system. Contains the Extract Broker for delta extracts and the Extract Data report for initial extracts. |
CDC Triggers & Timestamps | Detect data changes in Coins and create sy_timestamp EXTRACT records for each modified/deleted record. |
OneLake REST API | Azure Data Lake Storage Gen2 REST API. Coins authenticates via Service Principal and uploads JSON files. |
Microsoft Fabric Lakehouse | Destination. JSON files land in Init/ and Delta/ folder structures within the configured root directory (e.g., Bronze/). |
How It Works
Initial Extract: When first setting up, a full extract dumps all data for each enabled entity as JSON files into the Init folder structure in OneLake, and the Last Data Lake Extract datetime is stamped on the extract record.
CDC Triggers in Coins detect data changes and create sy_timestamp records (type EXTRACT) for each modified record.
Delta Extract (the Extract Broker): A background process periodically polls for sy_timestamp EXTRACT records created since the last extract. It generates delta JSON files containing only changed records (including a delete flag for removed records) and uploads them to the Delta folder structure.
Authentication: Coins authenticates to OneLake using an Azure Service Principal (App Registration).
Upload: Files are uploaded using the Azure Data Lake Storage Gen2 REST API.
Licensing
Data Lake extracts are subject to the same licensing as the existing SQL extract solution:
X-EXINITROWS - Maximum rows for initial extracts
X-EXINITSIZE - Maximum data size for initial extracts
X-EXDATAROWS - Maximum rows for incremental/delta extracts
X-DATASIZE - Maximum data size for incremental/delta extracts
Setup - Microsoft Fabric / OneLake Side
Prerequisites
An active Microsoft Fabric subscription/capacity.
A Fabric Workspace created for your Coins data.
A Lakehouse created within that Workspace.
Create an Azure App Registration (Service Principal)
Coins authenticates to OneLake using a Service Principal.
Navigate to Azure Portal > Microsoft Entra ID > App registrations > New registration.
Enter a name (e.g., "Coins OneLake Extract").
Set the supported account type as appropriate for your tenant.
Click Register.
Note the Application (client) ID and Directory (tenant) ID.
Generate a Client Secret
In your App Registration, go to Certificates & secrets > Client secrets > New client secret.
Add a description and set an expiry period.
Click Add.
Copy the secret value immediately - it will not be shown again.
Note: Coins sends expiry notifications for Azure App secrets at 30 days, 7 days, and 1 day before expiry.
Enable Service Principal Authentication in Fabric Admin Portal
Navigate to Fabric Admin Portal > Tenant settings.
Under Developer settings, find "Service principals can use Fabric APIs.".
Enable this setting for the entire organisation or a specific security group.
Add Service Principal as Contributor to Fabric Workspace
Open your Fabric Workspace.
Go to Manage access (or Workspace settings > Permissions).
Add the Service Principal (using the App Registration name or Application ID).
Assign the Contributor role.
Obtain the OneLake base URL
The OneLake base URL follows this format:
This URL will be configured in the SY/EXONELAKEURL parameter in Coins.
Setup - Access Coins Side
Configure the Azure Application in Coins
Navigate to System > System Setup > Azure Applications and create a new record:
Field | Value |
App Code | Enter a code (e.g., ONELAKE) |
Tenant ID | Azure AD Tenant (Directory) ID |
Client ID | Application (Client) ID from App Registration |
Permission Type | Application |
Secret | Client Secret value |
Expiry Date | Match the secret expiry date |
Configure SY Parameters
Set the following system parameters via System > System Parameters:
Parameter | Value | Description |
SY/EXDATALAKE | OneLake | Enables Data Lake extracts. Leave blank to disable. |
SY/EXDATALAKEDIR | e.g., Bronze | Root directory in the Lakehouse for extract data. |
SY/EXDATALAKEPOLL | e.g., 3600 | Poll interval (seconds) for delta extracts. |
SY/EXONELAKEAPP | e.g., ONELAKE | Azure Application code from Step 5.1. |
SY/EXONELAKEURL | OneLake base URL from Step 4.6. |
Enable Data Lake on Extracts
Navigate to System > Extracts > Extracts Maintenance.
Open the relevant extract.
Tick the Data Lake checkbox.
Tick the Timestamp checkbox if delta extracts are required. If you want full periodic re-extract instead of relying on timestamps, you could schedule the Extract Data report to run on the desired frequency (e.g., daily).
Save the extract.
Run the Initial Extract
Navigate to System > Extracts > Extract Data.
Select the extracts you wish to initialise.
Choose the Extract to Data Lake action.
Run the extract.
Delta Extracts (Automatic)
Once the initial extract is complete and the Last Data Lake Extract timestamp is set, the Extract Broker will automatically begin pushing delta changes at the interval specified in SY/EXDATALAKEPOLL. After every delta extract the Last Data Lake Extract timestamp is advanced.
SY Parameters Reference
SY/EXDATALAKE
Whether to allow extracts to a data lake in JSON format.
Options: (blank) = Disabled, OneLake = Microsoft Fabric OneLake
SY/EXDATALAKEDIR
Root data lake directory for Coins extract data.
Example: Bronze. Init path: Bronze/Init/{table}/init.json. Delta path: Bronze/Delta/{table}/{YYYY}/{MM}/{DD}/delta_{timestamp}_{guid}.json
SY/EXDATALAKEPOLL
Poll interval for delta data lake extracts (seconds).
Example: 3600 = once per hour
SY/EXONELAKEAPP
Azure Application code for OneLake uploads.
Service principal should be Contributor on Fabric Workspace.
SY/EXONELAKEURL
Base OneLake API URL.
Initial Extracts
Process
System captures current timestamp before data generation (so changes during extract are not missed).
If previous initial extract exists, Init directory is deleted.
Dataset program generates full data set.
Data dumped into flat JSON files (arrays expanded).
JSON files uploaded to OneLake via REST API.
"Last Data Lake Extract" timestamp is stamped.
File Structure
{EXDATALAKEDIR}/Init/{table}/init.json
{EXDATALAKEDIR}/Init/{table}/init_0000001.json (if batched)
{EXDATALAKEDIR}/Init/{table}/{kco}/init.json (KCO-specific)
Files batched by EXTRACTS/%SYBULKLIM (default: 5000 records per file).
Example: JCGroup with SY/EXDATALAKEDIR = "Bronze"
Bronze/Init/JCGroup/init.json
Bronze/Init/JCGroup/init_0000001.json (if large)
Bronze/Init/JCGroup/001/init.json (KCO-specific)
Retry Logic
If upload fails, system retries up to six times with configurable pause (EXTRACTS/%SYRETRY, default 10s).
Delta (Incremental) Extracts
Conditions
Extract configured for Data Lake and Timestamps.
Extract not on hold.
Initial extract completed (Last Data Lake Extract is set).
Delta dataset exists.
Poll interval (SY/EXDATALAKEPOLL) has elapsed.
sy_timestamp EXTRACT records exist to process.
File Structure
{EXDATALAKEDIR}/Delta/{table}/{YYYY}/{MM}/{DD}/delta_{timestamp}_{guid}.json
Delta JSON Example
[
{
"kco": 1,
"jgr_group": "01A",
"jgr_desc": "Phase Group A - Updated",
"deleteFlag": false,
"tim_timestamp": "2025-07-15T14:28:00.000"
},
{
"kco": 1,
"jgr_group": "02B",
"deleteFlag": true,
"tim_timestamp": "2025-07-15T14:29:30.000"
}
]
Record 01A: modified (deleteFlag=false). Record 02B: deleted (deleteFlag=true).
Financial Period-Based Extracts
Some entities use period-based extraction (PERIOD, WEEK, DAY). Initial extracts iterate through periods and include the period date in the folder path.
E.g., Bronze/Init/APInvoice/{kco}/{period}/init.json
JSON Data Format
Data Lake uses a flat JSON format where arrays are expanded into separate numbered fields:
Standard JSON (via REST):
{ "jgr_glacct": ["4000", "4001", "", "", "", ""] }
Flat JSON (Data Lake):
{ "jgr_glacct__1": "4000", "jgr_glacct__2": "4001", ... "jgr_glacct__6": "" }
Compatibility with Existing SQL Extract Solution
The SQL extract solution remains fully supported. Data Lake is an additional destination.
Both can run simultaneously - the Extract Broker processes CDC to SQL and timestamps to Data Lake in the same cycle.
Migrating from SQL Azure: you do NOT need to set up extracts from scratch. Enable the Data Lake checkbox, recompile, and run the initial extract.
Frequently Asked Questions
Q1: Multi-tenancy for different KCOs (different Azure tenants)?
Current architecture supports a single OneLake destination per Coins environment. KCO-specific data is separated into subdirectories. Different Azure tenants per KCO is not currently supported.
Q2: Reconciling data between Coins and Fabric bronze layer?
SQL extracts have a Reconcile SQL Record Count option. For Data Lake extracts, there is no equivalent built-in reconciliation currently.
Q3: JSON files in separate folders for each entity?
Yes. Init/{entity}/ for initial data. Delta/{entity}/{YYYY}/{MM}/{DD}/ for deltas. Each delta run produces a separate file with unique timestamp and GUID.
N.B.: Re-running the initial extract would clear the Init/{entity} directory and re-upload from scratch.
Q4: Handling fully deleted records?
Deleted records captured via CDC timestamp mechanism. Delta dataset includes deleteFlag: true for records that were deleted.
Q5: Automated handshake on successful loading?
No automated handshake/verification mechanism currently. The system provides: retry logic (up to six times), logging, timestamp tracking (only advanced after successful upload), email notifications for errors.
Q6: Report output to OneLake?
Currently there is no option. Data Extracts only. Report output to OneLake is a planned future feature.
Q7: What does the REST checkbox do?
Creates a REST API dataset endpoint for the extract (pull-based, external systems query on demand). Also creates the delta dataset program used by Data Lake deltas. Distinct from Data Lake (push-based). Both can be enabled simultaneously.
Q8: Need to set up all extracts again for Fabric?
No. Existing extract definitions preserved. Enable Data Lake checkbox, recompile, configure SY parameters, run initial extract. Existing SQL extracts continue as before.
Q9: Conflict between CDC/SQL extracts and Data Lake extracts?
No conflict. CDC-based SQL extracts and timestamp-based Data Lake extracts operate independently within the same Extract Broker. Data effectively extracted twice - once to SQL, once to OneLake.