Recently, my company moved to a new ERP system. We have approximately 5 years of legacy data as well as 6 months of data in the new ERP system. I’m in the planning phase of combining the two data sets into one Power BI Data Model.
While transitioning, we created mappings for our dimension tables so that dimensions such as Vendors, Manufacturers, and Customers were migrated as-is. We have mapping tables for each dimension with the old ID and the new ID.
Here’s my plan:
- I will leave the old (static) data in its own database.
- I will query the legacy database in Power BI, query the new ERP system, and combine the queries via append.
- The only tables I want to bring in from the legacy system are the Fact Tables. I would like all the dimension ID’s in the legacy fact tables to be updated to their new dimension ID in the new system.
- I will also query the dimension mapping tables, so I can map each legacy dimension ID to a new dimension ID via LOOKUPVALUE()
Essentially what I need to do is query a legacy fact table, use LOOKUPVALUE() against the dimension ID maps to pull in the new dimension ID, and remove the legacy dimension ID column. Now I have a legacy Fact record that correctly references new Dim tables.
So long as each legacy dimension id has one corresponding new ID, I think this method should work. A legacy Sales Order record should reference existing dimensions from the new system. I can use queries to create a single combined Fact table and throw out the legacy Dim tables.
Does this sound right? From the bit of thought I have put into this, it seems to be one of the simplest methods with the least moving parts, so long as my mapping tables are complete.
Let me know if you have questions or if my thoughts above are not clear.
Any suggestions or resources for this transition would be appreciated.