Data Integration Project
I have an Azure SQL Database which is functioning as a Data Warehouse. I have an ERP that feeds data into the DW via Data Factory. The ERP is a SQL Server 2019 VM. Our reporting environment is Power BI Semantic Models with Power BI Reports placed on top of them.
Due to contract requirements, we are looking at turning up a 2nd ERP (new SQL Server 2019 VM) from the same vendor and running them in parallel for up to three years. Same tables, same columns, different business rules.
I have around 112 tables that I'm pulling into the DW from the 1st ERP. I need the same 112 tables included into the DW from the 2nd ERP, and then combine the now 224 tables back into 112 tables that gives the same look / smell / feel as the original 112.
There is no guarantee that there wont be PK / FK overlap between the two systems.
My current thought process is:
Step 1) Duplicate current database code in the existing ERP schema and deploy it to the ERP2 schema
Step 2) Duplicate the data factory, point it at new ERP. Have it point to ERP2 Schema for destination.
Step 3) Combine both ERP1 and ERP2 into a new DW Schema, renumbering all PK / FK references during integration
Step 4) Migrate data models to new DW schema.
Is there a way to automate step 3, or am I looking at a lot of coding time? Is there a better method?
This is a unique vendor with a unique application that is not commonly used on the market.