model driven etl-design - driving the test process ¾conclusions. 4 - model driven etl design...

Atos, Atos and fish symbol, Atos Origin and fish symbol, Atos Consulting, and the fish symbol itself are registered trademarks of Atos Origin SA.© 2006 Atos Origin. Private for the client. This report or any part of it, may not be copied, circulated, quoted without prior written approval from Atos Origin or the client.

Model driven ETL-designImproving ETL Development by structuring logical design

2 - Model Driven ETL Design

Mark Zwijsen

Senior Consultant Business Intelligence and Data Warehousing

25 years IT-experience

Industries:Telecom, Public Sector, Retail, Transport

Customers include:KPN, Dutch Government, KLM, Unilever

Publications in dutch IT-magazines

Agenda

The problem area

Presentation of the proposed solution

The solution explained by examples

The step from logical to technical

Case tools and code generation

Driving the test process

Conclusions

Agenda

The problem area

Conclusions

Problem Area

Average datawarehouse project :100+ tablesOften as many or even many more ETL-programs

CauseA separate ETL-program is constructed per target tableOften more than 1 ETL-program for a target (e.g. when table is fed from more sources)

Datawarehouse is subject to change

ResultFrequent changes to ETL-programsThus huge design- and development cost

An efficient design and development method can result in significant cost saving

Levels in Design and Development

Logical

Technical

SourceData

TargetData

DataMapping

ERD ERD

PhysicalData Model

ETLProgram

In data modelling, the logical level is aimed towards structure, meaning, interdependence of information, without any notice of the technical details, whichcan differ from platform to platform

For ETL, designs often are a mixture of functional and technical specifications

This is caused by the absence of a formal ETL design language, and by the common practice in projects : ‘build first, document afterwards’

Quality = Sustaining Usability

Mainten

ance Transition

CompleteAccurateConsistentEfficientAccessible

PortableReusableIntegratable

StructuredUnderstandableFlexibleTestableChangeable

There is a need for high-quality logical ETL designs

Quality Issues for ETL Designs

UsageComplete, Accurate, Consistent, Efficient, Accessible• The objective is to BUILD WITHOUT FURTHER QUESTIONS

MaintenanceStructured, Understandable, Flexible, Testable, Changeable• The objective is to ALLOW FOR EASY ALTERATION

TransitionPortable, Reusable, Integratable• The objective is to BE PLATFORM/TOOL INDEPENDENT

Mapping on logical level: horizontal split

Logical

SourceData

TargetData

DataMapping

ERD Normalizedmodel

Star scheme+history

Generally, the mapping from source to target can globally be split in 2 phases

The first phase comprises the ‘hard part’of transforming from the source to the target language (the actual extracting and transforming steps)

The second phase comprises the ‘easy part’: handling history, aggregation, denormalization (the actual loading step)

Second Phase: Normalized Model to Target Model is relatively straighforward

DIM_CUSTOMER

CUSTOMER_KEYCUSTOMER_NUMBERCUSTOMER_NAMECUSTOMER_TYPECUSTOMER_IDENTIFICATIONCUSTOMER_COCNRCUSTOMER_ZIPCODECUSTOMER_DEMOGRAPHYCUSTOMER_SECTORVALID_FROMVALID_UNTIL

Base Object(s) of DimensionSCD Type per object/attribute

Usage of generated dimensionkeyType of validity-indicators

TBL_CUSTOMER

CUSTOMER_NUMBERCOCNRZIPCODECUSTOMER_NAMECUSTOMER_IDENTIFICATIONCUSTOMER_TYPE

TBL_COCDATA

COCNRCOC_NAMESECTOR_CODE

TBL_DEMOGRAPHY

ZIPCODEDEMOGRAPHY_TYPE

First phase: Source Model to Normalized Model, is the challenging part

Detect/Determine validityFilter irrelevant data

Standardize codes, names, etc.Merge multiple row-records

Join disparate recordsEliminate irrelevant levels

Clean dirty dataSplit recurring fields

