logo for information-management-architect.com
leftimage for information-management-architect.com

Database Mapping

Improve architecture and design process with David Bowman’s information management guidelines for database mapping

This site is designed for information technology professionals and information management consultants who want to learn more about data integration source to target mapping.

It provides information management guidelines for source to target mapping and a database mapping template that can help jump-start a project.

What Source to Target Mapping?

Databse mapping is a key component of a data warehouse requirements specification which specifies business transformation rules that should be applied to source data.

Requirements Analysis
  • Should deliver a logical data model, which confirms required structure and design;
  • Should deliver a data integration logical data flow diagram;
  • Should deliver interface requirements by identifying any existing or new interfaces needed to support the data requirements;
  • Should deliver data profiles  documenting key quality issues that will need to be handled; and
  • Should deliver a high level source to target map, or database  mapping document.
Source to Target Map
  • Should specify source input definition;
  • Should specify target output details;
  • Should specify business and data transformation rules;
  • Should specify absolute data quality requirements;
  • Should specify optional data quality requirements;
  • Should be completed during requirements analysis;
  • Should be a living document that is used as input to architecture and design; and
  • Should capture business rules, data flow mapping and data movement requirements.
Source Input
  • Should specify the requirement id that is the basis for the mapping to help ensure that we are not mapping data that is not required;
  • Should specify the interface id, or input file number, that is the basis for the mapping e.g. this is a direct reference to the interface id on the data movement model and helps ensure that we have not missed any interfaces;
  • Should specify source input file name, which identifies the interface name;
  • Should specify input field name, which identifies the interface column name or field name;
  • Should specify data type and field length, which is  the technical description of the column or field name e.g. Numeric(4), Char(25), Varchar(16) Date; and
  • Should specify field format, which is required for date and numeric fields e.g. ddmmyyyy, dd/mm/yy, mon-yy, 9999.99, 99999.
Target Output
  • Should specify the target entity number (each entity on the logical data model should be identified with a unique number);
  • Should specify entity name;
  • Should specify attribute name;
  • Should specify attribute definition, which should be the same definition that is documented in the logical data model and should also be included in the metadata repository;
  • Should specify attribute data type;
  • Should specify attribute format, which is required for date and numeric fields e.g. ddmmyyyy, dd/mm/yy, mon-yy, 9999.99, 99999; and
  • Should specify if nulls are allowed for this attribute.
Transform
  • Should specify mapping transformations or business rules e.g.  "straight mapping", "sysdate + 1", "if source value is null then set to 7", "If value is 999999999 populate field with 01/01/2000";
  • Should specify how changes to this attribute should be handled e.g. should the data be overwritten, a new record be created, or should a current record value be updated; and
  • Should specify referential integrity considerations e.g. key value must exist in key column of table xxx, do not populate if column s is null.
Absolute Data Quality
  • Should specify upper value limit e.g. the maximum allowable value for the target;
  • Should specify lower value limit e.g. the minimum allowable value for the target; and
  • Should specify valid values list e.g. a list of valid values for the target.
Optional Data Quality
  • Should specify valid value look-up, e.g. if a lookup file or table is used to validate the values for this target, specify the source of this lookup and values not contained in this file or table will be rejected;
  • Should specify default value e.g. if nulls are not allowed and source field is null this should specify what default value should be substituted to prevent this record from rejecting; and
  • Should record questions or concerns that should all be addressed before the requirements specification is complete.
Source to Target Mapping Template

     Under Construction  

Summary…

Source to target mapping is a key component of a data warehouse requirements specification which specifies business transformation rules that should be applied to source data.

This site provided information management guidelines for source to target mapping and a database mapping template that can help jump-start a project.