Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2013-07-25 : 09:13:59
|
| Hi guys,At my work we build out what we call "ETL Maps". These are basically excel documents, where each tab describes the business rules necessary for processing a specific data feed. It includes all the columns of the original data feed, any new columns that should be created during processing, as well as any other transformations/calculations that need to happen, reference tables that need to be used, and dimension/fact table each column will end up in.This ETL Map document serves as a blueprint that is followed for building out SSIS packages for data processing. And after SSIS implementation it also serves as a methodology document to show anyone the lineage/methodology that was implemented in SSIS.I am curious to know if anyone else had a similar experience and how they documented the business rules/requirements that drive SSIS implementations. Our process is constantly evolving as we recently started thinking about how we can store all this information in a database (instead of excel) and then use it to drive automatic generation of SSIS code - potentially utilizing tools like BIML.Looking forward to replies.Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-26 : 02:22:31
|
| We also use a similar document. Its called Data Mapping document in our case. It consists of mapping between source fields against the destination table columns with details on source (can be from multiple sources like file,XML,another RDBMS etc). In addition it has details on column metadata, transformation rules, profiling results(presence of NULLs, special patterns etc). This document is used as a basis for developing SSIS dataflow mappings and also tranformations.In case you need flexibility of storing this in a db, what you could do is put these details onto a sql table with rules for column defined using Regular Expression patterns, then inside SSIS fetch and parse the incoming data against patterns to identify violations.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2013-07-26 : 09:12:39
|
| visakh16 - thank you for your input.What you describe is very similar to what we have. Your suggestion for storing rules using Regular Expression patterns would help in data validation.I was thinking to take it a step forward and drive the creation of the SQL Tables as well as SSIS Packages themselves by using this document stored in a database in conjunction with BIML (Business Intelligence Markup Language). This could save hours and hours of development time if the initial time is allotted to set this up.Have you ever considered that?Thank you! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-26 : 11:19:26
|
| I've not had chance to play with BIML so far. Have to try if i get sometime.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
qoofy
Starting Member
1 Post |
Posted - 2015-05-06 : 23:57:37
|
| Hi there,we often found data professionals frustrated choosing the following when it comes to data mapping exercise:1) Excel/Word (unscalable - version control, scattered copies, silo, discourage of collaboration, etc.); or 2) Heavy & costly tool-set (big learning curve and migration execution focused)There is a light-weight and collaborative tool for ETL data mapping documentation at https://qoofy.comQoofy is a web-based tool to document, manage and share ETL data mapping rules and data migration rules with peers collaboratively. The tool is particularly helpful and valuable in a cross-functional team environment.https://qoofy.com |
|
|
|
|
|
|
|
|
ETL Maps