DeduplicateResolve self-references

Etc.Etc.

TBL_CUSTOMER

TBL_COCDATA

TBL_DEMOGRAPHY

CUSTOMER

CUSTOMER_NRKVKNUMMERADDRESS_NRNAMEPRIVATE_BUSINESS_FLAGIDENTIFICATIONSHIPTO_ADDRESS_NRTIMESTAMP

ADDRESS

ADDRESS_NRVALID_FROMCUSTOMER_NRSTREETHOUSE_NRHOUSE_NR_EXTPOSTCODECITYTIMESTAMP

COMPANYREGISTER

KVKNUMMERNAAMBRANCHE_CODEOMVANG

DEMOGRAFIE

POSTCODEDEMOGRAFIE_TYPE

Detect/Determine validityFilter irrelevant data

Standardize codes, names, etc.Merge multiple row-records

Join disparate recordsEliminate irrelevant levels

Clean dirty dataSplit recurring fields

DeduplicateResolve self-references

Etc.Etc.

Most ETL-programs move Source Model to Target Model in one flow

Base Object(s) of DimensionSCD Type per object/attribute

Usage of generated dimensionkeyType of validity-indicators

CUSTOMER

CUSTOMER_NRKVKNUMMERADDRESS_NRNAMEPRIVATE_BUSINESS_FLAGIDENTIFICATIONSHIPTO_ADDRESS_NRTIMESTAMP

ADDRESS

ADDRESS_NRVALID_FROMCUSTOMER_NRSTREETHOUSE_NRHOUSE_NR_EXTPOSTCODECITYTIMESTAMP

COMPANYREGISTER

KVKNUMMERNAAMBRANCHE_CODEOMVANG

DEMOGRAFIE

POSTCODEDEMOGRAFIE_TYPE

TBL_CUSTOMER

TBL_COCDATA

TBL_DEMOGRAPHY

DIM_CUSTOMER

Agenda

The problem area

Conclusions

Standard Mapping Model

Collect Enrich Filter Validate Convert

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enrichmentrules

Historyrules

HistoryLink

Linkrules

Consists of 8 specific logical transformation steps

The logical transformation steps can begrouped according to their orientationtowards either source or target model

Sourcemodel orientedCollect, Enrich, FilterRules for these steps are mainly in the context of the source model

Quality orientedValidate

Target orientedLink, ConvertRules for these steps are mainly in the context of the target model

Output results of the logical steps

Transformation step Output

COLLECT All candidates with their implicit attributes

ENRICH All candidates with the direct and indirect related attributes

FILTER All relevant candidates with the direct and indirect related attributes

LINK All relevant candidates with the direct and indirect related attributes, plus the foreign key attributes of the target model

VALIDATE All relevant and validated candidates with the direct and indirect relatedattributes, plus the foreign key attributes of the target model

CONVERT All relevant and validated candidatesplus all needed attributes of the target model

Agenda

The problem area

Conclusions

Target model: star scheme of phonecall factwith 3 dimensions

PHONECALL

CATEGORY_KEYCALENDAR_KEYCUSTOMER_KEYCALL_DURATIONCALL_CHARGE

DIM_CUSTOMER

DIM_CALENDAR

CALENDAR_KEYDATEMONTHYEAR

DIM_CATEGORY

CATEGORY_KEYCATEGORY_CODECATEGORY_OMSCHRIJVING

Source model: customer care system, 2 external sources

CUSTOMER CARE SYSTEEM

DEMOGRAFIEBEDRIJVENREGISTER

CUSTOMERCUSTOMER_NRADDRESS_NRNAMEPRIVATE_BUSINESS_FLAGIDENTIFICATIONSHIPTO_ADDRESS_NRTIMESTAMP ADDRESS

ADDRESS_NRVALID_FROMSTREETHOUSE_NRHOUSE_NR_EXTPOSTCODECITYTIMESTAMP

BEDRIJVENREGISTERKVKNUMMERNAAMBRANCHE_CODEOMVANG

