Data flow mapping - ETL

  • chess-288252

    Old Hand

    Points: 393

    Hi,

    I was wondering what do you guys use to map the data flow in ETL. I am looking at the field level flow of data. By this, I mean a document that can be passed on to the ETL developers which maps source table.column to destination table.column and the transformation logic between source and destination. This is also handy on the reporting side to map out the fields and logic in report.

    Currently we use a mix of excel and word to map this. I was wondering whether there is a better way/tool to do this?

    Thanks in advance 🙂

  • robert.gerald.taylor

    SSChampion

    Points: 13130

    chess-288252 (2/25/2013)


    Hi,

    I was wondering what do you guys use to map the data flow in ETL. I am looking at the field level flow of data. By this, I mean a document that can be passed on to the ETL developers which maps source table.column to destination table.column and the transformation logic between source and destination. This is also handy on the reporting side to map out the fields and logic in report.

    Currently we use a mix of excel and word to map this. I was wondering whether there is a better way/tool to do this?

    On Ralph Kimball's website, there is a MS Excel spreadsheet under the tools/resources for the 'Microsoft Data Warehouse Toolkit' book that we use. It's probably pretty similar to what you've developed.

    http://www.kimballgroup.com/wp-content/uploads/2012/07/Ch02_MDWToolkit_Datamodel_Spreadsheet_4.0_2008R2.zip

    HTH,

    Rob

  • chess-288252

    Old Hand

    Points: 393

    It is quite similar to what we are using now but I can certainly pick up some bits from this and put it in our template.

    Looks like excel is the tool for this! I was hoping there would be some other tool custom built for this.

    Thanks for the info.

  • sam.benedict

    Valued Member

    Points: 61

    Excel is a popular choice because there have not been many alternatives if any - you need to have a look at Analytixds.com. A purpose built solution for mapping at an enterprise level, that allows you to generate SSIS packages from the mappings... a ton of other features that are miles ahead of excel, and robust metadata features as well (impact analysis, lineage, truncation alerts driven off the mappings). Have a look. Excel is only the answer if you don't know about this tool - there is a wealth of information on the site, and you can get an evaluation license for free.

    Sam Benedict, VP Strategic Accounts
    Analytix Data Services - Mapping, Code-gen Platform for SSIS/All ETL Brands
    sam.benedict@analytixds.com
    www.analytixds.com

  • tanitelle

    SSC Veteran

    Points: 223

    I think you are looking for a Source To Target (STT matrix) solution.

    Several vendors (include some open-source) delivers this concept for ETL documenting and forward engineering.

    Give it a search.

  • Nexana

    SSC Enthusiast

    Points: 139

    It's 2019, and still couldn't find a tool for Source to Target mapping document generator. ERwin DM can easily do that, but $5k per user isn't feasible.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply