I am trying to make a small application to help our BAs with the data mapping they are working on. There are at least 5 different systems, with likely more than 10 eventually that all flow into each other. The first design that came into my head was a table for each source system with the table/field and a unique identifier. Then a mapping table that linked (Field1/Source1 to Field1/Source2). the problem i'm running into is how to effectively avoid any circular loops. I think a generic load will be the best way to attempt this; however, any given source/field could be on the left and/or right side of the mapping (the from and/or to side).
Any suggestions/thoughts would be greatly appreciated.
I think that what you are trying to do may be too complex due to the problem of looping without a fair amount of analysis before loading. One useful way to proceed (if data is similar) is to sort out the fact tables from the reference tables, and then append the fact tables together......
So Fact1 and Fact2 will be concatenated, BUT importantly you should use a field that you can set to reference the type of data in each Fact Table. So Fact1 may be costs and Fact2 may be sales, so I would concatenate them together and then set a field "DataType" equal to "Costs" for Fact1 and "Sales" for Fact2. This means that you can see all data together but can also separate data by Data Type with the use of a List Bar.
For each of the Data Types, they do not have to have the same numeric fields or reference fields but the closer they are to each other the better.
If we have a link in Fact1 and Fact2 to the Location table, this will work with no loops as there will only be 1 link and a star schema.
I think I understand what you are suggesting. I am not mapping/comparing the data in the tables/sources, but the metadata of the tables/sources themselves. i.e. The Cusip field from the reporting database maps is fed from the cusip field in the data warehouse, maps to the cusip9 field in the accounting system maps to security_id (where security_id_type=cusip) from the trading system etc...
Yeah, that's why I was hoping the right data structure would allow me to do a generic load, that way everything would be linked to everything else with no central fact table. Just haven't been able to figure out what that should be.