DEMOGRAFIEPOSTCODEDEMOGRAFIE_TYPE

SUBSCRIPTIONSUBSCRIPTION_IDCUSTOMER_NRITEM_NRVALID_FROMVALID_UNTILTIMESTAMP

CDRSTARTDATETIMEDURATIONMOBILE_NUMBERCALLED_NUMBERCHARGE

MOBILE_ NUMBER_ASSIGNMENTMOBILE_NUMBERVALID_FROMVALID_UNTILSUBSCRIPTION_ID

MOBILE_NUMBERMOBILE_NUMBERSTATUS

Collect: Retrieve candidates

Collect Enhance Filter Validate Convert

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enhancementrules

Historyrules

HistoryLink

Linkrules

Collect: Retrieve candidates

Retrieve data from the Trigger table

The Trigger table is the table that, in essence, contains the candidates to beprocessed

It is called the Trigger table, because the records trigger the process

Collect: Retrieve candidatesVisualized as tables with rows (candidates) and columns (attributes)

Customer

CN AN Nm PB Id ST TS

ChCnMnDrSd

Output = All candidates with their direct attributes

Sourcemodel

Triggerentiteiten

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enrichmentrules

Historyrules

HistoryLink

Linkrules

Enrich : add other source attributes

Trigger candidates may not yet possess all necessary attributes

Data is combined from different sources

Data is denormalized to populate a dimension

Enrich : add other source attributes Extra columns are added to the table

Customer Address BedrGeg Demogr

BC Ov DTSt Hn Hx Pc CtR#

InCnId

Subscription

MobileNr

ChCnMnDrSd

Output = All candidates with their direct and indirect related attributes

Filter: separate relevant/non-relevant candidates

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enrichmentrules

Historyrules

HistoryLink

Linkrules

Filter: separate relevant/non-relevant candidates

For various reasons, candidates can be irrelevantOnly changed records need to be processedSpecific categories are not interesting for reporting

CDR MobileNr Subscription

St Id Cn InR#

Sd Dr Mn Cn Ch

Output = All relevant candidates with their direct and indirect related attributes

Filter: separate relevant/non-relevant candidates, based on attribute values

Link: resolve target relations

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enrichmentrules

Historyrules

HistoryLink

Linkrules

Determine foreign key values to referenced tables

Describe the rules that must be used to determine these values

In star scheme models, this step mainly applies for fact tables

CDR MobileNr Subscription Gespreksfeit

Id Cn In Klant-key Cat-key Kal-KeyStR#

Sd Dr Mn Cn Ch

Output = All relevant candidates with their direct and indirect related attributes,plus the foreign key attributes of the target model

Validate: check candidate quality

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enrichmentrules

Historyrules

HistoryLink

Linkrules

Candidates need to fulfil certain quality requirements, likeDomain compliance of attributesLinks must be resolvedEnrichment must succeed

If a validation rule fails, appropriate actions must be taken, likeApply a default valueReject the candidate, or perhaps reject the whole collection of candidatesMove the candidate to a suspend table for later reprocessing

Id Cn In Klant-key Cat-key Kal-KeyStR#

Sd Dr Mn Cn Ch

Output = All relevant and validated candidates with their direct and indirect related attributes, plus the foreign key attributes of the target model

Reprocess: handle invalidated candidates

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enhancementrules

Historyrules

HistoryLink

Linkrules

Convert: derive target attributes

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enhancementrules

Historyrules

HistoryLink

Linkrules

All target attributes are derived from the available candidate attributes

Customer Address BedrGeg Demogr KLANT

DT Nr Nm Tp Id KvK Pc Dg BrBC OvSt Hn Hx Pc CtR#

Id Cn In Klant-key Cat-key Kal-Key Duur OmzetStR#

Sd Dr Mn Cn Ch

Output: All relevant and validated candidatesplus all needed attributes of the target model

History: handle changes in time

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enhancementrules

Historyrules

HistoryLink

Linkrules

History: handle changes in time

This step comprisesSlowly Changing Dimension rulesAggregation rules

