Informatica PowerCenter平台
ETL开发规范
Informatica PowerCenter平台 ETL开发规范 文档历史记录
变更历史
最后更新时间: 2015-07-21 版本 V1.0 更新日期 2015-07-20 作者 叶兆明 变更描述 创建全文 审 阅
姓名 角色 审阅章节 意见与签名 分 发
Name Role
Page: 2 of 39
Informatica PowerCenter平台 ETL开发规范 文 档 目 录
1. 文档介绍 ................................................................................................................................................... 5 1.1 1.2 1.3 1.4 1.5
文档目的 ........................................................................................................................................... 5 适用范围 ........................................................................................................................................... 5 文档读者 ........................................................................................................................................... 5 参考文献 ........................................................................................................................................... 5 术语与缩写解释 ............................................................................................................................... 5
2. POWERCENTER平台介绍 ................................................................................................................... 6 3. 环境介绍 ................................................................................................................................................... 8 3.1 3.2 3.3 3.4 3.5 3.6 3.7
概述 ................................................................................................................................................... 8 开发环境 ........................................................................................................................................... 8 测试环境 ........................................................................................................................................... 8 生产环境 ........................................................................................................................................... 8 数据库账号 ....................................................................................................................................... 8 数据连接 ........................................................................................................................................... 9 参数文件 ........................................................................................................................................... 9
4. 通用规范 ................................................................................................................................................. 16 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9 4.10 4.11
INFORMATICA文件夹配置 ............................................................................................................... 16 操作系统文件夹配置 ..................................................................................................................... 16 SESSION配置项 ................................................................................................................................ 17 INFORMATICA用户配置 ................................................................................................................... 17 ETL转换规则说明 .......................................................................................................................... 17 从平面文件导入数据 ..................................................................................................................... 17 从数据库导入数据 ......................................................................................................................... 18 从数据库导入数据结构 ................................................................................................................. 18 布局规范 ......................................................................................................................................... 18 注释规范 ......................................................................................................................................... 18 日志文件 ......................................................................................................................................... 20
5. 命名规范 ................................................................................................................................................. 21 5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 5.10 5.11 5.12 5.13 5.14 5.15 5.16 5.17 5.18
概述 ................................................................................................................................................. 21 FOLDER ............................................................................................................................................ 21 MAPPING .......................................................................................................................................... 22 MAPPLET .......................................................................................................................................... 22 SESSION ............................................................................................................................................ 22 WORKLET ......................................................................................................................................... 23 WORKFLOW ..................................................................................................................................... 23 SOURCE DEFINITION ........................................................................................................................ 23 TARGET DEFINITION ........................................................................................................................ 24 AGGREGATOR .................................................................................................................................. 24 EXPRESSION ..................................................................................................................................... 24 FILTER .............................................................................................................................................. 25 JOINER .............................................................................................................................................. 25 LOOKUP ............................................................................................................................................ 25 NORMALIZER ................................................................................................................................... 26 RANK ............................................................................................................................................... 26 ROUTER ............................................................................................................................................ 26 SEQUENCE GENERATOR ................................................................................................................... 26
Page: 3 of 39
Informatica PowerCenter平台 ETL开发规范 5.19 SORTER ............................................................................................................................................ 26 5.20 SOURCE QUALIFIER .......................................................................................................................... 26 5.21 STORED PROCEDURE ........................................................................................................................ 27 5.22 UPDATE STRATEGY .......................................................................................................................... 27 5.23 MAPPLET .......................................................................................................................................... 28 5.24 INPUT TRANFORMATION ( MAPPLET ONLY) .................................................................................... 28 5.25 OUTPUT TRANFORMATION ( MAPPLET ONLY) ................................................................................. 28 5.26 DATABASE CONNECTIONS ............................................................................................................... 28 5.27 FTP CONNECTIONS .......................................................................................................................... 29 6. 常用SQL知识介绍 ................................................................................................................................. 29 6.1 6.2 6.3 6.4
NOT IN与MINUS ........................................................................................................................... 29 多表连接中的条件限制（WHERE AND ON） ........................................................................... 29 游标尽量少使用 ............................................................................................................................. 31 异常捕捉 ......................................................................................................................................... 31
7. SQL编写规范 ......................................................................................................................................... 31 7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8
原则定义 ......................................................................................................................................... 31 大小写规则 ..................................................................................................................................... 32 缩进与换行 ..................................................................................................................................... 32 别名 ................................................................................................................................................. 33 运算符前后间隔要求 ..................................................................................................................... 33 变量引用 ......................................................................................................................................... 34 注释 ................................................................................................................................................. 34 其它 ................................................................................................................................................. 35
8. 脚本编写规范 ......................................................................................................................................... 35 8.1 8.2
ULTRAEDIT ...................................................................................................................................... 35 SHELL ............................................................................................................................................. 35
9. ETL开发流程 ......................................................................................................................................... 35 9.1 9.2 10. 11. 12.
ETL开发流程 .................................................................................................................................. 35 文档编写要求 ................................................................................................................................. 36 版本控制 ............................................................................................................................................. 36 ETL程序迁移 ..................................................................................................................................... 37 开发建议 ............................................................................................................................................. 38 SOURCE QUALIFIER .......................................................................................................................... 38 MAPPING .......................................................................................................................................... 38 MAPPLET .......................................................................................................................................... 38 EXPRESSION ..................................................................................................................................... 38 LOOKUP ............................................................................................................................................ 38 UPDATE STRATEGY .......................................................................................................................... 39
12.1 12.2 12.3 12.4 12.5 12.6
Page: 4 of 39
Informatica PowerCenter平台 ETL开发规范 1. 文档介绍
1.1 文档目的
本文档主要描述了ETL开发人员在Informatica PowerCenter平台开发Mapping、Session和Workflow需要遵循的统一标准，使得不同项目不同开发人员在Informatica PowerCenter平台交付的ETL程序符合同一标准。
1.2 适用范围
本规范适用于所有使用Informatica PowerCenter工具开发的项目。
1.3 文档读者
本规范面向ETL设计人员、ETL开发人员。
1.4 参考文献
1. 《Informatica PowerCenter官方命名规范》 2. 《Informatica_Standards.doc》
1.5 术语与缩写解释
无。
Page: 5 of 39