Forum Discussion
Merging Duplicate Datasets
I'm not sure how different the table structures between AX and D365 are, nor what the data volumes are you are looking at.
I would extract the different D365 sites into a reporting database as well as the AX sites, keeping the load on the source database as limited as possible (and possibly using some incremental loading - reload last x days). Using views you can then align these schema and load each into the semantic model. You can choose to union those tables in PowerBi Query editor (or even with a view in the reporting database) and report on them.
In this case I wouldn't opt for 3 semantic models, as you'll only report on 1 of them and add complexity without a specific benefit (at first sight).
Refreshing a powerbi semantic model by ingesting all data from all AX and D365 sites might be a big strain on the operational system, especially because all that data has already been read the day before (with exception of the new/modified data).
The views can also allow you to implement a filter at the cut-over date when migrating from AX to D365, so that accidental late entries are not considered.
- monserob1 year agoFrequent Visitor
Hi, thank you for responding. The AX data is in two on-premise databases and the D365 data as it comes online will be in two azure databases so no impact on source systems. There's no appetite to bring and maintain the AX data in Azure.