49901446 Etl Design Template
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View 49901446 Etl Design Template as PDF for free.
More details
- Words: 1,848
- Pages: 14
Loading documents preview...
Cisco Systems, Inc.
<Measure/Subject Area/Project Name/Application Name> ETL Specification Document Author:
Creation Date: Last Updated: Version:
1. Document Control 1.1. Revision History Date
Version Number
Author
Details Of Change
1.2. Document Reviewers Date
Reviewer’s Name
Reviewer’s Job Title
1.3. Document Approvers Date
Approver’s Name
Cisco Systems Inc. Confidential ETL Specification Document
Approver’s Job Title
3/2/2011 Page 2 of 14
Table of Contents
1. Document Control..................................................................................................................................2 1.1. Revision History..........................................................................................................................2 1.2. Document Reviewers..................................................................................................................2 1.3. Document Approvers..................................................................................................................2 2. High level overview................................................................................................................................4 2.1. Purpose / Overview.....................................................................................................................4 2.2. ETL / Technical Architecture.......................................................................................................4 3. Specification Details..............................................................................................................................5 3.1. Table / View Structures..............................................................................................................5 Table Name: Table 1...........................................................................................................5 Table Name: Table 2...........................................................................................................5 Source Table Name: Table 1...............................................................................................6 Source Table Name: Table 2...............................................................................................6 Target Table Name: Table 3................................................................................................6 Target Table Name: Table 4................................................................................................7 3.2. Program List................................................................................................................................7........................................................8 Subprogram 1 ......................................................8 Subprogram 2 ......................................................9 Source Qualifier Transformation 1 .......................................9 Transformation 1 ............10 Workflow 1 .......................................................................10 Session 1 ...........................................................................10 Session 2 ...........................................................................10
3.3. Detailed Column Mapping Specification....................................................................................11 3.4. Job Configuration and Scheduling Details................................................................................13 Job Name 1........................................................................................................................13 Job Name 2........................................................................................................................13 4. Appendix..............................................................................................................................................14 4.1. Livelink URL..............................................................................................................................14
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 3 of 14
2. High level overview
2.1. Purpose / Overview The purpose of this document is to record and communicate the ETL design for the <Measure/Subject Area/Project/Application Name> to be consumed by the IT and Business users. It explains the various components of the ETL design. The document includes the data flows as well as the outlines of the standards and guidelines that are followed during the development process.
2.2. ETL / Technical Architecture
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 4 of 14
3. Specification Details 3.1. Table / View Structures < Provide the list all the tables/views that are used in this measure/subject area/project.> Schema:
Database:
Table Name: Table 1
Last Revise Date:
Descript ion
Sizing
Current
Layer: By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Layer:
Schema:
Database:
Table Name: Table 2
Last Revise Date:
Descript ion
Sizing
Current
By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Cisco Systems Inc. Confidential ETL Specification Document
Key Type (PK/FK/U K)
Format
Null
Description
3/2/2011 Page 5 of 14
< If identifying the table usage as Source, Target, Staging adds clarity to the ETL design, feel free to indicate that in the Table Name field as Source, Target, Staging, etc. However, if the table is used as a combination of table usage types, then using the more generic table specification template above would be preferred, and then clarify the table usage in the Program sections below..>
Schema:
Database:
Source Table Name: Table 1
Last Revise Date:
Descript ion
Sizing
Current
Layer: By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Layer:
Schema:
Database:
Source Table Name: Table 2
Last Revise Date:
Descript ion
Sizing
Current
By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Schema:
Database:
Target Table Name: Table 3
Last Revise Date:
Descript
Sizing
Cisco Systems Inc. Confidential ETL Specification Document
Current
Layer: By: 3 mo
6 mo
1 yr 3/2/2011 Page 6 of 14
ion
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Schema:
Database:
Target Table Name: Table 4
Last Revise Date:
Descript ion
Sizing
Current
Layer:
By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
3.2. Program List This section details the list of ETL programs for this measure/subject area/project.
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 7 of 14
Technology Used Program
Description Program Level Filters Extraction Strategy Load Strategy
Error Strategy
< Indicate the ETL technology used to develop the program. Examples of ETL technologies may include Informatica, OWB, PL/SQL, etc. >
Error handling within Extraction Strategy Error handling within Load Strategy
Sub Program Description Sources
Subprogram 1 <Source Table 1 Name> <Source Table 2 Name> <Source Table 3 Name>
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 8 of 14
Targets Sub Program Level Filters Table Join Condition
DFD Reference BRD Reference Sub Program Description Sources Targets Sub Program Level Filters Table Join Condition
Subprogram 2 <Source Table 1 Name> <Source Table 2 Name> <Source Table 3 Name>
DFD Reference BRD Reference < Templates for documenting ETL designs to be implemented within Informatica. Only Source Qualifier and generic templates are provided for examples. Feel free to create new templates for different types of Transformations.> Transformation Table / Synonym / View
Source Qualifier Transformation 1
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 9 of 14
Filter(s)/Condition(s) SQL Override Transformation Field 1 Field 2
Transformation 1
< This section is specific to Informatica and can be used to document ETL design implemented within Workflows and Sessions which uses the Mapping (Program) of this chapter. These sections will typically be used when a generic mapping has been defined which can be used by multiple Sessions along with special SQL filters or conditions defined in each session. > WorkFlow Description Session
Workflow 1 < Provide a textual description about what this workflow does. Include the workflow diagram which consist of various tasks that are run concurrently or sequentially > Session 1 Table Name
Targets Session Level Filters Session
Session Level Filters
Update as Update
Update as Insert
Update else insert
Delete
Truncate Table
Update as Update
Update as Insert
Update else insert
Delete
Truncate Table
Session 2 Table Name
Targets
Insert
Insert
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 10 of 14
3.3. Detailed Column Mapping Specification
Microsoft Excel Worksheet
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 11 of 14
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 12 of 14
3.4. Job Configuration and Scheduling Details
Job Name
Job Name 1
Existing Job?
Dollar Universe Job Specifications Job Change Type
Session Name
Job Name
UPROC Name
Dependencies
DW Jobs Specifications Prerequisites
Stream
ETL program
Job Name 2
Session Name
UPROC Name
Cisco Systems Inc. Confidential ETL Specification Document
Frequency
Day/Time
Existing Job?
Dollar Universe Job Specifications Job Change Type
CONC
Scheduling Specs
Dependencies
DW Jobs Specifications Prerequisites
CONC
Stream
ETL program
Frequency
3/2/2011 Page 13 of 14
Day/Time
4. Appendix
4.1. Livelink URL This latest version of this document can be found on LiveLink at: http://ework.cisco.com/Livelink/livelink.exe?func=ll&objId=2248909&objAction=Open
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 14 of 14
<Measure/Subject Area/Project Name/Application Name> ETL Specification Document Author:
Creation Date: Last Updated: Version:
1. Document Control 1.1. Revision History Date
Version Number
Author
Details Of Change
1.2. Document Reviewers Date
Reviewer’s Name
Reviewer’s Job Title
1.3. Document Approvers Date
Approver’s Name
Cisco Systems Inc. Confidential ETL Specification Document
Approver’s Job Title
3/2/2011 Page 2 of 14
Table of Contents
1. Document Control..................................................................................................................................2 1.1. Revision History..........................................................................................................................2 1.2. Document Reviewers..................................................................................................................2 1.3. Document Approvers..................................................................................................................2 2. High level overview................................................................................................................................4 2.1. Purpose / Overview.....................................................................................................................4 2.2. ETL / Technical Architecture.......................................................................................................4 3. Specification Details..............................................................................................................................5 3.1. Table / View Structures..............................................................................................................5 Table Name: Table 1...........................................................................................................5 Table Name: Table 2...........................................................................................................5 Source Table Name: Table 1...............................................................................................6 Source Table Name: Table 2...............................................................................................6 Target Table Name: Table 3................................................................................................6 Target Table Name: Table 4................................................................................................7 3.2. Program List................................................................................................................................7
3.3. Detailed Column Mapping Specification....................................................................................11 3.4. Job Configuration and Scheduling Details................................................................................13 Job Name 1........................................................................................................................13 Job Name 2........................................................................................................................13 4. Appendix..............................................................................................................................................14 4.1. Livelink URL..............................................................................................................................14
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 3 of 14
2. High level overview
2.1. Purpose / Overview The purpose of this document is to record and communicate the ETL design for the <Measure/Subject Area/Project/Application Name> to be consumed by the IT and Business users. It explains the various components of the ETL design. The document includes the data flows as well as the outlines of the standards and guidelines that are followed during the development process.
2.2. ETL / Technical Architecture
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 4 of 14
3. Specification Details 3.1. Table / View Structures < Provide the list all the tables/views that are used in this measure/subject area/project.> Schema:
Database:
Table Name: Table 1
Last Revise Date:
Descript ion
Sizing
Current
Layer: By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Layer:
Schema:
Database:
Table Name: Table 2
Last Revise Date:
Descript ion
Sizing
Current
By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Cisco Systems Inc. Confidential ETL Specification Document
Key Type (PK/FK/U K)
Format
Null
Description
3/2/2011 Page 5 of 14
< If identifying the table usage as Source, Target, Staging adds clarity to the ETL design, feel free to indicate that in the Table Name field as Source, Target, Staging, etc. However, if the table is used as a combination of table usage types, then using the more generic table specification template above would be preferred, and then clarify the table usage in the Program sections below..>
Schema:
Database:
Source Table Name: Table 1
Last Revise Date:
Descript ion
Sizing
Current
Layer: By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Layer:
Schema:
Database:
Source Table Name: Table 2
Last Revise Date:
Descript ion
Sizing
Current
By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Schema:
Database:
Target Table Name: Table 3
Last Revise Date:
Descript
Sizing
Cisco Systems Inc. Confidential ETL Specification Document
Current
Layer: By: 3 mo
6 mo
1 yr 3/2/2011 Page 6 of 14
ion
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
Schema:
Database:
Target Table Name: Table 4
Last Revise Date:
Descript ion
Sizing
Current
Layer:
By: 3 mo
6 mo
1 yr
rows Mb
IN DW
Column / Field
Key Type (PK/FK/U K)
Format
Null
Description
3.2. Program List This section details the list of ETL programs for this measure/subject area/project.
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 7 of 14
Technology Used Program
Description Program Level Filters Extraction Strategy Load Strategy
Error Strategy
< Indicate the ETL technology used to develop the program. Examples of ETL technologies may include Informatica, OWB, PL/SQL, etc. >
Subprogram 1
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 8 of 14
Targets Sub Program Level Filters Table Join Condition
DFD Reference BRD Reference Sub Program Description Sources Targets Sub Program Level Filters Table Join Condition
Subprogram 2
DFD Reference BRD Reference < Templates for documenting ETL designs to be implemented within Informatica. Only Source Qualifier and generic templates are provided for examples. Feel free to create new templates for different types of Transformations.> Transformation Table / Synonym / View
Source Qualifier Transformation 1
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 9 of 14
Filter(s)/Condition(s) SQL Override Transformation Field 1 Field 2
< This section is specific to Informatica and can be used to document ETL design implemented within Workflows and Sessions which uses the Mapping (Program) of this chapter. These sections will typically be used when a generic mapping has been defined which can be used by multiple Sessions along with special SQL filters or conditions defined in each session. > WorkFlow Description Session
Workflow 1
Targets Session Level Filters Session
Session Level Filters
Update as Update
Update as Insert
Update else insert
Delete
Truncate Table
Update as Update
Update as Insert
Update else insert
Delete
Truncate Table
Targets
Insert
Insert
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 10 of 14
3.3. Detailed Column Mapping Specification
Microsoft Excel Worksheet
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 11 of 14
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 12 of 14
3.4. Job Configuration and Scheduling Details
Job Name
Job Name 1
Existing Job?
Dollar Universe Job Specifications Job Change Type
Session Name
Job Name
UPROC Name
Dependencies
DW Jobs Specifications Prerequisites
Stream
ETL program
Job Name 2
Session Name
UPROC Name
Cisco Systems Inc. Confidential ETL Specification Document
Frequency
Day/Time
Existing Job?
Dollar Universe Job Specifications Job Change Type
CONC
Scheduling Specs
Dependencies
DW Jobs Specifications Prerequisites
CONC
Stream
ETL program
Frequency
3/2/2011 Page 13 of 14
Day/Time
4. Appendix
4.1. Livelink URL This latest version of this document can be found on LiveLink at: http://ework.cisco.com/Livelink/livelink.exe?func=ll&objId=2248909&objAction=Open
Cisco Systems Inc. Confidential ETL Specification Document
3/2/2011 Page 14 of 14
Related Documents
49901446 Etl Design Template
January 2021 0
Etl Object Migration Request Template
March 2021 0
02- Etl Design Strategy
January 2021 0
Detailed Design Document Template
January 2021 1
Design Qualification Template
February 2021 1
Etl Architecture
January 2021 0More Documents from "api-3702057"