This article is a requirements document template for an integration (also known as Extract-Transform-Load) project, based on my experience as an SSIS developer over the years. Freelance Microsoft SQL Server Database developer and artchitect specializing in Business Intelligence, ETL, and Dashboard reporting solutions. Data mapping has been a common business function for some time, but as the amount of data and sources increase, the process of data mapping has become more complex, requiring automated tools to make it feasible for large data sets. Data mapping is the key to data management. Data mapping is an essential part of many data management processes. Downloading an Excel Template (Mapping Template) In Data Load Mapping using the import feature, you can select and import an Excel mapping, and specify whether to merge or replace the mappings. The mapping template also includes a macro script that pulls Oracle Hyperion Financial Management dimensions directly from the target application to. Mappings, Rules and Patterns in Template Based ETL Construction Kalle TOMINGASa, Margus KLIIMASKb and Tanel TAMMETa a Tallinn University of Technology, Ehitajate tee 5, Tallinn 19086 Estonia b Eliko Competence Center, Teaduspargi 6/2, Tallinn 12618 Estonia Abstract. Data mapping has been a common business function for some time, but as the amount of data and sources increase, the process of data mapping has become more complex, requiring automated tools to make it feasible for large data sets. Data mapping is the key to data management. Data mapping is an essential part of many data management processes.
In Data Load Mapping using the import feature, you can select and import an Excel mapping, and specify whether to merge or replace the mappings. Excel mapping templates with correct formatting are included in the EPM_ORACLE_HOME/products/FinancialDataQuality/templates directory.
The mapping template also includes a macro script that pulls Oracle Hyperion Financial Management dimensions directly from the target application to which you are connecting.
You must upload the Excel template to the Oracle Hyperion Financial Data Quality Management, Enterprise Edition server, and then pick the excel file as the file to load in the data load rule, or when prompted by the system if the file name is left blank. The system determines if the file being processed is an Excel file, and then reads the required formatting to load the file.
When working with a mapping template in Excel:
Do not have any blank lines in the map template.
You can insert lines in the template, but you must keep new lines in the designated area.
Each template supports a single dimension.
- ETL Testing Tutorial
- ETL Testing Useful Resources
- Selected Reading
Etl Mapping Specification Template
ETL Test Scenarios are used to validate an ETL Testing Process. The following table explains some of the most common scenarios and test-cases that are used by ETL testers.
Etl Data Mapping Template
| Test Scenarios | Test-Cases |
|---|---|
Structure Validation | It involves validating the source and the target table structure as per the mapping document. Data type should be validated in the source and the target systems. The length of data types in the source and the target system should be same. Data field types and their format should be same in the source and the target system. Validating the column names in the target system. |
Validating Mapping document | It involves validating the mapping document to ensure all the information has been provided. The mapping document should have change log, maintain data types, length, transformation rules, etc. |
Validate Constraints | It involves validating the constraints and ensuring that they are applied on the expected tables. |
Data Consistency check | It involves checking the misuse of integrity constraints like Foreign Key. The length and data type of an attribute may vary in different tables, though their definition remains same at the semantic layer. |
Data Completeness Validation | It involves checking if all the data is loaded to the target system from the source system. Counting the number of records in the source and the target systems. Boundary value analysis. Validating the unique values of primary keys. |
Data Correctness Validation | It involves validating the values of data in the target system. Misspelled or inaccurate data is found in table. Null, Not Unique data is stored when you disable integrity constraint at the time of import. |
Data Transform validation | It involves creating a spreadsheet of scenarios for input values and expected results and then validating with end-users. Validating parent-child relationship in the data by creating scenarios. Using data profiling to compare the range of values in each field. Validating if the data types in the warehouse are same as mentioned in the data model. |
Data Quality Validation | It involves performing number check, date check, precision check, data check, Null check, etc. Example − Date format should be same for all the values. |
Null Validation | It involves checking the Null values where Not Null is mentioned for that field. |
Duplicate Validation | It involves validating duplicate values in the target system when data is coming from multiple columns from the source system. Validating primary keys and other columns if there is any duplicate values as per the business requirement. |
Date Validation check | Validating date field for various actions performed in ETL process. Common test-cases to perform Date validation −
|
Full Data Validation Minus Query | It involves validating full data set in the source and the target tables by using minus query.
|
Other Test Scenarios | Other Test scenarios can be to verify that the extraction process did not extract duplicate data from the source system. The testing team will maintain a list of SQL statements that are run to validate that no duplicate data have been extracted from the source systems. |
Data Cleaning | Unwanted data should be removed before loading the data to the staging area. |