Transformation rules Customer Dimension (1)

Nr Step Rule1 Source

modelCUSTOMER

2 Sourcemodel

ADDRESS

3 Sourcemodel

Bedrijvenregister

4 Sourcemodel

Demografie

5 Collect CUSTOMER

These rules can be entered and presented in an easy-to-use table

Nr Step Rule6

Enrich CUSTOMER – BEDRIJVENREGISTER:

CUSTOMER.IDENTIFICATION = BEDRIJVENREGISTER.KVKNUMMER

CUSTOMER.PRIVATE_BUSINESS_FLAG = ‘Z’Enrich CUSTOMER – ADDRESS:

CUSTOMER.ADDRESS_NR = ADDRESS.ADDRESS_NR

CUSTOMER.TIMESTAMP >= ADDRESS.VALID_FROM

kies MAX(ADDRESS.VALID_FROM)

Nr Step Rule

8 Enrich ADDRESS – DEMOGRAFIE:

ADDRESS.POSTCODE = DEMOGRAFIE.POSTCODE9 Filter CUSTOMER.TIMESTAMP within [VerwerkingsPeriode]

10 Filter CUSTOMER.CUSTOMER_NR >= 1000

Validate CUSTOMER.PRIVATE_BUSINESS_FLAG = ‘Z’ or ‘P’

Convert KLANT_NUMMER = CUSTOMER.CUSTOMER_NR

Convert KLANT_NAAM = CUSTOMER.NAME

Transformation rules KLANT (4)

Nr Step Rule

17 Convert KLANT_POSTCODE = ADDRESS.POSTCODE

18 Convert KLANT_DEMOGRAFIE = DEMOGRAFIE.DEMOGRAFIE_TYPE

19 Convert KLANT_BRANCHE = BEDRIJVENREGISTER.BRANCHE_CODE

Convert KLANT_TYPE = CUSTOMER.PRIVATE_BUSINESS_FLAG

Convert KLANT_IDENTIFICATIE = CUSTOMER.IDENTIFICATION

Convert KLANT_KVKNUMMER = BEDRIJVENREGISTER.KVKNUMMER

Agenda

The problem area

Conclusions

From logical to technical transformationmodel

In fact, this is also a kind of mapping

Now we make use of development expertise, tool expertise, standards and techniques

Here we also must apply the design rules according to the quality model:Usage• Complete, Accurate, Consistent, Efficient, AccessibleMaintenance• Structured, Understandable, Flexible, Testable, ChangeableTransition• Portable, Reusable, Integrateable

Logical to technical: CUSTOMER

Collect Enhance Filter Validate Convertteren

Sourcemodel

Triggerentity

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enhancementrules

Historyrules

HistoryLink

Linkrules

AddrKlant

RDR1LKP1 LKP2

WRT1RTR1

5 6 7 8 9 10 11 12-19

1 2 3 4

Below is an example of a technical mapping in a specific tool, graphically presented

Each component of this mapping relates to a logical design step

‘Mapping’ table between Logical and Technical model

Step RDR1 FLT1 RTR1 LKP1 LKP2 EXP1

Validate 11

Convert 12

Source model

Collect

Enhance

Filter

Helps in checking completeness

Mapping-table Logical-Technical

Often this seems a trivial table

This is especially the case when there is a 1-1 relation between a logical functionand a technical ETL-program

However, there are many 1-1 situations that are not that trivial

Also, in case of a n-1 or 1-n or m-n relation, this table proves very valuable

For a specic ETL-tool (SAS, Powercenter, etc.) standards can be defined as whatsort of transformation is used for what sort of functional rule.

Agenda

The problem area

Conclusions

Can this ETL design and development process be automated?

Can we use a CASE-tool here?

Do such CASE-tools exist in the market?

What role does CWM play in this area?

Target modelSource model

Mapping as supported by ER-tools

Table 1

Table 2

Table 1

Table 2

Table 3

Reference to source model

Reference to source table

ƒ( )Reference to column

Plus formula

