Merging Duplicate Datasets

[monserob]

Hi, I'm involved in an AX to D365 migration project. The company has a number of sites and they are going to be migrated one by one. Some business users need to see data across all sites, which means until everyone has migrated I need to build some reports that bring together both the AX and D365 data. What are my options here? Initially I was thinking that I could bring the D365 data into the existing report and then merge each D365 query in with the existing equivalent AX query. Is this my best option? Would I be better building a separate semantic model for D365 and then combine the two semantic models into a third "composite" semantic model. Any suggestions would be greatly appreciated!

[mattiasdesmet]

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.

[monserob]

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.