BC2ADLS: MS Fabric enabled part 2
In part two we are looking at the Fabric side of the integration with Business Central and bc2adls extension.
Part one you can read here:
BC2ADLS: MS Fabric enabled part 1 – Discover Microsoft Business Central (bertverbeek.nl)
With the export to Fabric all the delta files are in place and must be consolidated into a new table or an excisting table.
That is done through a notebook script in Python in three steps. But first the settings:
In this case we have to set some parameters.
The first to parameters are the standard folders that doesn’t have to change.
The Workspace parameter you can fill in your workspace name or the GUID of it. That is also the same as the lakehouse parameter.
For the Remove_delta if you set it to True then all your delta files will be removed. In a production environment please put this always to True. Otherwise the step of removing duplicates will take a long time.
From CSV files to Dataframe
First we have to pick up all the CSV files from the directory and load it into a dataframe:
After that is loaded we are changing all the column types based on the manifest file we have exported from Business Central. In that way the column types are the same as in Business Central:
Delete the deleted records
When a record is deleted in Business Central it will get an entry in the table “ADLSE Deleted Record”. Those records will also be exported but with a field “SystemCreatedAt” that is blank. In this way we can filter on it in a new dataframe and do a join with our current dataframe:
A ‘left anti join’ returns only the rows from the left dataframe (df_new in this case) which do not have a matching key in the right dataframe.
Overwrite the changed records
Then the last action (before importing it into a lakehouse table is to overwrite the changed records in Business Central.
In that case we are sorting the dataset on systemId and SystemModifiedAt. After that we drop the duplicated.
With the function dropDuplicates it doesn’t drop the first record. But all other records will be dropped:
Now we are done and can override the dataframe with the data in the excisting table.
Schedule the notebook
You can also schedule the notebook. In this way you don’t have to create a pipeline and schedule that:
You can select the Repeat option. The start en end day is optional:
Access your date in PowerBI
In PowerBI desktop you can choose get Data in you can see there also Microsoft Fabric:
After that you can select your lakehouse:
And your Business Central data is in your PowerBI:
Because the data is already in OneLake and in a lakehouse with delta tables it can use Direct Lake. So it doesn’t have to load all your data into a dataset (like data from an Azure Data Lake) and present it in Power BI:
Learn about Direct Lake in Power BI and Microsoft Fabric – Power BI | Microsoft Learn
Alls the code of the notebook you can find in this branche:
bc2adls/fabric/CopyBusinessCentral.ipynb at Microsoft-Fabric-Integration · Bertverbeek4PS/bc2adls (github.com)
8 COMMENTS