Problems with incremental load and multiple refreshes

Question

We are a mid size company foraying into setting up a Data lake and a data warehouse. ERP is D365 and we use Azure as our platform. Presently we are extracting the data from D365 every night and loading the lake and the warehouse. This is beset with problems. One, the incremental refresh throws up an error and we have to do a full load by wiping out the data and loading it. Secondly, multiple refreshes pose a problem and we can do only 1 refresh per day.

So wondering if we can go directly from D365 to a SQL database with our extracts instead of first going to the Data Lake. Why not ETL the data directly into SQL tables where we have much better tools for replication including incrementals? Please advise.

Answer 1

Hello @Rumba and welcome to Microsoft Q&A.

Could you please tell us a little more about your situation? What error message are you getting, and is it coming from Data Lake, or D365 or some other service?

I would expect loading directly to a SQL database would be slower and more expensive than to Data Lake or Data Warehouse.

Might I suggest looking into Azure Synapse? Azure Synapse workspace bring the confluences of Data Orchestration (Azure Data Factory) , Azure Data Lake, Data Warehouse (dedicated SQL pools), Database, Analytics (Spark, PySpark,, etc.), on-demand serveless SQL, and many more tools into one place. More is always being added. There is a connector for D365.

By bringing all into one place, you can try out various setups and find what combination works best for you, without having to start from scratch each time.

Answer 2

Thanks Martin. Microsoft supports a functionality for data loads from D365 to the Lake called “Trickle Feed”. The issue is that the Trickle Feed function is not working and apparently has been stuck in Public Preview for quite some time. We have architected our data Lake/ Warehouse environment around Azure, and the lack of getting the incrementals done is causing us to have to do wipe the old data and copy the entire data to our data lake every day. The problem is this takes couple of hours or so to run. The second issue is the inordinate time (another 1 to 2 hours) it takes for the load to run from the Data Warehouse to the PowerBI datasets.