Business central db exported tables structure is not same with exported tables using dataflows gen2

[pavannarani]

Hi Everyone,

I have a requirement to go create a datawarehouse solution in fabric warehouse for Business central database. I went through few articles and found dataflows gen2 (Dynamics 365 Business Central ) connector is the only way to read data from business central database with my user login credentials am able to fetch the data and load them into warehouse till now it is fine.

But to implement the solution i felt it would be better to have business central database export first as it might contain some additional tables so i extracted the bacpac file from Business central admin center and loaded into azure storage account and imported to azure sql database to verify the structure. I thought structure would be same for API generating table data and database exported data.

For example, i considered here sales invoices table to compare the structure 

1. Sales Invoices table data extracted from dataflows gen2 bc connector and if you notice here in below screenshot it had total of 55 columns with 221 row count as shown below

2. After import i opened the same table in azure db from bacpac file, here i noticed few things table names are coming with concatenate (schema, company, tablename, tenantid) i believe row count is matching but the structure is completely different here i searched with all the columns from warehouse here few columns are present few are not and even some extra columns exists which i couldnt able to figure out

Even the table name is different in bacpac exported data, Did anyone faced this issue earlier and how to handle this situation as its structure is different.

so here my worry is to setup the warehouse solution and to extract the data. Should i use Dataflows Gen2 approach only to built the datawarehouse and later i am planning to use BC REST APIs to do incremental refresh using datapipelines. It seems i cant use exported data from bacpac file to built fabric warehouse.

Can anyone suggest me the approach to extract the bulk data once and later i want to do incremental refresh and also 

Update if existing records are changed and Insert if new records are loaded into Business central Database. Please suggest me the approach to follow to setup the warehouse i want to use Fabric warehouse only for reporting purpose.

Appreciate the help, Thanks 

[v-zhangtin-msft]

Hi, @pavannarani 

Please check out the following link hope it helps.

Fabric decision guide - copy activity, dataflow, or Spark - Microsoft Fabric | Microsoft Learn

Incrementally load data from Data Warehouse to Lakehouse - Microsoft Fabric | Microsoft Learn

Pattern to incrementally amass data with Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.