Question

Q 2.After completing source system analysis, a source-to-targetspecifications (or

source-to-target data map) can facilitatedevelopers to create a quality ETL logic.

You arerequired to draw a source-to-target data map using Kimballmethod

template for any sampledata.

Answer 1

Dear,

Kimball’ssubsystemsdescribe the things any ETLstrategy must have. we categorized the subsystems of ETL into 6 keycomponents. These 6categories included:

1.Design and data profiling

2.Source data extraction

3.Transformation and loading

4.Change data capture

5.Quality review, auditing and exceptionhandling/management

6.Integration with the production environment and businessprocess components

Source to target mapping: It contains transformationspecifications for each BI columns including instructions for datacleansing, RI checking,reconciliation and error handling, as wellas algorithms for aggregations andsummarizations.Source-to-target mapping document has beencompleted,the ETL lead developer, with the help of databaseadministrator and the data quality analyst, must design the ETLprocess flow.

1. Design and data profiling: Data profiling is a method ofassessing source data in a systematic and analytical way. The goalof data profiling is tobuild an exhaustive inventory detailing thecontent, context, and quality of source data. It entails much morethan reviewing a diagram or running a fewSQL statements. Dataprofiling leads to better data integration, which leads to betterdata quality.Column property analysis including discoveryofinferred domains, and structure analysis including candidateforeign key primary relationships, data rule analysis, and valuerule analysis.

2. Source data extraction: source data adapters,push/pull/dribble job schedulers, filtering and sorting at thesource, proprietary data formatconversions, and data staging aftertransfer to ETL environment. Operational interdependencies amongseveral source files and source databases from whichdata is beingextracted. Extract system. Source data adapters, push/pull/dribblejob schedulers, filtering and sorting at the source, proprietarydataformat conversions, and data staging after transfer to ETLenvironment.

3. Transformation and loading: Compare row counts from thebeginning and end of the transformation process to make suredidn’t drop any rows, you mayalso want to sum the salesactivity for the day, for comparison to expected value. Applysimple test immediately after the extract and transformationsteps,before loading the data into the data warehouse database.Transformations specificto a target database, such asaggregation and summarizationfor a specific data mart, shouldoccur toward the end of the ETL process flow. Kimball’s work,he spends a considerable amount of time and effort makingsure weunderstand the role of the transformation and loading parts of ETLis Data conformer, Surrogate key creation system, Fixed hierarchydimensionbuilder, Variable hierarchy dimension builder,Multivalued dimension bridge table builder, Junk dimension builder,Transaction grain fact table loader,Periodic snapshot grain facttable loader.

4. Change data capture: Source log file readers, source data andsequence number filters, and CRC based record comparison in ETLsystem. Slowly ChangingDimension (SCD) processor. Transformationlogic for handling three types of time variance possible for adimension attribute: Type 1 (overwrite), Type 2(create newrecord), and Type 3 (create new field).Late arriving dimensionhandler. Insertion and update logic for dimension changes that havebeendelayed in arriving at the data warehouse.

5. Quality review, auditing and exception handling/management:Data cleansing system. Typically a dictionary driven system forcomplete parsing of namesand addresses of individuals andorganizations, possibly also products or locations."De-duplication" including identification and removal usuallyofindividuals and organizations, possibly products or locations.Often uses fuzzy logic. "Surviving" using specialized data mergelogic that preservesspecified fields from certain sources to bethe final saved versions. Maintains back references (such asnatural keys) to all participating originalsources.

6. Integration with the production environment and businessprocess components: For ETL strategies, kimball identified fewprocesses to be consistentwith technology offerings: Jobscheduler, Workflow monitor, recovery and restart system,parallelizing/pipelining system, Problem escalation system,Versioncontrol system, Version migration system, Lineage and dependencyanalyzer, Compilance reporter, Security system, Backup system,Metadatarepository manager, and Project management system.