CWM – Common Warehouse Metadata

Website scanSAS, Informatica, Oracle, Microsoft, Business Objects

ImpressionSupport for CWM is growingETL tools can export mapping to CWM, in order to support for lineage or impact analysisCWM can not be used to design an ETL program

Agenda

The problem area

Conclusions

Driving the test processLogicalDesign

DefineTest cases

Input OutputProcess

Testcase X Prediction Y

RunTest

InputRecords

OutputRecords

ETLprogram

Testing is an essential step in software development

From the logical design, testcases are defined

Testcases are used to create testdata

Testdata is used as input for the ETL program to be tested

How to do adequate and efficient testing

The logical ETL design is both input for the software developer as for the tester

So, the design must be highly usable for both disciplines

To do testing efficiently, one needs to define testdata sets as small as possible, yetit must contain all logical testcases

Using production or production like data is not an option, because it often has a toolarge volume, and the predicting the outcome on an adequate detailed level is hard if not impossible

Example : square root function

The test approach is based on the theory of graphs

The graph depicts all decision points and possible paths a function can take fromstart to stop

Every route (= chain of paths) is a potential test case

Error message Calculate square root

Creating test graph from logical ETL model

The logical ETL model is very useful when specifying test cases and creating a test graph

Especially, the steps ‘Selection’ and ‘Validation’ are very important. They map one-to-one to decision points in the graph

History-handling adds its own decision pointsNew record vs. changed recordType 1 or type 2 changes

Graph of ETL programstart

8 - Validate

5-Select

6-Select

7-Validate

ok failure

<1000 >=1000

before

between

P Z not in {P,Z}

History

unchanged

changed

Determining test cases

Search in the transformation rules for the decision points;

For each decision point, specify the possible options;

Create a graph with the decision points and options;

Specify the test cases by combination/sequence of options;

Create test data for each test case

Agenda

The problem area

Conclusions

Conclusions (1)

The problem areaDesign and development cost of ETL-programs represents a big slice of the Data warehousing Cost pieA structured high-quality design approach will yield benefits

Presentation of the proposed solutionA logical transformation model, independent of technical implementation issuesStrongly linked to the source and target data model

The solution explained by examplesApplicable for all kinds of data warehouse data modelsApplicable for transactional and dimensional entity types

From logical to technicalPer tool conversion standards will enable efficiency

Conclusions (2)

Case tools and code generationProgress is mainly found in ER-modelling toolsCWM useful for reporting lineage and impact, not for ETL code generation

Driving the test processModel driven logical design is a solid base for preparing test casesBased on the theory of graphsThus the design meets the quality criterium ‘testablility’

OVERALL CONCLUSIONA high-quality design approachBoosting efficiencyProven in a number of ETL projects

Atos, Atos and fish symbol, Atos Origin and fish symbol, Atos Consulting, and the fish symbol itself are registered trademarks of Atos Origin SA.© 2006 Atos Origin. Private for the client. This report or any part of it, may not be copied, circulated, quoted without prior written approval from Atos Origin or the client.

Thank you!mark.zwijsen@atosorigin.com

model driven etl-design - driving the test process ¾conclusions. 4 - model driven etl design...

Documents

etl european etl spain 2002 s.l. - etl hungary kft

solution-driven integrated learning paths -...

moving from on-premises etl to cloud-driven elt

louisiana dotd data warehouse dotd data warehous… · data...

socially driven solution -...

build a metadata-driven etl platform by extending microsoft...

getting started with pentaho data integration - bmc...

customer focused. solution driven

big data driven business solution

a data driven etl test framework sqlsat madison

ontology-driven conceptual design of etl processes...

hadoop for etl professionals & etl developers

transformation manager is an integrated ... - etl...

reforming schools through solution-driven advocacy

usability comparison white paper - doblerconsulting.com ·...

adapting your etl solution to use ssis 2012 presentation by...

h3c application-driven wan solution

etl process etl: overview

huawei intent-driven ip wan solution

-training business intelligence - bi - etl - developer ·...