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

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

Usage

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

R#

1

2

3

4

5

CN AN Nm PB Id ST TS

5

4

3

2

1

R#

CDR

ChCnMnDrSd

Output = All candidates with their direct attributes



Sourcemodel

Triggerentiteiten

Filterrules

Validationrules

Conversionrules

Targetmodel

Reprocess

Enrichmentrules

Historyrules

HistoryLink

Linkrules

Enrich : add other source attributes


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#

1

2

3

4

5


InCnId

Subscription

St

MobileNr

5

4

3

2

1

R#

CDR

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




1

2

3

4

5


CDR MobileNr Subscription

St Id Cn InR#

1

2

3

4

5

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





1

2

3

4

5


CDR MobileNr Subscription Gespreksfeit

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

1

2

3

4

5

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





1

2

3

4

5



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

1

2

3

4

5

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#

1

2

3

4

5



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

1

2

3

4

5

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

7

Enrich CUSTOMER – BEDRIJVENREGISTER:

CUSTOMER.IDENTIFICATION = BEDRIJVENREGISTER.KVKNUMMER

-en-

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

CUSTOMER.ADDRESS_NR = ADDRESS.ADDRESS_NR

-en-

CUSTOMER.TIMESTAMP >= ADDRESS.VALID_FROM

-en-

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

11

12

13

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

14

15

16

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

Cust

BDR

AddrKlant

err

RDR1LKP1 LKP2

EXP1

WRT1RTR1

5 6 7 8 9 10 11 12-19

Demo

FLT1

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

13

...

19

1

2

10

3

4

5

6

7

8

9

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

Model

Table 1

col1

col2

col3

Table 2

col1

col2

Model

Table 1

col1

col2

Table 2

col1

col2

Table 3

ƒ( )

ƒ( )

Reference to source model

Reference to source table

ƒ( )

col1

ƒ( )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

start

stop

X?>=0

<0

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

stop

8 - Validate

5-Select

6-Select

7-Validate

ok failure

<1000 >=1000

before

between

P Z not in {P,Z}

History

new

unchanged

changed

after


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 [email protected]

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

Documents