4 Replies Latest reply: Apr 28, 2014 10:16 AM by Mark campbell RSS

    Data Mapping Application (data structure)

      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.

        • Re: Data Mapping Application (data structure)
          Rupert Cavendish

          Mark,

           

          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 hope that this helps and makes sense